mySQL Tuning Tips

[AutoItScript]

Background:
I'm running CentOS 5.3 (linux) with mysql 5.0.58. My IPB site has around 500 users online and 700,000 posts. I've spent the best part of a week learning about tuning mysql instead of blindly using my-huge.cnf, or randomly plugging in values. I've written my findings here as there appears to be a load of anecdotes and misinformation out there on this topic. I'm still a newbie though so if you find that something I've said it utterly wrong then let me know!

For the purposes of this guide I'll assume you have a simple dedicated server with web server and mysql on the same box and the main database being run by mysql is IPB. That said, I think most of the concepts are applicable to other systems.

Ok, so when tweaking my.cnf the first "easy win" is to turn on query caching (which is off by default) with these lines:

query_cache_type=1

query_cache_limit=1M

query_cache_size=32M

This just turns caching on, uses 32M for the cache, and allows the biggest single entry to be 1M. You can adjust this later when we monitor the server (below) but these values are pretty good for most.


The next most important settings are these:

key_buffer - used for caching table indexes. 

sort_buffer_size - used when performing sorts

read_rnd_buffer_size - used after a sort for ORDERBY operations

read_buffer_size - used for table scans


I've read loads of articles where "general wisdom" is given for these. But after reading all of them I've come to the conclusion that they are all talking rubbish. The best method is to monitor what's going on and then tweak. Download these two scripts, tuning-primer.sh and mysqltuner.pl:

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

wget http://mysqltuner.com/mysqltuner.pl

chmod 700 tuning-primer.sh

chmod 700 mysqltuner.pl

These scripts will give you easy access to some monitoring statistics and even try to give advice. Even if you ignore the advice they are very useful to give easy access to stats like total memory that could be used by mysql, cache hit rates, etc. Both give slightly different output so I've been using both as required.


key_buffer (or key_buffer_size) - (Global)

This is a global value - it is shared between all databases.


There are two widely accepted comments about how to set this:

1. Set between 25% and 50% of RAM

2. Try and set as large as the total of all your myISAM index files (*.MYI files) (mysqltuner.pl shows this size this neatly)


Option 1 - Setting as an amount of RAM is crazy. My board has 380M of .MYI files setting key_buffer larger than this does NOTHING apart from totally and utterly waste the RAM. 


What about Option 2 - Setting key_buffer large enough to contain all the .MYI files? This is a bit better but even still it probably won't ever use the entire amount unless every single index is read. And (this applies to the other memory settings as well) mysql is only caching index lookups, when the actual data is read from disk it is the OS that will be doing the majority of the caching. The more memory you needlessly waste in mysql the worse the OS caching will perform if you get low on RAM.


So the best thing to do is to montior the key_buffer hit rate and aim for a 99% ratio (that is 99% of index lookups are done from RAM). After your server has been running for a while (the tools suggest 2 days, but you can do that when fine tuning). Run mysqltuner.pl and see what the key buffer ratio is. My server has 380M of index files and 2GB of RAM so conventional wisdom is to set it to either 512M-1024M (option 1) or 380M (option 2). Well, here is the mysqltuner.pl excerpt from my server:

[OK] Key buffer size / total MyISAM indexes: 64.0M/379.7M

[OK] Key buffer hit rate: 99.3%

What the heck?! I'm only using a 64M key_buffer and yet I have a 99% hit rate!  Is it worth increasing it to 390M just to get 99.9%?  Maybe if you have more RAM than sense - I've only got a 2GB server so that extra RAM is much better utilized by the OS for general caching. My recommendation would be to start the key_buffer low, monitor it and increase it by a small amount each time until you hit a 99% hit rate.


Conventional wisdom size: 512M 

My server size: 64M


sort_buffer_size - (Per-connection)

This is allocated per-connection - if you have 100 connections then you use 100 * sort_buffer_size


As with key_buffer there is lots of advice on a "allocate 1M per GB of RAM" advice. Again I would ignore that and instead just monitor how the buffer is used. On my server I started with 1M and then upped it by 1M at a time until 99% of sorts where done in RAM and only 1% needed a temporary table. On my server that required a sort_buffer_size of 4M.  Here's the mysqltuner.pl output:

