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

MySQL Performance Cheat Sheet

 

MySQL 확장가능하고 원하는 성능을 위해 최적화하고 조정할 영역이 많습니다. 일부 변경은 동적으로 수행할 있고 다른 변경은 서버를 다시 시작해야 합니다. 기본 구성으로 MySQL 설치를 하는 것은 매우 일반적이지만 때로는 워크로드 설정 자체에 적합하지 않을 있습니다.

 

MySQL 주요 영역을 요약하여 이를 살펴보겠습니다.

 

System Variables

MySQL에는 변경을 고려할 있는 많은 변수가 있습니다. 일부 변수는 동적이므로 SET 문을 사용하여 설정할 있습니다. 다른 것들은 구성 파일(: /etc/my.cnf, etc/mysql/my.cnf)에서 설정한 서버를 다시 시작해야 합니다. 서버를 최적화하기 위해 조정하는데 필요한 일반적인 일반적인 사항을 살펴보겠습니다.

 

sort_buffer_size

변수는 파일 정렬 버퍼의 크기를 제어합니다. , 쿼리에서 행을 정렬해야 할때마다 변수의 값을 사용하여 할당해야 하는 크기를 제한할 있습니다. 변수는 처리된 쿼리당(또는 연결당) 기준이라는 점에 유의해야 합니다. , 변수를 높게 설정하고 정렬이 필요한 여러 연결이 있는 경우 메모리가 많이 소모됩니다. 그러나 전역 상태 변수 Sort_merge_passes 확인하여 요구 사항을 모니터링할 있습니다. 값이 크면 sort_buffer_size 시스템 변수의 값을 높이는 것을 고려해야 합니다. 그렇지 않으면 필요한 적당한 한도까지 사용합니다. 값을 너무 낮게 설정하거나 처리할 쿼리가 경우 디스크 다이브를 수행하여 데이터가 무작위로 검색되기 때문에 정렬 효과가 예상보다 느릴 있습니다. 이로 인해 성능이 저하될 있습니다. 그러나 쿼리를 수정하는 것이 가장 좋습니다. 그렇지 않고 애플리케이션이 대규모 쿼리를 가져오도록 설계되고 정렬이 필요한 경우 Redis 같은 쿼리 캐싱을 처리하는 도구를 사용하는 것이 효율적입니다. 기본적으로 MySQL 8.0에서 현재 세트는 256KiB입니다. 정렬을 많이 사용하거나 호출하는 쿼리가 있는 경우에만 적절하게 설정합니다..

 

read_buffer_size

MySQL 문서에서는 테이블의 순차 스캔을 수행하는 요청에 대해 읽기 버퍼를 할당한다고 언급합니다. read_buffer_size 시스템 변수는 버퍼 크기를 결정합니다. MyISAM에도 유용하지만 변수는 모든 스토리지 엔진에도 영향을 미칩니다. MEMORY테이블의 경우 메모리 블록 크기를 결정하는데 사용됩니다.

 

기본적으로 MyISAM 테이블에 대한 순차 스캔을 수행하는 스레드는 스캔하는 테이블에 대해 크기(바이트 단위) 버퍼를 할당합니다. 모든 스토리지 엔진(InnoDB 포함)에도 적용되므로 ORDER BY 사용하여 행을 정렬하고 인덱스를 임시 파일에 캐싱하는 쿼리에 유용합니다. 많은 순차 스캔을 수행하는 경우 파티션 테이블에 대량 삽입, 중첩 쿼리의 결과를 캐싱한 다음 값을 높이는 것을 고려합니다. 변수의 값은 4KB 배수여야 합니다. 4KB 배수가 아닌 값으로 설정된 경우 해당 값은 가장 가까운 4KB 배수로 내림됩니다. 값을 높게 설정하면 서버 메모리의 많은 부분을 소비한다는 점을 알아야 합니다. 환경에 대한 적절한 벤치마킹 모니터링 없이는 사용하지 않는 것이 좋습니다.

 

read_rnd_buffer_size

