MySQL Memory
MySQL은 하나의 프로세스로 동작 합니다. 세션은 thread로 확장되고 시스템 자원을 할당 받습니다.
MySQL의 Memory 부분은Connection 수의 영향을 받는 부분이며 항상 주의를 요해야 합니다.
● Memory Management
MySQL 레퍼런스에 나온 식 :
innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
레퍼런스에 달린 코멘트 (by David Tonhofer)
total memory =
innodb_buffer_pool_size +
key_buffer_size +
innodb_additional_mem_pool_size +
innodb_log_buffer_size +
max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size + thread_stack)
|
항목 (64 Bit 기준) |
Default |
Range |
Scope |
Dynamic |
|
innodb_buffer_pool_size |
134217728 |
10전체 메모리의 70~80%가 적당합니다. G -> 12G , 32G -> 26G 정도를 설정 합니다. |
Global |
No |
|
key_buffer_size |
8388608 |
8-4294967295 |
Global |
Yes |
|
max_connections |
151 |
1-100000 |
Global |
Yes |
|
sort_buffer_size |
2097144 |
18446744073709547520 |
Global, Session |
Yes |
|
read_buffer_size |
131072 |
8200-2147479552 |
Global, Session |
Yes |
|
binlog_cache_size |
32768 |
4096-18446744073709547520 |
Global |
Yes |
|
innodb_additional_mem_pool_size |
8388608 |
2097152-4294967295 |
Global |
No |
|
innodb_log_buffer_size |
8388608 |
262144-4294967295 |
Global |
No |
|
thread_stack |
262144 |
131072-18446744073709547520 |
Global |
No |
innodb_buffer_pool_size
: InnoDB 가 data 와 index 를 캐싱하기 위해 사용하는 공간 입니다. default 값은 128M 입니다. 이 공간을 크게 잡을수록 디스크로의 I/O 는 감소됩니다. 데이터베이스용 서버라면 이 공간을 Physical Memory Size 의 80% 까지 잡기도 합니다.
다음 상황에 대해 크기를 줄이는 것을 고려해야 합니다.
1)Physical Memory 부족으로 OS 상에서 Paging 이 발생할 때
2)InnoDB 는 buffer 와 control structures 를 위해 일부 공간을 남겨둡니다. 그렇기 때문에 정의된 크기보다 10% 정도의 공간이 추가적으로 필요로 합니다.
3)buffer pool 을 초기화하는 시간은 이 파라미터의 크기에 비례 합니다. 이를테면 10GB 의 경우 6초 정도가 소요 됩니다.
key_buffer_size
: MyISAM 이 Index 를 캐싱하는데 사용하는 영역으로 key cache 로도 알려져 있습니다. 통상 Physical Memory 의 25% 정도는 허용범주로 볼 수 있지만 이 영역을 지나치게 크게 잡는 경우는 페이징이 발생하고 시스템이 매우 느려질 수 있습니다. 데이터 영역의 캐싱은 OS 의 SYSTEM CACHING 을 이용하기 때문입니다. 또한 다른 Storage Engine 이 사용할 영역도 고려해야 합니다.
max_connections
: mysql 에 접속할 있는 세션수를 의미 합니다. 해당 파라미터의 값을 변경해야 할 때는 시스템의 메모리 사용량을 고려해야 합니다.
sort_buffer_size
: 해당 Variable 은 특정 Storage 에 정의된 값이 아니어서 일반적으로 사용 가능합니다. (MyISAM, InnoDB 든 똑같은 영향을 받습니다.)
특징으로는 이 sort 공간을 사용하는 경우 일량에 상관없이 전체 공간을 할당받아 사용한다는 것 입니다. 그렇기 때문에 특정 쿼리를 위해 global 하게 변경하는 것은 시스템 성능의 저해를 초래할 수 있으며 굳이 변경을 한다면 scope 를 session 으로 특정짓고 진행하는 것이 바람직 합니다.
read_buffer_size
: 각각의 시퀀셜 스캔을 수행하는 쓰레드는 스캔하는 테이블에 대해 설정된 크기(bytes) 만큼의 버퍼를 할당 받습니다. 해당 값은 4KB 의 배수값으로 설정되어야 하며 다른 값이 설정된 경우 내림으로 배수셋팅이 됩니다.
binlog_cache_size
: 트랜잭션 중 바이너리 로그에 대해 발생한 변경사항을 보유하기 위한 캐시 사이즈를 할당 합니다. 이 캐시는 서버가 트랜잭션을 지원하는 스토리지 서버를 사용하고 ( ex : InnoDB ) binary log 를 enable 한 상태라면 각 Client 에 대해 할당 됩니다.
innodb_log_buffer_size
: InnoDB 가 log files 를 disk 에 쓰기위한 buffer size 입니다. default 는 8M 이다. 큰 크기의 log buffer 는 large transactions 이 commit 되기 전에 log 를 disk 로 내려쓰는 것을 피할 수 있습니다.
thread_stack
: 각 쓰레드에 대한 스택사이즈를 정의 합니다. default 값은 256KB (64-bit system) 이며 통상적인 경우 충분 합니다. 만약 thread stack size 가 지나치게 작으면 그것은 서버가 핸들할 수 있는 복잡한 SQL 구문의 사용에 제약을 가하게 됩니다. (stored procedures 의 recursion depth, 기타 memory-consuming actions 들도 영향 받습니다. )
● 튜닝
1) MySQL이 사용 가능한 최대 메모리를 계산 합니다.
2) 정렬 버퍼나 임시테이블 등의 개별적인 연결에 필요한 메모리 양을 계산 합니다.
3) 운영체제가 필요한 메모리양을 계산 합니다. ( 주기적인 배치 작업 및 다른 프로그램이 실행되는데 필요한 메모리 양 고려)
4) 나머지 메모리를 InnoDB 버퍼 풀과 같은 MySQL 캐시로 사용 합니다.
참고 사항
: MyISAM 을 사용하지 않는다면 innodb_additional_mem_pool_size은 기본값인 8M으로 충분 합니다.
InnoDB를 사용하지 않는다면 buffer size 를 OS 가 data 캐싱할 것을 고려하여 정해야 합니다. 이 경우 최초 MySQL 이 사용할 최대 메모리를 70 % 로 잡았지만 더 낮춰야 할 수 있습니다.
● Connection Memory 사용량 조회
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW VARIABLES LIKE 'thread_stack';
● Connection 별 메모리 사용률 확인
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;
SET @innodb_buffer_pool_size = 2 * @giga_bytes;
SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = 192 * @kilo_bytes;
SELECT
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
+ @innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @thread_stack
) ) / @giga_bytes AS MAX_MEMORY_GB;
실제메모리사용량 (Actual Used Physical Memory) Mem: total -(free + buffers + cached) buffers/cache: used
실제메모리 사용률 ( total - free - buffers - cached) / total (total - buffer/cashe: free) /total