MySQL & Maria
[ Maria ] DDL시 Replication Delay 발생
- -
반응형
MariaDB / MySQL은 현재 Version에서 Online DDL을 지원한다. Replication 상태에서 Master DB에 사이즈가 큰 테이블에 DDL이 발생하였을 때 Slave의 상태는 괜찮을까라는 의문이 들었다. TEST를 해본 결과 Online DDL시 Slave의 Seconds_Behind_Master 값( Slave가 Master에 비해 얼만큼 늦는가 ) 이 높아지는 것을 확인할 수 있었다. percona에서 만든 pt-online-schema-change를 사용하게 되면 Seconds_Behind_Master를 조절하면서 Online DDL을 수행할 수 있다.
■ 1. 일반 Online DDL
- 일반적인 DDL로 ALGORITHM=INPLACE / LOCK=NONE 사용
- Master DB에서 Online DDL이 끝난이후 Binary log에 써지고 Slave DB에서 Online DDL이 시작되게 된다.
- Slave DB에서 Online DDL이 발생하면서 Seconds_Behind_Master 수치가 높아짐
1. Table 확인
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| SQ_MEMBER |
+----------------+
MariaDB [test]> select count(*) from SQ_MEMBER;
+----------+
| count(*) |
+----------+
| 1778639 |
+----------+
MariaDB [test]> select round((data_length+index_length)/1024/1024/1024,2) as "GB" from information_schema.tables where table_name='SQ_MEMBER';
+------+
| GB |
+------+
| 2.22 |
+------+
2. Online DDL 실행
MariaDB [test]> ALTER TABLE SQ_MEMBER ADD COLUMN CERTIFY_DATE VARCHAR(8);
Query OK, 0 rows affected (2 min 11.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
3. Slave DB Seconds_Behind_Master 값 확인
MariaDB [(none)]> show processlist;
+----+-------------+-----------+-----------+-----------+-------+----------------------------------+----------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+-----------+-----------+-------+----------------------------------+----------------------------------------------------------+----------+
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 9 | system user | | NULL | Slave_IO | 54751 | Waiting for master to send event | NULL | 0.000 |
| 10 | system user | | test | Slave_SQL | 87 | altering table | ALTER TABLE SQ_MEMBER ADD COLUMN CERTIFY_DATE VARCHAR(8) | 0.000 |
| 41 | root | localhost | test | Sleep | 47 | | NULL | 0.000 |
| 49 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
+----+-------------+-----------+-----------+-----------+-------+----------------------------------+----------------------------------------------------------+----------+
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.50
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql.000007
Read_Master_Log_Pos: 119888876
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 2888
Relay_Master_Log_File: mysql.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 119888706
Relay_Log_Space: 3367
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 77
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: altering table
1 row in set (0.00 sec)
■ 2. pt-online-schema-change 사용
1) pt-online-schema-change 설치
[root@master ~]# sudo yum -y install perl-DBI perl-DBD-MySQL perl-TermReadKey perl perl-IO-Socket-SSL perl-Time-HiRes perl-devel
[root@master ~]# wget percona.com/get/percona-toolkit.tar.gz
[root@master ~]# tar -zxvf percona-toolkit.tar.gz
[root@master ~]# cd percona-toolkit-3.3.1/
[root@master percona-toolkit-3.3.1]#
[root@master percona-toolkit-3.3.1]# perl ./Makefile.PL
[root@master percona-toolkit-3.3.1]# make
[root@master percona-toolkit-3.3.1]# make install
[root@master percona-toolkit-3.3.1]# vim ~/.bash_profile
alias pt-online-schema-change="/root/percona-toolkit-3.3.1/bin/pt-online-schema-change"
[root@master percona-toolkit-3.3.1]# . ~/.bash_profile
2) pt-online-schema-change 이용한 Online DDL
https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html
pt-online-schema-change
pt-online-schema-change
www.percona.com
- pt-online-schema-change시 사용되는 mysql user는 slave DB 쪽에도 존재해야 한다.
- --max-lag를 사용하여 replication lag가 60초 이상 발생하면 Online-DDL을 잠깐 멈춘 후 60초 이하로 내려가면 다시 Online-DDL 시작
- Master / Slave DB 동시에 Online-DDL이 발생하게 된다.
1. pt-online-schema-change 실행
[root@master lee]# pt-online-schema-change --alter "ADD COLUMN CERTIFY_DATE VARCHAR(8) DEFAULT NULL" D=gseek_new,t=SQ_MEMBER \
--no-drop-old-table \
--chunk-size=500 \
--chunk-size-limit=600 \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--ask-pass \
--charset=UTF8 \
--alter-foreign-keys-method=auto \
--execute \
--preserve-triggers \
--max-lag=60
Enter MySQL password:
Found 1 slaves:
slave -> 192.168.100.51:socket
Will check slave lag on:
slave -> 192.168.100.51:socket
(중략)
# A software update is available:
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
No foreign keys reference `gseek_new`.`SQ_MEMBER`; ignoring --alter-foreign-keys-method.
Altering `gseek_new`.`SQ_MEMBER`...
Creating new table...
Created new table gseek_new.__SQ_MEMBER_new OK.
Altering new table...
Altered `gseek_new`.`__SQ_MEMBER_new` OK.
2022-04-15T17:19:35 Creating triggers...
2022-04-15T17:19:35 Created triggers OK.
2022-04-15T17:19:35 Copying approximately 1612681 rows...
Replica lag is 117 seconds on slave. Waiting.
Replica lag is 60 seconds on slave. Waiting. ** Replication Lag가 발생하니 알아서 정지했다가 다시시작한다.
Copying `gseek_new`.`SQ_MEMBER`: 0% 1+03:18:32 remain
Copying `gseek_new`.`SQ_MEMBER`: 9% 15:07 remain
Copying `gseek_new`.`SQ_MEMBER`: 17% 09:39 remain
Copying `gseek_new`.`SQ_MEMBER`: 25% 07:27 remain
Copying `gseek_new`.`SQ_MEMBER`: 31% 06:33 remain
Copying `gseek_new`.`SQ_MEMBER`: 36% 06:02 remain
Copying `gseek_new`.`SQ_MEMBER`: 42% 05:34 remain
Copying `gseek_new`.`SQ_MEMBER`: 47% 05:07 remain
Copying `gseek_new`.`SQ_MEMBER`: 51% 04:42 remain
Copying `gseek_new`.`SQ_MEMBER`: 56% 04:16 remain
Copying `gseek_new`.`SQ_MEMBER`: 61% 03:49 remain
Copying `gseek_new`.`SQ_MEMBER`: 65% 03:28 remain
Copying `gseek_new`.`SQ_MEMBER`: 70% 03:00 remain
Copying `gseek_new`.`SQ_MEMBER`: 74% 02:32 remain
Copying `gseek_new`.`SQ_MEMBER`: 79% 02:06 remain
Copying `gseek_new`.`SQ_MEMBER`: 83% 01:43 remain
Copying `gseek_new`.`SQ_MEMBER`: 87% 01:19 remain
Copying `gseek_new`.`SQ_MEMBER`: 90% 00:59 remain
Copying `gseek_new`.`SQ_MEMBER`: 93% 00:38 remain
Copying `gseek_new`.`SQ_MEMBER`: 96% 00:20 remain
Copying `gseek_new`.`SQ_MEMBER`: 99% 00:01 remain
2022-04-15T17:32:20 Copied rows OK.
2022-04-15T17:32:20 Adding original triggers to new table.
2022-04-15T17:32:20 Analyzing new table...
2022-04-15T17:32:20 Swapping tables...
2022-04-15T17:32:20 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2022-04-15T17:32:20 Dropping triggers...
2022-04-15T17:32:20 Dropped triggers OK.
Successfully altered `gseek_new`.`SQ_MEMBER`.
2. Master DB processlist
+------+-------------+----------------------+-----------+-------------+-------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+------+-------------+----------------------+-----------+-------------+-------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| 1 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 |
| 2 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 5 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 |
| 1505 | repl | 192.168.100.51:48488 | | Binlog Dump | 76720 | Master has sent all binlog to slave; waiting for binlog to be updated | | 0.000 |
| 1543 | root | localhost:57370 | gseek_new | Query | 0 | Sending data | INSERT LOW_PRIORITY IGNORE INTO `gseek_new`.`__SQ_MEMBER_new` (`user_id`, `comp_code`, `dept_code`, | 0.000 |
| 1544 | root | localhost:57372 | gseek_new | Sleep | 130 | | | 0.000 |
| 1545 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
+------+-------------+----------------------+-----------+-------------+-------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
3. Slave DB processlist
+------+-------------+----------------------+----+-----------+-------+-------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+------+-------------+----------------------+----+-----------+-------+-------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 1 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 |
| 2 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 5 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 |
| 9 | system user | | | Slave_IO | 76707 | Waiting for master to send event | | 0.000 |
| 10 | system user | | | Slave_SQL | 0 | Write_rows_log_event::write_row(-1) | INSERT LOW_PRIORITY IGNORE INTO `gseek_new`.`__SQ_MEMBER_new` (`user_id`, `comp_code`, `dept_code`, | 0.000 |
| 3838 | root | 192.168.100.50:43950 | | Sleep | 0 | | | 0.000 |
| 3872 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
+------+-------------+----------------------+----+-----------+-------+-------------------------------------+------------------------------------------------------------------------------------------------------+----------+
+------+-------------+----------------------+----+-----------+-------+-------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+------+-------------+----------------------+----+-----------+-------+-------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 1 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 |
| 2 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 5 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 |
| 9 | system user | | | Slave_IO | 76708 | Waiting for master to send event | | 0.000 |
| 10 | system user | | | Slave_SQL | 0 | Write_rows_log_event::write_row(-1) | INSERT LOW_PRIORITY IGNORE INTO `gseek_new`.`__SQ_MEMBER_new` (`user_id`, `comp_code`, `dept_code`, | 0.000 |
| 3838 | root | 192.168.100.50:43950 | | Sleep | 0 | | | 0.000 |
| 3872 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
| 4954 | root | 192.168.100.50:46140 | | Sleep | 0 | | | 0.000 |반응형
'MySQL & Maria' 카테고리의 다른 글
| 성능테스트 SYSBENCH CUSTOM SCRIPT (0) | 2022.08.10 |
|---|---|
| [Maria] Galera Cluster to Single DB(DR) (0) | 2022.05.07 |
| [ MySQL ] Online DDL - Varchar (0) | 2022.04.12 |
| [Maria] Maxscale GUI 설치 (2) | 2022.03.22 |
| [ MySQL & Maria ] Innodb_buffer_pool 사용량 조회 (0) | 2022.03.21 |
Contents
소중한 공감 감사합니다