Mydumper [less] locking_MySQL
Related MicroZone Resources
"AppOps": Power to the Devs!
FREE Top 10 Java Performance Problems EBook
Ushering in a New Era of APM for the Enterprise
Performance in the Enterprise: A Survival Guide
4 Success stories of APM for E-commerce
Like this piece? Share it with your friends:
|More
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 implementbackup locksthat will avoid us to run FTWRL.
The postmydumper [less] lockingappeared first onMySQL Performance Blog.
Published at DZone with permission ofPeter Zaitsev, author and DZone MVB. (source)
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)
Tags:- MySQL
- Tips and Tricks
- Performance