Sorts requiring temporary tables: 1%

For my 2GB server the "wisdom" setting of 2M setting was too small and I had about 15% of sorts needing to use temporary tables.


Conventional wisdom size: 2M

My server size: 4M


read_buffer_size - (Per-connection)

This is allocated per-connection - if you have 100 connections then you use 100 * read_buffer_size


This is used to buffer table scans. In theory as long as threads are cached (see below) then bigger is better, I've seen values from 2M to 16M suggested in articles and our old friend "1M per GB of RAM" is the most common. That's probably ok UNLESS YOU ARE RUNNING LINUX. Yep, it's bugged. In most versions of mysql 5 (and certainly in my server version of 5.0.58) any any read_buffer_size above 256K will actually make performance worse! The bug report (and repo steps) can be found at http://bugs.mysql.com/bug.php?id=44723 - I followed the steps and on my server a 2M buffer was TWICE as slow as a 256K buffer. The more I increased the buffer the worse it got. I highly recommend running the repro steps on your linux server to see if you are affected before going above 256K


Conventional wisdom size: 2M

My server size: 256K


read_rnd_buffer_size - (Per-connection)

This is allocated per-connection - if you have 100 connections then you use 100 * read_rnd_buffer_size


This is used after a sort operation for certain queries like ORDER_BY. It's used quite a bit by IPB but I couldn't really detect any performance differences for values greater than 256K


Conventional wisdom size: 2M

My server size: 256K



Those are some of the most "controversial" values.  As said before, it's better to use buffers that are correctly sized rather than just using a percentage of RAM and leave as much memory as possible for the OS to do general caching. 


Per connection memory use is:

sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size 

(join_buffer_size defaults to 128K and I've left it low as IPB doesn't use queries that require it).


Ensure that your max_connections value actually doesn't use more RAM than you actually have! mysqltuner.pl shows this information neatly:

[--] Total buffers: 178.0M global + 4.9M per thread (50 max threads)

[OK] Maximum possible memory usage: 421.8M (21% of installed RAM)

The "global" value is essentially key_buffer + tmp_table_size.


tmp_table_size - (Global)

This is a global value - it is shared between all databases.


When mysql needs to create temp tables this value alters whether the table is created in RAM or on disk. First, you must set max_heap_table_size and tmp_table_size to the same value as the lower of the two is used. The defaults are 16M and 32M respectively which essentially means that the default tmp_table_size is 16M. I currently have mine set to 32M and even when I set it to 512M the number of tmp tables created on disk is the same as when the value was 32M. Further research shows that TEXT and BLOB tables cannot be created in RAM. If you are running IPB only you are probably in the same position and won't see benefit above 32M either.



So, what are the differences between the conventional wisdom sizing and my "just right" sizing for my server for 100 connections?

Wisdom: 512M + 32M + (100 * (2M + 2M + 2M + 128K)) = 1156M

My Server tweaked: 64M + 32M + (100 * (4M + 256K + 256K + 128K)) = 558M


Quite a saving... half a gig extra for the OS to use for other things :) 


Ok, some more important variables


thread_cache_size - during normal operation you don't really want to be creating threads (expensive memory allocations especially with large buffers), use mysqltuner.pl to monitor and tune


table_cache - table open operations that have been cached. use mysqltuner.pl to monitor and tune. For a single IPB forum my value was 500


I also found some advice on a vBulletin for improving mysql searching which I'm still testing and researching (http://dev.mysql.com/doc/refman/5.0/en/midpoint-insertion.html):

low_priority_updates=1

concurrent_insert=2

key_cache_division_limit=40

Ok, that's it I hope it's been useful. Please feedback on anything that is completely wrong - I'm still learning :)   Oh, and here is my my.cnf for reference for my IPB.

[mysqld]

set-variable=local-infile=0

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

skip-networking

skip-locking

skip-bdb


query_cache_type=1

query_cache_limit=1M

query_cache_size=32M


