Ten essential tools for MySQL admins
Free and open source tools to monitor and extend your MySQL performance
By Daniel Nichter | InfoWorld | Published: 14:08, 01 August 2011
MySQL is a complex system that requires many tools to repair, diagnose and optimise. Fortunately for admins, MySQL has attracted a vibrant community of developers who are putting out high quality open source tools to help with the complexity, performance and health of MySQL systems, most of which are available for free.
The following 10 open source tools are valuable resources for anyone using MySQL, from a standalone instance to a multiple-node environment. The list has been compiled with variety in mind. You will find tools to help back up MySQL data, increase performance, guard against data drift and log pertinent troubleshooting data when problems arise.
There are several reasons why you should consider these tools instead of creating your own in-house tools.
First, thanks to their wide use, they're mature and field tested. Second, because they are free and open source, they benefit from the knowledge and experience of the continually expanding MySQL community. Finally, these tools are actively developed, and many are professionally supported (either for free or commercially), so they continue to improve and adapt with the evolving MySQL industry.
Keep in mind that there are many more tools worthy of your attention. I have chosen to emphasise free and open source, and to err on the side of usefulness and usability. Also note that all are Unix command-line programs but one, in large part because MySQL is more widely deployed and developed on Unix systems. If I missed a favourite, feel free to highlight it in the comments below.
Now, let's meet the first of the 10 essential MySQL tools.
Nothing frustrates like slow MySQL performance. All too often faster hardware is thrown at the problem, a solution that works only if hardware is in fact to blame.
More often than not, poor performance can be attributed to slowly executing queries that are blocking other queries, creating a ripple effect of slow response times. Since it's a lot cheaper to optimise queries than to upgrade hardware, the logical first step in MySQL optimisation is query log analysis.
Database administrators should analyse query logs frequently, depending on the volatility of the environment. And if you've never performed query log analysis, it's time to start, even if you are relying on third party software, which is often assumed to be optimised when in fact it is not.
Today's best query log analyser is mk-query-digest. Co-written by Baron Schwartz and myself, it is actively developed, fully documented and thoroughly tested. MySQL distributions include the query log analyser mysqldumpslow, but the tool is outdated, poorly documented and untested. Other query log analyzers like mysqlsla, which I wrote several years ago, suffer the same problems as mysqldumpslow.
mk-query-digest analyses query logs and generates reports with aggregated, statistical information about execution times and other metrics. Since query logs usually contain thousands, if not millions of queries, query log analysis requires a tool.
mk-query-digest can help you find the queries that take the longest time to execute as compared to other queries. Optimising these slow queries will make MySQL run faster by reducing the greatest delays. The real art of query optimisation is more nuanced, but the basic goal is the same. Find slow queries, optimise them and increase response times.
The tool is easy to use, executing mk-query-digest slow-query.log will print the slowest queries in slow-query.log. The tool includes support for "query reviews," for reporting queries you have not yet seen or approved, making frequent log analyses quick and efficient.
Maintainers: Daniel Nichter and Baron Schwartz
Being able to generate data dumps quickly is vital for backups and server cloning. Unfortunately, mysqldump, which ships with MySQL distributions, is single-threaded and thus too slow for data-intensive jobs. Thankfully, the modern replacement, mydumper, uses multiple threads, making it 10 times as faster than mysqldump.
Also known as MySQL Data Dumper, this tool does not manage backup sets, differentials, or other parts of a complete backup plan. It just dumps data from MySQL as quickly as possible, enabling you to complete backups under tight time constraints, such as overnight, while employees are offline, or to perform backups more frequently than you would with mysqldump.
One technical point to know about mydumper is that it locks tables, so it is not the ideal tool for performing backups during operating hours. Then again, professional data recovery costs hundreds of dollars per hour, and you always get a bill even if the data isn't recoverable. mydumper is free and well worth exploring for even basic backups.
mydumper also comes in handy when cloning servers. Other tools perform complete hard drive duplications, but when all you need is MySQL data, mydumper is the fastest way to get it. Servers provisioned in a cloud are particularly suited to cloning using mydumper. Just dump your MySQL data from an existing server and copy it to the new instance.
Cloning is worthwhile for creating slave servers, benchmarking and profiling, but nowhere is it more vital than in testing and development. Being able to spin up a replica for quick testing before going live is essential for dynamic MySQL environments. With mydumper, you can quickly create a server that is nearly identical to your production server, enabling your test results to better mimic production results.
Maintainers: Domas Mituzas, Andrew Hutchings, Mark Leith
If your databases are in use every day, all day, giving you no "overnight" during which tables can be locked for backup, xtrabackup is your solution. Also known as Percona XtraBackup, this tool performs non-blocking backups and is the only free, open source tool that can do this. By comparison, proprietary non-blocking backup software can cost more than £3,000 per server.
xtrabackup also offers incremental backups, allowing you to back up only the data that has changed since the last full backup. Adding incremental backups to your backup process is powerful, given the reduced performance hit of these tremendously smaller backups.
Furthermore, another project has grown up around xtrabackup that makes managing a full backup plan even easier: xtrabackup-manager. Although this tool is new and still in development, it holds a lot of potential because it offers advanced features like rotating backups, with groups and backup set expiring. Together, xtrabackup and xtrabackup-manager are a formidable and free backup solution.
Download xtrabackup: http://www.percona.com/software/percona-xtrabackup/downloads/
Download xtrabackup-manager: http://code.google.com/p/xtrabackup-manager/
Maintainer: Lachlan Mulcahy
tcprstat is probably the most esoteric of the 10 on this list. The tool monitors TCP requests and prints statistics about low-level response times. When you become familiar with the response time way of thinking about performance, the payoff of tcprstat is significant.
The principle is elaborated in the book "Optimising Oracle Performance" by Cary Millsap and Jeff Holt, and it applies equally well to MySQL. The basic idea is that a service, in this case MySQL, accepts a request, fulfills that request and responds with results. The service's response time is the time span between receiving a request and sending a response. The shorter the response time, the more requests can be served in the same amount of time.
Parallel processing and other low-level factors play a significant part here, but the simplified upshot is that there are 28,800 seconds in an 8 hour workday, so reducing response times by just four-tenths of a second (from 0.5 to 0.1 second) results in 230,400 more requests served each day. tcprstat helps you achieve this.
I have only enough space in this article to pique your curiosity, so I'll finish this tool's introduction by telling you the first step toward getting started with MySQL response time optimization. Read "Optimising Oracle Performance." Then start using tcprstat.