MySQL Server Tweaking Basics

[PerfectSQL]

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.

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

[DChapman]

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?

[AWS]

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.

[PerfectSQL]

Quote:

Originally Posted by thegunowner914 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?

For a dual xeon with a ht I would set it to 4, as it only has two real processors.

[Brook]

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]

[Brook]

Heres a 'free' reading with 31 users online:

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

[tekkitan]

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?

[Brook]

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.

[AWS]

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.

[Brook]

Quote:

Originally Posted by AWS 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.

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 :-)

[PerfectSQL]

Quote:

Originally Posted by tekkitan 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?

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

[PerfectSQL]

Quote:

Originally Posted by Raze Thanks for replying AWS. I have 256meg on the server but am thinking about upgrading it. Here's that info... ........ ........ Do you need to see anything else like top stats etc? Thanks for your help :-)

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

[Brook]

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:

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

after

             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?

[Brook]

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...

[PerfectSQL]

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.

[Brook]

I will. Thank you very much for your help, you guys certainly seem to know what you're on about!

[SaN-DeeP]

/me subscribes this thread.
Thanks for handy tut

[Brook]

OK... Keeping you posted as promised.

42users online and 'free' reading as follows:

             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:

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!

[MaXBoost]

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

[Brook]

Quote:

Originally Posted by MaXBoost 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

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.

:-)