Database Server Tuning Tip

A client recently contracted me to perform database tuning for their Website's online transactional processing (OLTP) db. They were experiencing performance issues as their dataset has grown, and were looking at a looming size increase of nearly 10x. Their MySQL database server was Linux based, running in a virtual machine with a local datastore. Using htop, iostat, sar, and innotop, I found a case of heavy IO contention causing concurrent queries to stack up.

On this client's master db server, ordinarily light-weight operations were taking significantly longer to perform. Upon closer analysis, I found that IO operations such as file handle allocation and fsyncs were taking unusually long to complete, resulting in IO wait. On a database server, this causes cascading slowdowns and failures.

Like the Kenny Rogers song goes, "You've got to know when to hold 'em, know when to fold 'em..." This is as true for database tuning as it is for poker. A quick run of sysbench revealed poor IO performance and extraordinarily high IO wait for the entire system. I had to tell this client that what they needed was better hardware, not database server and query tuning.

Don't put the cart before the horse!

Something to remember when approaching performance issues: understand your baseline benchmarks before attempting specialized tuning.