변수는 정렬 작업에 따라 정렬된 순서로 MyISAM 테이블에서 읽기를 처리하며, 디스크 탐색을 피하기 위해 버퍼를 통해 행을 읽습니다. 문서에 따르면 정렬 작업에 따라 임의의 순서로 또는 MyISAM 테이블에서 정렬된 순서로 행을 읽을때 디스크 탐색을 피하기 위해 버퍼를 통해 행을 읽고( 버퍼 크기를 통해 결정)행을 읽습니다. 변수를 값으로 설정하면 ORDER BY 성능이 상당히 향상될 있습니다. , 이는 클라이언트별로 할당된 버퍼이므로 전역 변수를 값으로 설정해서는 안된다. 대신 대용량 쿼리를 실행해야 하는 클라이언트 내에서만 세션 변수를 변경합니다. 그러나 특히 MRR 활용할 MariaDB에는 적용되지 않는다는 점을 고려해야 합니다. MariaDB mrr_buffer_size 사용하고 MySQL read_buffer_size read_rnd_buffer_size 사용합니다.

 

join_buffer_size

기본적으로 값은 256K입니다. 일반 인덱스 스캔, 범위 인덱스 스캔 인덱스를 사용하지 않아 전체 테이블 스캔을 수행하는 조인에 사용되는 버퍼의 최소 크기입니다. BKA 최적화에서도 사용됩니다(기본적으로 비활성화됨). 인덱스를 추가할 없는 경우 빠른 전체 조인을 얻으려면 값을 늘리십시오. 값을 너무 높게 설정하면 메모리 문제가 발생할 있으므로 주의하십시오. 테이블 간의 전체 조인에 대해 하나의 조인 버퍼가 할당된다는 점을 기억하십시오. 인덱스가 사용되지 않는 여러 테이블 간의 복합 조인의 경우 여러 조인 버퍼가 필요할 있습니다. 전체 조인이 필요한 세션(SET SESSION 구문 사용)에서는 전역적으로 낮게 설정하고 높게 설정하는 것이 가장 좋습니다. 64비트 플랫폼에서 Windows 경고와 함께 4GB 초과하는 값을 4GB-1 자릅니다.

- BKA : Batched Key Access Flags

BKA 결합 알고리즘의 사용을 제어합니다.

on으로 설정된 경우 batched_key_access 영향을 미치려면 mrr 플래그도 켜져 있어야합니다. 현재 MRR 비용 산정은 너무 비관적입니다. 따라서 BKA 사용하려면 mrr_cost_based 해제해야합니다.

 

max_heap_table_size

이것은 사용자 생성 MEMORY 테이블이 증가할 있는 최대 크기(바이트)입니다. 이는 애플리케이션이 MEMORY 스토리지 엔진 테이블을 처리할 유용합니다. 서버가 활성 상태일때 변수를 설정해도 기존 테이블을 다시 만들거나 변경하지 않는 기존 테이블에 영향을 주지 않습니다. max_heap_table_size tmp_table_size 작은 값도 내부 메모리 테이블을 제한합니다. 변수는 또한 내부 메모리 테이블의 크기를 제한하기 위해 tmp_table_size 함께 사용되며(max_heap_table_size 적용하므로 Engine=MEMORY 명시적으로 생성된 테이블과 다름) 둘중 작은 값이 적용됩니다.

 

tmp_table_size

max_heap_table_size 작은 경우 하한이 적용되지만 메모리 임시 테이블(MEMORY 테이블 아님) 가장 크기입니다. 메모리 임시 테이블이 제한을 초과하면 MySQL 자동으로 이를 디스크 임시 테이블로 변환합니다. 많은 고급 GROUP BY 쿼리를 수행하고 사용 가능한 메모리 공간이 경우 tmp_table_size( 필요한 경우 max_heap_table_size) 값을 늘립니다. Created_tmp_disk_tables Created_tmp_tables 변수의 값을 비교하여 생성된 내부 온 디스크 임시 테이블 수와 생성된 내부 임시테이블 수를 비교할 있습니다. ClusterControl에서 대시보드 -> Temporary Objects graph 통해 이를 모니터링할 있습니다.

 

table_open_cache

데이터 세트에서 자주 액세스하는 테이블이 많은 경우 변수의 값을 늘릴 있습니다. 연결 기준으로 모든 스레드에 적용됩니다. 값은 서버가 하나의 테이블 캐시 인스턴스에서 열린 상태로 유지할 있는 최대 테이블 수를 나타냅니다. 값을 증가시키면 mysqld 필요로 하는 파일 디스크립터의 수가 증가하지만 open_files_limit 값을 확인하거나 *nix 운영 체제에 설정된 SOFT HARD 제한이 얼마나 큰지 확인하는 것이 좋습니다. Opened_tables 상태 변수를 확인하여 테이블 캐시를 늘려야 하는지 여부를 모니터링할 있습니다. Opened_tables 값이 크고 FLUSH TABLES 자주 사용하지 않는 경우(모든 테이블을 강제로 닫고 다시 열어야 ) table_open_cache 변수의 값을 늘려야 합니다. table_open_cache 대한 값이 작고 자주 액세스하는 테이블이 많으면 서버 성능에 영향을 있습니다. MySQL 프로세스 목록에 "테이블 열기" 또는 "테이블 닫기" 상태의 항목이 많이 있는 경우 변수의 값을 조정해야 하지만 앞서 언급한 주의 사항에 유의하십시오. ClusterControl Dashboards -> Table Open Cache Status 또는 Dashboards -> Open Tables에서 확인할 있습니다. 자세한 내용은 여기에서 확인할 있습니다.

 

