November 26, 2014

Recovery after DROP & CREATE

In a very popular data loss scenario a table is dropped and empty one is created with the same name. This is because  mysqldump in many cases generates the “DROP TABLE” instruction before the “CREATE TABLE”:

If there were no subsequent CREATE TABLE the recovery would be trivial. Index_id of the PRIMARY index of […]

The relationship between Innodb Log checkpointing and dirty Buffer pool pages

This is a time-honored topic, and there’s no shortage of articles on the topic on this blog. I wanted to write a post trying to condense and clarify those posts, as it has taken me a while to really understand this relationship. Some basic facts Most of us know that writing into Innodb updates buffer […]

Bug#12704861

As Mark pointed out, there isn’t a lot of detail in the release notes about what could potentially be a very serious problem that is fixed in MySQL 5.1.60. I’ll repeat here the full documentation from the release notes: “InnoDB Storage Engine: Data from BLOB columns could be lost if the server crashed at a precise […]

How to debug long-running transactions in MySQL

Among the many things that can cause a “server stall” is a long-running transaction. If a transaction remains open for a very long time without committing, and has modified data, then other transactions could block and fail with a lock wait timeout. The problem is, it can be very difficult to find the offending code […]

Faster MySQL failover with SELECT mirroring

One of my favorite MySQL configurations for high availability is master-master replication, which is just like normal master-slave replication except that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the best general-purpose way to get fast failover and a bunch of other benefits (non-blocking ALTER […]

Recovering Innodb table Corruption

Assume you’re running MySQL with Innodb tables and you’ve got crappy hardware, driver bug, kernel bug, unlucky power failure or some rare MySQL bug and some pages in Innodb tablespace got corrupted. In such cases Innodb will typically print something like this: InnoDB: Database page corruption on disk or a failed InnoDB: file read of […]

How to estimate time it takes Innodb to Recover ?

Today seems to be Innodb day in our Blog, but well this is the question which pops ups quite frequently in Innodb talks and during consulting engagements. It is well known to get better performance you should normally set innodb_log_file_size large. We however usually recommend caution as it may significantly increase recovery time if Innodb […]

ORDER BY … LIMIT Performance Optimization

Suboptimal ORDER BY implementation, especially together with LIMIT is often the cause of MySQL Performance problems. Here is what you need to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY with LIMIT is most common use of ORDER BY in interactive applications with large data sets being sorted. On many […]

SHOW INNODB STATUS walk through

Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about […]