Xtrabackup 사용 시 주의사항

Xtrabackup 은 mysqldump 대신 많이 사용되는 오픈소스 hotbackup utility 입니다.
널리 사용되는 유틸리티라서 사용하는데에 큰 이슈는 없지만 다만 사용할 때 꼭 기억해둬야할 주의점이 있습니다.
Xtrabackup은 백업 수행이후 FLUSH TABLES WITH READ LOCK을 수행하는데 이 때 실행되는 쿼리가 있는 경우 flush 구문은 테이블락을 획득하지 못하고 waiting하며
lockwait timeout 이후 세션이 종료되어 백업이 완료되지 못하거나 backup이 비정상적으로 길어지는 현상이 발생할 수 있다는 점입니다.

백업서버에서 롱 쿼리 수행

mysql> select a,b,c,count(*) from tb_test group by a,b,c order by a,b,c desc ;

동시에 xtrabackup 수행

[testuser ]$ innobackupex --defaults-file=/home1/testuser/db/mysql/my.cnf --user bk_user --password test --socket /home1/testuser/db/mysql/tmp/mysql.sock --slave-info --backup .
.
.
.
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 21 for sys/sys_config, old maximum was 0
201213 15:39:24 [01] Copying ./ibdata1 to /home1/testuser/bk_Test/2020-12-13_15-39-23/ibdata1
201213 15:39:24 >> log scanned up to (2905973944)


201213 15:39:25 >> log scanned up to (2905973944)
201213 15:39:26 [01]        ...done
201213 15:39:26 [01] Copying ./sys/sys_config.ibd to /home1/testuser/bk_Test/2020-12-13_15-39-23/sys/sys_config.ibd
.
.
.
201213 15:39:35 >> log scanned up to (2905973944)
201213 15:39:36 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
201213 15:39:36 >> log scanned up to (2905973944)
201213 15:39:37 >> log scanned up to (2905973944)
201213 15:39:38 >> log scanned up to (2905973944)
201213 15:39:39 >> log scanned up to (2905973944)
201213 15:39:40 >> log scanned up to (2905973944)
201213 15:39:41 >> log scanned up to (2905973944)
201213 15:39:42 >> log scanned up to (2905973944)
201213 15:39:43 >> log scanned up to (2905973944)
201213 15:39:44 >> log scanned up to (2905973944)
201213 15:39:45 >> log scanned up to (2905973944)
201213 15:39:46 >> log scanned up to (2905973944)
201213 15:39:47 >> log scanned up to (2905973944)
.
.
.

=> Xtrabacup은 innodb 테이블의 ibd 파일을 copy 한 후 MyIsam 같은 transaction을 지원하지 않는 테이블들의 백업을 위해 Executing FLUSH NO_WRITE_TO_BINLOG TABLES… 즉 flush tables with read lock을 수행합니다.
이때 long query가 돌고 있다면 flush table with read lock을 걸지 못하고 아래와 같이 lock 대기하게 됩니다.

문제는 ‘Waiting for table flush’ 으로 인해 뒤이어 들어오는 모든 쿼리들이 전부 대기하게 된다는 점인데
single instance에서 백업을 돌렸는데 이런 현상이 발생하게 되면 그대로 서비스 장애로 연결되는 이슈가 있습니다.

mysql> show processlist;
+------+-------------+-----------+---------------+---------+------+--------------------------------------------------------+----------------------------------------------------------------------------+
| Id   | User        | Host      | db            | Command | Time | State                                                  | Info                                                                       |
+------+-------------+-----------+---------------+---------+------+--------------------------------------------------------+----------------------------------------------------------------------------+
| 1250 | admin       | localhost | repltest_1029 | Query   |   51 | Creating sort index                                    | select a,b,c,count(*) from tb_test group by a,b,c order by a,b desc ,c asc |
| 2476 | admin       | localhost | repltest_1029 | Query   |    0 | starting                                               | show processlist                                                           |
| 2485 | bk_user     | localhost | NULL          | Query   |   38 | Waiting for table flush                                | FLUSH NO_WRITE_TO_BINLOG TABLES                                            |
| 2488 | admin       | localhost | repltest_1029 | Query   |   20 | Waiting for table flush                                | select * from tb_test limit 1                                              |
+------+-------------+-----------+---------------+---------+------+--------------------------------------------------------+----------------------------------------------------------------------------+

심지어 Xtrabackup 수행 시 자체적으로 lock_wait_timeout=31536000 으로 설정하고 돌기 때문에 DB 설정이 몇초간에 백업이 취소되지 않고 비정상적으로 길어질 수 있습니다.

2020-12-13T06:49:03.391760Z      2182 Query     SET SESSION lock_wait_timeout=31536000
2020-12-13T06:49:03.391876Z      2182 Query     FLUSH NO_WRITE_TO_BINLOG TABLES

대응방안

  • 백업 전용 Slave 구성하기
  • –ftwrl-wait-timeout , –ftwrl-wait-threshold => flush tables with read lock 커맨드를 실행하기 전에 wait하는 시간을 설정하는 옵션, –ftwrl-wait-timeout default=0, ftwrl을 바로 수행함
    FTWRL을 실행 하기 전에 –ftwrl-wait-timeout = 30 만큼 기다렸는데도 –ftwrl-wait-threshold(default 60)에 설정된 시간 보다 더 오래 실행되는 쿼리가 있으면 백업을 취소시켜 lock 대기로 인한 이슈를 최소화 시킴
201213 16:16:52 Waiting 30 seconds for queries running longer than 60 seconds to finish
201213 16:16:52 Executing FLUSH TABLES WITH READ LOCK...

ftwrl 을 수행하기 전에 –ftwrl-wait-threshold 만큼의 long query 가 없어서 ftwrl 을 수행했는데 –ftwrl-wait-threshold 미만의 롱 쿼리로 인해 락 대기할 수 있음
=> –ftwrl-wait-threshold=0 으로 설정하면 됨

  • –kill-long-queries-timeout , –kill-long-query-type
    => flush tables with read lock 수행 후 global lock 획득을 방해하는 쿼리를 kill 하는 옵션
    –kill-long-query-type 옵션을 통해 select , 혹은 모든 쿼리를 지정할 수 있음
innobackupex --ftwrl-wait-threshold=60 --ftrwl-wait-query-type=all \
    --ftrwl-wait-timeout=60 --kill-long-queries=30 \ 
    --kill-long-query-type=all

=> 60초이상 수행되는 모든 쿼리에 대해 60초이상 기다리지 않고 (남아있으면 백업 취소) ftwrl을 수행하고 30초 이내 ftwrl 락을 얻지 못하면 그 이전에 수행된 쿼리들을 모두 kill하겠다는 의미

  • MySQL 8.0 & Xtrabackup 8.0 사용 시 InnoDB만 사용한다면 –no-lock 옵션이 자동적용 되어 FTWRL을 수행하지 않습니다.
    다만, –slave-info 옵션이 주어질 땐 binlog position 정보를 위해 마찬가지로 FTWRL이 수행됩니다.