I’m using innotop again every day, for the first time in a few years. I found that I didn’t like the tool that the younger and less experienced version of me created. It is very flexible and has the ability to surface a lot of information about MySQL, but not all on one screen. I wanted a “single pane of glass” health dashboard for the servers I’m monitoring, instead of having to look on various screens for important bits of information.
Ensuring that everyone has a chance to attend Percona’s training for MySQL, we are announcing the following dates for Europe. You can always find the current schedule at Percona Training.
Killing long-running idle transactions is a good way to increase the uptime of a MySQL server. This may sound strange, but open transactions will eventually bring the server down, and it is better to hurt a single application than the many that will be hurt when that happens.
Long-running idle transactions are usually caused by a programmer mistake or an unexpected condition that causes an application not to be able to do its work. The potential number of sources for such problems is unlimited, so it’s virtually impossible to prevent long-running transactions.
Recently I had a case when a customer deleted the InnoDB main table space – ibdata1 – and redo logs – ib_logfile*.
MySQL keeps InnoDB files open all the time. The following recovery technique is based on this fact and it allowed to salvage the database.
Actually, the files were deleted long time ago – 6 months or so. As long as file is open physically it still exits in the file system and reachable to processes which have opened it.
I’m hosting MySQL Indexes: Best Practices Webinar next week, Wednesday August 15. In which I will talk about how MySQL indexes are structured and how they can be used, as well as go over the best practices in selecting correct indexes for your database workloads.
The use of tmpfs/ramfs as /tmp partition is a common trick to improve the performance of on-disk temporary tables. Servers usually have less RAM than disk space so those kind of partitions are very limited in size and there are some cases were we can run out of space.
Let’s see one example. We’re running a typical master-slave replication with ROW based binary log file. The application connects to the database using persistent connection technique to avoid creating new connections for every request so it just recycle old connections.