Mysql tuning. Tools, tips, and links on optimizing mysql for Drupal

Published in: 

Here are some basic, but high impact ways to optimize MySQL for Drupal (there are much more sophisticated and expensive ways to speed up your database of course):

Am not sure if these tips do any good for someone on a shared hosting plan or not (do shared plans have access to a my.cnf file?). Also, I can only confirm these setting for MySQL 4.0.2 thru the latest 4.0.x version, but I think it would work for 5.x (maybe someone can confirm this and leave a comment...).

Actually, it will work for below 4.0.2 I think as long as you add set-variable = before each line (see this page for more on set-variable)

1. Get this script, upload it, unzip it, and install it in your /etc folder (at the root of your server, not your Drupal install, right). Then run it from the command line by entering sh /path-to-file/tuning-primer.sh

The script will run and what you'll be left with is an output with some info and suggestions about your MySQL settings. Was shocked to learned that on my VPS the cache was not even enabled - very helpful to know!

2. Next open your my.conf file in pico or some kind of proper code/text editor:

Depending on the memory resources you have available you'll want to paste in something like these examples (adjust up or down depending on how your system differs, of course):

For a setup with 500mb or ram paste this in your my.cnf file:

[mysqld]
max_connections = 800
max_user_connections = 800
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 999999
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M

For a system with 256mb of ram:

[mysqld]
max_connections=500
max_user_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 999999
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M

3. Save your my.cnf file and restart mysql. This can be done via WHM or the command line (not sure what that command is - sorry)

Your new settings are now active and you can see run the script from above again and see the difference in your results. After some experiementing I've found that it is useful to look at the script results right after making a change just to see if your modifications were recognized by the system and get the early returns from whether things were improved or not - but - to get a truly accurate reading from the script you should check back in 24-48 hours after rebooting mysql (this is actually noted at the top of the script itself, but it doesn't really explain why). Also, I've found that the way I've got Drupal set up it is particularly demanding in the tmp_table_size and table_cache areas (e.g., you may want to bump up the number for both of these areas in the settings above)

If you'd like to read up on more about mysql tuning I suggest taking a look at these resources:

Tuning MySQL for Drupal

Tuning a MySQL server in 5 minutes

MySQL variables

21 January, 2007

Comments

Great post indeed. I am sending the link to my host to see what can be done to optimize it. Also

* To start mysql server:
# /etc/init.d/mysqld start

* To stop mysql server:
# /etc/init.d/mysqld stop

The skunkworks site is no longer available.

Your script has been a great help, but I'm puzzled by the output of the table_cache test. I set table_cache = 1024, and the script said:

TABLE CACHE
Current table_cache value = 457 tables
You have a total of 1094 tables
You have 457 open tables.
Current table_cache hit rate is 3%, while 100% of your table cache is in use
You should probably increase your table_cache

So I raised it to 2048 - but the script output didn't change.

...this is not *my* script. However, for your issue I'm guessing that perhaps you didn't restart mysql after editing your my.cnf file.

How to configure it, if the Server has 4 GB Ram?

Regards,
Don