Apr 7, 2011

my.cnf and MySQL Tuning

You have an urgent performance case in a MySQL system and you have to get several low hanging fruits fast. What should you do?

MySQL Tuner is Your First Try!
MySQL Tuner is an open source Perl script that analyzes your system and let you know regarding hot spots. You can find a nice example of its results bellow:

-------- Performance Metrics -------------------------------------------------
[--] Total buffers: 34.0M global + 2.6M per thread (1000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.6G (16% of installed RAM)
[OK] Slow queries: 0% (141/99M)
[!!] Highest connection usage: 100%  (1001/1000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/5.0G

my.cnf is the First Answer!
my.cnf, MySQL configuration file is probably the first place to check. In this file you configure the small items that make the difference between a local store webpage to the next hit in the web application. The first place to take a look at is your my.cnf file that is located at /etc/my.cnf.

Where Can I Find my.cnf Examples?
Lucky us, MySQL provides out of the box 5 my.cnf best practices for different machine size. These files are located at /usr/share/doc/mysql-server-5.X.XX/ and in includes: my-small.cnf, my-medium.cnf, my.large.cnf, my-huge.cnf and my-innodb-heavy-4G.cnf. Yet, although the naming, these configurations are targeted to small systems and none will fits even a 16GB RAM machine (How large? take a look at the comment in the head of my-huge.cnf: "This is for a large system with memory of 1G-2G").

So What Should I Do?
Go over the sample configurations and analyze what you need to optimize to meet your actual needs. Don't forget to check your current system configuration using SHOW VARIABLES before getting into the cold water. Some of the following hints will help you meet your target:
  1. key-buffer: 
    1. Check using SHOW GLOBAL STATUS that the key_reads/key_read_requests ratio is less than 0.01 to avoid disk fetch.
    2. If you fail to meet this ratio, enlarge the key-buffer parameter.
  2. sort_buffer_size: 128-512MB, will be allocated for sort by each thread.
  3. read_buffer_size: 8M, will be allocated for sequential scan by each thread.  
  4. write_buffer_size: 8M, will be allocated for plain index scans by each thread.
  5. thread_concurrency: should be equal to 2XAvailable Cores.
What Next?
Well it's time to scan the SQL statements that takes too long:
  1. Enable the slow query log in the my.cnf: log-slow-queries=/home/mysqld-slow.log 
  2. Set the minimum processing time to be included in the log: long_query_time = 1
  3. Analyze the results: tail -n 1000 /home/mysqld-slow.log
  4. Verify there is no "connection leakage": there are not too many open connections in the database. Check it using SHOW PROCESSLIST;
  5. Analyze locking using SHOW STATUS LIKE 'Table%'; and SHOW ENGINE INNODB STATUS if you use InnoDB as your database engine.
Keep Performing,


Intense Debate Comments

Ratings and Recommendations