November 21, 2014

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 […]

OpenStack users shed light on Percona XtraDB Cluster deadlock issues

I was fortunate to attend an Ops discussion about databases at the OpenStack Summit Atlanta this past May as one of the panelists. The discussion was about deadlock issues OpenStack operators see with Percona XtraDB Cluster (of course this is applicable to any Galera-based solution). I asked to describe what they are seeing, and as […]

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 […]

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 […]

Percona Server: Thread Pool Improvements for Transactional Workloads

In a previous thread pool post, I mentioned that in Percona Server we used an open source implementation of MariaDB’s thread pool, and enhanced/improved it further. Below I would like to describe some of these improvements for transactional workloads. When we were evaluating MariaDB’s thread pool implementation, we observed that it improves scalability for AUTOCOMMIT […]

LVM read performance during snapshots

For the same customer I am exploring ZFS for backups, the twin server is using regular LVM and XFS. On this twin, I have setup mylvmbackup for a more conservative backup approach. I quickly found some odd behaviors, the backup was taking much longer than what I was expecting. It is not the first time […]

How to STOP SLAVE on Amazon RDS read replica

We are doing a migration from Amazon RDS to EC2 with a customer. This, unfortunately, involves some downtime – if you are an RDS user, you probably know you can’t replicate an RDS instance to an external server (or even EC2). While it is annoying, this post isn’t going to be a rant on how […]

Logging Deadlock errors

The principal source of information for InnoDB diagnostics is the output of SHOW ENGINE INNODB STATUS but there are some sections that are not very useful. For example, LATEST DETECTED DEADLOCK only shows, as the name implies, the latest error detected. If you have 100 deadlocks per minute you will be able to see only […]

thread_concurrency doesn’t do what you expect

Over the last months I’ve seen lots of customers trying to tune the thread concurrency inside MySQL with the variable thread_concurrency. Our advice is: stop wasting your time, it does nothing on GNU/Linux Some of the biggest GNU/Linux distributions includes the variable thread_concurrency in their my.cnf file by default. One example is Debian and its […]