MySQL Tuning with HQ

May 7th, 2008 Scott Feldstein

This past Monday, May 5th, I had the opportunity to speak at Community One on Scaling MySQL. The presentation was based on several months of work to integrate and tune MySQL in to our fastest supported database. MySQL is the fourth database I have developed on, and while working with it I have found a bunch of nice features that are simple to use which made HQ run extremely fast. Unfortunately MySQL has some big issues with their query optimizer that made the work a bit more tedious than it needed to be.

Here are my top 3 keys to tuning MySQL ->

1) Query Tuning
Always run “explain” on your queries. Make sure they use indexes where applicable and do not table scan where you can help it.
Try to avoid using tmp tables where you can in queries. If you cannot avoid them make sure the tmp table size is as small as possible.

2) Use of Indexes
Make sure you use your clustered index / primary key wisely. For a table which is written a lot don’t use too many indexes due to overhead of CRUD operations, and on the flip side for a static table make sure it has plenty of index access points and use covering indexes where applicable.

3) Know the MySQL server options and know how to monitor them to make sure they are not out of control.

innodb_buffer_pool_size
innodb_flush_log_at_trx_commit
tmp_table_size, max_heap_table_size, and max_tmp_tables
innodb_flush_method

For slow query management, make sure you monitor the tmp table status from “show global status”
Created_tmp_files, Created_tmp_tables and especially Created_tmp_disk_tables

HQ offers a very easy and effective way to facilitate this monitoring. If these status vars grow too fast make sure to turn on your slow query log and debug the slow queries. There is usually always something that can be done.

Also, feel free to check out http://support.hyperic.com/confluence/display/DOC/MySQL+DB+Preparation for our recommended options. It is not a one size fits all, but a good place to start in order to tune the MySQL server options.

HQ running on MySQL exemplifies the potential that the database has to scale any application. It still has a ways to go in terms of features to compete in all spaces, but every major release it keeps getting better.

Here is a link to my presentation from Community One:

http://download.hyperic.com/documentation/commone-mysql.ppt

Entry Filed under: HQ