[Mysql - Performance parameter]

mysql에서 사용되는 성능관련 파라미터 요약 정리를 해보았습니다.

 

## System variables 

동적으로 Set문을 사용하여 설정할 수 있는 파라미터

 

 

• sort_buffer_size

파일 정렬 버퍼의 크기 제어함. 

변수를 높게 설정하고 행 정렬이 필요한 여러 연결이 있는 경우 메모리가 많이 사용됨.

이 값을 너무 낮게 설정해도 디스크 다이브를 수행하여 데이터가 무작위로 검색되기 때문에 더 느릴 수 있음.

쿼리 수정하는 것이 가장 좋으며, Redis와 같은 쿼리 캐싱을 처리하는 도구를 사용하는 것이 효율적.

mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

default : 0.25 MB

 

 

• read_buffer_size

테이블의 순차 스캔을 수행하는 각 요청에 대해 읽기 버퍼 할당.

이 변수 값은 4KB의 배수여야함.(4KB의 배수가 아닌 경우 설정된 값을 가장 가까운 4KB의 배수로 내림)

이 값을 높게 설정하면 서버 메모리의 많은 부분을 소비함.

mysql> show variables like 'read_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)

default : 0.125MB

 

 

• join_buffer_size

일반 인덱스 스캔, 범위 인덱스 스캔 및 인덱스를 사용하지 않아 전체 테이블 스캔을 수행하는 조인에 사용되는 버퍼의 최소 크기.

인덱스 추가할 경우 빠른 전체 조인을 얻으려면 값을 늘릴 수 있으나, 너무 높게 설정하면 메모리 문제 발생 가능.

인덱스가 사용되지 않는 여러 테이블 간의 복합 조인의 경우 여러 조인 버퍼가 필요할 수 있습니다.

mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

default : 0.25 MB

 

 

• max_heap_table_size

사용자 생성 memory테이블이 증가할 수 있는 최대 크기.

동적으로 수정가능하며, 변수를 설정해도 기존 테이블을 다시 만들거나 변경하지 않는 한 기존 테이블에 영향을 주지 않음.

내부 메모리 테이블의 크기를 제한하기 위해 tmp_table_size와 함께 사용.

mysql> show variables like 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.00 sec)

default : 16MB

 

 

• tmp_table_size

max_heap_table_size가 더 작은 경우 하한이 적용되지만 메모리 내 임시테이블의 가장 큰 크기.

많은 고급 group by 쿼리를 수행하고 사용 가능한 메모리 공간이 큰 경우 tmp_table_size 값을 증가시킴.

mysql> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.01 sec)

default : 16MB

 

 

•  table_open_cache_instances

열린 테이블 캐시 인스턴스의 수를 제한함.

세션 간의 경합을 줄이는 성능을 개선하는데 도움이 됨.

16개 이상의 코어를 사용하는 시스템에서는 값 8 또는 16 권장.

mysql> show variables like 'table_open_cache_instances';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache_instances | 16    |
+----------------------------+-------+
1 row in set (0.00 sec)

 

 

• table_definition_cache

create table이 캐시되어 테이블 open속도를 높이고 테이블당 하나의 항목만 캐시하는 곳.

테이블 수가 많은 경우 값을 높이는 것이 합리적.

해당 변수는 일반 테이블 캐시와 달리 공간을 덜 차지하며 파일 설명자를 사용하지 않음.

기본값 2000 -> MIN(400 + table_open_cache /

mysql> show variables like 'table_definition_cache';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| table_definition_cache | 2000  |
+------------------------+-------+
1 row in set (0.00 sec)

 

 

• max_allowed_packet

반환된 SQL쿼리 또는 행의 연결당 최대 크기.

추출해야하는 큰 BLOB 행이 있는 경우 크기 조정 고려.

mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)

default : 64MB

 

 

• max_connections

mysql서버에 허용된 연결 수 의미.

'Too many connections' 오류 발생시 더 높게 설정하는 것 고려.

너무 높여서 설정하면 서버가 응답하지 않거나 높은 로드 발생 가능.

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

 

 

• thread_cache_size

과도한 스레드 생성 방지를 위한 캐시.

