[MySQL / Maria] Innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit 파라미터는 Transaction이 Commit되었을때 디스크에 저장되는 방법을 지정하는 변수이다. 해당 파라미터를 이용해서 Insert 작업 성능을 높일 수 있다.

 

펌한 그림

* innodb_flush_log_at_trx_commit = 0 

-MySQL 서버에 문제가 생기면 마지막 1초의 Transaction 유실 발생

* innodb_flush_log_at_trx_commit = 1

- Default 값으로 데이터 유실 발생하지 않는다.

* innodb_flush_log_at_trx_commit =2 

-  OS가 crash되거나 파워가 나가면 마지막 1초(혹은 그 이상..)의 트랜잭션이 유실될 수 있습니다.

 

■ innodb_flush_log_at_trx_commit 1로 (Default)

- Innodb_flush_log_at_trx_commit 파라미터를 1로하엿을떄 Insert시 속도 측정

MariaDB [(none)]> show variables like '%innodb_flush_log_at%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 1     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

2.TEST TABLE 생성
drop table if exists test_bak;
create table test_bak (a int primary key,name varchar(10),test varchar(100),Address varchar(500));

3.INSERT PROCEDURE 생성
drop procedure if exists PInsert;
DELIMITER $$
CREATE PROCEDURE PInsert()
begin
declare i int;
declare b int;
set i=10004;
set b=76834;
while(i < 100000) do
Insert into test.test_bak values (i,b,now(),now());
set i=i+1;
set b=b+1;
end while;
end$$
delimiter ; $$

4.PROCEDURE 실행
MariaDB [test2]> call PInsert();
Query OK, 1 row affected (1 min 8.68 sec)

 

■ innodb_flush_log_at_trx_commit 2

- 기존 Default값인 1로 실행시 1분 이상걸렸던 Insert Procedure가 2초로 줄어든 것을 볼 수 있다.

1.innodb_flush_log_at_trx_commit 값 변경
MariaDB [test]> set global innodb_flush_log_at_trx_commit=2;

2.innodb_flush_log_at_trx_commit 값 확인
MariaDB [test]> show variables like '%innodb_flush_log_at%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 1     |
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+

3.Procedure 실행
MariaDB [test]> call PInsert();
Query OK, 1 row affected (2.26 sec)

 

* 데이터의 유실에 민감한 서비스라면 Default 값인 1로 설정해두고 서버가 내려갔을때 1초 정도의 Data 유실은 민감하게 받아들이지 않는 서비스라면 0, 2로 값을 변경하여 성능을 높이는 것이 좋다.

* 만약 Replication 복제를 사용하고있다면 innodb_flush_log_at_trx_commit =2는 문제가되지 않는다.

 

 

■ 참고 URL

http://minsql.com/mysql/innodb_flush_log_at_trx_commit-%EA%B0%9C%EB%85%90%EB%8F%84%EC%99%80-%ED%8A%9C%EB%8B%9D-%ED%8F%AC%EC%9D%B8%ED%8A%B8/

innodb_flush_log_at_trx_commit 개념도와 튜닝 포인트