information technology

2020년 10월 17일 토요일

XtraBackup에 의해서 발생하는 락 경합에 대하여

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...

맨 마지막 두 줄 FLUSH NO_WRITE_TO_BINLOG TABLES, 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

2020년 10월 10일 토요일

CREATE TEMPORARY TABLE에 대하여

MySQL애서 일시 테이블을 사용하기 위한 CREATE TEMPORARY TABLE구문에 대하여 알아보자.

일시 테이블을 생성한 세션 내에서만 유효하고, 세션을 끊으면 자동적으로 테이블이 삭제된다.
사용 가능한 storage engine은 InnoDB, MEMORY, MyISAM, MERGE가 있고 Default는 InnoDB이다

CREATE TEMPORARY TABLE작성
일시 테이블을 사용하기 위해서는 CREATE TEMPORARY TABLES 권한이 필요하다.

mysql> CREATE TEMPORARY TABLE tmp_t1 (
    -> id INT,
    -> name varchar(256)
    -> );
Query OK, 0 rows affected (0.03 sec)

# innodb_strict_mode가 ON인 경우는 ROW_FORMAT지정이 불가능하다.
# 해당 변수는 MySQL 5.7부터 Default가 ON으로 변경되었다.
# SQL표준에 준거하지 않으면 경고가 아닌 에러를 반환하도록 하는 역할
mysql> CREATE TEMPORARY TABLE tmp_t2 (id INT, name varchar(256)) ROW_FORMAT = COMPRESSED;
ERROR 3500 (HY000): CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------+
| Level | Code | Message                                                                             |
+-------+------+-------------------------------------------------------------------------------------+
| Error | 3500 | CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE. |
| Error | 1031 | Table storage engine for 'tmp_t2' doesn't have this option                          |
+-------+------+-------------------------------------------------------------------------------------+

실제로 동일한 세션 안에서만 유효한지 확인해보자.

conn1> CREATE TEMPORARY TABLE tmp_t1 as SELECT * FROM t1;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