클라이언트가 연결을 끊을 때 스레드가 thread_cache_size보다 적으면 클라이언트의 스레드가 캐시에 저장되는데 스레드 요청시 가능하면 캐시에서 가져온 스레드를 재사용하고 캐시가 비어있을 경우 새 스레드 생성.

8+(max_connections / 100)을 지정하면 충분하다고 설명서에 명시되어 있습니다.

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+
1 row in set (0.01 sec)

 

 

• query_cache_size

높은 로드와 읽기로 부하가 높은 시스템의 경우 해당 변수로 인하여 빈번히 문제 발생.

변수 사용을 끄기위해서는 query_cache_type=0 으로 설정.

mysql8.0에서는 지원 중단, 쿼리 캐싱을 사용하는 경우 Redis / ProxySQL 사용하는 것을 권장.

 

 

 

 

 

 

## Storage Engine - InnoDB

외래키 지원과 함께 다양한 기능을 갖춘 ACID 호환 스토리지 엔진.

 

 

• innodb_buffer_pool_size

InnoDB는 버퍼풀에 의존하기 때문에 서버 total 메모리의 70~80%로 설정하는 것을 권장.

데이터 세트보다 더 큰 메모리 공간을 갖고 버퍼 풀에 대해 더 높은 값을 설정하지만 너무 많이 설정하지 않는 것이 좋음.

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

128 MB

 

 

• innodb_buffer_pool_instances

동시성 워크로드의 경우 이 변수를 설정하면 캐시된 페이지에 대한 읽기/쓰기의 다른 스레드로 동시성을 개선하여 경합 감소 가능.

최소 1 ~ 최대 64  사이에 있어야함.

버퍼 풀에 저장되거나 버퍼풀에서 읽는 각 페이지는 해시 함수를 사용하여 버퍼 풀 인스턴스 중 하나에 무작위 할당.

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+
1 row in set (0.00 sec)

 

 

• innodb_flush_log_at_trx_commit

1로 설정되면 디스크의 로그 파일에 대한 모든 트랜잭션 커밋시 로그 버퍼가 플러쉬되고 최대 데이터 무결성 제공하지만 성능에도 영향.

2로 설정시 모든 트랜잭션 커밋에서 로그 버퍼가 OS파일 캐시로 플러시, ACID요구 사항 완화와 OS충돌의 경우 트랜잭션 손실을 감당할 수 있는경우 최적이며 성능향상.

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.01 sec)

 

 

• innodb_log_file_size

로그 그룹의 로그 파일.

로그 파일의 결합 크기는 512GB보다 약간 작은 최대값을 초과할 수 없음.

로그 파일 크기가 클수록 성능이 더 좋지만 충돌 후 복구 시간을 고려해야함.

드물게 크래시 복구가 발생하는 경우 복구 시간과 피크 작업 중 처리량 최대화의 균형을 맞춰야함.

mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
1 row in set (0.00 sec)

default : 46MB

 

 

• innodb_log_buffer_size

디스크 I/O를 저장하기 위해 변경 데이터를 로그 버퍼에 기록.

트랜잭션 커밋 전에 디스크에 변경 로그를 기록할 필요가 없기 때문에 특히 큰 트랜잭션에 유용.

쓰기 트래픽이 너무 높을때 버퍼를 더 크게 만들면 디스크 I/O 절약 가능.

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)

default : 16MB

 

 

 

 

## MyISAM

현재 프로덕션에서 MyISAM 사용률은 특수한 경우가 아니면 거의 없음.

InnoDB는 현재 MySQL의 기본 스토리지 엔진.

 

 

• slow_query_log

성능이 느린 쿼리를 분석하는데 도움.

값을 0 또는 OFF로 설정하여 로깅을 비활성화할 수 있음.

값을 1 또는 ON으로 활성화.

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

 

 

• long_query_time

쿼리가 수행된 시간이 해당 변수에 설정된 시간보다 오래 걸리는 경우 slow_queries 상태 변수를 증가시킴.

slow_query_log가 활성화된 경우 해당 파일에 기록.

최소값 0 , 최대값 10.

CPU시간이 아닌 실시간으로 측정.

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

 

 

참고

https://myinfrabox.tistory.com/250?category=829169

[MySQL] Performance Cheat Sheet(성능 관련 파라미터들)