MYSQL Memory Management v1.0
|
2011.04.24 |
v1.0 |
|
|
|
|
|
|
|
|
|
1.
MySQL Memory
MySQL 은 하나의 Process 로 동작한다.
세션은 스레드로 확장되고 컴퓨터의 자원을 할당 받는다.
컴퓨터의 자원 중에서도 메모리 부분은 커넥션 수의 영향을 받는 부분으로 주의해야 한다.
2.
Memory Management
|
innodb_buffer_pool_size + key_buffer_size +
max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) (* 출처 A) 코멘트 중 다음과 같이 계산해야 한다는 이야기도 있다. (David Tonhofer) total = 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) ( 참고 : maximum_thread_stack_size 라는 Variable 은 없고 thread_stack 은 있다. 위의 thread_stack 은 수정한 값이다. ) |
* 위의 식은 MyISAM 과 InnoDB 를
Storage Engine 으로 사용하는 경우를 전제로 한다. *
MySQL 의 Memory 사용과 관련해 위의 공식을 참조한다.
Reference 에 나온 공식은 첫 번째 공식이고 두번째 공식은 코멘트로 달린 공식이다. 세부적인 차이가 있는데
그 차이는 아래와 같다.
innodb_additional_mem_pool_size
innodb_log_buffer_size
maximum_thread_stack_size
위 Variable 에 대해서는 ‘3’ 에서 다시 언급하겠다.
3.
Memory Management ( Factor)
아래의 Varaiable 에 대해 사용할 수 있는 range 는 Architecture 영향을 받는다. (* 참고 B)
여기서는 32 비트에 대한
Range 및 일부 정보를 생략한다. (아래의 표는 64 bit 기준이며 표의 값은 byte다.)
|
|
Default |
Range |
Scope |
Dynamic |
|
innodb_buffer_pool_size |
134217728 |
1048576-2**64-1 |
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 |
a.
innodb_buffer_pool_size
InnoDB 가 data 와 index 를 캐싱하기 위해 사용하는 공간이다. default 값은 128M 이다.
이 공간을 크게 잡을수록 디스크로의 I/O 는 감소된다.
데이터베이스용 서버라면 이 공간을 Physical Memory Size 의 80% 까지 잡기도 한다. 다만 다음 상황에 대해 크기를 줄이는 것을
고려해야 한다.
-
Physical
Memory 부족으로 OS 상에서 Paging 이 발생할 때
-
InnoDB 는 buffer 와
control structures 를 위해 일부 공간을 남겨둔다. 그렇기 때문에 정의된
크기보다 10% 정도의 공간이 추가적으로 필요하다.
-
buffer
pool 을 초기화하는 시간은 이 파라미터의 크기에 비례한다. 이를테면 10GB 의 경우 6초
정도가 소요된다.
b.
key_buffer_size
MyISAM 이 Index 를 캐싱하는데 사용하는 영역으로 key cache 로도
알려져있다. 통상 Physical Memory 의 25% 정도는 허용범주로 볼 수 있지만 이 영역을 지나치게 크게 잡는 경우는 문제가 생길 수 있다. 그 결과로 페이징이 발생하고 시스템이 매우 느려질 것이다. 그 이유는
데이터 영역의 캐싱은 OS 의
SYSTEM CACHING 을 이용하기 때문이다. 또한 다른 Storage Engine 이
사용할 영역도 고려해야 한다.
c.
max_connections
mysql 에 접속할
있는 세션수를 의미한다. 해당 파라미터의 값을 변경해야 할 때는 시스템의 메모리 사용량을 고려해야 한다.
해당 변수는 온라인상에 변경 가능한 Variable 이며 이를
변경시 영향 받는 부분은 다음과 같다. (2의 공식 참조)
max_connections *
(sort_buffer_size + read_buffer_size +
binlog_cache_size + thread_stack)
d.
sort_buffer_size
해당 Variable 은 특정 Storage 에 정의된 값이 아니어서 일반적으로 사용 가능하다.
(MyISAM 이든 InnoDB 든 똑같이 영향 받는다는 이야기)
특징으로는 이 sort 공간을 사용하는 경우 일량에 상관없이
전체 공간을 할당받아 사용한다는 것이다. 그렇기 때문에 특정 쿼리를 위해 global 하게 변경하는 것은 시스템 성능의 저해를 초래할 수 있으며 굳이 변경을 한다면 scope 를 session 으로 특정짓고 진행하는 것이 바람직하다.
e.
read_buffer_size
각각의 시퀀셜 스캔을 수행하는 쓰레드는 스캔하는 테이블에 대해 설정된 크기(bytes) 만큼의 버퍼를 할당받는다. 해당 값은 4KB 의 배수값으로 설정되어야 하며 다른 값이 설정된 경우 내림으로 배수셋팅이 된다.
f.
binlog_cache_size
트랜잭션 중 바이너리 로그에 대해 발생한 변경사항을 보유하기 위한 캐시 사이즈를 할당한다. 이 캐시는 서버가 트랜잭션을 지원하는 스토리지 서버를 사용하고 ( ex :
InnoDB ) binary log 를 enable 한 상태라면 각 Client 에 대해 할당된다.
g.
innodb_additional_mem_pool_size
InnoDB 를 위한
공간으로 Data Dictionary Information 과
Internal Data Structures 를 저장하는데 사용된다. InnoDB 테이블이
많으면 많을수록 공간 사용량도 늘어난다. 만약 이 공간이 부족한 경우는 OS 로부터 메모리를 할당받아 이용하기 시작하고 error log 에 warning message 를 기록한다. default 사이즈는 8MB 이다.
h.
innodb_log_buffer_size
InnoDB 가 log files 를 disk 에 쓰기위한 buffer size 이다. default 는 8M 이다. 큰 크기의 log
buffer 는 large transactions 이
commit 되기 전에 log 를 disk 로
내려쓰는 것을 피할 수 있다.
i.
thread_stack
각 쓰레드에 대한 스택사이즈를 정의한다. crash-me test 의
많은 케이스가 이 값의 영향을 받는것으로 확인되었다.
(Section 7.12.2, “The MySQL
Benchmark Suite | http://dev.mysql.com/doc/refman/5.5/en/mysql-benchmarks.html )
default 값은 256KB
(64-bit system) 이며 통상적인 경우 충분하다. 만약 thread stack size 가 지나치게 작으면 그것은 서버가 핸들할 수 있는 복잡한 SQL 구문의 사용에 제약을 가하게 된다. (stored procedures 의 recursion depth, 기타 memory-consuming
actions 들도 영향 받는다. )
4.
Memory Tuning
메모리 튜닝을 위해 다음과 같은 과정을 밟는다. (* 참고 C)
a.
MySQL 이 사용가능한 최대메모리
b.
정렬 버퍼나 임시테이블 등의 개별적인
연결에 필요한 메모리 양
c.
운영체제가 필요한 메모리양 ( 주기적인 배치 작업 및 다른 프로그램이 실행되는데 필요한 메모리 양 고려)
d.
나머지 메모리를 InnoDB 버퍼 풀과 같은 MySQL 캐시로 사용한다.
위의 내용은 책에 나와 있는 절차이다.
어디까지나 참조로 두고 2번에 이야기 했던 공식을 사용해 보겠다.
|
total
= 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) |
( 시스템이 16기가(64bit)라 가정하고 이야기 하겠다. )
먼저 앞서 이야기 한 MySQL 이 사용가능한 최대 메모리를
생각하겠다.
이 부분은 서버가 MySQL 만을 위한 장비라면 더욱 쉽게
계산할 수 있다.
OS 를 위한 공간 + MyISAM data caching + 메모리 증설까지의 버퍼
위와 같은 요소들을 정할 수 있을 것이다.
결과적으로 30% 정도를 미래(?) 를 위해 남겨둘 공간으로 정했다고 하면 11GB 정도가 남는다.
그 다음으로 max_connections 에 추측해야 한다. 장비에 대해 얼마나 붙느냐에 대해서는 전례가 있다면 (이관, 업그레이드 등등) 쉽겠지만 새로 시작한다면 추후에 값이 확장될 것을
고려해야 한다. Max 로 몇까지 사용할 수 있을까를 미리 생각하는 것도 좋지 않을까 생각한다. (다른 값이 다 정해진 상황에서…)
여기서는 200 으로 가정하겠다.
커넥션 수의 영향을 받는 부분으로 sort_buffer_size,
read_buffer_size, binlog_cache_size, thread_stack 과 같이 네가지 항목이 있다. 네개 모두 global variable 로 각 쓰레드에 영향을 주는
값들이다. 통상적인 경우라면 default 값을 사용할 것이다. 각기 default 값을 합산하면
2463KB 정도가 나왔다. 이를 connections 와
곱하여 약 482MB 가 나왔다.
여기까지 진행하여 미지수로 남아있는 항목은 아래와 같다.
- innodb_buffer_pool_size
- key_buffer_size
- innodb_additional_mem_pool_size
- innodb_log_buffer_size
Variables 를 보면 InnoDB 용과 MyISAM 용으로 나뉘어진다.
MyISAM 을 사용하지
않는다고 하면 default 인 8M 로 충분할 것이다. 여기서 값을 설정한 이유는 mysql database 가 MyISAM 을 사용하기 때문이다. 그리고 남은 공간을 InnoDB 에 할당을 할텐데
innodb_additional_mem_pool_size 는 수정할 일이 많지 않을 것으로
buffer size 와 buffer pool size 를 고려하면 된다.
InnoDB 를 사용하지
않는다고 하면 key buffer size 를 OS 가 data 캐싱할 것을 고려하여 정해야 할 것이다. 이 경우 최초 MySQL 이 사용할 최대 메모리를 70 % 로 잡았지만 더 낮춰야
할 것이다.
장황하게 이야기 했지만 결국엔 최적이란 값은 서비스 운영에 들어가 겪어야 알 수 있다. 다만 위 절차와 함께 샘플로 참고할 수 있는 서비스가 있다고 하면 시행착오가 줄어들 것으로 기대된다.
5.
References
A.
MySQL
5.5 Reference Manual | 13.6.2. Configuring InnoDB
http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html
B.
MySQL 성능 최적화 | O’RELLY, 위키북스 | 06 서버 설정 최적화
C.
MySQL
5.5 Reference Manual | 5.1.3. Server System Variables
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html