In my previous blog post, Understanding trx-consistency-only on MyDumper Before Removal, I talked about --trx-consistency-only removal, in which I explained that it acts like a shortcut, reducing the amount of time we have to block the write traffic to the database by skipping to check if we are going to backup any non-transactional tables.
Now, I will explain why we also remove --less-locking and what the new option is that has replaced both, improving the locking mechanism.
Merge of trx-consistency-only and less-locking
I found that --trx-consistency-only is useful when you have transactional tables only, and --less-locking is useful when you have at least one non-transactional table.
That is why I replaced both variables with --trx-tables, which by default is disabled up to version v0.19.3 but it will change on v0.19.5 (https://github.com/mydumper/mydumper/issues/1908). This means that mydumper will consider that you don’t have transactional tables and use the less-locking logic, and since v0.19.5, it will fail if non-transactional tables are found, and you will need to use --trx-tables=0.
If you know that you are going to backup transactional tables, you must use the trx-tables option, which will release the global lock after the synchronization of the threads, and since v0.19.5, it will do it automatically.
This is a diagram that describes how the main thread and the workers interact with each other:
An important task of mydumper is the “Send Global Lock” step, whose main objective is to stop all the writes to the tables that we are going to backup so that the threads can synchronize at the same point in time.
Thread sync mechanisms
When you use multiple threads to take the backup, all of them must be at the same point in time. The new option sync-thread-lock-mode sets the mode that mydumper will use to sync the threads.
The available modes are:
FLUSH TABLE WITH READ LOCK
By default, mydumper uses the simplest way. The main connection sends a FLUSH TABLE WITH READ LOCK, and the threads connect to the database.
|
1 |
--sync-thread-lock-mode=FTWRL |
Lock all tables
In cases where FLUSH TABLE WITH READ LOCK is not available or it is too expensive, we can use LOCK_ALL to lock all the tables that we are going to export.
|
1 |
--sync-thread-lock-mode=LOCK_ALL |
No locks
If you don’t want to use FLUSH TABLE WITH READ LOCK nor LOCK TABLE, you can simply disable it using –no-locks which might cause an inconsistent backup.
We removed no-locks, and now you can avoid using lock like this:
|
1 |
--sync-thread-lock-mode=NO_LOCK |
Using GTID
On Percona Server for MySQL, we can check the status variable binlog_snapshot_gtid_executed when you execute START TRANSACTION WITH CONSISTENT SNAPSHOT to check the GTID position, which mydumper uses to determine if all the threads are watching the same point in time of the database.
|
1 |
--sync-thread-lock-mode=GTID |
AUTO
This is the default, which on MySQL and Percona Server will use FTWRL and on MariaDB will use https://mariadb.com/kb/en/backup-stage/ if available.
Conclusions
All these changes gave us more control and flexibility in determining the sync mechanism. They also removed several options that could cause confusion or unexpected behaviors. This refactoring allowed us to simplify the code and add more features.
About the Author
David DucosDavid studied Computer Science in National University of La Plata and has worked as a DBA consultant since 2008. For the past 3 years he worked with a worldwide platform of free classifieds up until he joined Percona's consulting team in November 2014. David lives near Buenos Aires, Argentina and in his free time loves to spend time with his family.
