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