[MySQL & MariaDB - innodb_flush_log_at_trx_commit 파라미터]

innodb_flush_log_at_trx_commit 파라미터는 Transaction이 Commit되었을때 디스크에 저장되는 방법을 지정하는 변수이다. 해당 파라미터에 따라 성능을 차이가 날 수 있으므로 잘 고려해야합니다.

 

 

* 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초(혹은 그 이상..)의 트랜잭션이 유실될 수 있습니다.

 

nnodb_flush_log_at_trx_commit controls the durability in ACID compliance

A – atomicity

C – consistency

I – isolation

D – durability

 

■ innodb_flush_log_at_trx_commit 1로 설정시 속도 측정

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)


mysql> use test;
Database changed


mysql> create table test_bak (a int primary key,name varchar(10),test varchar(100),Address varchar(500));
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER $$
mysql> 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$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ; $$


mysql> call PInsert();
Query OK, 1 row affected (43.50 sec)

 

 

■ innodb_flush_log_at_trx_commit  2로 설정시

mysql> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)


mysql> call PInsert();
Query OK, 1 row affected (2.26 sec)

 

 

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

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

 

 

사진 출처

https://sqlconjuror.com/mariadb-mysql-innodb_flush_log_at_trx_commit/

MariaDB/MySQL - InnoDB_flush_log_at_trx_commit | SQL Conjuror

 

참고

https://yunhyeonglee.tistory.com/41

[MySQL / Maria] Innodb_flush_log_at_trx_commit