MySQL 백업을 할 때 사용하는 XtraBackup tool의 락 경합에 의해 일어날 수 있는 이슈에 대해 알아보자.
Single 구성 뿐만 아니라 Master-Slave에서도 일어날 수 있다
테스트 한 환경은 MySQL 5.7과 XtraBackup 2.4이다
XtraBackup 스크립트 작성
간단하게 백업을 실행하는 스크립트를 아래와 같이 작성 후 실행
#!/bin/bash
# test.sh
xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/data/backups/mysql/
실행 로그
201016 14:00:11 >> log scanned up to (4633666800) 201016 14:00:11 [01] ...done 201016 14:00:11 [01] Copying ./test/pg_category_mst.ibd to /data/backups/mysql/wtpayweb/test/pg_category_mst.ibd 201016 14:00:11 [01] ...done 201016 14:00:11 [01] Copying ./test/wm_log.ibd to /data/backups/mysql/test/wm_log.ibd 201016 14:00:12 [01] ...done ...skipping... 201016 14:00:13 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 201016 14:00:13 Executing FLUSH TABLES WITH READ LOCK...
락 경합에 의해 발생하는 이슈
1. Replication lag
- FLUSH TABLES 명령어는 meta data lock을 요구하므로 앞단의 쿼리가 끝날 때 까지 대기한다
# Master에서 테이블 생성 mysql> SELECT COUNT(1) SlaveThreadCount FROM information_schema.processlist WHERE user='system user'; +------------------+ | SlaveThreadCount | +------------------+ | 0 | +------------------+ 1 row in set (0.01 sec) mysql> CREATE TABLE lock_test ( -> id int not null auto_increment, -> title varchar(50), -> primary key(id) -> ) engine=innodb; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO lock_test (title) VALUES ('First title'); Query OK, 1 row affected (0.02 sec) # Slave에서 20초 걸리는 SELECT실행 connection-1> SELECT *, SLEEP(20) FROM lock_test; # 백업을 진행하고 있는 프로세스에서 아래의 명령어가 실행되었다고 가정 connection-2> FLUSH NO_WRITE_TO_BINLOG TABLES; # Master에서 Insert mysql> INSERT INTO lock_test (title) VALUES ('Second title'); Query OK, 1 row affected (0.01 sec) # Slave processlist connection-3> SHOW PROCESSLIST; +----+-------------+-----------+------------+---------+-------+----------------------------------+-------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------------+---------+-------+----------------------------------+-------------------------------------------------------+ | 20 | system user | | NULL | Connect | 80358 | Waiting for master to send event | NULL | | 22 | system user | | test | Connect | 5 | Waiting for table flush | INSERT INTO lock_test (title) VALUES ('Second title') | | 28 | root | localhost | test | Query | 12 | User sleep | SELECT *, SLEEP(20) FROM lock_test | | 29 | root | localhost | NULL | Query | 11 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES | | 30 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST | +----+-------------+-----------+------------+---------+-------+----------------------------------+-------------------------------------------------------+ 5 rows in set (0.00 sec) connection-3> SHOW PROCESSLIST; +----+-------------+-----------+------------+---------+-------+----------------------------------+-------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------------+---------+-------+----------------------------------+-------------------------------------------------------+ | 20 | system user | | NULL | Connect | 80363 | Waiting for master to send event | NULL | | 22 | system user | | test | Connect | 10 | Waiting for table flush | INSERT INTO lock_test (title) VALUES ('Second title') | | 28 | root | localhost | test | Query | 17 | User sleep | SELECT *, SLEEP(20) FROM lock_test | | 29 | root | localhost | NULL | Query | 16 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES | | 30 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST | +----+-------------+-----------+------------+---------+-------+----------------------------------+-------------------------------------------------------+ 5 rows in set (0.00 sec) connection-3> SHOW PROCESSLIST; +----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------+ | 20 | system user | | NULL | Connect | 80403 | Waiting for master to send event | NULL | | 22 | system user | | NULL | Connect | 50 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 28 | root | localhost | test | Sleep | 57 | | NULL | | 29 | root | localhost | NULL | Sleep | 56 | | NULL | | 30 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST | +----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------+ 5 rows in set (0.00 sec)
2. 접속 지연
- 1번과 같은 상황에서 DB 접속을 하기 위해선 "-A" 옵션 지정해야 한다. 미지정 시 대기상태에 빠진다
[root@346ecc2d8c40 /]# mysql -uroot -p test_db_01
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
...3. Select waiting
- 1번과 같은 상황에서 동일한 테이블에 대한 SELECT도 대기상태에 빠진다.
connection-4> SELECT * FROM my_table LIMIT 1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) connection-4> SELECT * FROM lock_test LIMIT 1; connection-5> SHOW PROCESSLIST; +----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------------------------+ | 20 | system user | | NULL | Connect | 83315 | Waiting for master to send event | NULL | | 22 | system user | | NULL | Connect | 2962 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 28 | root | localhost | test | Query | 31 | User sleep | SELECT *, SLEEP(20) FROM lock_test | | 29 | root | localhost | NULL | Query | 28 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES | | 33 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST |
| 37 | root | localhost | test | Query | 12 | Waiting for table flush | SELECT * FROM lock_test LIMIT 1 |
+----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------------------------+ 6 rows in set (0.01 sec) mysql> SHOW PROCESSLIST; +----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------------------------+ | 20 | system user | | NULL | Connect | 83323 | Waiting for master to send event | NULL | | 22 | system user | | NULL | Connect | 2970 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 28 | root | localhost | test | Query | 39 | User sleep | SELECT *, SLEEP(20) FROM lock_test |
| 29 | root | localhost | NULL | Query | 36 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES | | 33 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST |
| 37 | root | localhost | test | Query | 20 | Waiting for table flush | SELECT * FROM lock_test LIMIT 1 |
+----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------------------------+ 6 rows in set (0.00 sec) mysql> SHOW PROCESSLIST; +----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------+ | 20 | system user | | NULL | Connect | 83516 | Waiting for master to send event | NULL | | 22 | system user | | NULL | Connect | 3163 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 28 | root | localhost | test | Sleep | 232 | | NULL |
| 29 | root | localhost | NULL | Sleep | 229 | | NULL | | 33 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST |
| 37 | root | localhost | test | Sleep | 213 | | NULL |
+----+-------------+-----------+------------+---------+-------+-----------------------------------------------------------------------------+------------------+ 6 rows in set (0.00 sec)
대책
1. xtrabackup 옵션인 "ftwrl-wait-timeout"을 설정하여 방지
2. MySQL 8.0부터 서포트된 "LOCK INSTANCE FOR BACKUP"을 사용
# Refer
https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/xbk_option_reference.html
https://dev.mysql.com/doc/refman/5.7/en/flush.html
https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html
댓글 없음:
댓글 쓰기