# Set to combined size of all .MYI files, or between .25 and .5 of total RAM

# Turns out this is nonsense, just monitor the key buffers and go for 99% hit rate

key_buffer=64M


# The following are created for each connection, so are important for sizing

# per thread/connection memory = sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size

# Defaults:

#   sort_buffer_size=1M

#   read_buffer_size=128K

#   read_rnd_buffer_size=256K

#   join_buffer_size=128K

#

# If running on linux there is a bug that anything over read_buffer_size=256K will actually

# harm performance.  See http://bugs.mysql.com/bug.php?id=44723.

# I've verified on CentOS 5.3 with mysql 5.0.58


sort_buffer_size=4M

read_buffer_size=256K

read_rnd_buffer_size=256K

join_buffer_size=128K


# Ensure you only use the number of connections you can support in memory (see above for per thread memory):

# Memory used = key_buffer + tmp_table_size + ((per thread mem)*max_connections)

max_connections=50


# Timeouts, set to not let idle connections - the defaults are huge

interactive_timeout=90

wait_timeout=90

connect_timeout=10


# Check opened tables and adjust accordingly after running for a while.

open_files_limit=1500

table_cache=500


# Check created_tmp_disk_tables, also needs heap table size as uses the smallest value

# Have tried 512M for IPB but still same number of temp tables, might as well keep small

tmp_table_size=32M

max_heap_table_size=32M


# thread_concurrency = 2 * (no. of CPU) - BUT only works on solaris so don't need to bother

thread_concurrency=4

thread_cache_size=8


# Stuff that can help with mysql searches

# For key_cache_division_limit see http://dev.mysql.com/doc/refman/5.0/en/midpoint-insertion.html

low_priority_updates=1

concurrent_insert=2

key_cache_division_limit=40


# Log slow queries

log_slow_queries=/var/log/mysqld.slow.log

long_query_time=5

expire_logs_days=7

[rct2·com]

That's all very comprehensive analysis.

Some of is it relevant whatever server spec you are running.

But the most critical factor in determining optimal mysql settings is the amount of memory on the server and the amount of free memory available. Some of your recommended settings may be too low or too high, depending on server memory.

[AutoItScript]

[quote name='rct2dotcom' date='11 July 2009 - 09:22 PM' timestamp='1247343766' post='1824515']
But the most critical factor in determining optimal mysql settings is the amount of memory on the server and the amount of free memory available. Some of your recommended settings may be too low or too high, depending on server memory.

Yeah, that was my point. No point listening to people who just tell you numbers based on your server's memory. You need to monitor what the load is and then decide what to do based on what free memory you have. I just saw an old post on here where the answer to "I have 8GB of RAM" was to set key_buffer to 2GB. Heh :)

[rct2·com]

I have been very successful tuning mySQL by using mysqltuner.com I used it over about 6 weeks, reviewing the recommendations [and applying them] evry other day for the first week, then weekly after that.

[Zero5854]

hey thats cool though...if someone is messing with their php/mysql settings they SHOULD be able to decipher the settings and be able to adjust according to their servers memory. Thanks for the info :)

[Velvet Elvis]

another handy tool:

http://hackmysql.com/mysqlreport

[DiecastX]

Is it okay to use values such as, say, 48MB? Instead of doubling from 32M to 64M, is it okay to use a 50% increase?


Extremely appreciative of this thread!

[DiecastX]

Disregard. Resolved.

[DiecastX]

mysqltuner is telling me, among other things, this:



Does anyone know what I should do about it?

Adjust your join queries to always utilize indexes

[rct2·com]

That requires changes to the database design, and possibly code changes. Unless it's referring to your own code, there is little you can safely do about it.

[DiecastX]

Interestingly, mysqltuner and tuning-primer conflict on join_buffer_size. I am at 6M now, mysqltuner is telling me to increase, tuning-primer is telling me to decrease. Ideas?

[Clickfinity]

Just wanted to say thanks for this thread ... it's helped me tweak MySQL on my server to optimise it some more.

Cheers,
Shaun :D

[Leooooo]

Nice initiative AutoItScript. Thanks.