I have been working on MyDumper for over three years now, and I usually don’t use the tax-consistency-only feature during backups because it wasn’t an option I quite understood. So, when reviewing another issue, I stepped into a curious scenario, and I finally got it and decided to share with you what I learned and when it should be used.
What is trx-consistency-only on MyDumper?
The help doesn’t say much:
|
1 |
--trx-consistency-only Transactional consistency only |
And the explanation from here says:
You can think of this as –single-transaction for mysqldump, but still with binlog position. Obviously this position only applies to transactional tables (TokuDB included). One of the advantages of using this option is that the global read lock is only held for a short amount of time at the start of execution for the threads and binlog coordination, so it’s released as soon as the transactions are started.
The last sentence is the most important information because it gives you an idea that the global read lock will be released before. But why? Why don’t we always release the lock at this point? In order to understand why we need to hold a global read lock, we need to understand how the thread coordination works.
In order to sync all the threads, MyDumper needs to stop the database writes. The simplest way to block writes is using FLUSH TABLE WITH READ LOCK (FTWRL) on MySQL and Percona Server, and you can also use BACKUP STAGE on MariaDB. We need to block the writes so all MyDumper threads can execute START TRANSACTION WITH CONSISTENT SNAPSHOT (STWCS) at the same time to get a consistent backup.
Why do we have trx-consistency-only then?
After executing STWCS in all the threads, we could release the FTWRL, but before doing so, we must collect table information, such as the Storage Engine, as we might need to hold FTWRL until we back up the non-transactional tables. However, if you know that you are going to export only from transactional storage engines, then we could release FTWRL before getting the information about the tables.
And here comes the important information: As with single-transaction, trx-consistency-only “Works ONLY for tables stored in storage engines which support multi-versioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines.” Basically, trx-consistency-only is letting MyDumper know that we are going to backup transactional tables ONLY, and we are not checking or informing if we found any non-transactional tables.
Is this the best way to coordinate the threads?
MyDumper implements an option to take consistent backups without blocking. However, it has requirements. It is available from Percona Server for MySQL 5.7, and you need to enable GTID. It doesn’t use FTWRL, which is always good to avoid, and it checks binlog_snapshot_gtid_executed per thread to confirm the coordination between all the threads.
Conclusions
If a DBA knows that all tables are transactional, we can think that trx-consistency-only 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. In the next blog post, I will explain why we removed trx-consistency-only and how we replaced it.
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.
