MySQL performance tuning

This document serves as a starting point for MySQL performance tuning. This document is a combination of research and experience. When I started this document, I utilized a great Google video [1] as a reference for the document structure and many bullet items. I would suggest watching this video. I then filled in a few blanks, and combined a few other articles into this overview.You may also want to check out this good article from MySQL on performance tuning.. Now onto the show.. BenchmarkingAs with all improvement activities, it is better to start with a known problem area that is able to be defined, or an activity where improvement will provide maximum benefit (the 80/20 rule works everywhere). Nobody can really grasp an intangible goal like "we want to achieve excellence" [4].The following are decent guidelines to start with [1]:

• Target. You must have a target.
• Establish a baseline.
• Change only one thing at a time (if possible).
• Record Everything, you will need it at some point.
• Optionally disable query_cache, by setting query_cache_size = 0 for benchmarking.

In addition, the following benchmarking utilities may prove to be useful [5]:

• Super Smack - http://vegan.net/tony/supersmack/

Super Smack is a benchmarking, stress testing, and load generation tool for MySQL (and PostgreSQL). Super Smack was originally written by Sasha Pachev, and then hosted and maintained by Jeremy Zawodny.

• SysBench - http://sysbench.sourceforge.net/

SysBench is a modular, cross-platform and multi-threaded benchmark tool for evaluating OS parameters that are important for a system running a database under intensive load.

• mybench - http://jeremy.zawodny.com/mysql/mybench/

mybench is a simple benchmarking framework for MySQL, written in Perl. It consists of a module (MyBench.pm) and an example script (bench_example) that you should customize to suit your needs.

Profiling concepts

• Be familiar with the Explain command and access types in the explain command.
• Use slow query log and mysqldumpslow command to help you determine where problems really exists.
• Low hanging fruit. Tackle stuff that is getting you the best ROI on your time.
• Use MyTOP by Jeremy Zawodny
• Use tuning-primer.sh - http://www.day32.com/MySQL/

Sources of problem

• Poor indexes
• Inefficient or Bloated schema design.
• Bad coding practices, using joins is typically more efficient than subqueries.
• Server variables
• Tuning parameters help but are specific to scenario OLTP, OLAP, memory, system Architecture, storage engine. Meaning, no one size fits all solution really exists. However, you can tune based on some recommendations from other parameters.
• Tweak one thing, rerun benchmarks..
• Server and network bottlenecks

Index Guidelines

• Poor or missing indexes are the fastest way to kill a system.
• Look for covering index opportunities.
• MySQL gets all info from index records, and uses it to complete the query, without having to go into the data records.
• Ensure good selectivity on index fields.
• Make sure you look at selectivity, or cardinality (unique values / total values in table). [ cardinality of 1 is the best case ].
• Multi-column indexes, pay attention to order of fields.
• As database grows ensure distribution is good. Know how your data changes over time. Re-analyze your data and distribution of indexes over time.
• Remove redundant indexes for faster write performance.

System tuning

• Be aware of global vs threaded parameters, key buffers size, global. Sort buffer size, per thread.
• Make small changes (preferably single), retest.
• System parameter tuning is often a quick, temporary solution to a larger issue.
• Query_cache, off by default.
• read intensive applications, turn query_cache on.
• Key_buffer_size != innodb_buffer_pool_size
• innodb_buffer_pool_size about 50-90% of total memory suggested typically.
• Ensure you have analyzed and optimized your tables.

You may also be surprised at just what an effect optimizing a MyISAM table that has dynamic rows will do.. use the 'optimize table' SQL commandParameterstable_cache

Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. Use the tuning-primer.sh (http://www.day32.com/MySQL/) script to evaluate the current value.

query_cache_size

If your application is read intensive and you do not have application level caches this can be great help. Use the tuning-primer script to evaluate the size after you enable it. A good starting point may be 8M.The query_cache_size will be aligned to the nearest 1024 byte block. The value reported may therefore be different from the value that you set.If the query cache size is greater than 0, then query_cache_type variable influences how it works. This variable can be set to the following values:

query_cache_type

* A value of 0 or OFF prevents caching or retrieval of cached results.* A value of 1 or ON allows caching except of those statements that begin with SELECT SQL_NO_CACHE.* A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.

thread_cache

mysql> SHOW STATUS LIKE 'Thread%';mysq> show status like 'Connections';Threads_created details the number of threads that have been created since the MySQL server started, and Connections is the total number of client connections to the MySQL server since startup. To work out the thread cache hit ratio, we use this calculation:100 - ((Threads_created / Connections) * 100)100 - ((10 / 78298) * 100) = ~99.987 Thread cache hit ratioThe ideal situation is to get Threads_created as close as possible to thread_cache_size - no new connections having to wait for new thread allocation - staying as close to around a 99% hit ratio as you can.

mysql> show status like 'Select_full_join';

The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.ratio of disk tmp tables vrs in memory tmp tables (tmp_table_size)

Handler_read_rnd

mysql> show status like 'Handler_read_rnd%';

The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don't use keys properly.

Handler_read_rnd_next

The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

key_buffer_size

Very important if you use MyISAM tables. key_buffer_size is important for MyISAM temporary tables performance to avoid OS writes, see this page for more detail. Again, evaluate the value using the tuning-primer.sh script.

innodb_buffer_pool_size

This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM.

innodb_additional_mem_pool_size

This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

innodb_log_file_size

Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

innodb_log_buffer_size

Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory - it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

innodb_flush_logs_at_trx_commit

Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns).References

1. Performance Tuning Best Practices for MySQL, Google TechTalks April 28, 2006. http://video.google.com/videoplay?docid=2524524540025172110

2. [HowTo] Optimising MYSQL, 01-20-2005, 03:59 AM. http://interworx.info/forums/showthread.php?p=2346

3. Kaiser's 7-Step Benchmarking Process. http://www.kaiserassociates.com/about/bench_7step.html

4. How do you identify a suitable activity to benchmark? http://www.quality.co.uk/benchadv.htm

5. MySQL Benchmarking. http://mysqldatabaseadministration.blogspot.com/2006/08/mysql-benchmarking-1.html