[ MySQL ] innodb_onlin_alter_log_max_size 중요성

MySQL 5.7 이후부는 Online DDL을 지원하게 되었습니다. Online DDL시 장애를 줄이기 위해서 알아두면 좋은 환경설정에 대해 포스트팅 해보도록 하겠습니다.

 

■ Online DDL시 아래 작업들이 발생하게 됩니다.

- online ddl시 row log buffer에 DDL이후의 데이터 변경사항을 저장하게 된다.  

- row log buffer는 sort_buffer_size 시스템 변수에 정의된 크기만큼씩 할당하며, 최대 innodb_online_alter_log_max_size 만큼 증가하게 된다. 

- innodb_online_alter_log_max_size ⇒ ONLINE DDL 중 DML문을 적재하는 Size 이다. 대부분 ONLINE DDL 중 해당 파라미터를 1G로 설정하고 진행한다.

 

■ Innodb_online_alter_log_max_size Defautl Value

MariaDB [test]> show variables like '%innodb_online_alter_log_max_size%';
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| innodb_online_alter_log_max_size | 134217728  |
+----------------------------------+------------+

 

■ 테스트 Table 생성

- Online DDL시 발생하는 Error를 보기위해서 Test Table을 생성

echo "create database IF NOT EXISTS  test" | mysql -uroot -proot

echo "DROP TABLE IF EXISTS test.dup_error; CREATE TABLE test.dup_error(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, uid INT NOT NULL, v DATETIME, UNIQUE KEY ux_uid(uid));" | mysql -u root -proot

 

■ 테이블 용량을 올리기위해 칼럼 추가

seq -f "ALTER TABLE test.dup_error ADD COLUMN c%02.0f CHAR(255) DEFAULT '';" 1 30 | mysql -u root -proot

 

■ 테스트 데이터 적재 ( 빠른 적재를 위해 sql_log_bin / commit 관련 파라미터 수정 )

for n in {1..200000}; do printf "INSERT INTO test.dup_error (id, uid) VALUES (%d, %d);\n" $n $n; done | mysql -u root -proot  --init-command="SET sql_log_bin=off; SET GLOBAL innodb_flush_log_at_trx_commit=0;"

 

■ ONLINE DDL

ALTER TABLE test.dup_error
DROP PRIMARY KEY,
DROP INDEX ux_uid,
ADD UNIQUE INDEX ux_id(id),
ADD PRIMARY KEY(uid),
ALGORITHM=inplace, LOCK=none;

 

■ innodb_online_alter_log_max_size를 넘어가도록 Test 데이터 적재

for n in {200001..600000}; do printf "INSERT INTO test.dup_error (id, uid) VALUES (%d, %d);\n" $n $n; done | mysql -u root -proot  --init-command="SET sql_log_bin=off; SET GLOBAL innodb_flush_log_at_trx_commit=0;"

 

■ ONLINE DDL ERROR 발생

ERROR 1799 (HY000): Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again