MySQL Server Tweaking Basics

Discussion in 'Servers and Control Panels' started by PerfectSQL, Mar 19, 2005.

  1. PerfectSQL

    PerfectSQL Server Admin

    364
    115
    +1
    This is a basic guide to understanding what the directives in your my.cnf mean, and what they do. We'll also try to give some general advise to help you get the most out of these settings. We will not cover every directive, only those that can generally be changed to give better performance.

    Here is an exerpt of a my.cnf we run on a dual xeon with 2 GB's of ram, this is a shared hosting machine that runs MySQL and web, so all memory is not allocated to MySQL.

    Code:
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    skip-innodb
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    max_connections=900
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_cache_size=128
    #key_buffer=16M
    key_buffer=200M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=1536
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=1M
    max_connect_errors=10
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency=4
    myisam_sort_buffer_size=64M
    #log-bin
    server-id=1
    
    Query caching was added as of MySQL version 4, the following three directives will greatly enhance mysql server performance.

    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1

    Query caching is a server wide variable, so set these generous. I have found the above levels are generally best if you server has at least 512 ram. If you run a server just for DBs with a lot of ram, you can up these quite a bit, like 2m limit and a 64+M cache size.

    The key buffer is a variable that is shared amongst all MySQL clients on the server. A large setting is recomended, particularly helpful with tables that have unique keys. (Most do)

    key_buffer=150M

    The next set of buffers are at a per client level. It is important to play around with these and get them just right for your machine. With the setting below, every active mysql client will have close to 3 MB's in buffers. So 100 clients = almost 300 MB. Giving too much to these buffers will be worse than giving too little. Nothing kills a server quite like memory swapping will.

    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=768K

    The following directive should be set to 2X the number of processors in your machine for best performance.

    thread_concurrency=2

    Heres a few example configurations for servers running MySQL and web for common memory sizes. These are not perfect, but good starting points.

    Server with 512MB RAM:

    thread_cache_size=50
    key_buffer=40M
    table_cache=384
    sort_buffer_size=768K
    read_buffer_size=512K
    read_rnd_buffer_size=512K
    thread_concurrency=2

    For servers with 1 GB ram:

    thread_cache_size=80
    key_buffer=150M
    table_cache=512
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=768K
    thread_concurrency=2

    We hope you have found this TAZ Tutorial helpfull :)
     
    • Like Like x 1
  2. DChapman

    DChapman Devotee

    2,878
    730
    +13
    Excellent post, thank you. What exactly is thread concurrency? And if I have a dual Xeon with HT, should I set it to 4 or 8?
     
  3. AWS

    AWS Long Time Gone

    2,898
    742
    +305
    Thread concurrency tells mysql how many threads to open at the same time. It is recommended you double the number processors. This will only work on systems that support thread_concurrency.
     
  4. PerfectSQL

    PerfectSQL Server Admin

    364
    115
    +1
    For a dual xeon with a ht I would set it to 4, as it only has two real processors.
     
  5. Brook

    Brook Devotee

    2,330
    730
    +19
    Hi PefectSQL

    I've just changed my my.cnf to the following taking into consideration your recommendations above.

    I noticed however that my cnf file did not contain:

    read_rnd_buffer_size
    or
    thread_concurrency=2

    so I just added them... si that ok?

    I will also paste a 'free' reading here when there's 40 or so members online.

    What do you suggest now? install eAcc? Thanks for your help.

    [mysqld]
    skip-innodb
    max_connections = 500
    key_buffer = 16M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 768K
    read_buffer_size = 512K
    sort_buffer_size = 768K
    read_rnd_buffer_size = 512K
    thread_concurrency = 2
    table_cache = 384
    thread_cache_size = 50
    wait_timeout = 7200
    connect_timeout = 10
    tmp_table_size = 32M
    max_allowed_packet = 160M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 16M
    query_cache_type = 1

    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M




    [For those who haven't seen our previous posts, my server spec is currently: Pentium 3 1Ghz with 256MB RAM]
     
    Last edited: Mar 23, 2005
  6. Brook

    Brook Devotee

    2,330
    730
    +19
    Heres a 'free' reading with 31 users online:


    Code:
                 total       used       free     shared    buffers     cached
    Mem:        252944     241104      11840          0      11856      85448
    -/+ buffers/cache:     143800     109144
    Swap:       522104          0     522104
    
    
     
  7. tekkitan

    tekkitan Forum Junkie

    636
    283
    +0
    how do i check to make sure my mysql server is using these settings? my my.conf was blank so i dont think it's ever used a config file before. what command do i use to start mysql?
     
  8. Brook

    Brook Devotee

    2,330
    730
    +19
    ok I've just reverted back to the original file because people were complaining it had gone slow:

    [mysqld]
    skip-innodb
    max_connections = 500
    key_buffer = 16M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 1024
    thread_cache_size = 64
    wait_timeout = 7200
    connect_timeout = 10
    tmp_table_size = 32M
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 16M
    query_cache_type = 1

    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M

    I'll wait for specific instructions from yourself before making any further changes. :bonk:
     
  9. AWS

    AWS Long Time Gone

    2,898
    742
    +305
    You need to run this:
    mysqladmin -u root -p extended-status
    To calculate the value of key_buffer, the most important variable, you have to divide key_reads by key_read_requests and key_writes by key_writes_request. You want to have it factor out to less than 0.01 for reads and 0.1 for writes. A general rule of thumb is key_buffer should be no more than 1/4 the amount of memory on the server, however you don't want to set it that high if you don't need to. Some mysql people will tell you key_buffer should be the total size of all .myi files. I rather use the calculations.
    There are other things we need to see from extended-status to tweak out the other variables.
    Also post how much memory you have on the server.
    Just looking at your my.cnf I'd say your thread_cache_size is too large, table_cache is too large, key_buffer is too low, max_connections is too high and wait_timeout is too high.
    Post those stats and we'll tweak you up.
     
  10. Brook

    Brook Devotee

    2,330
    730
    +19
    Thanks for replying AWS.

    I have 256meg on the server but am thinking about upgrading it.

    Here's that info...

    +--------------------------+----------+
    | Variable_name | Value |
    +--------------------------+----------+
    | Aborted_clients | 0 |
    | Aborted_connects | 1 |
    | Bytes_received | 92853 |
    | Bytes_sent | 3883764 |
    | Com_admin_commands | 0 |
    | Com_alter_table | 0 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 0 |
    | Com_change_db | 27 |
    | Com_change_master | 0 |
    | Com_check | 0 |
    | Com_commit | 0 |
    | Com_create_db | 0 |
    | Com_create_function | 0 |
    | Com_create_index | 0 |
    | Com_create_table | 0 |
    | Com_delete | 3 |
    | Com_delete_multi | 0 |
    | Com_drop_db | 0 |
    | Com_drop_function | 0 |
    | Com_drop_index | 0 |
    | Com_drop_table | 0 |
    | Com_flush | 0 |
    | Com_grant | 0 |
    | Com_ha_close | 0 |
    | Com_ha_open | 0 |
    | Com_ha_read | 0 |
    | Com_insert | 20 |
    | Com_insert_select | 0 |
    | Com_kill | 0 |
    | Com_load | 0 |
    | Com_load_master_data | 0 |
    | Com_load_master_table | 0 |
    | Com_lock_tables | 0 |
    | Com_optimize | 0 |
    | Com_purge | 0 |
    | Com_rename_table | 0 |
    | Com_repair | 0 |
    | Com_replace | 2 |
    | Com_replace_select | 0 |
    | Com_reset | 0 |
    | Com_restore_table | 0 |
    | Com_revoke | 0 |
    | Com_rollback | 0 |
    | Com_savepoint | 0 |
    | Com_select | 148 |
    | Com_set_option | 0 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 0 |
    | Com_show_create | 0 |
    | Com_show_databases | 0 |
    | Com_show_fields | 0 |
    | Com_show_grants | 0 |
    | Com_show_keys | 0 |
    | Com_show_logs | 0 |
    | Com_show_master_status | 0 |
    | Com_show_new_master | 0 |
    | Com_show_open_tables | 0 |
    | Com_show_processlist | 0 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 1 |
    | Com_show_innodb_status | 0 |
    | Com_show_tables | 0 |
    | Com_show_variables | 1 |
    | Com_slave_start | 0 |
    | Com_slave_stop | 0 |
    | Com_truncate | 0 |
    | Com_unlock_tables | 0 |
    | Com_update | 42 |
    | Connections | 31 |
    | Created_tmp_disk_tables | 2 |
    | Created_tmp_tables | 8 |
    | Created_tmp_files | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Delayed_errors | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 1 |
    | Handler_read_first | 50 |
    | Handler_read_key | 4425 |
    | Handler_read_next | 5966 |
    | Handler_read_prev | 60 |
    | Handler_read_rnd | 2270 |
    | Handler_read_rnd_next | 26588 |
    | Handler_rollback | 0 |
    | Handler_update | 34 |
    | Handler_write | 605 |
    | Key_blocks_used | 279 |
    | Key_read_requests | 5983 |
    | Key_reads | 279 |
    | Key_write_requests | 39 |
    | Key_writes | 37 |
    | Max_used_connections | 1 |
    | Not_flushed_key_blocks | 0 |
    | Not_flushed_delayed_rows | 0 |
    | Open_tables | 65 |
    | Open_files | 124 |
    | Open_streams | 0 |
    | Opened_tables | 71 |
    | Questions | 397 |
    | Qcache_queries_in_cache | 77 |
    | Qcache_inserts | 148 |
    | Qcache_hits | 125 |
    | Qcache_lowmem_prunes | 0 |
    | Qcache_not_cached | 0 |
    | Qcache_free_memory | 15767088 |
    | Qcache_free_blocks | 11 |
    | Qcache_total_blocks | 214 |
    | Rpl_status | NULL |
    | Select_full_join | 2 |
    | Select_full_range_join | 0 |
    | Select_range | 30 |
    | Select_range_check | 0 |
    | Select_scan | 37 |
    | Slave_open_temp_tables | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 0 |
    | Slow_queries | 0 |
    | Sort_merge_passes | 0 |
    | Sort_range | 13 |
    | Sort_rows | 2271 |
    | Sort_scan | 17 |
    | Table_locks_immediate | 320 |
    | Table_locks_waited | 0 |
    | Threads_cached | 1 |
    | Threads_created | 2 |
    | Threads_connected | 1 |
    | Threads_running | 1 |
    | Uptime | 427 |
    +--------------------------+----------+

    Do you need to see anything else like top stats etc?
    Thanks for your help :)
     
  11. PerfectSQL

    PerfectSQL Server Admin

    364
    115
    +1

    its "my.cnf" not "my.conf", make sure you editted the correct file..

    it varies depending on your machine how to restart mysql, but try these..

    service mysql restart
    or
    service mysqld restart
    or
    /etc/rc.d/init.d/mysql restart
    or
    /etc/rc.d/init.d/mysqld restart
     
  12. PerfectSQL

    PerfectSQL Server Admin

    364
    115
    +1
    Raze, first thing.... was your forum running slow before you started changing things? Second off, if your thinking about upgrading your ram, then do. Rule of thumb, sequential databases love memory...

    as aws mentioned, you could probably bump your key_buffer up to 35-40 megs

    lower the thread_cache to 32 or lower

    table_cache to 512 or lower

    wait_timeout in the neighborhood of smallest 10, largest 100

    adjust your buffers till you get a good balance of performance and speed... judging from you previous posts re: memory usage i'd say if your not getting any lag then keep them at 1 meg each.. looking at the output from your status you don't have alot of connected clients, so i don't think keeping your buffers that high will hurt, unless you expect sudden growth

    join_buffer_size=1M
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=768

    if you get slowdown, adjust them all down to 768 or lower

    and connections .. well 500 isn't gonan happen on that machine, try 250

    now, if you manage to get a setup that works well for you and doesn't eat up all your ram, throw a bit more space to the query cache
     
  13. Brook

    Brook Devotee

    2,330
    730
    +19
    Thanks.

    My my.cnf now looks like this:


    [mysqld]
    skip-innodb
    max_connections = 300
    key_buffer = 38M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 1M
    read_rnd_buffer_size = 768k
    table_cache = 512
    thread_cache_size = 32
    wait_timeout = 50
    connect_timeout = 10
    tmp_table_size = 32M
    max_allowed_packet = 160M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 16M
    query_cache_type = 1

    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M

    I tool a 'free' reading before and after the changes:

    before:

    Code:
    
                 total       used       free     shared    buffers     cached
    Mem:        252944     244616       8328          0      14040      79788
    -/+ buffers/cache:     150788     102156
    Swap:       522104     138444     383660
    
    after

    Code:
                 total       used       free     shared    buffers     cached
    Mem:        252944     246688       6256          0      13980      91172
    -/+ buffers/cache:     141536     111408
    Swap:       522104     118092     404012
    
    
    Although there were slightly more users on in the 'before' reading.

    How quick should I see any differences? Sometimes it seems faster and sometimes it seems slower :-/ Sometimes it feels like there is a 'lag' after clicking a link and it going to the page :-/ tbh I can't really tell :-(

    What do you think?
     
  14. Brook

    Brook Devotee

    2,330
    730
    +19
    Ok... It went through a very slow stage which lasted a few hours!! So I went and changed the file to:

    [mysqld]
    skip-innodb
    max_connections = 500
    key_buffer = 40M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 512K
    read_buffer_size = 512K
    sort_buffer_size = 512K
    read_rnd_buffer_size = 512K
    table_cache = 512
    thread_cache_size = 32
    wait_timeout = 50
    connect_timeout = 10
    tmp_table_size = 32M
    max_allowed_packet = 160M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 16M
    query_cache_type = 1

    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M

    ------------------

    And then left it a few hours.

    I'm not sure if there was a prblem with my ISP or the DNS of the server but now it is very fast! I'm not keeping my hopes up tho, cos there are only about 20 odd users online now and just one msn search bot...
     
  15. PerfectSQL

    PerfectSQL Server Admin

    364
    115
    +1
    well keep us posted, you don't have alot of ram to play with as we said, so it takes a bit of posting to get it just right. The more ram you have, the more leeway you have for poor my.cnf settings.
     
  16. Brook

    Brook Devotee

    2,330
    730
    +19
    I will. Thank you very much for your help, you guys certainly seem to know what you're on about!
     
  17. SaN-DeeP

    SaN-DeeP TechArena.IN

    4,157
    730
    +4
    * SaN-DeeP subscribes this thread. :)
    Thanks for handy tut
     
  18. Brook

    Brook Devotee

    2,330
    730
    +19
    OK... Keeping you posted as promised.

    42users online and 'free' reading as follows:

    Code:
    
                 total       used       free     shared    buffers     cached
    Mem:        252944     249692       3252          0       8564      71856
    -/+ buffers/cache:     169272      83672
    Swap:       522104     149628     372476
    
    Server is still going well! Everything look ok?

    Here are my 'top' stats:

    Code:
    15:19:42  up 1 day,  1:54,  1 user,  load average: 0.50, 0.59, 0.48
    93 processes: 91 sleeping, 2 running, 0 zombie, 0 stopped
    CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
               total   18.7%    0.0%    2.5%   0.0%     0.7%    1.1%   76.6%
    Mem:   252944k av,  242276k used,   10668k free,       0k shrd,    8436k buff
                        181040k actv,   43540k in_d,    1264k in_c
    Swap:  522104k av,  149928k used,  372176k free                   70292k cached
    
      PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
    10003 apache    15   0 10436 7664  2196 S     8.3  3.0   1:36   0 httpd
    10113 apache    15   0 10552 7704  2256 S     5.9  3.0   1:32   0 httpd
    13643 apache    15   0 10180 7592  2400 S     3.9  3.0   0:03   0 httpd
     9876 apache    15   0 10208 7412  2064 S     1.7  2.9   1:45   0 httpd
     3354 mysql     16   0 34736  26M  1156 S     0.5 10.8   0:45   0 mysqld
     3598 mysql     15   0 34736  26M  1156 S     0.1 10.8   0:40   0 mysqld
     9881 apache    15   0 11108 8364  2284 S     0.1  3.3   1:40   0 httpd
    13741 admin     15   0  1212 1212   900 R     0.1  0.4   0:00   0 top
        1 root      15   0   484  456   436 S     0.0  0.1   0:04   0 init
        2 root      15   0     0    0     0 SW    0.0  0.0   0:00   0 keventd
        3 root      15   0     0    0     0 SW    0.0  0.0   0:00   0 kapmd
        4 root      34  19     0    0     0 SWN   0.0  0.0   0:00   0 ksoftirqd/0
        7 root      25   0     0    0     0 SW    0.0  0.0   0:00   0 bdflush
        5 root      15   0     0    0     0 SW    0.0  0.0   0:01   0 kswapd
        6 root      15   0     0    0     0 SW    0.0  0.0   0:00   0 kscand
        8 root      15   0     0    0     0 SW    0.0  0.0   0:00   0 kupdated
        9 root      25   0     0    0     0 SW    0.0  0.0   0:00   0 mdrecoveryd
       13 root      15   0     0    0     0 SW    0.0  0.0   0:02   0 kjournald
       69 root      25   0     0    0     0 SW    0.0  0.0   0:00   0 khubd
     1384 root      15   0     0    0     0 SW    0.0  0.0   0:00   0 kjournald
     2298 root      15   0   268  240   192 S     0.0  0.0   0:01   0 syslogd
     2302 root      15   0   200  188   144 S     0.0  0.0   0:00   0 klogd
     2328 rpc       15   0   140   64    60 S     0.0  0.0   0:00   0 portmap
     2347 rpcuser   25   0   200  128   124 S     0.0  0.0   0:00   0 rpc.statd
     2416 root      25   0    92   40    36 S     0.0  0.0   0:00   0 apmd
     3062 root      15   0   832  428   340 S     0.0  0.1   0:00   0 cupsd
     3102 root      15   0   468  328   232 S     0.0  0.1   0:00   0 sshd
     3116 root      16   0   500  464   384 S     0.0  0.1   0:00   0 xinetd
     3132 root      15   0   376  336   284 S     0.0  0.1   0:00   0 da-popb4smtp
     3155 mail      15   0   616  368   288 S     0.0  0.1   0:00   0 exim
     3187 ftp       15   0   556  272   188 S     0.0  0.1   0:00   0 proftpd
     3197 root      15   0   128   96    56 S     0.0  0.0   0:00   0 vm-pop3d
     3216 root      15   0   184  168   116 S     0.0  0.0   0:00   0 crond
    
    Can anyone tell me what the 'processes' bit at the top means? I had two zombies earlier! :yikes:
     
  19. MaXBoost

    MaXBoost Adherent

    270
    63
    +0
    Raze:

    I agree with PerfectSQL, try to add at lease 256 mb more ram (1 gig is better). then best to edit my.cnf after you add the extra ram
     
  20. Brook

    Brook Devotee

    2,330
    730
    +19
    Hi Maxboost, thanx for replying.

    It's actually running very well at the moment - I know it's not as busy as say the forums here but it seems just as quick at present (testing on a 1mb broadband connection). I will definately be getting an extra 256 too.

    I was posting the 'free' and 'top' stats above as I don't really know what they mean and thought if there was something 'wrong' someone could point it out to me.

    :)
     
Verification:
Draft saved Draft deleted