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
- FLUSH TABLES WITH READ LOCK
- START TRANSACTION WITH CONSISTENT SNAPSHOT;
- dump non-InnoDB tables
- UNLOCK TABLES
- dump InnoDB tables
- FLUSH TABLES WITH READ LOCK
- START TRANSACTION WITH CONSISTENT SNAPSHOT;
- LOCK TABLES non-InnoDB
- UNLOCK TABLES
- dump non-InnoDB tables
- UNLOCK non-InnoDB
- 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.
For the next release we will implement backup locks that will avoid us to run FTWRL.
Comments (7)
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.
Wonderful Max, Thanks for explanation!
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!
I know the in the percona5.6,the server has global status Binlog_snapshot_file/Binlog_snapshot_position ,if we use mysqldump with –single-transaction,we just need the lock un-innodb tables to enure the consist data. if we don’t care about the un-innodb tables ,we don’t need lock tables at all.. So,I think we can dump data like this:
Main Thread:
LOCK UN-INNODB TABLES WITH READ
Dump Thread X:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
LL Dump Thread X
dump non-InnoDB tables
Main Thread
UNLOCK TABLES
Dump Thread X
dump InnoDB tables
Hi caijinlong, is not so easy when you have multiple threads, as we need to coordinate them to be in the same position. This can be addressed with snapshot cloning which is also supported by PS 5.6. I just need to time to complete it.
The other detail here is that we have status vars only from master status, mydumper also gets the position from the slave status which is really useful and the only option to do this is to stop slave while we are starting the transactions. But stoping a slave is not something I can add by default to run on a production environment.
Yes,I also think use snapshot cloning is the best less lock method,multi thread share same transaction ! I also plan test the snapshot cloning replace the FTWRL。Very expect the next version mydumper use no-lock with innodb tables 。 haha
Comments are closed.
Use Percona's Technical Forum to ask any follow-up questions on this blog topic.