EmergencyEMERGENCY? Get 24/7 Help Now!

mydumper [less] locking

 | June 13, 2014 |  Posted In: Insight for DBAs, MySQL

PREVIOUS POST
NEXT POST
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 backup tools as it also needs to coordinate all threads with the same snapshot to be consistent. So let review how mydumper does this with the default settings.

By default mydumper uses 4 threads to dump data and 1 main thread

Main Thread
  • FLUSH TABLES WITH READ LOCK
Dump Thread X
  • START TRANSACTION WITH CONSISTENT SNAPSHOT;
  • dump non-InnoDB tables
Main Thread
  • UNLOCK TABLES
Dump Thread X
  • dump InnoDB tables
As you can see in this case we need FTWRL for two things, coordinate transaction’s snapshots and dump non-InnoDB tables in a consistent way. So we have have global read lock until we dumped all non-InnoDB tables.
What less locking does is this:
Main Thread
  • FLUSH TABLES WITH READ LOCK
Dump Thread X
  • START TRANSACTION WITH CONSISTENT SNAPSHOT;
 LL Dump Thread X
  • LOCK TABLES non-InnoDB
Main Thread
  • UNLOCK TABLES
 LL Dump Thread X
  • dump non-InnoDB tables
  • UNLOCK non-InnoDB
Dump Thread X
  • dump InnoDB tables

So now the global read lock its in place until less-locking threads lock non-InnoDB tables, and this is really fast. The only downsize is that it uses double the amount of threads, so for the default (4 threads) we will end up having 9 connections, but always 4 will be running at the same time.

Less-locking really helps when you have MyISAM or ARCHIVE that are not heavily updated by production workload, also you should know that LOCK TABLE … READ LOCAL allows non conflicting INSERTS on MyISAM so if you use that tables to keep logs (append only) you will not notice that lock at all.

For the next release we will implement backup locks that will avoid us to run FTWRL.

PREVIOUS POST
NEXT POST
Max Bubenick

Max Bubenick has been working with MySQL for more than 10 years. Before joining Percona's Remote DBA team in 2013 he worked as lead DBA for one of the biggest social gaming companies at that time. Also he maintains mydumper.

4 Comments

  • Max,

    In this case we’re locking all non-Innodb tables… I wonder what does it mean in practice and how it plays with servers which have very large amount of tables…. Do you do information_schema query as part of that logical operation to discover all those tables ? Do we when have one very large LOCK TABLES statement to lock them all ?

  • Peter,

    It get all tables from SHOW TABLE STATUS, then it add those to different queues, one for innodb (and tokudb was recently added) and other for non-innodb. Later all the non-innodb tables are distributed by the number of threads tacking care of their sizes, and then each thread lock it’s own tables at the beginning. So is not one LOCK TABLES but one per thread. After each thread got the locks FTWRL is released.

  • Why I didn’t use mydumper is mainly lack of ‘–single-transaction’ in mysqldump,since involving ‘–less-dumper’ option,I have no reason not institute mysqldump,haha!

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.