MySQL Tips and Tricks
Introduction
Background
MySQL is an open source relational database management system that's been around for quite some time now. It's mostly used for backing webbased blogs, CMSes, calendars and other online collaborative tools. It's also a vital part in the so-called LAMP system architecture, where LAMP depicts:
- Linux, the base operating system kernel
- Apache, the open-source webserver software delivering applications
- MySQL, the database storage software
- PHP, the webscripting language used for writing applications
Because of this hugely popular software base, MySQL has gotten a lot of attention and users over the last years. There are many who would argue that MySQL is inferior to other open-source offerings like PostgreSQL and in a way they're right. MySQL is only recently becoming a viable software package for trusting your database to.
In this article, which will be updated over time to become a central source of information about MySQL ins and outs, I will present some techniques which will make your life easier. Especially the administration and tuning of the database server will be described.
A Poor Man's Query Profiler
When luxury is miles away
You might encounter a situation where the server system's performance is horrible and there's about twenty customers breathing heavily down your neck. You're wondering what happened since the server hardware has always kept up with demand and from one day to the next the performance dropped considerably.
There are a lot of profiling tools available which integrate nicely into applications you write, but as a DBA you often don't have access to the source code of the programs accessing your database server. Luckily, there's a really simple way to profile queries on a basic level.
No, I'm not talking about the slow query log since that requires more hassle than required. The following trick can be used without restarting MySQL. Curious? Let's go then!
Jack of all trades: mysqladmin
The real trick lies in the mysqladmin tool which offers a variety of functions. By simply running this command:
user@host$ mysqladmin
You can see the list of available commands in the lower part of the output. The one we'll be using here is the processlist command which shows all client connections and, more importantly, what each connection is doing.
Try executing the following command:
user@host$ mysqladmin -uroot -p processlist
Enter password: type the root user's password and press [ENTER] +--------+------+-----------+----+---------+------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+------+-----------+----+---------+------+-------+-----------------------+ | 100030 | root | localhost | | Query | 0 | | show full processlist | +--------+------+-----------+----+---------+------+-------+-----------------------+
As you can see, there's not much happening on my testing server. But try this on your own system and you'll see a lot of connections and for each connection you can see the query currently being processed. If the statements appear trimmed, use the '-v' command line parameter to gradually receive more of the query statement.
In the 'time' column you can see how many seconds have already been used to process the client request. This is vital for this trick: using the time column we can easily spot slow queries which take longer than five seconds. The icing on the cake comes in the form of the '--sleep' or '-i' parameter.
Piecing it all together
Using the '--sleep' or '-i' parameter the client connection table is displayed every few seconds. Using this technique you can easily spot the incrementing time columns and see which queries are taking long. For instance, try running the mysqladmin command like this:
mysqladmin -v -uroot -p -i 1 processlist
This will print out the table each second. Try this on your overloaded server and you can easily determine which queries or query categories are taking too much time. You can see the username, database and query being executed in real time.
Once you've identified the cause of the performance hit, contact the supplier of the software doing the queries so they can add missing indices or clean up some legacy data.
Binary Binlogs to ASCII
For those days you don't speak binary
This tip's definitely a short one but it could save you some hassle when you're working on your master / slave replicating system under pressure. If you ever find yourself wanting to see which data-altering queries have been executed on a system you can rely on your binlogs for all your needs.
In each binlog file there's a log of all INSERT, UPDATE and DELETE queries performed on the databases. If you open such a file in a text editor, it's kind of hard to make out what exactly went on, but there's a nice tool to convert those binary binlog files to plain ASCII SQL statements.
It's called:
mysqlbinlog
Badum-ching. If you don't know this tool exists, there you go. Just run this tool and provide on of your binlog files as an argument and you'll get a full ASCII dump of all queries. For example the following command:
mysqlbinlog test-bin.001 > testqueries.log
This will convert the full binlog file 'test-bin.001' to an ASCII SQL log in the file 'testqueries.log'.
That's all for this tip, as I stated earlier it was short but hopefully helpful.
Conclusion
Conclusion, or lack thereof
There's not much of a conclusion for this article, but I hope these tips provide useful for you. I know they've saved myself in a few situations so I'd be glad if they worked for you.
Related pages
See also the PostgreSQL Tips and Tricks page on this site.
About this article
This article was added to the site on the 15th of March 2006 and revised on the 21st of September 2007 to ensure compatibility with newer MySQL versions.