For the last year my primary production database is MySQL. We are using community version that comes precompiled with Ubuntu. It is a very good and stable product suitable for production. We buy support directly from MySQL and due to the nature of their business model, the support is excellent.
I have two issues with supporting MySQL in production: profiling and backup/restore.
Profiling MySQL is very primitive and virtually impossible. MS SQL for example, has built-in instrumentation and can provide a lot of useful real time, and with appropriate tools like Symantec I3 for databases, historical information. In the past I successfully used I3 to improve application performance of a real time ASP application for VoIP tellecomm. With MySQL all you have is show processlist, show status and show innodb status (if you are using innodb engine). There are some tools that can help analyzing the output of the commands and provide some trending information. Since we are MySQL paying customers we have access to the MySQL Enterprise Dashboard that monitors the database and the server. With this tool, I can see that the server has I/O bottle neck or had a CPU load 2 hours ago, but I have no idea which query caused it. It looks like for really deep profiling there are two options, try to write custom solutions, my colleague Chetan, did that or use DTrace with Solaris. Since Chetan as developer took the first path, I will take the second.
The second operational issue for me is backup/restore. MySQL has no API for both in versions 5.0 and 5.1. API was introduces in version 6.0 which is in alpha stage right now. There are serveral ways to hot backup MySQL with innodb database. I do not like the tool provided by innodb it self since it is slow, expensive and the license is tight to the server name. The other two options are to do sql dump of the server, not a recommended for databases bigger than 5Gb, or do LVM snapshot. LVM option doesn’t allow backup/restore of individual databases and because of that I only use it to create backup for setting up replication. So yes, right now I’m using the dump option with understanding that the restore process will take time.
July 31, 2008 at 7:24 am
We have a product to monitor MySQL called DBTuna http://www.dbtuna.com. This does not overlap too much with MySQL Enterprise, but works in a very similar way to i3 in that it gives a complete insight into historical MySQL query performance. e.g. you can find out the resource consumption profile of your database, and then drilldown into any time period to find out your top SQL Statements.
Feel free to download and trial!