MySQL Connection Memory Management

2021. 4. 11. 03:36 Database/mysql tip

 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

 

출처 : code-factory.tistory.com/41?category=724516