Last updated March 24, 2013. Created by federico on September 24, 2006.
Edited by EuroDomenii, rsvelko, nevergone, pfrenssen. Log in to edit this page.
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):
Note that if you are on a shared hosting plan then only your host will be able to tune MySQL since you won't have access to the 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. You can analyze your MySQL performance with the MySQL Performance Tuning Primer Script.
# cd /usr/local/src/
# wget http://day32.com/MySQL/tuning-primer.sh
# chmod u+x tuning-primer.sh
# ./tuning-primer.shIt will ask for your database root user name and password and print out a list of recommendations. Was shocked to learned that on my VPS the cache was not even enabled - very helpful to know!
2. Next open your my.cnf 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 of RAM your my.cnf file may look like this:
[mysqld]
max_connections = 150
max_user_connections = 150
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 = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
innodb-flush-log-at-trx-commit=2For a system with 256mb of ram it may look like this:
[mysqld]
max_connections = 75
max_user_connections = 75
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 = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
innodb-flush-log-at-trx-commit=2Please note that every server configuration is going to differ and simply pasting these in may cause unexpected results.
3. Save your my.cnf file and restart mySQL. This can be done via your control panel or the command line (on some unixes: service mysqld restart otherwise /etc/rc.d/init.d/mysqld restart or /etc/init.d/mysqld restart)
Your new settings are now active and you can run the script from above again and see the difference in your results. After some experimenting 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) depending on your site traffic. 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:
Comments
Expert Review?
Has anyone really looked at this advice lately? I'm no expert, but in reviewing other peoples my.cnf files (including the huge-my.cnf and large-my.cnf that ship with MySQL) and running Matt Montegomery's performance tuning script, 800 connections seems really excessive unless you're running a heavily trafficked site, like Digg or Slashdot. In huge-my.cnf file from MySQL max_connections only has about 150 connections.
MySQL gurus -- does this page give good advice?
Updated
Note that the suggested settings for max_connections, max_user_connections and max_connect_errors have been updated (see http://drupal.org/node/270235) and are now more reasonable. Further expert review would still be welcome...
gpk
----
www.alexoria.co.uk
gpk
----
www.alexoria.co.uk
This is alright advice, but
So much depends on your setup and your site. Google "tuning-primer.sh" and run that on your server. Make the adjustments is says to make in your /etc/my.cnf file. This won't be perfect, but it will get you 80% there to having a well tuned MySQL. Remember that after you make the adjustments, you must restart MySQL and wait a few hours or days (depending on traffic of your site) to get an accurate reading again of what MySQL is doing. Then make further adjustments and restart MySQL again.
CiviCRM specialist
Would be nice that we have
Would be nice that we have some optimal apache/php/mysql settings Instructions by some pros. I know it depends on the situation and configuration but some good starting point or lets say balanced setting would be very helpful to have written.
Drupal tips
Suggested settings for 500MB server cause my server to barf
I have tried the settings above given for a server of 500 MB RAM. I have a dedicated server with 500 MB RAM and with the settings given above, my server was over allocating memory to mysql and this caused it to hang a couple times. After much researching, I used the following settings:
max_connections = 90
max_user_connections = 90
wait_timeout = 100 (this was originally 1800 as posted in this original page and i think this was the main culprit)
max_connect_errors = 10
With the settings given above, the mysqld service will utilize about 786 MB of memory when only 500 MB is available. So its only a matter of time before it hangs.
I am not a mysql expert but you can approximate your memory allocation as follow:
key_buffer + (soft_buffer_size + read_buffer_size) * max_connections = total memory allocation
If you plug in the values given in the top of this page you will get:
36 (3 + 2)*150 = 786 which is > 500 MB
If you used the my settings you will get:
36 (3 + 2)*90 = 486 which is <= 500 MB
Please use these settings at your own risk and remember each server is different so be prepared to see your server eventually hang with the wrong settings.
Here are some links that I found very useful:
http://www.ibm.com/developerworks/linux/library/l-tune-lamp-3.html
http://www.databasejournal.com/features/mysql/article.php/3110171
http://www.transcendlinux.com/mysql-performance-tuning
Also google "mysql query cache". This significantly speeds your your page processing as well.
/etc is meant for config
/etc is meant for config files, not scripts
script link is broken
script link is broken
Web Hosting | Dedicated Web Hosting
tuning-primer.sh can't open /etc/webmin/mysql/config
I downloaded Matt Montgomery's tuning-script tuning-primer.sh, but I can't get it to run. It complains that it can't open:
$ sh tuning-primer.sh
Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
grep: /etc/webmin/mysql/config: Permission denied
grep: /etc/webmin/mysql/config: Permission denied
Could not auto detect login info!
Found Sockets: /var/lib/mysql/mysql.sock
Using: /var/lib/mysql/mysql.sock
Would you like to provide a different socket?: [y/N]
Do you have your login handy ? [y/N] :
Please create a valid login to MySQL
Or, set correct values for 'user=' and 'password=' in ~/.my.cnf
Would you like me to create a ~/.my.cnf file for you? [y/N] :
/etc/webmin/mysql/config and its parent dirs are owned by root. I have root access, but am afraid to run this script as root when it has not been signed. Has anyone successfully run it as root?
-Larry Siden
http://westside-consulting.com
Yes, you can run it as root.
Yes, you can run it as root. The script does not change anything on your server, it just gives recommendations.
bug in MySQL
I had my query_cache at 0 without understanding why for hours! In fact they is a bug in MySQL http://bugs.mysql.com/bug.php?id=55556 "when the database name contains a minus-sign (db-test) and the table type is innodb, the query cache is not used."
------
cloud33.com
antistatique.net
tuning-primer.sh page
The link provided next to wget was dead when I checked, anyways, the script page is here:
https://launchpad.net/mysql-tuning-primer
Oh - I forgot! I'm very grateful
Drupal Expert (Certified to rock at level 2... out of 11)
Latest project: Rekruteur - SaaS for Human Resources
max_allowed_packet
It seems that the max_allowed_packet setting in the above recommendations will often end up being too low for large queries coming from modules like Views and Panels. It will throw an error like "MySQL server has gone away," as discussed here: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html and in the comments on these support issues: http://drupal.org/node/163987 and http://drupal.org/node/227445. It is likely that you will need a much higher value. Just wanted to point that out for anyone who runs into a similar problem trying to optimize their database.
Thanks
@ 4Elemental
I did run into this problem when setting up MAMP. You saved me a lot of work googling.
General Recommendations Are Best
Although it is good to have some comparative resources, ultimately a copy and paste of someone else's config is a bad idea (as has been clearly stated).
What I would love to see here is a description of generally what the config options mean, and how would a systems administrator calculate appropriate values for Drupal. That is to say, math rather than values...
* Why would someone choose a specific value such as '150' vs '75' for max_connections?
* How does one decide or calculate good values for the buffer sizes? Are they based on the amount of system RAM available, on the type of traffic expected, or both? Or additional concerns to consider?
* When choosing a value for connection_timeout, should the administrator consider whether the web and database servers are communicating across a physical network versus a single system with HTTP and DB servers both on 'localhost'?
* etc
innodb-flush-log-at-trx-commit=2 best optimization
Relaxed ACID constraints innodb-flush-log-at-trx-commit=2
According to our benchmarks with drupal devel enabled front page with advanced forum enabled, the improvement is huge
1) innodb-flush-log-at-trx-commit=1 Executed 628 queries in 3221.57 ms.
2) innodb-flush-log-at-trx-commit=2 Executed 628 queries in 73.59 ms
See also
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_inn...
https://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance