August 22, 2014

Innodb Table Locks

Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and rightfully so as Innodb table locking is a bit more complicated than traditional MyISAM table locks. Let me start with some examples. First lets run SELECT Query:

As you can […]

Table locks in SHOW INNODB STATUS

Quite frequently I see people confused what table locks reported by SHOW INNODB STATUS really mean. Check this out for example:

This output gives us an impression Innodb has taken table lock on test/t1 table and many people tend to think Innodb in fact in some circumstances would abandon its row level locking and […]

How FLUSH TABLES WITH READ LOCK works with Innodb Tables

Many backup tools including Percona Xtrabackup, MyLVMBackup and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period for which server has to be made read only is very short, just few seconds, yet the impact of FLUSH TABLES WITH READ LOCK can be quite large because […]

How fast is FLUSH TABLES WITH READ LOCK?

A week or so ago at the MySQL conference, I visited one of the backup vendors in the Expo Hall. I started to chat with them about their MySQL backup product. One of the representatives told me that their backup product uses FLUSH TABLES WITH READ LOCK, which he admitted takes a global lock on […]

When (and how) to move an InnoDB table outside the shared tablespace

In my last post, “A closer look at the MySQL ibdata1 disk space issue and big tables,” I looked at the growing ibdata1 problem under the perspective of having big tables residing inside the so-called shared tablespace. In the particular case that motivated that post, we had a customer running out of disk space in his […]

mydumper [less] locking

In this post I would like to review how my dumper for MySQL works from the point of view of locks. Since 0.6 serie we have different options, so I will try to explain how they work As you may know mydumper is multithreaded and this adds a lot of complexity compared with other logical […]

Why ALTER TABLE runs faster on Percona Server 5.5 vs. MySQL 5.5

Some of us Perconians are at OpenStack summit this week in Atlanta. Matt Griffin, our director of product management, tweeted about the turbo-hipster CI talk about their experience of ALTER TABLEs running faster on Percona Server. Oracle’s Morgan Tocker then tweeted in response, asking why this was the case. I decided that the simplest way to […]

Introducing backup locks in Percona Server

TL;DR version: The backup locks feature introduced in Percona Server 5.6.16-64.0 is a lightweight alternative to FLUSH TABLES WITH READ LOCK and can be used to take both physical and logical backups with less downtime on busy servers. To employ the feature with mysqldump, use mysqldump –lock-for-backup –single-transaction. The next release of Percona XtraBackup will […]

Many-table joins in MySQL 5.6

I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good at […]

How to monitor ALTER TABLE progress in MySQL

While working on a recent support issue as a Percona Support Engineer,  I got one question from a customer asking how to monitor ALTER TABLE progress. Actually, for MySQL 5.5 and prior versions, it’s quite difficult to ALTER the table in a running production environment especially for large tables (with millions records). Because it will rebuild and lock the table […]