table_open_cache_instances

변수를 설정하면 확장성과 물론 세션 간의 경합을 줄이는 성능을 개선하는 도움이 됩니다. 여기에서 설정한 값은 열린 테이블 캐시 인스턴스의 수를 제한합니다. 열린 테이블 캐시는 table_open_cache / table_open_cache_instances 크기의 여러 작은 캐시 인스턴스로 분할될 있습니다. 세션은 DML 문에 액세스하기 위해 하나의 인스턴스만 잠그면 됩니다. 이렇게 하면 인스턴스 간의 캐시 액세스가 분할되어 테이블에 액세스하는 세션이 많을 캐시를 사용하는 작업의 성능이 향상됩니다. (DDL 문은 여전히 ​​전체 캐시에 대한 잠금을 요구하지만 이러한 문은 DML 문보다 훨씬 빈번합니다.) 16 이상의 코어를 일상적으로 사용하는 시스템에서는 8 또는 16 권장됩니다.

 

table_definition_cache

Cache table definitions i.e. this is where the CREATE TABLE are cached to speed up opening of tables and only one entry per table. It would be reasonable to increase the value if you have large number of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. Peter Zaitsev of Percona suggest if you can try the setting of the formula below,

 

캐시 테이블 정의, CREATE TABLE 캐시되어 테이블 열기 속도를 높이고 테이블당 하나의 항목만 캐시하는 곳입니다. 테이블 수가 많은 경우 값을 높이는 것이 합리적입니다. 테이블 정의 캐시는 일반 테이블 캐시와 달리 공간을 차지하며 파일 설명자를 사용하지 않습니다. Percona Peter Zaitsev 아래 공식의 설정을 시도해 있는지 제안합니다.

1. 사용자 정의 테이블 + 50K+ 테이블이 아닌 경우 10%

 

그러나 기본값은 2000으로 제한되는 다음 공식을 기반으로 합니다.

1. MIN(400 + table_open_cache / 2, 2000)

 

따라서 기본값에 비해 테이블 ​​수가 많을 경우 값을 높이는 것이 합리적입니다. InnoDB에서 변수는 데이터 사전 캐시에 대한 열린 테이블 인스턴스 수의 소프트 제한으로 사용됩니다. 변수의 현재 값을 초과하면 LRU 메커니즘을 적용합니다. 제한은 다음 서버가 다시 시작될 때까지 거의 사용되지 않는 테이블 인스턴스를 캐시하는데 상당한 양의 메모리가 사용되는 상황을 해결하는데 도움이 됩니다. 따라서 외래키 관계가 있는 부모 자식 테이블 인스턴스는 LRU 목록에 배치되지 않고 table_definition_cache 의해 정의된 제한보다 높게 부과할 있으며 LRU 동안 메모리에서 축출되지 않습니다. 또한 table_definition_cache 한번에 열수 있는 테이블당 파일 테이블스페이스 수에 대한 소프트 제한을 정의합니다. 테이블스페이스는 innodb_open_files 의해 제어되며 실제로 변수가 모두 설정되어 있는 경우 이러한 변수 가장 높은 설정이 사용됩니다. 변수가 모두 설정되지 않은 경우 기본값이 높은 table_definition_cache 사용됩니다. 열린 테이블스페이스 파일 핸들의 수가 table_definition_cache 또는 innodb_open_files 의해 정의된 제한을 초과하는 경우 LRU 메커니즘은 테이블스페이스 파일 LRU 목록에서 완전히 플러시되고 현재 확장되지 않는 파일을 검색합니다. 프로세스는 테이블스페이스가 열릴 때마다 수행됩니다. "inactive" 테이블스페이스가 없으면 테이블스페이스 파일이 닫히지 않습니다. 따라서 이것을 염두에 두어야 합니다.

 

max_allowed_packet

