Announcement

Collapse
No announcement yet.

Any speedup suggestions welcome

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Any speedup suggestions welcome

    Our forums at http://forums.overclockers.co.uk are in need of some help I think.

    They're down today for some harsh pruning (30 days), but I'm more interested in mySQL configration - there's so much in it that I don't even begin to understand (and my host is useless) but I'm sure we could reap great benefits from optimising it a little.

    We have about 800,000 posts (atm), 1GB ram, Dual P3-600, SCSI 10k - dedicted server (freeBSD) which runs both apache/php and mysql. I loaded the medium cnf to get going and it seems okay but I awnt to make sure we're getting everything we can (every time I say anything to hour host I get told the prices for RAM and CPU upgrades - very helpful).

    I'm also wondering how to turn off mySQL's logging - we just don't need a log, I wouldn't know how to rollback even if we did have a problem and we have a daily backup of the DB anyway - I'm thinking saving on that disc access would be a bit of a boost?

    Also - with all the talk of slow deleting threads I've noted a lot of stuff about attachments - they're disabled on our forums, totally - I'm assuming they're still being checked for even though we don't use them? Any suggestions where I could 'tweak' to save on that wasted process?

    At busy periods TOP shows mysqld using about 70% of the CPU, with apache eating the rest - aout 100-200MB free (varies).

  • #2
    Extended-Status

    +--------------------------+------------+
    | Aborted_clients | 98 |
    | Aborted_connects | 0 |
    | Bytes_received | 2780260373 |
    | Bytes_sent | 3783267873 |
    | Connections | 677323 |
    | Created_tmp_disk_tables | 14 |
    | Created_tmp_tables | 534766 |
    | Created_tmp_files | 28 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Delayed_errors | 0 |
    | Flush_commands | 1 |
    | Handler_delete | 259413 |
    | Handler_read_first | 1094580 |
    | Handler_read_key | 145418839 |
    | Handler_read_next | 814965143 |
    | Handler_read_prev | 0 |
    | Handler_read_rnd | 142534009 |
    | Handler_read_rnd_next | 1207559483 |
    | Handler_update | 1533323 |
    | Handler_write | 377019948 |
    | Key_blocks_used | 249376 |
    | Key_read_requests | 648379793 |
    | Key_reads | 515370 |
    | Key_write_requests | 1387481 |
    | Key_writes | 1222706 |
    | Max_used_connections | 100 |
    | Not_flushed_key_blocks | 0 |
    | Not_flushed_delayed_rows | 0 |
    | Open_tables | 256 |
    | Open_files | 293 |
    | Open_streams | 0 |
    | Opened_tables | 43659 |
    | Questions | 16116102 |
    | Select_full_join | 14 |
    | Select_full_range_join | 0 |
    | Select_range | 2377051 |
    | Select_range_check | 0 |
    | Select_scan | 2625627 |
    | Slave_running | OFF |
    | Slave_open_temp_tables | 0 |
    | Slow_launch_threads | 0 |
    | Slow_queries | 3975 |
    | Sort_merge_passes | 14 |
    | Sort_range | 2086004 |
    | Sort_rows | 145106114 |
    | Sort_scan | 1856675 |
    | Table_locks_immediate | 17496050 |
    | Table_locks_waited | 942306 |
    | Threads_cached | 4 |
    | Threads_created | 52704 |
    | Threads_connected | 4 |
    | Threads_running | 1 |
    | Uptime | 470864 |
    +--------------------------+------------+

    Comment


    • #3
      Some useful variables

      | version 3.23.37log |
      | wait_timeout | 28800
      tmp_table_size | 1048576
      transaction_isolation | READ-COMMITTED
      thread_stack | 65536
      thread_cache_size | 8
      table_type | MYISAM
      table_cache | 256
      sort_buffer | 1048568
      query_buffer_size | 0
      record_buffer | 1044480
      open_files_limit | 0
      myisam_sort_buffer_size | 67108864
      myisam_max_sort_file_size | 2047
      myisam_max_extra_sort_file_size | 256
      max_write_lock_count | 4294967295
      max_tmp_tables | 32
      max_user_connections | 0
      max_sort_length | 1024
      max_join_size | 4294967295
      max_heap_table_size | 16777216
      max_delayed_threads | 20
      max_connect_errors | 10
      max_connections | 100
      max_binlog_size | 1073741824
      max_binlog_cache_size | 4294967295
      max_allowed_packet | 1047552
      log_bin | ON
      log_update | OFF
      log | OFF
      large_files_support | ON
      key_buffer_size | 268431360
      join_buffer_size | 131072
      interactive_timeout | 28800
      have_isam | YES
      have_innodb | NO
      have_gemini | NO
      have_bdb | NO
      flush | OFF
      delayed_queue_size | 1000
      delayed_insert_timeout | 300
      delayed_insert_limit | 100
      delay_key_write | ON
      connect_timeout | 5
      binlog_cache_size | 32768
      back_log | 50
      ansi_mode | OFF

      I hope someone can make sense of that lot - cause I'm way out of my depth here

      Comment


      • #4
        you using mysql 3.23.47 ???

        replace your my.cnf file with this one and restart mysql

        [client]
        port = 3306
        socket = /var/lib/mysql/mysql.sock

        [mysqld]
        port = 3306
        socket = /var/lib/mysql/mysql.sock
        skip-locking
        set-variable = max_connections=500
        set-variable = key_buffer=16M
        set-variable = myisam_sort_buffer_size=64M
        set-variable = join_buffer=2M
        set-variable = record_buffer=2M
        set-variable = sort_buffer=3M
        set-variable = table_cache=1024
        set-variable = thread_cache_size=256
        set-variable = wait_timeout=3600
        set-variable = connect_timeout=10

        [safe_mysqld]
        open-files-limit=8192

        [myisamchk]
        set-variable = key_buffer=384M
        set-variable = sort_buffer=384M
        set-variable = read_buffer=16M
        set-variable = write_buffer=16M
        other additional benefits would be

        1. upgrade to vB 2.2.2 - should fix mass prune problem
        2. latest PHP and MySQL versions
        3. add a 2nd scsi 10k hdd and move mysql data to that disk
        :: Always Back Up Forum Database + Attachments BEFORE upgrading !
        :: Nginx SPDY SSL - World Flags Demo [video results]
        :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

        Comment


        • #5
          Thanks for the cnf info eva - just what I needed

          We put vb2.2.2 on the other day, we pruning now to get rid of the slack that built up in 2.2.1. Although I did notice a funny thing immediatley after installing 2.2.2, it would take 15 seconds to open a thread and TOP would show we had 20% cpu idle and 200MB free - ftp+shell was fast and responsive also, funny that.

          Umm, we got a second 10k scsi, I'm not entirely sure what is on it though - backups I know are on it. How would I go about telling mySQL to look there for it's data ?

          I think I shall pester my host about versions - they don't like changing anything but I reckon an upgrade to latest freebsd may be in order too (on 4.0 atm, 4.4 is latest and is much faster on PIII apparently). They gonna hate me

          Checking mysql -version I got :
          mysql Ver 11.15 Distrib 3.23.37, for unknown-freebsdelf4.0 (i386)

          I presume mysql has better optimisation in later versions?

          Comment


          • #6
            yes mysql 3.23.47 or later would have more bug fixes etc

            also 2.2.2 thread 2nd post here http://www.vbulletin.com/forum/showt...?postid=237893 be sure to replace the forumdisplay.php with the new one

            if you're upgrading mysql you could compile it to place mysql data files in the 2nd disk partition with --localstatedir=/home2 option

            i.e. if 2nd disk is /home2

            compile with configure options

            ./configure --prefix=/usr/local/mysql --localstatedir=/home2 --with-unix-socket-path=/home2/mysql.sock --with-innodb --with-berkeley-db --without-debug --with-extra-charsets=none --enable-assembler

            assuming --prefix=/usr/local/mysql is where mysql client apps are you can find that out by locating a mysql client app like mysqladmin

            locate mysqladmin

            if it returns

            /usr/local/mysql/bin/mysqladmin

            then the path --prefix=/usr/local/mysql

            of course ask your host about this as well
            :: Always Back Up Forum Database + Attachments BEFORE upgrading !
            :: Nginx SPDY SSL - World Flags Demo [video results]
            :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

            Comment


            • #7
              Thanks, I just uplaoded all the files in the uplaod dir, as I allways do - so I presume everything was replaced.

              I'll bundle that info up and send it off to my host - I'm not even confident about restarting mysql never mind the rest of it

              Thanks for all your help.

              [update] Ah gotcha - found the updated forumdisplay now
              Last edited by telescopi; Sat 2nd Feb '02, 7:02am.

              Comment


              • #8
                forgot to mention if you compile new mysql to place data in new disk partitioned as /home2

                you'd need to while mysql was down copy the data files from where they were into the new location /home2

                but if your /home2 has backup files

                then you might want to specify mysql to placed in

                /home2/mysql instead so in that case you need to copy data files from current location to /home2/mysql and use this configure line to compile mysql new/latest version

                ./configure --prefix=/usr/local/mysql --localstatedir=/home2/mysql --with-unix-socket-path=/home2/mysql.sock --with-innodb --with-berkeley-db --without-debug --with-extra-charsets=none --enable-assembler
                :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                :: Nginx SPDY SSL - World Flags Demo [video results]
                :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                Comment


                • #9
                  Thanks for the tips eva - I've implemented the cnf change and host has scheduled an upgrade (at same time as upgrading to freebsd 4.4). Server seems to be responding faster than it was when under load

                  My boss has been asking about server upgrades, currently running dual P3 600's with 1GB RAM and 10k SCSI.

                  Our host is suggesting (heavily) RAM or CPU upgrades, what do you think? I'm sure we could use more RAM, and CPU is allways helpful - but is there a better way? Is it much better if running two servers (one powerful one for mysql and another, maybe RAQ4 for the apache/php) ? If we stick with one box how much RAM would you suggest would be 'enough' ?

                  Comment


                  • #10
                    How many users online at the same time do you ever have at peak?

                    Do you have a slow-queries log to see what those slow queries are coming from?

                    Also, try setting up another apache server stripped down without all the unnecessary modules to run all your images from on the server. Make a domain like images.mydomain.com and set vbulletin to pull the images from there instead of wasting all the resources from a full apache compile.

                    Marc
                    Marc
                    -------

                    Comment


                    • #11
                      Pretty steady 300 users mid-evening, once or twice a week we can get 350 on - but 300 is normal.

                      I'll pass that idea to server admins, I allways wondered why some people had 'image servers' - guess I know now

                      Comment


                      • #12
                        We also get 300-350 peak so we are in the same boat...I just moved to a dual 1Ghz with 2GB RAM.

                        Prior to this I was on 800Mhz w/ 1GB RAM.

                        Check to see what your post table looks like. Post it here:

                        Issue this at the mySQL command line or phpmyadmin:

                        DESCRIBE post;


                        Post what you have here...
                        Marc
                        -------

                        Comment


                        • #13
                          +---------------+----------------------+------+-----+---------+----------------+
                          | Field | Type | Null | Key | Default | Extra |
                          +---------------+----------------------+------+-----+---------+----------------+
                          | postid | int(10) unsigned | | PRI | NULL | auto_increment |
                          | threadid | int(10) unsigned | | MUL | 0 | |
                          | username | varchar(50) | | | | |
                          | userid | int(10) unsigned | | MUL | 0 | |
                          | title | varchar(100) | | | | |
                          | dateline | int(10) unsigned | | | 0 | |
                          | attachmentid | smallint(5) unsigned | | | 0 | |
                          | pagetext | mediumtext | YES | | NULL | |
                          | allowsmilie | smallint(6) | | | 0 | |
                          | showsignature | smallint(6) | | | 0 | |
                          | ipaddress | varchar(16) | | | | |
                          | iconid | smallint(5) unsigned | | MUL | 0 | |
                          | visible | smallint(6) | | | 0 | |
                          | edituserid | int(10) unsigned | | | 0 | |
                          | editdate | int(10) unsigned | | | 0 | |
                          +---------------+----------------------+------+-----+---------+----------------+

                          Comment


                          • #14
                            Do you edit your database? Allways thought vbulletin would throw a wobbly if I played

                            Comment


                            • #15
                              Not so much editing but tweaking little things...

                              Try adding an index to your post table on the dateline column. This may speed things up a bit...I tried it right before my hardware upgrade and it wiped out my slow queries coming from users checking for new posts.

                              Here is the command.

                              ALTER TABLE post ADD INDEX dateline;

                              after that when you rerun the "DESCRIBE post" query "MUL" should appear under the key column for dateline.

                              This should speed things up and eliminate what I'm guessing is the cause for many of your slow-queries. Howerver enabling the slow-query log will at least tell you what other queries may be causing a slowdown.

                              Add this to my.cnf for that log:

                              log-slow-queries=/var/log/mysql/slow-queries.log


                              make sure whatever path to the log directory is writable by mysql (in the above example /var/log/mysql is writable for mysql daemon.
                              Marc
                              -------

                              Comment

                              Loading...
                              Working...
                              X