« Where am I?
MySQL Snapshot from a full disk. Tar over ssh »

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.

    This entry was posted on Monday, March 6th, 2006 at 6:43 pm and is filed under Geek, MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

    8 Responses to “3 Minute MySQL (tuning)”

    1. Matt Montgomery Says:
      March 7th, 2006 at 2:41 pm

      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

    2. Joy Elvis Says:
      May 18th, 2006 at 2:45 am

      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

    3. Caleb Says:
      June 5th, 2006 at 2:43 pm

      Joy,

      The URL has changed recently, http://www.day32.com/MySQL/tuning-primer.sh

    4. Steven Ghyselbrecht » links for 2007-07-13 Says:
      July 13th, 2007 at 4:19 pm

      [...] Eric Bergen » 3 Minute MySQL (tuning) (tags: mysql tuning performance tips 3-minutes) [...]

    5. Alex Devry Says:
      January 28th, 2008 at 10:50 am

      The script http://www.day32.com/MySQL/tuning-primer.sh is just amazing !
      thanks a lot !
      Alex Devry 8)

    6. links for 2008-03-18 « Caiwangqin’s delicious bog Says:
      March 18th, 2008 at 5:17 pm

      [...] Eric Bergen » 3 Minute MySQL (tuning) (tags: MySQL Optimization Tuning Guide performance script) [...]

    7. Performance Tuning OpenCMS by Example » Neural Glue Says:
      June 16th, 2008 at 5:06 am

      [...] 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/ [...]

    8. links for 2008-09-07 Says:
      September 7th, 2008 at 6:59 am

      [...] Eric Bergen » 3 Minute MySQL (tuning) (tags: mysql script tuning performance) [...]

    Leave a Reply