이것은 반환된 SQL쿼리 또는 행의 연결당 최대 크기입니다. 값은 MySQL 5.6에서 마지막으로 증가했습니다. 그러나 MySQL 8.0(최소한 8.0.3 이상)에서 현재 기본값은 64MiB입니다. 추출해야 하는(또는 읽어야 하는) BLOB 행이 있는 경우 이를 조정하는 것을 고려할 있습니다. 그렇지 않으면 기본 설정을 8.0으로 그대로 있지만 이전 버전에서는 기본값이 4MiB이므로 다음 경우에 대비할 있습니다. ER_NET_PACKET_TOO_LARGE 오류가 발생했습니다. MySQL 8.0 서버 또는 클라이언트와 주고받을 있는 가장 패킷은 1GB입니다.

 

skip_name_resolve

MySQL 서버는 호스트 이름 확인으로 들어오는 연결을 처리합니다. 기본적으로 MySQL DNS 조회를 수행한다는 것을 의미하는 호스트 이름 확인을 비활성화하지 않으며 우연히 DNS 느린 경우 데이터베이스 성능이 저하될 있습니다. DNS 확인이 필요하지 않은경우 기능을 켜고 DNS 조회가 비활성화된 경우 MySQL 성능을 개선하는 것을 고려합니다. 변수는 동적이지 않으므로 MySQL 구성 파일에서 설정한 경우 서버를 다시 시작해야 합니다. 선택적으로 mysqld 데몬을 시작하고 --skip-name-resolve 옵션을 전달하여 이를 활성화할 있습니다.

 

max_connections

이것은 MySQL 서버에 허용된 연결수입니다. MySQL 'Too many connections' 오류를 발견하면 높게 설정하는 것을 고려할 있습니다. 기본적으로 151 값은 특히 프로덕션 데이터베이스에서 충분하지 않고 서버 리소스가 많다는 점을 고려하면 충분합니다(특히 MySQL 전용 서버인 경우 서버 리소스를 낭비하면 안됩니다). 그러나 충분한 파일 디스크립터가 있어야 하며 그렇지 않으면 파일 디스크립터가 부족합니다. 경우 *nix 운영 체제의 SOFT HARD 제한을 조정하고 MySQL에서 높은 값의 open_files_limit 설정하는 것을 고려합니다(5000 기본 제한임). 응용 프로그램이 데이터베이스에 대한 연결을 올바르게 닫지 않는 경우가 매우 빈번하다는 점을 유의해야 합니다. max_connections 높게 설정하면 서버가 응답하지 않거나 높은 로드가 발생할 있습니다. 애플리케이션 수준에서 연결 풀을 사용하면 여기에서 문제를 해결하는 도움이 있습니다.

 

 

thread_cache_size

과도한 스레드 생성을 방지하기 위한 캐시입니다. 클라이언트가 연결을 끊을 스레드가 thread_cache_size보다 적으면 클라이언트의 스레드가 캐시에 저장됩니다. 스레드 요청은 가능하면 캐시에서 가져온 스레드를 재사용하여 충족되며 캐시가 비어 있는 경우에만 스레드가 생성됩니다. 연결이 많은 경우 변수를 늘려 성능을 향상시킬 있습니다. 일반적으로 좋은 스레드 구현이 있는 경우 눈에 띄는 성능 향상을 제공하지 않습니다. 그러나 서버가 초당 수백 개의 연결을 보는 경우 일반적으로 대부분의 연결이 캐시된 스레드를 사용하도록 thread_cache_size 충분히 높게 설정해야 합니다. Connections Threads_created 상태 변수의 차이점을 조사하여 스레드 캐시가 얼마나 효율적인지 있습니다. 설명서에 명시된 공식을 사용하면 8 + (max_connections / 100)이면 충분합니다.

 

query_cache_size

For some setup, this variable is their worst enemy. For some systems experiencing high load and are busy with high reads, this variable will bog you down. There has been benchmarks that were well-and-tested by e.g., Percona. This variable must be set to 0 along with query_cache_type = 0 as well to turn it off. The good news in MySQL 8.0 is that, the MySQL Team has stopped supporting this, as this variable can really cause performance issues. I have to agree on their blog that it is unlikely to improve predictability of performance. If you are engaged to use query caching, I suggest to use Redis or ProxySQL.

 

