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.

    13 Comments

    1. 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:

      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:

      Joy,

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

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

    5. Alex Devry says:

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

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

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

    8. Julius Tsui says:

      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

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

    10. h rogerio says:

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

    Leave a Reply