Server level의 메모리 설정 변수
- key_buffer_size와 innodb_buffer_pool_size 는 필요 시 동적으로 계속 할당을 받아서 증가시키다가 정의된 사이즈만큼 확장하게 된다.
Session level의 메모리 설정 변수
- 기본적으로 초기에 설정치 만큼 할당이 바로 되는 변수가 있는 반면, 조금씩 할당해 가면서 최대 정의된 용량만큼 확장되는 형태의 변수도 있다.
- Session 별로 항상 할당되는 변수와 쿼리 수행 시 필요한 경우에만 할당되는 변수도 있다.
- MySQL 뿐만 아니라 일반적인 서버 프로그램에서 메모리 할당은 상당히 고비용의 작업이며, 특히나 MySQL은 더더욱 그러하다.
이러한 메모리 할당의 특성들은 Session 또는 Query level로 빠르게 할당되었다가 즉시 사라지는 이러한 메모리 버퍼들의 사이즈는 성능에 상당한 영향을 미치게 된다.
그래서 무조건 많은 량의 메모리를 할당하는 것이 항상 좋은 것은 아니다.
Net_buffer_length & max_allowed_packet :
각 Thread는 Connection buffer와 Result buffer를 가지게 된다. 두 개 buffer 모두 초기에는 net_buffer_length 에 정의된 만큼 초기 할당하게 되고, 필요 시 최대 max_allowed_packet 에 정의된 사이즈만큼 확장된다. 쿼리가 완료되면, Result buffer는 초기 net_buffer_length에 정의된 사이즈로 다시 줄어들지만, Connection buffer는 net_buffer_length 사이즈만큼 초기화되지 않는다.
Net_buffer_length 변수는
MySQL 5.1 미만에서는 설정 가능한 변수이며 1KB ~ 1MB내에서 설정 가능하며 Default 설정 값은 16KB이다.
MySQL 5.1 이상에서는 이 값은 Default 설정 값은 16KB이며, 변경이 불가능하다.
Max_allowed_packet :
Query 문장에 대용량의 BLOB이나 TEXT 타입이 사용될 경우, 32MB 이상을 설정해야 할 경우도 있다.
Thread_stack :
Thread stack의 사이즈가 너무 작으면, 복잡한 쿼리 문장이나 프로시져의 재귀적 함수의 처리를 제한하게 될 수 있다. MySQL 5.0 그리고 MySQL 5.1 공히, 128KB 이상 설정이 가능하며, Default 값은 32Bit에서는 192KB, 64Bit에서는 256KB이다. Default 설정 값을 그대로 사용할 것을 추천한다.
Read_buffer_size :
MySQL에서 Sequential scan (full table scan)을 사용할 경우, 사용하게 되는 Buffer이며, Manual에서는 Sequential scan이 많으면 크게 설정하는 것이 좋다고 되어 있지만, 인터넷에 공유된 벤치마킹 게시물들을 보면 크게 영향이 없는 것으로 보인다. Storage engine의 종류에 관계 없이 사용되며 8KB 이상 설정 가능하며 4KB 단위로 할당이 되므로 4KB의 배수로 설정하는 것이 좋다. Default 설정 값은 128KB이며 인터넷에 공유된 벤치마킹 결과에서도 128KB가 최고의 성능을 내는 것으로 보인다.
Read_rnd_buffer_size :
정렬 작업 이후, 정렬된 순서대로 데이터를 다시 읽어 들일 때 사용되는 Buffer로서, Storage engine에 관계없이 사용된다. MySQL의 정렬은 Single-pass와 Two-pass 알고리즘이 있으며, Two-pass 알고리즘으로 정렬되는 경우에만 사용된다. 8KB 이상 설정이 가능하며, Default 값은 256KB이다. 특별한 요건이 없다면 Read_buffer_size와 동일하게 Default 값을 그대로 사용할 것은 권장한다. (자세한 내용은 Manual의 “ORDER BY optimization” 부분을 참조할 것)
Join_buffer_size :
다른 Session level 변수와 달리, 이 버퍼는 Session level에서 Join 단위로 생성이 된다. 단, Join 중에서도 인덱스를 사용할 수 없는 경우의 Join에서 이 버퍼가 할당되고 사용된다. 이 버퍼는 필요한 만큼 할당되는 것이 아니라 Join buffer가 필요한 시점에 모두 한꺼번에 할당 받게 된다. 그래서 너무 큰 설정은 성능에 상당히 나쁜 영향을 미치게 된다. Join buffer 가 사용되는지 여부는 실행 계획상에서 쉽게 확인할 수 있다. Join buffer를 이용한 Join의 방법은 다음 기회에 설명하도록 하겠다. 8KB 이상의 값을 설정할 수 있으며, Default 값은 128KB 이다.
Sort_buffer_size :
Index를 이용한 정렬이 불가능할 경우 MySQL의 별도의 filesort라는 정렬 작업을 하게 되는데, 이때 사용하는 버퍼가 Sort buffer이다. 특정 사이즈로 할당된 Sort buffer에 몇 개 정도의 레코드가 동시에 저장될 수 있는지는 정렬 알고리즘이 Single-pass인지 Two-pass인지에 따라 달라지며, 사용되는 알고리즘은 SELECT되는 필드(컬럼)들의 타입이나 전체 결과의 길이가max_length_for_sort_data 변수의 설정 값보다 큰지 작은지에 따라서 결정된다. Sort_buffer_size는 설정 값의 제한이 없으며 Default 값은 2MB이다. Sort_buffer_size 가 적절한지 여부는Sort_merge_passes 상태 변수 값으로 확인이 가능한 것으로 Manual에는 되어 있지만, Sort_merge_passes값이 2이상의 값이 된다고 해서 성능이 떨어진다고 보기에는 적절하지 않은 것 같다. 이 buffer 또한 여러 벤치마킹의 결과를 통해서 보면 많이 설정하는 것이 항상 좋은 것은 아닌 것으로 보인다. (심지어 어떤 벤치마킹에서는 450KB 이상의 sort_buffer_size는 성능 향상 효과가 없는 것으로 나타나기도 한다). 하지만 너무 작게 Buffer size를 할당하면 대 부분의 정렬 작업이 Disk I/O를 유발하게 되므로 적절한 값을 설정하는 것이 아주 중요하다. 추천하는 방법은 초기 32KB로 설정하고 시스템의 성능이나 부하를 봐가면서 최대 512KB까지 사이에서 결정하는 것으로 생각된다.
Tmp_table_size :
GROUP BY 또는 Inline view (Derrived table)가 필요한 작업 등에서는 Temporary table이 필요하게 된다. Temporary table은 초기에는 메모리에 생성되지만, Temporary table의 사이즈가 MIN(tmp_table_size,
max_heap_table_size) 의 설정 값보다 테이블의 사이즈가 커지는 경우에는 Disk Temporary table로 변환된다. Tmp_table_size는 1KB이상의 값을 설정할 수 있으며, Default 값은 System에 의존적이다. 너무 크게 설정되면 메모리의 낭비가 심해지고 너무 작게 설정되면 Disk I/O가 낭비될 가능성이 있는 설정 변수이다. (Created_tmp_disk_tables / Created_tmp_tables)의 비율을 봐 가면서 512KB ~ 4MB 이내에서 적절히 선택하는 것이 좋아 보인다.전체적인 MySQL 서버의 메모리 사용량 예측
Min_thread_memory =
thread_stack_size
+ (net_buffer_length * 2)
Max_thread_memory =
thread_stack_size
+ (max_allowed_packet*2)
+ read_buffer_size
+ join_buffer_size
+ sort_buffer_size
+ tmp_table_size
+ myisam_sort_buffer_size
Max_server_memory =
query_cache_size
+ innodb_buffer_pool_size
+ innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ key_buffer_size
Max_memory =
(Min_thread_memory ~ Max_thread_memory) * max_connections
+ Max_server_memory;
댓글 없음:
댓글 쓰기