일부 설정의 경우 변수가 최악의 적입니다. 높은 로드를 경험하고 높은 읽기로 부하가 높은 일부 시스템의 경우 변수로 인해 문제가 발생합니다. Percona 같이 테스트된 벤치마크가 있습니다. 변수를 끄려면 query_cache_type = 0 함께 0으로 설정해야 합니다. MySQL 8.0 좋은 소식은 변수가 실제로 성능 문제를 일으킬 있기 때문에 MySQL 팀에서 지원을 중단했다는 것입니다. 쿼리 캐싱을 사용하는 경우 Redis 또는 ProxySQL 사용하는 것이 좋습니다.

 

 

 

 

Storage Engine - InnoDB

InnoDB 외래키 지원(선언적 참조 무결성) 함께 제공할 다양한 기능을 갖춘 ACID 호환 스토리지 엔진입니다. 여기에는 말이 많지만 튜닝을 위해 고려해야 특정 변수는 다음과 같습니다.

 

innodb_buffer_pool_size

변수는 MyISAM 버퍼처럼 작동하며 제공할 것이 많습니다. InnoDB 버퍼풀에 크게 의존하기 때문에 일반적으로 값을 서버 메모리의 70%-80% 설정하는 것이 좋습니다. 또한 데이터 세트보다 메모리 공간을 갖고 버퍼 풀에 대해 높은 값을 설정하지만 너무 많이 설정하지 않는 것이 좋습니다. ClusterControl에서 이것은 대시보드 -> InnoDB 메트릭 -> InnoDB 버퍼 페이지 그래프를 사용하여 모니터링할 있습니다. 변수 Innodb_buffer_pool_pages* 사용하여 SHOW GLOBAL STATUS 이를 모니터링할 수도 있습니다.

 

innodb_buffer_pool_instances

동시성 워크로드의 경우 변수를 설정하면 캐시된 페이지에 대한 읽기/쓰기의 다른 스레드로 동시성을 개선하고 경합을 줄일 있습니다. 최소 innodb_buffer_pool_instances 1(최소) 64(최대) 사이에 있어야 합니다. 버퍼 풀에 저장되거나 버퍼풀에서 읽는 페이지는 해시 함수를 사용하여 버퍼 인스턴스 하나에 무작위로 할당됩니다. 버퍼 풀은 자체 여유 목록, 플러시 목록, LRU 버퍼 풀에 연결된 기타 모든 데이터 구조를 관리하며 자체 버퍼풀 뮤텍스에 의해 보호됩니다. 옵션은 innodb_buffer_pool_size >= 1GiB이고 크기가 버퍼 인스턴스로 나누어져 있을때만 적용된다는 점에 유의합니다.

 

innodb_log_file_size

변수는 로그 그룹의 로그 파일입니다. 로그 파일의 결합 크기(innodb_log_file_size * innodb_log_files_in_group) 512GB보다 약간 작은 최대값을 초과할 없습니다. 로그 파일 크기가 클수록 성능이 좋지만 걱정해야 하는 단점(중요한 ) 있습니다. 바로 충돌 복구 시간입니다. 드물게 크래시 복구가 발생하는 경우 복구 시간과 피크 작업 처리량 최대화의 균형을 맞춰야 합니다. 제한으로 인해 충돌 복구 프로세스가 20 길어질 있다는것을 유의해야 합니다.

 

자세히 설명하자면 값이 클수록 InnoDB 트랜잭션 로그에 좋고 안정적이고 좋은 쓰기 성능에 매우 중요합니다. 값이 클수록 버퍼 풀에서 필요한 체크포인트 플러시 활동이 적어 디스크 I/O 절약됩니다. 그러나 데이터베이스가 비정상적으로 종료되면(충돌 또는 종료, OOM 또는 우발적) 복구 프로세스가 상당히 느립니다. 이상적으로는 프로덕션 환경에 1-2GiB 있을 있지만 물론 조정할 있습니다. 변경 사항을 벤치마킹하면 특히 충돌 성능을 확인하는데 이점이 있습니다.

 

innodb_log_buffer_size

InnoDB 디스크 I/O 저장하기 위해 변경 데이터를 로그 버퍼에 기록하고 기본값이 8MiB innodb_log_buffer_size 값을 사용합니다. 이것은 트랜잭션 커밋 전에 디스크에 변경 로그를 기록할 필요가 없기 때문에 특히 트랜잭션에 유용합니다. 쓰기 트래픽이 너무 높을대(삽입, 삭제, 업데이트) 버퍼를 크게 만들면 디스크 I/O 절약됩니다.

 

innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit 1 설정되면 디스크의 로그 파일에 대한 모든 트랜잭션 커밋시 로그 버퍼가 플러시되고 최대 데이터 무결성을 제공하지만 성능에도 영향을 미칩니다. 2 설정하면 모든 트랜잭션 커밋에서 로그 버퍼가 OS 파일 캐시로 플러시됩니다. 2 의미는 ACID 요구 사항을 완화할 있고 OS 충돌의 경우 마지막 1~2 동안 트랜잭션 손실을 감당할 있는 경우 최적이며 성능을 향상시킵니다.

 

innodb_thread_concurrency

InnoDB 엔진이 개선됨에 따라 엔진이 동시성을 기본값(0)으로 유지하여 동시성을 제어할 있도록 하는 것이 좋습니다. 동시성 문제가 있는 경우 변수를 조정할 있습니다. 권장되는 값은 CPU 수와 디스크 수의 2배입니다. 동적 변수는 MySQL 서버를 다시 시작하지 않고도 설정할 있음을 의미합니다.

 

innodb_flush_method

변수는 가장 적합한 하드웨어에서 시도하고 테스트해야 합니다. 배터리 지원 캐시와 함께 RAID 사용하는 경우 DIRECT_IO I/O 압력을 완화하는데 도움이 됩니다. 직접 I/O 캐시되지 않으므로 버퍼풀 파일 시스템 캐시로 이중 버퍼링을 방지합니다. 디스크가 SAN 저장되어 있으면 O_DSYNC 대부분 SELECT문을 사용하여 읽기가 많은 워크로드에 빠를 있습니다.

 

innodb_file_per_table

innodb_file_per_table MySQL 5.6에서 기본적으로 ON입니다. 이것은 거대한 공유 테이블스페이스를 피하고 테이블을 삭제하거나 자를때(Truncate) 공간을 재확보할 있도록 하므로 일반적으로 권장됩니다. 별도의 테이블스페이스는 Xtrabackup 부분 백업 구성표에도 유용합니다.

 

innodb_stats_on_metadata

이것은 더티 페이지의 비율을 제어하려는 시도이며 Innodb 플러그인 이전에는 이것이 실제로 더티 버퍼 플러싱을 조정하는 유일한 방법이었습니다. 그러나 3% 더티 버퍼가 있는 서버를 보았고 최대 체크포인트 수명에 도달했습니다. 이것이 더티 버퍼 플러싱을 증가시키는 방식은 높은 io 가진 서브 시스템에서도 확장되지 않으며 일정 퍼센트(%) 더티 페이지가 양을 초과할 초당 더티 버퍼 플러싱을 효과적으로 배로 늘립니다.

 

innodb_io_capacity

설정은 Innodb 모든 작업에서 IO 사용할 있기를 바라는 모든 희망에도 불구하고 단순히 초당 더티 페이지 플러시( 미리 읽기와 같은 기타 백그라운드 작업) 양을 제어합니다. 이것을 크게 만들면 초당 많이 플러시됩니다. 이것은 적응하지 않고 플러시할 더티 버퍼가 있는 경우 매초마다 많은 IOPS 수행합니다. 쓰기 워크로드가 충분히 낮은 경우(, 더티 페이지가 거의 즉시 플러시되므로 경우 트랜잭션 로그가 없는 것이 나을 있음) IO 통합의 최적화를 효과적으로 제거합니다. 또한 너무 높게 설정하면 트랜잭션 로그에 대한 데이터 읽기 쓰기가 빠르게 중단될 있습니다.

 

innodb_write_io_threads

디스크에 대한 쓰기가 진행 중인 스레드 수를 제어합니다. Linux 기본 AIO 사용할 있다면 이것이 여전히 유용한지 모르겠습니다. 또한 이상의 스레드가 동일한 파일에 병렬 쓰기를 허용하지 않는 파일 시스템에서 쓸모 없게 수도 있습니다(특히 테이블이 상대적으로 적거나 전역 테이블스페이스를 사용하는 경우).

 

innodb_adaptive_flushing

워크로드에 따라 InnoDB 버퍼 풀에서 더티 페이지 플러시 속도를 동적으로 조정할지 여부를 지정합니다. 플러시 속도를 동적으로 조정하는 것은 I/O 활동의 버스트를 방지하기 위한 것입니다. 보통 기본적으로 활성화되어 있습니다. 변수를 활성화하면 더티 페이지 수와 트랜잭션 로그 증가율을 기반으로 보다 스마트하게 적극적으로 플러시합니다.

 

innodb_dedicated_server

