MySQL Tweaking

From WCooke
Jump to navigation Jump to search

The reference below was an excellent source for writing this and there are a few more suggestions in it that you may want to look at as well. Make sure to back up your configuration file and databases before you start playing around with it.

Calculating Free Memory

We need to find out how much memory is available for MySQL to use. This example is not going to include swap space in the total because what would be the point in tweaking MySQL to run faster if it is going to be swapping off of the hard drive? When calculating the amount of free memory, don't forget to include the amount of memory MySQL is currently using. The "ps -o vsz,cmd -C mysqld" command can be used to find out how much memory MySQL is using.

localhost mysql # ps -o vsz,cmd -C mysqld
   VSZ CMD
123456 /usr/sbin/mysqld

So, we take 123456 divided by 1024 and we get about 120 of memory being used by MySQL in its current configuration. After running the "free -m" command and finding out there is 415 Megabytes of free memory, we add that to the previous 120 and get 535 megabytes. We will round this down to a nice number of 512.

localhost mysql # free -m
             total       used       free     shared    buffers     cached
Mem:          7991       7575        415          0        722       5778
-/+ buffers/cache:       1074       6917
Swap:            0          0          0

Configuration File

The following is a template to add into the [mysqld] section in your my.cnf file. It will usually be in /etc/mysql or /etc. Remember to use the free memory calculated from above when figuring the percentages.

Query_cache_limit               = 2M
Query_cache_size                = 25%
Query_cache_type                = 1
Thread_cache_size               = 128
Key_buffer                      = 25%
Join_buffer                     = 4M
Table_cache                     = 25%
Sort_buffer                     = 4M
Read_rnd_buffer_size            = 1%
Tmp_table_size                  = 10% (or 32M, whichever is greater)

Examples

This example is for a computer with 512MB of memory (including what is being used by mysqld).

[mysqld]
query_cache_limit               = 2M
query_cache_size                = 128M
query_cache_type                = 1
thread_cache_size               = 128
key_buffer                      = 128M
join_buffer                     = 4M
table_cache                     = 128M
sort_buffer                     = 4M
read_rnd_buffer_size            = 5M
tmp_table_size                  = 51M

This example is for a computer with 4GB of memory (including what is being used by mysqld).

[mysqld]
query_cache_limit               = 2M
query_cache_size                = 1024M
query_cache_type                = 1
thread_cache_size               = 128
key_buffer                      = 1024M
join_buffer                     = 4M
table_cache                     = 1024M
sort_buffer                     = 4M
read_rnd_buffer_size            = 41M
tmp_table_size                  = 410M

References