3 Minute MySQL (tuning)

While reading planetmysql.org I ran across this Tune a MySQL server in 5 minutes.. I think that entry is really missing a lot in terms of actual tuning for the real world. Also I thought max_user_connections is number of connections per hour. Since I only have 3 minutes I can’t look it up! You probably don’t want to set this with apache.

So here in three minutes are the four variables you really need and some generic guidlines for setting them. This assumes that MySQL is running on it’s own server and has one apache server connecting to it.

max_connections - set to the same (or a little bigger than) as MaxChildren from apache if you’re only using one mysql connection per request (this is most common)

key_buffer_size - Set to roughly half your available ram. Remember this is a quick and dirty tuning. We only have 3 minutes!

thread_cache_size - set it until show status like ‘threads_created’ quits increasing.

table_cache_size - set until show status like ‘opened_tables’ quits increasing. If you have thousands of tables this might not be possible

It took me 5 mintues to write this entry so you should be able to implement it in 3 (ok maybe five or six). For a MySQL server serving only MyISAM tables this style of configuration will be good enough.

March 6, 2006 • Posted in: Geek, MySQL

13 Responses to “3 Minute MySQL (tuning)”

Matt Montgomery - March 7, 2006

OK…. I’ll do you one better…. Tuning a mysql server in 1.672s.

Since I’m part of a team that manages +7,000 linux servers and since most of which have completely virgin my.cnf files, I wrote this little script.

http://www.day32.com/tuning-primer.sh

It evaluates the following:

slow query count
long_query_time
query_cache_size
% query cache actually used
max_connections
thread_cache
key_buffer_size
ratio of sort_merge operations to sorts (sort_buffer_size)
number of full joins
ratio of disk tmp tables vrs in memory tmp tables (tmp_table_size)
table_cache
ratio of table locks immediate to table locks waited
ratio of table scans (read_buffer_size)

To use the script add your user name and password to a [client] group in your ~/.my.cnf file
Joy Elvis - May 18, 2006

HI Eric,
Can you please send me the tuning-primer.sh script. I am not able to see this at http://www.day32.com/tuning-primer.sh.
Thanks
Caleb - June 5, 2006

Joy,

The URL has changed recently, http://www.day32.com/MySQL/tuning-primer.sh
Steven Ghyselbrecht » links for 2007-07-13 - July 13, 2007

[...] Eric Bergen » 3 Minute MySQL (tuning) (tags: mysql tuning performance tips 3-minutes) [...]
Alex Devry - January 28, 2008

The script http://www.day32.com/MySQL/tuning-primer.sh is just amazing !
thanks a lot !
Alex Devry
links for 2008-03-18 « Caiwangqin’s delicious bog - March 18, 2008

[...] Eric Bergen » 3 Minute MySQL (tuning) (tags: MySQL Optimization Tuning Guide performance script) [...]
Performance Tuning OpenCMS by Example » Neural Glue - June 16, 2008

[...] background take a look at [ http://www.ibm.com/developerworks/library/l-tune-lamp-3.html ] and [ http://ebergen.net/wordpress/2006/03/06/3-minute-mysql-tuning/ [...]
links for 2008-09-07 - September 7, 2008

[...] Eric Bergen » 3 Minute MySQL (tuning) (tags: mysql script tuning performance) [...]
Julius Tsui - January 31, 2009

I notice that our server running this website, the mysql is very busy and I have no idea why. Can you help?

many thanks
Julius
Melhore o desempenho do MySQL sem a ajuda de um DBA - May 1, 2009

[...] nesse artigo, vou passar aqui umas dicas rápidas que farão a diferença no uso do MySQL em sua [...]
sanatate - May 6, 2009

http://www.day32.com/MySQL/tuning-primer.sh is great ! Thank you!!!
Tuneando MySQL « Hermoso día… - July 31, 2009

[...] Visto aquí [...]
h rogerio - December 2, 2009

thanks for your tunning script very handy made a lot of difference…