변수는 전역적으로 적용되는 MySQL 8.0 새로운 기능이며 동적 변수가 아니므로 MySQL 다시 시작해야 합니다. 그러나 설명서에 따르면 변수는 MySQL 전용 서버에서 실행되는 경우에만 활성화되는 것이 좋습니다. 그렇지 않으면 공유 호스트에서 기능을 활성화하거나 다른 응용 프로그램과 시스템 리소스를 공유하지 않습니다. 이것이 활성화되면 InnoDB innodb_buffer_pool_size, innodb_log_file_size, innodb_flush_method 변수에 대해 감지된 메모리 양에 대한 자동 구성을 수행합니다. 단점은 언급된 감지된 변수에 원하는 값을 적용할 가능성이 없다는 것뿐입니다.

 

MyISAM

key_buffer_size

InnoDB 현재 MySQL 기본 스토리지 엔진이며, 애플리케이션의 일부로 MyISAM 생산적으로 사용하지 않는한 key_buffer_size 기본값을 줄일 있습니다(그러나 현재 프로덕션에서 MyISAM 사용률은 특수한 경우가 아니면 이제 거의 없습니다). 메모리가 있고 OS 캐시 InnoDB 버퍼 풀을 위한 나머지 메모리를 전용으로 사용하는 경우 시작시 RAM 1% 또는 256MiB 설정하는 것이 좋습니다.

 

Other Provisions For Performance

slow_query_log

변수는 성능이 느린 쿼리를 분석하는 도움이 있습니다. 값을 0 또는 OFF 설정하여 로깅을 비활성화할 있습니다. 이것을 활성화하려면 1 또는 ON으로 설정합니다. 기본값은 --slow_query_log 옵션이 제공되는지 여부에 따라 다릅니다. 로그 출력 대상은 log_output 시스템 변수에 의해 제어됩니다. 해당 값이 NONE이면 로그가 활성화된 경우에도 로그 항목이 기록되지 않습니다. slow_query_log_file 변수를 설정하여 쿼리 로그 파일의 파일 이름 또는 대상을 설정할 있습니다.

 

long_query_time

쿼리가 이 시간(초)보다 오래 걸리는 경우 서버는 Slow_queries 상태 변수를 증가시킵니다. 느린 쿼리 로그가 활성화된 경우 쿼리는 느린 쿼리 로그 파일에 기록됩니다. 이 값은 CPU 시간이 아닌 실시간으로 측정되므로 부하가 적은 시스템에서 임계값 미만인 쿼리가 부하가 많은 시스템에서 임계값보다 높을 수 있습니다. long_query_time의 최소값과 기본값은 각각 0과 10입니다. 또한 min_examined_row_limit 변수가 0보다 크게 설정되면 반환되는 행 수가 min_examined_row_limit에 설정된 값보다 적더라도 시간이 너무 오래 걸리더라도 쿼리를 기록하지 않습니다.

 

sync_binlog

변수는 MySQL binlog 디스크에 동기화하는 빈도를 제어합니다. 기본적으로(>=5.7.7) 1 설정되어 트랜잭션이 커밋되기 전에 디스크와 동기화됩니다. 그러나 이는 쓰기 횟수 증가로 인해 성능에 부정적인 영향을 미칩니다. 그러나 이것은 슬레이브와 함께 ACID 엄격하게 준수하려는 경우 가장 안전한 설정입니다. 또는 디스크 동기화를 비활성화하고 OS 의존하여 바이너리 로그를 때때로 디스크에 플러시하려는 경우 값을 0으로 설정할 있습니다. 1보다 높게 설정하면 N개의 바이너리 로그 커밋 그룹이 수집된 binlog 디스크에 동기화됨을 의미합니다. 여기서 N > 1입니다.

 

Dump/Restore Buffer Pool

프로덕션 데이터베이스가 Cold start/Restart 부터 워밍업하는 것은 매우 일반적인 일입니다. 다시 시작하기 전에 현재 버퍼 풀을 덤프하여 버퍼풀의 내용을 저장하고 버퍼풀이 가동되면 내용을 다시 버퍼 풀로 로드합니다. 따라서 이렇게 하면 데이터베이스를 캐시로 다시 워밍업할 필요가 없습니다. 버전은 이후 5.6에서 도입되었지만 Percona Server 5.5에서 이미 사용할 있습니다. 기능을 활성화하려면 innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON 변수를 모두 설정합니다.

 

Hardware

