MySQL Performance Tuning
Information about MySQL performance tuning gathered around the web.
Table of Contents
1 Tools
2 Query Cache
- will not always increase performance
- don't size excessively large
- size in tens of MB usually beneficial (source)
mysqltuner.plsays "Increasing the query_cache size over 128M may reduce performance"
- systems with frequent
INSERTstatements might benefit less due to constant cache invalidation
show variables like ‘query%’;
show status like ‘qc%’;
- see also MySQLQuery Cache on MySQL Performance Blog
3 Key Buffer
- between 25% and 50% of total memory on dedicated DB server
- smaller if less indices
- check key reads
mysqladmin extended-status -uadmin -p -r -i 10 | grep Key_reads
- determine key hit ratio (closer to 100 is better)
100 – (key_reads * 100 / key_read_requests)
- percentage of buffer in use
100 – ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )
- get values from
SHOW VARIABLES LIKE ‘key_%’;SHOW STATUS LIKE ‘Key_%’;
4 Indices
- find missing indices through slow query log and
EXPLAIN
- maximum one index per query (the one with less rows)
5 Join Buffer Size
6 OPTIMIZE TABLE on fragmented tables
- query to find fragmented tables
SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024*1024 ), 2), 'MB') DATA, CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'MB')FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free < 0;
- rule of thumb for outage time
Table Size is KB Seconds of outage Table Size is MB Minutes of outage Table Size is GB Hours of outage
- fix with
optimize table <table_name>;oralter table <table_name> = INNODB;