conn1> SELECT COUNT(1) FROM tmp_t1;
+----------+
| COUNT(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

# conn2 세션에서만 테이블이 존재하지 않는다.
conn2> SELECT COUNT(1) FROM tmp_t1;
ERROR 1146 (42S02): Table 'd1.tmp_t1' doesn't exist

# 트랜잭션을 종료한 후 세션을 끊고 재차 테이블을 검색하면 존재하지 않는다.
conn1> COMMIT RELEASE;
Query OK, 0 rows affected (0.00 sec)

conn1> SELECT COUNT(1) FROM tmp_t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    93
Current database: d1

ERROR 1146 (42S02): Table 'd1.tmp_t1' doesn't exist

CREATE TEMPORARY TABLE과 리플리케이션
BINLOG_FOTMAT이 ROW 또는 MIXED인 경우는 CREATE TEMPORARY TABLE가 바이너리 로그에 출력되지 않는다. 즉, 마스터에서 작성한 일시 테이블이 슬레이브에서는 생성되지 않는다.
하지만, 일시 테이블을 이용해서 일반 테이블을 갱신하는 경우에는 갱신 데이터의 바이너리 로그가 출력되므로 복제가 이루어진다.

BINLOG_FOTMAT이 STATEMENT인 경우는 CREATE TEMPORARY TABLE도 바이너리 로그에 출력된다.

일시 테이블이 존재하는 슬레이브를 재기동하면 해당 테이블들이 삭제되므로 리플리케이션 에러가 발생할 수도 있다.

BINLOG_FOTMAT=STATEMENT에서 일시 테이블을 이용하고 있는 경우에는 STOP SLAVE 후 Slave_open_temp_tables=0 인지 확인한 후 재기동 할 필요가 있다.

mysql> SHOW STATUS like 'Slave_open_temp_tables';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Slave_open_temp_tables | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

일시 테이블을 이용할 때의 주의점
작성한 일시 테이블은 SHOW TABLES로는 확인할 수 없다. 세션 내에 일시 테이블이 있는 지 확인하고 싶은 경우는 SHOW CREATE TABLE 또는 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO로 status가 activity인 테이블을 확인할 수 있다.

mysql> SELECT * FROM INNODB_TEMP_TABLE_INFO;
+----------+---------------+--------+------------+
| TABLE_ID | NAME          | N_COLS | SPACE      |
+----------+---------------+--------+------------+
|     1577 | #sql3e04_1e_c |      6 | 4294501266 |
|     1576 | #sql3e04_1e_1 |      6 | 4294501266 |
+----------+---------------+--------+------------+
2 rows in set (0.04 sec)

동일한 일시 테이블은 쿼리 안에서 한번만 이용할 수 있다.
만약 두번 이상 이용하고 싶은 경우는 MySQL 8.0 신기능인 CTE(WITH문)를 이용할 필요가 있다.

mysql> SELECT * FROM tmp1 JOIN tmp1 as _tmp2 on tmp1.id = _tmp2.id;
ERROR 1137 (HY000): Can't reopen table: 'tmp1'

# Refer
https://dev.mysql.com/doc/refman/5.7/en/create-table.html
https://dev.mysql.com/doc/refman/5.7/en/create-temporary-table.html

2020년 10월 5일 월요일

Resource group에 대하여

MySQL 8.0 신기능인 Resource group에 대하여 알아보자.

Resource group이란
Mysql thread가 사용할 수 있는 서버 리소스에 제한을 걸 수 있는 기능이다
장기간 실행되는 트랜잭션, 다른 처리를 CPU의 코어에 할당해서 처리량을 안정시키는 의도로 이용한다.

Resource group생성
생성하기 위해서는 RESOURCE_GROUP_ADMIN권한이 필요하다.
배치 작업에서 사용할 그룹을 만들어보자

mysql> CREATE RESOURCE GROUP Batch
TYPE = USER
VCPU = 0
THREAD_PRIORITY = 10;

TYPE은 user, system user로 나눠이지고
VCPU는 CPU 코어의 스레드 번호
THREAD_PRIORITY는 처리 우선순위이다.
* THREAD_PRIORITY를 설정하기 위해서는 CPS_SYS_NICE capability설정이 필요하다.

$ sudo setcap cap_sys_nice+ep /sbin/mysqld

$ getcap /sbin/mysqld

/sbin/mysqld = cap_sys_nice+ep

Resource group할당
할당하기 위해서는 RESOURCE_GROUP_ADMIN 또는 RESOURCE_GROUP_USER권한이 필요하다.

# 현재 접속해있는 스레드에 리소스 그룹 적용
mysql> SET RESOURCE GROUP Batch;

# Thread id를 지정해서 적용
mysql> SET RESOURCE GROUP Batch for 93;

Batch리스소 그룹은 VCPU=0이기 때문에 0번 째 코어만 사용하는 걸 확인할 수 있다.

$ mpstat 1 -P ALL : 10:34:40 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle

10:34:41 all 41.33 0.00 11.22 0.00 0.00 3.06 0.51 0.00 0.00 43.88 10:34:41 0 76.77 0.00 18.18 0.00 0.00 4.04 1.01 0.00 0.00 0.00 10:34:41 1 4.17 0.00 5.21 0.00 0.00 1.04 0.00 0.00 0.00 89.58 10:34:41 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle 10:34:42 all 41.75 0.00 11.86 0.00 0.00 2.58 0.00 0.00 0.00 43.81 10:34:42 0 77.23 0.00 17.82 0.00 0.00 4.95 0.00 0.00 0.00 0.00 10:34:42 1 4.21 0.00 5.26 0.00 0.00 1.05 0.00 0.00 0.00 89.47

Resource group변경
변경한 내용은 리스소 그룹에 속해 있는 스레드에 즉시 반영된다.

# Batch리소스 그룹이 사용할 CPU코어를 1번 째로 변경
mysql> ALTER RESOURCE GROUP Batch VCPU = 1;

# 유저에게 할당되어 있는 리소스 그룹을 무효화 시키는 명령어
mysql> ALTER RESOURCE GROUP Batch DISABLE FORCE;
(Batch 리소스 그룹에 속해 있는 스레드는 Default 리소스 그룹으로 변경된다)

# 리소스 그룹을 유효화 시키는 명령어
mysql> ALTER RESOURCE GROUP Batch ENABLE;

Resource group삭제

# Batch 리소스 그룹을 사용 중인 스레드가 있으면 삭제 불가능
mysql> DROP RESOURCE GROUP Batch;
ERROR 3656 (HY000): Resource group Batch is busy.

# 위의 경우를 무시하고 강제로 삭제하는 방법
mysql> DROP RESOURCE GROUP Batch FORCE;

# Refer
https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html