What to Consider When Reviewing MySQL Performance



Relational databases such as MySQL are often cranky for reasons that baffle even the most seasoned database admin. One minute MySQL will be chugging along happily, and the next minute it’s having the virtual version of a hissy fit and has crawled to a complete stop.

When MySQL performance problems are (once again) driving you mad, it may help to remember that the root cause is almost always human error – either an option that was misconfigured or a workload issue. Remember, MySQL really is not out to get you personally.
But before the next big slowdown hits, try these tips to troubleshoot and tweak MySQL performance.

1. Profile the Workload

Profiling the server’s workload allows you to spot the most sluggish tasks and tune specifically to improve their response time queries. You can also see which tasks are running the most efficiently and repeatedly. Workload profiling tools you can use include MySQL Enterprise Monitor’s query analyzer or mk-query-digest. It’s much less costly and often far more effective to optimize queries than to add new hardware to the mix in the hope you’ll see improved MySQL performance. In fact, if you have time to perfect only one arcane MySQL art, learn to optimize queries.

2. Check the Resource Use

MySQL, like any power hungry application, will perform at its best in a balanced environment. The CPU and disk should be fast and memory sufficient, and they should be network efficient. Check the resource utilization of these four essential components to spot where MySQL may be getting bogged down. Take note: tossing cheap memory at this problem isn’t going to fix it. The server hardware, network, and memory all need to be optimal performers that play well with others.

3. Perform Tune Ups and Tweaks

MySQLTuner is a quick and effective way to find adjustments that you can implement to increase MySQL performance and stability. Run the script and you’ll receive the current configuration variables and status data with some basic performance suggestions. MySQLTuner does not fix issues; it delivers information. The product’s documentation warns that you should not implement any configuration change that you don’t fully understand. Test your changes on staging environments, and remember that improvements in one area can negatively affect MySQL in other areas.

4. Stay Away From the Queue

MySQL does not do well when it’s forced to deal with a work process that presents as a queue. Lining up tasks for it to do later results in slowdowns because tasks will be serialized rather than running in parallel, creating a heavier workload. Additionally, if parallel tasks need to stop and crosstalk with each other in order to accomplish anything, performance will slow.

5. Deal with Data Drift

Over time, dynamic MySQL environments will often be hindered by master and slave data that is out of synch. The unfortunate thing is that data can drift alarmingly far out of synch without producing errors or warnings. To address this, use a tool like mk-table-checksum from the Percona Toolkit for MySQL to verify the integrity of data replication, as well as to perform optimized checks on table sizing without bringing your servers to a grinding halt.

MySQL is challenging in that there will never be a one-size-fits-all answer to any single question or a single solution that fixes every problem. Each situation and its potential cure needs to be evaluated individually to see what works best in your environment. The good news is that since MySQL is open-source and very popular, there is a wealth of quality information and support available for you to leverage.

Joseph Janecka
Joseph Janecka is the Vice President of Information Systems and Technology for SalesStaff, where his responsibilities include alignment of technology systems and management of SalesStaff’s vast data assets. Joseph’s contributions to client campaigns include alignment of marketing automation, enriched sales and marketing analytics, and campaign tracking enablement – resulting in increased pipeline velocity and an enhanced level of qualification for leads and appointments delivered to SalesStaff customers. In his position, Joseph continues to execute on a comprehensive Information Technology roadmap and set the vision for the future of SalesStaff’s information systems.
Joseph Janecka

Written by Joseph Janecka


Joseph Janecka is the Vice President of Information Systems and Technology for SalesStaff, where his responsibilities include alignment of technology systems and management of SalesStaff’s vast data assets. Joseph’s contributions to client campaigns include alignment of marketing automation, enriched sales and marketing analytics, and campaign tracking enablement – resulting in increased pipeline velocity and an enhanced level of qualification for leads and appointments delivered to SalesStaff customers. In his position, Joseph continues to execute on a comprehensive Information Technology roadmap and set the vision for the future of SalesStaff’s information systems.

View all author posts →




What's Next?