CPU 경우, 다중 코어가 있는 빠른 프로세서는 최소한 5.6 이후의 최신 버전에서 MySQL 최적입니다. Intel Xeon/Itanium 프로세서는 비싸지만 확장 가능하고 안정적인 컴퓨팅 플랫폼에 대해 테스트되었습니다. Amazon ARM 아키텍처에서 실행되는 EC2 인스턴스를 출시했습니다. 개인적으로 ARM 아키텍처에서 MySQL 실행하거나 기억해 보지는 않았지만 전에 만들어진 벤치마크가 있습니다. 최신 CPU 온도, 부하 OS 절전 정책에 따라 주파수를 확장 축소할 있습니다. 그러나 Linux OS CPU 설정이 다른 거버너로 설정될 가능성이 있습니다. 다음을 수행하여 "성능" 거버너로 이를 확인하거나 설정할 있습니다.

 

1. echo performance | sudo tee /sys/devices/system/cpu/cpu[0-9]*/cpufreq/scaling_governor

메모리의 경우 메모리가 크고 데이터 세트의 크기와 동일할 있어야 한다는 것이 매우 중요합니다. swappiness = 1인지 확입합니다. sysctl 확인하거나 procfs에서 파일을 확인하여 확인할 있습니다. 이것은 다음을 수행하여 달성됩니다.

$ sysctl -e vm.swappiness

vm.swappiness = 1

 

또는 다음과 같이 값을 1 설정합니다.

$ sudo sysctl vm.swappiness=1

vm.swappiness = 1

 

메모리 관리를 위해 고려해야할 다른 좋은점은 THP(Transparrent Huge Pages) 끄는 것을 고려하는 것입니다. 예전에는 CPU 사용률과 관련하여 이상한 문제가 발생했으며 이것이 디스크 I/O 때문이라고 생각했습니다. 문제는 런타임 중에 동적으로 메모리를 할당하는 커널 kugepaged 스레드에 있는 것으로 나타났습니다. 뿐만 아니라 커널이 조각 모음을 수행하는 동안 메모리가 THP 전달되면서 빠르게 할당됩니다. 표준 HugePages 메모리는 시작 사전 할당되며 런타임 중에 변경되지 않습니다. 다음을 수행하여 이를 확인하고 비활성화할 있습니다.

$ cat /sys/kernel/mm/transparent_hugepage/enabled

$ echo "never" > /sys/kernel/mm/transparent_hugepage/enabled

 

디스크의 경우 처리량이 중요합니다. RAID10 사용하는 것은 배터리 백업 장치가 있는 데이터베이스에 가장 적합한 설정입니다. 높은 디스크 처리량과 읽기/쓰기를 위한 높은 디스크 I/O 제공하는 플래시 드라이브의 출시로 높은 디스크 활용도와 디스크 I/O 관리할 있는 것이 중요합니다.

 

Operating System

MySQL에서 실행되는 대부분의 프로덕션 시스템은 Linux에서 실행됩니다. MySQL Linux에서 테스트 벤치마킹되었으며 MySQL설치의 사실상 표준인 것처럼 들리기 때문입니다. 그러나 물론 Unix 또는 Windows 플랫폼에서 사용하는 것을 막을 있는 것은 없습니다. 플랫폼이 테스트되었고 문제가 발생할 경우를 대비하여 도움을 있는 광범위한 커뮤니티가 있다면 쉬울 것입니다. 대부분의 설정은 RHEL/Centos/Fedora Debian/Ubuntu 시스템에서 실행됩니다. AWS에서 Amazon에는 Amazon Linux 있으며 일부에서는 프로덕션에서도 사용하고 있습니다.

 

설정시 고려해야할 가장 중요한 것은 파일 시스템이 XFS 또는 Ext4 사용하고 있다는 것입니다. 파일 시스템 사이에는 장단점이 있으며 어떤 사람들은 XFS Ext4 능가한다고 말하지만 Ext4 XFS 능가한다는 보고도 있습니다. ZFS 또한 대체 파일 시스템의 좋은 후보로 떠오르고 있습니다. Jervin Real(Percona) 이에 대한 훌륭한 리소스를 가지고 있습니다.

 

원문 : 

https://severalnines.com/database-blog/mysql-performance-cheat-sheet

 

External Links :

https://developer.okta.com/blog/2015/05/22/tcmalloc

https://www.percona.com/blog/2012/07/05/impact-of-memory-allocators-on-mysql-performance/

https://www.percona.com/live/18/sessions/benchmark-noise-reduction-how-to-configure-your-machines-for-stable-results

https://zfs.datto.com/2018_slides/real.pdf

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ladbi/disabling-transparent-hugepages.html#GUID-02E9147D-D565-4AF8-B12A-8E6E9F74BEEA

 

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※

 

 

Designed by JB FACTORY