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
MySQL 8.0 Reference Manual / The InnoDB Storage Engine / InnoDB Startup Options and System Variables 15.13 InnoDB Startup Options and System Variables System variables that are true or false can be enabled at server startup by naming them, or disabled
dev.mysql.com
요약하자면
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로 사용
'job > mysql' 카테고리의 다른 글
| mysql log file size error (0) | 2019.06.26 |
|---|---|
| mysql replication relay log relay-bin.999999 bug (0) | 2019.05.03 |
| mysql 대용량 DB 백업 구성 (0) | 2019.04.09 |
| mysql dead lock 확인하기 (0) | 2019.01.24 |
| mysql innodb buffer pool size (0) | 2019.01.24 |