FTWRL on MyDumper Removed
source link: https://www.percona.com/blog/ftwrl-on-mydumper-removed/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Percona Database Performance Blog
The title is not entirely true, but ‘FTWRL on MyDumper is not needed anymore for consistent backups’ was a long title. One more time, I wanted to share a new feature in MyDumper. This is related to an important piece: the locking mechanism that mydumper uses to sync all the threads.
MyDumper was born because, at that time, we didn’t have a tool that could take a consistent logical backup using multiple threads. Syncing all the threads was one of the problems, which has been solved using FLUSH TABLE WITH READ LOCK (FTWRL), til all the threads execute START TRANSACTION WITH CONSISTENT SNAPSHOT (STWCS), then we release the FTWRL and all the threads are in sync. We all know that FTWRL is very expensive and difficult to acquire on some database workloads.
I started to think about alternatives to avoid using FTWRL, and my first thought was, why don’t we use the SHOW ENGINE INNODB STATUS to check if all the threads are at the same point in time? That is doable but I didn’t like it, as threads know at what point in time they are!
I asked internally at Percona and I got my answer: https://jira.percona.com/browse/PS-4464. Since Percona Server for MySQL versions 8.0.19-10 and 5.7.30-33, we have a status variable that shows the last GTID executed in our STWCS which was all I needed.
I worked on the solution, which required some testing with high write traffic and forcing infrequent scenarios in real life.
How does it work?
On the next MyDumper release, when you use –no-locks, you can get a consistent backup anyway:
# ./mydumper -B myd_test -o data -v 4 --no-locks ** Message: 12:29:42.333: Thread 2: binlog_snapshot_gtid_executed_status_local succeeded with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2180547'. ** Message: 12:29:42.333: All threads in the same position. This will be a consistent backup. ** Message: 12:29:42.333: Thread 3: binlog_snapshot_gtid_executed_status_local succeeded with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2180547'. ** Message: 12:29:42.333: All threads in the same position. This will be a consistent backup. ** Message: 12:29:42.333: Thread 1: binlog_snapshot_gtid_executed_status_local succeeded with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2180547'. ** Message: 12:29:42.333: All threads in the same position. This will be a consistent backup. ** Message: 12:29:42.333: Thread 4: binlog_snapshot_gtid_executed_status_local succeeded with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2180547'. ** Message: 12:29:42.333: All threads in the same position. This will be a consistent backup. |
In the log, you will see a line per thread letting you know the thread GTID position and confirming that all the threads are at the same point in time. We get the GTID from the execution of SHOW STATUS LIKE 'binlog_snapshot_gtid_executed' per thread, this value is compared with a global shared variable and if they are not the same, it fails, and mydumper is going to try five times to sync, and inform you the result:
** Message: 12:32:48.968: Thread 2: All threads in same pos check ** Message: 12:32:48.971: Thread 3: All threads in same pos check ** Message: 12:32:48.972: Thread 4: All threads in same pos check ** Message: 12:32:48.973: Thread 1: binlog_snapshot_gtid_executed_status_local failed with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2196157'. ** Message: 12:32:48.973: Thread 2: binlog_snapshot_gtid_executed_status_local failed with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2196164'. ** Message: 12:32:48.973: Thread 3: binlog_snapshot_gtid_executed_status_local failed with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2196165'. ** Message: 12:32:48.973: Thread 4: binlog_snapshot_gtid_executed_status_local failed with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2196165'. ** Message: 12:32:48.975: Thread 1: All threads in same pos check ** Message: 12:32:48.975: Thread 2: All threads in same pos check ** Message: 12:32:48.977: Thread 4: All threads in same pos check ** Message: 12:32:48.980: Thread 3: All threads in same pos check ** Message: 12:32:48.980: Thread 1: binlog_snapshot_gtid_executed_status_local succeeded with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2196167'. ** Message: 12:32:48.980: All threads in the same position. This will be a consistent backup. ** Message: 12:32:48.980: Thread 2: binlog_snapshot_gtid_executed_status_local succeeded with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2196167'. ** Message: 12:32:48.980: All threads in the same position. This will be a consistent backup. ** Message: 12:32:48.980: Thread 4: binlog_snapshot_gtid_executed_status_local succeeded with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2196167'. ** Message: 12:32:48.980: All threads in the same position. This will be a consistent backup. ** Message: 12:32:48.980: Thread 3: binlog_snapshot_gtid_executed_status_local succeeded with gtid: '498aa664-fd29-11ec-a793-0800275ff74d:1-2196167'. ** Message: 12:32:48.980: All threads in the same position. This will be a consistent backup. |
Remember that you will need to enable GTID and the binlogs to get the value of binlog_snapshot_gtid_executed which is the status variable that we use.
Conclusion
With this new feature, you will be able to reduce the contention caused by mydumper because of the usage of FTWRL. So, starting from the next release, you will be able to use –no-locks if you don’t need any DDL locking mechanism.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK