innodb mysql/mariadb insert 속도 높이기

innodb 의 경우 instert 속도가 myisam에 비해 느리다.

 

이경우 여러가지 튜닝을 할 수 있는데 대표적인것만 몇개 수정, 비교 해보려고 한다.

1.innodb_flush_log_at_trx_commit 를 0으로 변경하면 속도를 많이 올릴 수 있다고 한다.
innodb_flush_log_at_trx_commit 란...
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html

MySQL :: MySQL 8.0 Reference Manual :: 15.13 InnoDB Startup Options and System Variables

요약하자면

innodb_flush_log_at_trx_commit 이란 쿼리 요청이 디스크에 저장되는 방법이 지정된 변수이다. 즉 이 값을 변경하면 트랜잭션 방법을 수정해서 insert 속도를 올릴 수 있따.

##트랜잭션, commit이란

먼저 DB에서의 트랜잭션이란 정상적으로 완료된 일련의 작업들이라고 생각하면된다. 예를들어 insert , update , delete가 순차적으로 일어나고 그후 commit을 하게되면 트랜잭션이 정상적으로 완료됐다할 수 있다. 즉 commit이란 저장되지 않은(메모리에 적혀있는)데이터를 디비에 저장 후 트랜잭션을 종료한다.

 

innodb_flush_log_at_trx_commit  값이 디폴트일때의 과정(즉 1일 때)은 아래 처럼 진행된다.

쿼리 하나당 [log buffer(메모리 영역)를 거쳐서 >os buffer/cache(메모리 영역)를 거쳐서  > 마지막으로 ib_logfile(디스크영역)에 저장(flush) 된다.] 즉 1회 요청당 1회 commit - 한번의 요청때마다 매번 commit을 진행한다. - (요게 핵심임)

0 또는 2로 바꾼다면 저 과정이 간략해진다.

innodb_flush_log_at_trx_commit  =0
쿼리 하나당 [log buffer(메모리 영역)저장]하고 1초에 한번씩 나머지 commit을 수행(os buffer/cache(메모리 영역)를 거쳐서  > 마지막으로 ib_logfile(디스크영역)에 저장(flush)])

innodb_flush_log_at_trx_commit  = 2

쿼리 하나당 [log buffer(메모리 영역)를 거쳐서 >os buffer/cache(메모리 영역)에 저장] 후 1초에 한번씩 나머지 commit을 수행ib_logfile(디스크영역)에 저장(flush)])

 

즉 0과 2의 차이는 쿼리하나당 log buffer까지 저장하느냐 os buffer/cache 까지 저장하느냐의 차이임

 

(혹시 이글을 읽으시는 분들중 틀린 내용이 있다면 제발 코멘트 부탁드립니다. 구글링하면서 다른 내용이 적잖이 보여서 사실 저도 좀 헷갈립니다.)

 

이론적으로 보면 속도 순서는 0 - 2 -1 순으로 빨라야 한다. 

비교를 해보자.

파일용량(각각 실 사용 데이터)	0일때	1일때	2일때
12GB	45분	60분	50분
10GB	40분	50분	45분
15GB	65분	80분	65분
18GB	75분	95분	75분
14GB	테스트 안함	75분	60분
19GB	테스트 안함	105분	85분
19GB	테스트 안함	105분	85분

0이랑 2랑 거의 비슷하고(0이쫌더 빠르긴함) 확실히 1로 줬을때보단 빠르긴 한듯합니다. 용량 작으면 그냥 하고 용량 크면 데이터 유실될 가능성이 좀 더 적은 2로 주고 하는게 좋겠네요. 

 

 

2.innodb buffer pool size 

아마 이게 가장 대표적이고 또 확실히 눈에 보이게 성능 향상이 이루어질걸로 판단한다.

극단적으로 보기위해 기존에는 디폴트 값(128MB)과 서버 메모리(32GB)에 맞게끔 변경해서 비교했는데

7GB 짜리 덤프파일 복원 하는데 3시간 쫌 넘게 걸렸고
real    187m17.825s 
user    2m21.802s 
sys     0m6.869s

 

이번에는 innodb buffer pool size = 20GB로 변경하고 .sql 용량 =8GB 짜리 복원시(키 버퍼랑 등등 추가로 알맞게 진행)

real    40m44.386s
user    2m21.319s
sys     0m8.349s

역시... innodb buffer pool size 는 튜닝 필수다.

 

 

내용 요약.

1.innodb buffer pool size 수정은 필수(그에따른 키 버퍼나 솔트 버퍼등도 구글링해서 알맞게 수정)

2.용량이 큰 dump파일 복원시 innodb_flush_log_at_trx_commit 는 2로 변경
ㄴ실제 사용시 상황에 맞게(안전성이냐 속도냐) 1또는 2로 사용