Mysql 메모리 튜닝가이드

이번에 stored function 관련 full scan 튜닝이 끝나면 innodb status를 보고 좀더 개선포인트가 있는지 본격적으로 my.cnf 메모리 튜닝을 검토해볼 생각이다.

튜닝 가이드 중에서 잘 정리된 내용(SK Cloud Z Support Portal > Cloud Z DB >이승철님)이 있어 정리해놓는다.

 

Mariadb의 메모리는 크게 두 가지로 분류가 됩니다.

-> 글로벌 메모리영역은 공유하고 세션메모리영역(connection당)은 공유하지 않는다.

이것에 대한 하나 예를 들면 오라클과 postgres를 mysql로 마이그레이션을 진행한다면 다른 것보다 function과 procedure의 사용이 많은지 확인해봐야하고 어느부분에서는 Dynamic sql로 변경을 해야할 수도 있다.

오라클, postgres는 function과 procedure를 글로벌메모리영역에서 사용하지만 mysql은 일단 성능도 그들의 1/2배이며, global 메모리 영역에서 쓰지 않고 session 메모리 영역에서만 사용하기 때문에 성능이슈가 발생할 가능성이 더 크다. 인덱스스캔이 제대로 된다면 별문제 없지만.. 이후 function이나 procedure가 바뀌고 기존의 실행계획이 틀어진다면 바로 풀스캔 및 디스크I/O로 이어져 성능이슈가 발생할 가능성이 있다......mysql 잇점이 있다면 경험상 postgres는 procedure가 뭔짓을 하는지 모르다가 out of memory 떨구고 DB가 죽었지만, mysql은 session(connection) 메모리가 full이어서 disk로 넘어가게 되면 이미 성능이슈로 서버의 응답시간이 지연되는 것을 이미 다른 SM이 알기 때문에 DB 장애로 넘어가기 전에 미리 확인이 가능했다.

 

1. Global 메모리 영역
: DB가 최초 기동되었을 때에는 메모리를 최소한만 사용하다가 설정된 값 까지 증가하며 증가한 이후에는 "메모리를 반환하지 않고" 설정 된 값 이내에서 계속 사용됩니다.
(오라클의 경우 DB기동시 설정된 값 만큼 메모리를 할당 받고 올라가는 반면 Mariadb 는 기동시 설정된 메모리 값만큼 할당 받는것이 아닌 설정된 값 만큼 서서히 증가하게 됩니다)
•Innodb_buffer_pool_size
•Key_buffer_size
•innodb_log_buffer_size
•tmp_table_size
→ Global 메모리 = Innodb_buffer_pool_size + Key_buffer_size + innodb_log_buffer_size + tmp_table_size

 

--> 대용량 테이블을 union all을 사용하는 sql호출이 많아지면서 성능이슈가 있었고 tmp_table_size을 사이즈를 512M -> 2G로 늘려서 DB 성능을 개선한 적이 있다. 해당 옵션을 튜닝하려면 max_heap_table_size도 같이 늘려줘야한다.

 

임시테이블 튜닝 가이드참조: https://sungwookkang.com/m/1229

 

2. Session 메모리 영역
Mariadb session 별로 사용되어지는 메모리 공간으로 Max connection 설정 값과 관련이 있습니다.
(커넥션을 맺고만 있을 경우에는 커넥션에 필요한 최소한의 메모리만 사용되어지며 조인, 정렬 등이 필요할 경우에는 아래 설정된 값만큼을 메모리에서 사용하게 됩니다.
때문에 정확한 예측값은 동시에 Active한 세션 수 이지만 기본적으로는 Max connection 수 만큼 동시 사용 세션이 수행될 수 있기에 아래 공식으로 계산되어 집니다.)
•sort_buffer_size
•read_buffer_size
•read_rnd_buffer_size
•join_buffer_size
•thread_stack
•binlog_cache_size
•Max connection
→ Session 메모리 = (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size + thread_stack + binlog_cache_size) x Max connection

 

*예시
만약 Mariadb의 설정값이 다음과 같다면...
Global 메모리
•Innodb buffer pool size: 2048 MB
+ Key buffer size: 16MB
+ innodb log buffer: 10MB
+ tmp table size: 64MB
= 약 2.1 GB

 

Session 메모리
•( sort_buffer_size: 2MB
+ read_buffer_size: 1MB
+ read_rnd_buffer_size: 1MB
+ join_buffer_size: 0.128 MB
+ thread_stack: 0.128 MB
+ binlog_cache_size: 1MB)
x Max connection 300
= 약 1.6 GB
이 됩니다.

따라서 global 메모리 영역 (2.1GB) + Session 메모리 영역(1.6GB) 에 추가로 Mariadb 기본 기동(350MB) + performance_schema data(150MB) + OS / 파일 버퍼링 공간(전체 메모리의 약 10%) 을 고려하여
위 설정된 값을 기준으로 전체 메모리의 적정 수치는 4,5GB ~ 5GB 정도가 적당해 보입니다.
위의 설명 및 예시를 참고하시어 MariaDB의 메모리를 설정해주시면 됩니다.

 

참조사이트:

support.cloudz.co.kr/support/solutions/articles/42000064656--cloud-z-db-mariadb의-메모리-설정을-어떻게-해야-하나요-