Amazon RDS for MySQL의 파라미터 구성 모범 사례, 1부: 성능 관련 파라미터

https://aws.amazon.com/ko/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-1-parameters-related-to-performance/

Best practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance | Amazon Web Servi

이 블로그 게시물은 2022년 5월에 마지막으로 검토되거나 업데이트되었습니다.

MySQL용 Amazon Relational Database Service(Amazon RDS)를 사용 하면 비용 효율적이고 크기 조정 가능한 하드웨어 용량으로 몇 분 만에 확장 가능한 MySQL 서버를 배포할 수 있습니다. Amazon RDS를 사용하면 백업, 소프트웨어 패치 적용, 모니터링, 확장 및 복제를 포함하여 시간이 많이 소요되는 데이터베이스 관리 작업을 관리하여 애플리케이션 개발에 집중할 수 있습니다.

대부분의 MySQL용 Amazon RDS 사용자는 400개 이상의 사용 가능한 서버 구성 파라미터 값을 변경할 필요가 없습니다. 그러나 Amazon RDS에서 제공하는 기본 구성을 사용자 지정하려는 경우 AWS Management 콘솔 또는 AWS CLI를 사용하여 사용자 지정 파라미터 그룹을 생성하고 데이터베이스 인스턴스에 적용할 수 있습니다.

참고: 이 게시물에 언급된 기본값은 Amazon RDS for MySQL 5.7에 적용됩니다. 해당 MySQL 설명서 또는 AWS CLI 또는 Amazon RDS 콘솔 을 사용하여 액세스할 수 있는 RDS for MySQL 8.0 기본 파라미터 그룹에서 MySQL 8.0용 Amazon RDS의 기본값을 찾을 수 있습니다 .

일부 매개변수 설정 기본 사항

파라미터 그룹을 사용하여 전역적으로 파라미터를 설정할 수 있습니다. SET또는 명령 을 사용하여 특정 세션에 대해 설정할 수 있습니다 . 매개변수 그룹에서 매개변수는 전역적으로 모든 세션에 대해 설정됩니다. 대조적으로 SET명령을 사용하여 특정 세션에 대해서만 매개변수 값을 설정할 수 있습니다. 이때 변수 이름 앞에 SESSION, @@session, 또는 를 붙 @@입니다. 이러한 set 문의 예는 SET sort_buffer_size=10000; 또는  SET @@local.sort_buffer_size=10000;.

수정자가 없으면 SET세션 변수를 변경합니다. 인스턴스에 기본 파라미터 그룹을 사용하는 경우 먼저 사용자 지정 파라미터 그룹을 생성하여 인스턴스에 연결해야 합니다. 사용자 지정 파라미터 그룹 사용에 대한 자세한 내용은 AWS 지원 문서 Amazon RDS DB 파라미터 그룹의 값을 수정하려면 어떻게 해야 합니까? 를 참조하십시오.

매개변수는 정적이거나 동적일 수 있습니다. 정적 매개변수 변경 사항을 적용하려면 인스턴스를 다시 시작해야 합니다 . 동적 매개변수 변경 사항은 다시 시작하지 않고도 온라인으로 적용되므로 변경할 수 있습니다.

동적 매개변수는 세션 수준 또는 전역 범위를 가질 수 있습니다. 전역 범위 의 변수 는 전체 서버와 모든 세션에 영향을 미친다는 것을 의미합니다. 반면 세션 범위 가 있는 변수는 해당 변수가 설정된 세션에만 유효합니다. 일부 변수에는 전역 및 세션 범위가 있습니다. 이러한 경우 전역 값은 세션 범위의 기본값입니다. 세션 범위가 있는 매개변수에 대한 전역 변경은 새 세션에만 영향을 줍니다. 연결을 끊었다가 다시 연결하거나 데이터베이스를 다시 시작하거나 현재 연결에 대한 세션 설정을 변경해야 합니다.

변수의 현재 값을 쿼리하려면 show variable명령을 사용합니다. 다음은 예입니다.

show variables like "max_connections";

이 명령은 현재 최대 연결 설정이 무엇인지 보여 주는 다음과 같은 결과 집합을 반환합니다.

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

global앞의 것과 같이 show 명령에 키워드를 지정하지 않으면 결과에 세션 수준 값이 표시됩니다. 예를 들어 세션 수준 값을 보려면 를 사용하십시오 show session variables like "max_connections";. 전역 값을 보려면 를 사용하십시오 show global variables like 'max_connections';.

경우에 따라 Amazon RDS for MySQL 파라미터의 잘못된 구성으로 인해 운영 문제가 발생할 수 있습니다. 이 블로그 시리즈는 데이터베이스 인스턴스의 안정성과 안정성을 개선하기 위해 가장 일반적으로 수정되는 일부 데이터베이스 매개변수에 대한 지침을 제공하는 것을 목표로 합니다.

성능과 관련된 매개변수 구성을 위한 모범 사례

다음은 성능과 관련된 매개변수를 나열하고 각각을 구성하기 위한 모범 사례 제안과 함께 나열합니다.

innodb_buffer_pool_size

이 매개변수는 InnoDB가 테이블 및 인덱스 데이터를 캐시하는 메모리 영역인 버퍼 풀 의 크기(바이트)를 결정합니다 . 이 매개변수는 MySQL 인스턴스에서 가장 중요한 설정 중 하나이며 종종 80% 이상의 메모리가 여기에 할당됩니다.

RDS에서 이 매개변수의 기본값은 { DBInstanceClassMemory*3/4}입니다. 그러나 많은 경우 워크로드에 따라 이 값을 조정해야 할 수도 있습니다.

예를 들어 100GB의 데이터가 있지만 애플리케이션이 정기적으로 1GB의 데이터에만 액세스한다고 가정합니다. 그런 다음 버퍼 풀에 몇 기가바이트만 있으면 충분합니다. 반면에 10GB의 데이터가 있고 애플리케이션이 이 모든 데이터에 지속적으로 액세스하는 경우 데이터와 인덱스 크기를 포함할 수 있을 만큼 충분히 큰 버퍼 풀이 필요합니다. 따라서 애플리케이션 요구 사항 및 워크로드 패턴에 따라 이 매개변수의 값을 결정하는 것이 좋습니다.

show engine 명령을 사용하여 서버가 버퍼 풀을 얼마나 잘 사용하고 있는지 확인할 수 있습니다 innodb status \G. 시간이 지남에 따라 사용 가능한 버퍼의 비율이 높다는 것이 출력에서 ​​일관되게 확인되면 최대 워크로드 중에도 할당된 버퍼 풀 크기가 너무 클 수 있습니다.

의 동일한 출력에서 ​​다음과 같은 show engine innodb status \G메시지를 볼 수 있습니다 evicted without access X.XX/s. 값 evicted without access이 0이 아니면 데이터가 버퍼 풀로 읽혀지고 액세스되기 전에 다시 푸시된다는 의미입니다(버퍼 풀 변동이라고도 함). 이 값이 시간이 지남에 따라 추적되고 증가하는 것으로 보이면 값을 늘려야 할 수 있습니다 innodb_buffer_pool_size. innodb_buffer_pool_size그러나 너무 크게 설정하지 않는 것이 좋습니다 . 그렇게 하면 운영 체제에서 페이징이 발생할 수 있으며 성능에 부정적인 영향을 미칠 수 있습니다. 더 늘려야 하는 경우 innodb_buffer_pool_size더 큰 총 메모리를 확보하기 위해 더 큰 인스턴스로 확장한 다음 innodb_buffer_pool_size.

InnoDB의 성능은 데이터가 캐시에 있는지 여부에 따라 다릅니다. 캐시 누락이 있을 때마다 디스크에 대한 임의의 I/O 요청이 발생하여 작업이 매우 느려집니다.

일정 기간 동안 다음 SQL 문을 두 번 실행하여 InnoDB 버퍼 풀 누락률을 얻을 수 있습니다.

show global status where variable_name in ('Innodb_buffer_pool_reads','Uptime');

다음 공식을 사용하여 초당 실패율을 계산할 수 있습니다.

Miss_rate = delta(Innodb_buffer_pool_reads)/delta(Uptime)

경우에 따라 상태변수의 증가량이 innodb_buffer_pool_reads감소하고 상태변수  innodb_buffer_pool_pages_free는 증가 후 이전 기간에 비해 증가하지 않습니다 innodb_buffer_pool_size. 이러한 경우, 우리는 innodb_buffer_pool_size.

innodb_log_file_size

이 매개변수는 MySQL의 다시 실행 로그에 대한 고정 크기를 결정합니다. 이 값을 조정하면 충돌 복구 시간과 전체 시스템 성능에 영향을 줍니다. 기본값은 134,217,728(약 128MB)입니다.

의 기본값 innodb_log_file_size은 워크로드에 대해 작을 수 있습니다. 변경률이 높은 워크로드에 대해 이 값을 늘리는 것이 좋습니다. 실질적인 삽입, 업데이트, 삭제 활동을 위해 권장되는 초기 설정은 다음과 같습니다.

innodb_log_file_size = 600M
innodb_log_files_in_group = 2

innodb_log_files_in_group매개변수는 로그 그룹의 로그 파일 수 를 정의합니다 . 값이 2보다 크면 큰 이점이 없습니다. 최대 허용 값 은 MySQL 버전 5.6부터 512GB입니다. 따라서 2보다 크게 증가할 경우 제한 범위 내에서 유지되도록 감소해야 합니다.innodb_log_files_in_groupinnodb_log_file_size * innodb_log_files_in_groupinnodb_log_files_in_groupinnodb_log_file_size

MySQL 5.6 이상에서 늘리는 것이 좋습니다 innodb_log_file_size. 기본적으로 128MB입니다. 증가는 로그 플러시 빈도가 적다는 것을 의미합니다. 그렇지 않으면 데이터를 가져오는 동안 플러시가 자주 발생합니다(예: 5분 이내에 여러 번).

그러나 innodb_log_file_size(파일 크기) 값이 클수록 충돌 복구 시간이 길어집니다. 따라서 I/O 수를 줄이되 복구 시간에 큰 영향을 미치지 않도록 최적의 크기를 지정해야 합니다.

이 매개변수의 크기를 최적으로 조정하려면 서버의 최대 사용 시간에 다음 쿼리를 실행하십시오.

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb statusG select sleep(60); show engine innodb statusG
Log sequence number 82 3836410821
1 row in set (0.06 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 84 3838334625
1 row in set (0.05 sec)

앞의 쿼리에서 트랜잭션 로그에 기록된 총 바이트 수를 볼 수 있습니다. 이제 1분 동안 로그에 기록된 MB 수를 확인할 수 있습니다.

mysql> select (3838334625 - 3836410821) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83468246| 
+------------+

참고: 대략적인 경험에 따르면 최대 1시간 정도의 다시 실행을 저장할 수 있을 만큼 로그를 크게 만들 수 있습니다. 또한 innodb_log_file_size데이터베이스에서 가장 큰 BLOB 크기의 10배 이상이어야 합니다.

innodb_change_buffering

변경 버퍼 는 버퍼 풀에 없는 보조 인덱스 페이지의 변경 사항을 캐싱하는 데 사용되는 특수 데이터 구조입니다 . 이 innodb_change_buffering매개변수는 DML(데이터 조작 언어) 작업 후 보조 인덱스를 최신 상태로 유지하는 데 사용되는 상당한 I/O 작업을 줄이는 데 도움이 됩니다. 이 매개변수는 변경 버퍼링 작업의 범위를 제어하는 ​​데 사용됩니다.

다음 표는 이 매개변수에 허용되는 값을 보여줍니다.

값	설명
없음	어떤 작업도 버퍼링하지 마십시오.
인서트	버퍼 삽입 작업.
삭제	버퍼 삭제 표시 작업 - 엄밀히 말하면 제거 작업 중에 나중에 삭제할 수 있도록 인덱스 레코드를 표시하는 쓰기입니다.
변화	버퍼 삽입 및 삭제 표시 작업.
숙청	백그라운드에서 발생하는 물리적 삭제 작업을 버퍼링합니다.
모두	이 값은 기본값입니다. 버퍼 삽입, 삭제 표시 작업 및 제거.

기본값은 all입니다. 업그레이드를 위한 종료 시간이 매우 길기 none때문에 값을 로 설정하여 변경 버퍼링을 비활성화하는 것이 좋습니다 . all이 기능은 과거에는 느린 디스크에서 유용했지만 지금은 유용하지 않습니다.

innodb_io_capacity

이 매개변수는 InnoDB가 InnoDB 백그라운드 작업에 대해 수행할 수 있는 초당 최대 I/O 작업 수를 제어합니다. 이러한 작업의 몇 가지 예는 MySQL 설명서에 설명된 대로 버퍼 풀 에서 페이지를 플러시 하고 변경 버퍼 에서 데이터를 병합하는 것 입니다.

이 매개변수의 기본값은 200이고 허용되는 값 범위는 100–18,446,744,073,709,551,615입니다. I/O 집약적 시스템의 경우 일반적으로 1,000 값이 작동합니다. 더 낮은 값이 충분하지 않다는 것을 이미 입증하지 않은 경우 20,000과 같은 극단적인 값은 권장하지 않습니다. 플러시가 너무 느리고 IOPS가 남아 있을 때 이러한 값을 사용할 수 있습니다.

플러시가 느린지 확인하려면 다음 방법을 사용하여 더티 페이지의 비율을 확인할 수 있습니다. 매우 높은 비율의 더티 페이지는 플러시가 예상만큼 빠르게 발생하지 않는다는 것을 보여줍니다. 버퍼 풀에 있는 더티 데이터의 양을 보려면 출력 Innodb_buffer_pool_bytes_dirty과 함께 메트릭을 사용할 수 있습니다. SHOW GLOBAL STATUS다음은 예를 보여줍니다.

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
...
| Innodb_buffer_pool_pages_data         | 3271        |
| Innodb_buffer_pool_bytes_data         | 38192680    |
| Innodb_buffer_pool_pages_dirty        | 1024        |
| Innodb_buffer_pool_bytes_dirty        | 16760878    |
...
| Innodb_buffer_pool_pages_total        | 5395        |
...
+---------------------------------------+-------------+
10 rows in set (0.01 sec)

이전 출력에서 ​​변수는 다음과 같습니다.

• Innodb_buffer_pool_pages_data및 Innodb_buffer_pool_bytes_data: 버퍼 풀의 버퍼 양.
• Innodb_buffer_pool_pages_dirty및 Innodb_buffer_pool_bytes_dirty: 버퍼 풀에 있는 더티 버퍼의 양.
• Innodb_buffer_pool_pages_total: 버퍼 풀의 총 페이지 수입니다.

더티 페이지의 현재 백분율을 계산하려면 다음 공식을 사용하십시오.

                            Innodb_buffer_pool_pages_dirty
Dirty page percentage = ------------------------------------- x 100%
                            Innodb_buffer_pool_pages_total

또한 를 information_schema사용하면 다음 쿼리를 사용할 수 있습니다.

mysql> SELECT dirty.Value AS 'Dirty Pages', total.Value AS 'Total Pages', ROUND(100*dirty.Value/total.Value, 2) AS 'Dirty Pct' FROM (SELECT VARIABLE_VALUE AS Value FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') AS total INNER JOIN (SELECT VARIABLE_VALUE AS Value FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty') AS dirty;
+-------------+-------------+-----------+
| Dirty Pages | Total Pages | Dirty Pct |
+-------------+-------------+-----------+
| 200        | 1000        |     20.00 |
+-------------+-------------+-----------+
1 row in set (0.00 sec)

show engine innodb status다음 공식 을 사용하여 더티 페이지 비율을 얻을 수도 있습니다 .

                                   Modified DB pages
Dirty page percentage = ------------------------------------- x 100%
                            Database pages + Free buffers

다음은 해당 정보 스키마 쿼리입니다.

mysql> SELECT MODIFIED_DATABASE_PAGES, DATABASE_PAGES, FREE_BUFFERS, ROUND(100*MODIFIED_DATABASE_PAGES/(DATABASE_PAGES+FREE_BUFFERS), 2) AS 'Dirty Pct' FROM information_schema.INNODB_BUFFER_POOL_STATS;
+-------------------------+----------------+--------------+-----------+
| MODIFIED_DATABASE_PAGES | DATABASE_PAGES | FREE_BUFFERS | Dirty Pct |
+-------------------------+----------------+--------------+-----------+
|                     207 |           4333 |          599 |     4.19  |
+-------------------------+----------------+--------------+-----------+
1 row in set (0.00 sec)

innodb_io_capacity더티하거나 변경된 페이지 비율을 원하는 값으로 늘릴 수 없으면 늘리거나 줄이는 것이 좋습니다 . 이 매개변수는 플러시 속도 및 관련 디스크 I/O를 제어합니다. 그러나 이 매개변수 또는 innodb_io_capacity_max값을 너무 높게 설정하면 성능이 심각하게 저하될 수 있습니다. 또한 조기 플러시로 디스크 IOPS를 낭비할 수 있습니다.

이 매개변수의 최적 값은 워크로드 및 데이터 액세스 패턴에 따라 다릅니다. DML 집약적 워크로드에서 이 매개변수의 값이 너무 낮게 설정되면 더티 페이지의 비율이 매우 높아 메모리 사용량이 과도해질 경우 플러시가 뒤처질 수 있습니다. 유사하게, 이 매개변수의 값을 너무 높게 설정하면 IOPS/처리량 용량을 플러시하고 포화시키기 때문에 과도한 조기 플러시 및 높은 디스크 쓰기가 발생할 수 있습니다.

innodb_io_capacity_max

innodb_io_capacity이 매개변수는 InnoDB가 플러싱 활동이 뒤처지는 경우 설정을 확장할 수 있는 최대값을 정의합니다 . 이 값을 지정하지 않으면 기본값은 의 두 배이며 innodb_io_capacity최소값은 2,000입니다. 기본값은 2,000이고 허용되는 값은 2,000–18,446,744,073,709,547,520입니다.

쓰기 로드가 낮은 시스템에는 더 작은 값을 사용하고 쓰기 활동이 많은 시스템에는 더 큰 값을 사용하는 것이 가장 좋습니다. 이 값은 , 보다 낮을 수 없으며 innodb_io_capacity종종 의 값의 2배가 innodb_io_capacity좋은 선택입니다. 적응형 플러싱 의 경우 실제로 플러싱 속도를 제한하는 변수입니다.

innodb_purge_threads

이 매개변수는 MySQL이 innoDB 제거 작업에 사용하는 백그라운드 스레드 수를 정의합니다. 이 매개변수의 최소값은 1이므로 제거 작업은 항상 백그라운드 스레드에서 수행되며 마스터 스레드의 일부로 수행되지 않습니다. 하나의 개별 스레드를 사용하여 innoDB 제거 작업을 수행하면 InnoDB와의 내부 경합을 줄이고 확장성을 개선하는 데 도움이 됩니다. RDS MySQL에서 기본값은 1이고 최대값은 32입니다.

제거 스레드가 여러 개 있으면 DML 작업이 여러 테이블에 분산되어 있는 경우 DML 집약적 워크로드에 대한 실행 취소 레코드 제거의 효율성이 향상될 수 있습니다. DML 작업이 단일 테이블에 집중되거나 매우 적은 테이블에 집중되는 경우 퍼지 스레드 간의 경합을 피하기 위해 이 값을 가능한 한 낮게 유지하십시오. 여러 테이블에서 실행되는 DML 작업에 대한 제거 작업을 가속화해야 하는 강력한 이유가 없는 한 권장 값은 1입니다. 이 MySQL 버그 에서 볼 수 있듯이 값이 높을수록 퍼지 스레드 경합이 발생할 수 있습니다 .

innodb_stats_persistent

이 매개변수는 명령에 의해 생성된 InnoDB 테이블 및 인덱스 통계가 ANALYZE TABLE디스크에 저장되는지 여부를 지정합니다. 이러한 통계는 mysql.innodb_table_stats및 mysql.innodb_index_stats테이블에 저장됩니다. 통계가 저장되지 않으면 오버헤드를 유발할 수 있는 다시 시작할 때마다 자주 다시 계산해야 합니다.

사용 가능한 값은 1(ON) 또는 0(OFF)입니다. 기본적으로 이 매개변수는 켜져 있습니다. 이 매개변수를 활성화하면 인스턴스 재시작 시 지속되는 옵티마이저 통계를 얻을 수 있습니다. 따라서 더 큰 계획 안정성과 더 일관된 쿼리 성능을 허용합니다.

이 매개변수는 전역 수준에서 지속적 통계를 활성화합니다. STATS_PERSISTENT그러나 이 지속성은 테이블 을 생성하는 동안 절을 사용하여 테이블 수준에서 활성화 또는 비활성화할 수도 있습니다 .

이 매개변수가 OFF로 설정되면 옵티마이저 통계가 비영구적이 되며 다음 테이블 액세스에서 각 인스턴스가 다시 시작된 후 다시 계산해야 합니다. 이 상태에서 통계를 다시 계산할 때 다른 추정치가 생성될 수 있으며 다른 실행 계획을 생성할 수 있습니다. 따라서 일관되고 안정적이며 최적화된 실행 계획을 위해 이 매개변수를 ON으로 설정하는 것이 좋습니다.

ANALYZE TABLE영구 통계를 사용하는 경우 명령 을 실행하여 통계를 주기적으로 업데이트해야 합니다 . 데이터가 안정적이거나 점진적으로 변하는 테이블의 경우 매주 또는 매월 분석하는 것이 좋습니다. 크기가 작고 데이터가 빠르게 변경되는 테이블의 경우 ANALYZE TABLE더 자주 실행하는 것이 좋습니다. 영구 통계 설정의 유일한 단점은 ANALYZE TABLE때때로 실행해야 하고 통계 데이터를 디스크에 써야 한다는 것입니다. 그러나 이 오버헤드는 최소화되며 인스턴스 재시작 후 테이블에 액세스할 때마다 비용이 많이 드는 통계 재계산을 방지합니다.

innodb_thread_concurrency

이 매개변수를 사용하면 활성 스레드 수가 vCPU 수보다 훨씬 큰 경우 발생하는 문제를 해결하는 데 도움이 될 수 있습니다.

작은 인스턴스 크기의 경우 대부분의 세션이 유휴 상태이면 많은 수의 세션을 처리할 수 있습니다. 그러나 활성 스레드 수가 vCPU 수보다 훨씬 더 커질 수 있습니다. 이 경우 운영 체제는 사용 가능한 모든 vCPU를 사용 가능한 스레드로 나누려고 합니다. 이것은 컨텍스트 스위칭이라는 프로세스에 의해 수행되며, 이 프로세스는 CPU 사용에 상당한 오버헤드를 추가합니다. Enhanced Monitoring 콘솔의 시스템 범주에서 이 오버헤드를 볼 수 있습니다 . 작은 인스턴스 클래스의 경우 더 큰 인스턴스로 확장하여 이 문제를 극복할 수 있지만 큰 인스턴스의 경우 이 옵션을 사용할 수 없습니다.

운영 체제 수준에서 Linux는 각 스레드가 수행한 작업을 알지 못합니다. 그러나 InnoDB는 이에 대해 알고 그에 따라 스레드의 우선 순위를 지정할 수 있습니다. 동시 스레드 수를 제한하는 innodb_thread_concurrency 매개변수 를 사용하여 이 우선순위를 지정 합니다. 이 매개변수의 기본값은 0이며, 이는 무한 동시성(동시성 검사 없음)으로 해석됩니다.

실행 스레드 수가 이 제한에 도달한 후 추가 스레드는 큐에 들어가기 전에 마이크로초 동안 휴면합니다. innodb_thread_sleep_delay 구성 매개변수를 사용하여 스레드가 절전 모드로 전환되는 시간을 마이크로초로 설정합니다 . innodb_thread_concurrency 매개변수는 큰 인스턴스 크기에 대한 컨텍스트 전환을 방지하는 데 도움이 됩니다 .

이 매개변수를 조정하는 또 다른 사용 사례는 모두 작성해야 하는 동시 삽입이 높은 경우입니다. 이러한 경우 더 많은 스레드 동시성이 도움이 됩니다. 이 매개변수의 최적 값을 결정하려면 주어진 워크로드에 대해 테스트하십시오. 종종 기본값이 가장 좋습니다.

innodb_sync_array_size

이 매개변수를 사용하여 스레드를 조정하는 데 사용되는 내부 데이터 구조를 분할할 수 있습니다. 이렇게 하면 많은 수의 스레드가 대기 중인 고도로 동시 작업을 수행하는 작업에 특히 유용합니다. 향상된 모니터링 메트릭 loadAverageMinute은 대기 스레드를 나타내는 좋은 지표입니다. 높은 부하 평균은 시스템이 과부하되었음을 의미합니다. 많은 스레드가 CPU 시간을 기다리고 있습니다. 부하 평균 값이 vCPU 수보다 높으면 vCPU에 대한 수요가 높음을 의미합니다.

대기 중인 스레드가 자주 발견되는 동시 작업이 많은 워크로드의 경우 값을 늘리는 것이 좋습니다. 매개변수 의 기본값 innodb_sync_array_size은 1입니다. 이러한 낮은 값은 종종 대규모 동시 작업 부하에 대해 많은 경합을 유발합니다. 그러나 이 매개변수의 최적값은 예상되는 동시 연결 수에 따라 달라지며 철저한 부하 테스트를 통해 결정되어야 합니다. 종종 innodb_sync_array_size=16좋은 결과를 제공합니다.

innodb_flush_log_at_trx_commit

모든 트랜잭션의 내구성을 달성하려면 로그 버퍼를 내구성 있는 저장소로 플러시해야 합니다. 그러나 디스크에 쓰는 것은 성능에 영향을 미칩니다. 시스템에서 내구성보다 성능이 더 우선시되는 경우 innodb_flush_log_at_trx_commit매개변수를 조정하여 로그 버퍼가 디스크로 플러시되는 빈도를 제어할 수 있습니다.

가능한 설정은 다음과 같습니다.

0 – 이 설정은 트랜잭션 커밋 시 아무 작업도 수행하지 않지만 로그 버퍼를 로그 파일에 쓰고 매초 로그 파일을 플러시합니다. OS가 로그를 플러시하려고 하지만 이 플러시가 보장되지는 않습니다. 따라서 이 경우 충돌이 발생하면 가장 최근에 커밋된 트랜잭션의 일부가 손실되는 것을 감당할 수 있어야 합니다. 여기서 데이터 손실은 트랜잭션뿐만 아니라 잠재적인 데이터 손실을 의미하기도 합니다. 따라서 이 설정은 잠재적인 손상의 원인이 될 수 있습니다.

1 – 이 설정은 로그 버퍼를 로그 파일에 기록하고 트랜잭션이 커밋될 때마다 이를 영구 저장소에 플러시합니다. 이것이 기본(가장 안전한) 설정입니다. 디스크나 운영 체제가 플러시 작업을 "가짜"하지 않는 한 커밋된 트랜잭션을 잃지 않도록 보장합니다.

2 – 이 설정은 모든 커밋 작업에서 파일에 로그 버퍼를 기록하지만 버퍼를 플러시하지는 않습니다. 이 설정은 초당 한 번만 데이터를 디스크로 플러시합니다. OS가 로그를 플러시하려고 하지만 이 플러시가 보장되지는 않습니다.

이 설정과 0 설정 사이의 가장 중요한 차이점은 2로 설정하면 MySQL 프로세스가 충돌하더라도 트랜잭션이 손실되지 않는다는 것을 의미한다는 것입니다. 그러나 전체 서버가 충돌하거나 전원이 꺼지면 트랜잭션이 손실될 수 있습니다. 이것은 충돌이 OS로 인해 발생한 것과 동일한 데이터 손실입니다. 값 0과 마찬가지로 이 설정은 잠재적으로 손상을 일으킬 수 있으며 데이터 손실은 트랜잭션 손실만이 아닙니다.

값이 1로 설정되지 않으면 InnoDB는 ACID 속성을 보장하지 않습니다. 충돌이 발생하면 가장 최근 트랜잭션의 최대 1초 가치가 손실될 수 있습니다. 따라서 충돌이 발생한 경우 이 매개변수를 사용하면 읽기 전용 복제본 작업이 중단되거나 스냅샷에서 데이터가 손실될 수 있습니다. 복제 시나리오에서 충돌이 발생한 경우 이러한 문제를 방지하려면 마스터에서 이 매개변수를 설정하지 마십시오. RDS 모범 사례를 위해 복제 마스터와 해당 복제본에 대해 별도의 파라미터 그룹을 보유하는 것이 좋습니다.

참고: 버퍼를 로그에 기록하면 InnoDB의 메모리 버퍼에서 메모리에 있는 운영 체제의 캐시로 데이터가 이동됩니다. 실제로 내구성 있는 저장소에 데이터를 쓰지는 않습니다. 로그를 내구성 있는 저장소로 플러시한다는 것은 InnoDB가 운영 체제에 실제로 캐시에서 데이터를 플러시하고 디스크에 기록되도록 요청한다는 것을 의미합니다.

tmp_table_size 및 max_heap_table_size

MySQL 문서 에 따르면 "내부 임시 테이블이 인메모리 테이블로 생성되었지만 너무 커지면 MySQL은 자동으로 이를 온디스크 테이블로 변환합니다. 메모리 내 임시 테이블의 최대 크기는 tmp_table_size 및 max_heap_table_size 값 중 더 작은 값에서 결정됩니다." 온디스크 테이블과 관련된 작업은 메모리 내 임시 테이블보다 훨씬 느립니다. MySQL 5.7에 대한 문서에서 유사한 논의 를 찾을 수 있습니다 . 이 두 매개변수의 기본값은 16,777,216바이트입니다.

오랜 시간(일반적으로 5~15분)이 소요되는 복잡한 쿼리를 실행하는 고객이 에서 볼 때 종종 문제가 발생 copying to tmp table on disk합니다 show full processlist. 이러한 종류의 문제는 종종 큰 쓰기 IOPS 및 디스크 대기열 깊이(100 이상)와 같은 증상과 함께 발생합니다. 메모리 및 디스크 임시 테이블에 대한 생성을 확인하려면 비교를 위해 이전 쿼리 실행을 완료하기 전과 종료하는 동안 status like 'Created_tmp%'다음과 같이 표시를 시도합니다.

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%'; 
+-------------------------+--------+ 
| Variable_name           | Value  | 
+-------------------------+--------+ 
| Created_tmp_disk_tables | 582    | 
| Created_tmp_files       | 5      | 
| Created_tmp_tables      | 188654 | 
+-------------------------+--------+ 
3 rows in set (0.01 sec) 

이러한 경우 tmp_table_size 및 max_heap_table_size64MB, 128MB 또는 512MB로 변수를 늘리면 문제가 해결됩니다. 이 변경 전과 후의 성능을 비교하는 것이 좋습니다. 이 tmp_table_size값은 인메모리 내부 임시 테이블의 최대 크기를 결정합니다. 그러나 메모리 내 임시 테이블의 실제 최대 크기를 결정하는 max_heap_table_size최소값 입니다. tmp_table_size종종 구성만 tmp_table_size하지만 실제로는 함께 증가해야 합니다.

예를 들어 다음이 있다고 가정합니다.

tmp_table_size      = 64 MB
max_heap_table_size = 32 MB

이 경우 MySQL tmp_table_size은 32MB의 값과 32MB의 값을 할당합니다 max_heap_table_size.

다음이 있다고 가정합니다.

tmp_table_size      = 32 MB
max_heap_table_size = 64 MB

이 경우 MySQL은 tmp_table_size32MB 값과 32MB 값 도 할당합니다 max_heap_table_size.

코드는 sql_select.cc 파일에서 찾을 수 있습니다.

if (thd->variables.tmp_table_size == ~ (ulonglong) 0)        // No limit
    table->s->max_rows= ~(ha_rows) 0;
  else
    table->s->max_rows= (ha_rows) (((table->s->db_type == DB_TYPE_HEAP) ?
                                    min(thd->variables.tmp_table_size,
                                        thd->variables.max_heap_table_size) :
                                    thd->variables.tmp_table_size)/
                                   table->s->reclength);

다음 min()은 MySQL에서 사용되는 함수를 찾을 수 있습니다.

min(thd->variables.tmp_table_size,thd->variables.max_heap_table_size)

수동 상태 MySQL은 두 변수에 할당된 더 낮은 값을 취하여 tmp_table_size. 따라서 tmp_table_size및 max_heap_table_size는 동일한 값이어야 합니다.

그러나 이러한 값을 늘리기 전에 특히 조인 및 열별 그룹화를 위해 데이터베이스가 올바르게 인덱싱되었는지 확인하십시오. 적절한 인덱싱은 임시 테이블 생성을 줄입니다. 그렇지 않으면 단순히 이러한 매개변수 값을 늘리면 비효율적인 쿼리가 인덱스 없이 실행되고 필요한 것보다 더 많은 임시 테이블이 생성될 수 있습니다.

일부 조건은 메모리 내 임시 테이블의 사용을 방지합니다. 이 경우 서버는 대신 디스크 상의 테이블을 사용하므로 쓰기 I/O가 증가합니다. 이러한 조건은 다음과 같습니다.

• 테이블에 BLOB 또는 TEXT 열이 있는지 여부.
• 이진 문자열의 경우 512바이트 또는 비이진 문자열의 경우 512자보다 큰 GROUP BY 또는 DISTINCT 절의 문자열 열이 있는지 여부. (MySQL 5.6.15 이전에는 문자열 유형에 관계없이 512바이트로 제한되었습니다.)
• UNION 또는 UNION ALL이 사용되는 경우 SELECT 목록에 최대 길이가 512(이진 문자열의 경우 바이트, 이진이 아닌 문자열의 경우 문자)보다 큰 문자열 열이 있는지 여부.
• SHOW COLUMNS 및 DESCRIBE 문은 일부 열의 유형으로 BLOB를 사용하므로 결과에 사용되는 임시 테이블은 디스크에 있는 테이블입니다.

foreign_key_checks

외래 키 검사가 활성화되면 데이터 가져오기 프로세스가 느려지는 경우가 많습니다. 이 foreign_key_checks매개변수는 기본적으로 활성화되어 있으며 이 매개변수의 기본값은 1(ON)입니다. 그러나 파라미터 그룹에서는 사용할 수 없습니다. 데이터 무결성보다 성능을 보장하는 것이 목표인 경우 가져오기 성능을 개선하기 위해 가져오기 SQL 문을 실행하기 전에 이 매개변수를 0으로 설정하여 이 매개변수를 끌 수 있습니다.

다음은 예를 보여줍니다.

SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;

SET FOREIGN_KEY_CHECKS=1;가져오기 파일의 끝에 추가하는 것을 잊지 마십시오 .

큰 테이블의 경우 이렇게 하면 많은 디스크 I/O를 제거할 수 있습니다. 잠재적인 문제는 가져오는 동안 외래 키 검사를 실패하게 만드는 데이터 불일치가 외래 키가 다시 켜진 후에도 데이터베이스에 여전히 존재한다는 것입니다.

전역 설정을 동적으로 활성화 및 비활성화하기 위한 몇 가지 래핑된 저장 프로시저를 제공합니다. 절차의 이름은 mysql.rds_set_fk_checks_on및 mysql.rds_set_fk_checks_off입니다. 이러한 절차는 RDS MySQL 5.6 버전 5.6.29 이상 및 RDS MySQL 5.7 버전 5.7.17 이상에서 사용할 수 있습니다.

다음은 이러한 절차를 사용하는 예를 보여줍니다.

CALL mysql.rds_set_fk_checks_off();
CALL mysql.rds_set_fk_checks_on();

UNIQUE_CHECKS

데이터에 대해 실행하기 전에 SET UNIQUE_CHECKS=0비활성화 로 실행하여 큰 테이블에 대한 삽입 성능을 크게 향상시킬 수도 있습니다 . 그런 다음 삽입이 완료되면 실행합니다. 도 기본적으로 활성화되어 있으며 이 매개변수의 기본값은 1(ON)이지만 매개변수 그룹에서는 사용할 수 없습니다.UNIQUE_CHECKSINSERTSET UNIQUE_CHECKS=1UNIQUE_CHECKS

예를 들어 이렇게 하려면 파일 맨 위에 다음 줄을 추가합니다.

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

마지막에 다음을 추가합니다.

SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

이렇게 하면 많은 양의 디스크 액세스를 방지하는 데 도움이 됩니다. 관련된 데이터에 중복 키가 없는지 확인하십시오.

query_cache_size

기본값 query_cache_size은 1,048,576(바이트)입니다. 대부분의 경우 쿼리 캐시를 구성하면 성능이 저하됩니다. 쿼리 캐시는 문제가 너무 커서 MySQL 5.7.20에서 더 이상 사용되지 않고 MySQL 8.0에서 제거되었습니다. 따라서 사용하지 않는 것이 좋습니다.

쿼리 캐시가 켜져 있는지 확인하려면 두 가지 변수를 사용할 수 있습니다.

• query_cache_size: 이 값은 쿼리 캐시에 할당된 메모리 양입니다. 0 이면 query_cache_size쿼리 캐시가 효과적으로 꺼집니다. query_cache_type그러나 OFF로 설정 하지 않는 한 여전히 약간의 오버헤드가 있습니다 .
• query_cache_type: 설정에는 세 가지 유효한 값이 query_cache_type 있으며 쿼리 캐시를 끄려면 0이 필요합니다.
  • 0 또는 OFF: 쿼리 캐시를 완전히 끕니다. 그러나 버퍼도 할당 해제하려면 query_cache_size0으로 설정하십시오.
  • 1 또는 ON: 쿼리 캐시가 기본적으로 사용됩니다. 쿼리에 대한 쿼리 캐시를 비활성화하려면 SQL_NO_CACHE힌트를 사용하십시오.
  • 2 또는 DEMAND: 이 경우 SQL_CACHE힌트가 지정된 경우에만 쿼리 캐시를 사용합니다.

다음은 예입니다.

mysql> SHOW VARIABLES LIKE 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1448576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.00 sec)

이전 출력에서는 쿼리 캐시가 비활성화됩니다. 우리 query_cache_size는 0이고 query_cache_typeOFF임을 알 수 있습니다.

옵티마이저 스위치

MySQL 문서 에서 알 수 있듯이 최적화 프로그램 은 DBMS가 쿼리에 대해 취해야 하는 실행 경로를 결정하는 일련의 루틴입니다. 옵티마이저는 결과를 얻기 위해 가장 효율적인 쿼리 계획을 선택하는 역할을 합니다. 이 매개변수는 옵티마이저의 동작을 제어하는 ​​데 사용되며 플래그 집합으로 구성됩니다. 이 플래그를 켜거나 끄면 최적화 프로그램의 동작을 제어할 수 있습니다.

다음은 예입니다.

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,
                    index_merge_intersection=on,
                    engine_condition_pushdown=on,
                    index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,
                    block_nested_loop=on,batched_key_access=off,
                    materialization=on,semijoin=on,loosescan=on,
                    firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on

기본값은 다음과 같습니다. index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on, mrr_cost_based=on, block_nested_loop=on, batched_key_on , semijoin=on, loosescan=on, firstmatch=on, subquery_materialization_cost_based=on, use_index_extensions=on

일반적으로 기본값이 권장됩니다. 잘못된 쿼리 계획 또는 최적화 프로그램의 잘못된 추정의 경우 이러한 플래그를 조정해야 할 수 있습니다. 예를 들어 5.6에서 5.7로 업그레이드한 후 optimizer_switch플래그 condition_fanout_filter및 derived_merge. 이러한 플래그는 성능에 상당한 영향을 미칠 수 있으므로 이러한 플래그를 활성화하기 전에 워크로드를 철저히 테스트하십시오.

Innodb_read_io_threads 및 Innodb_write_io_threads

이 매개변수는 InnoDB에서 각각 읽기 및 쓰기 작업을 위한 I/O 스레드 수입니다. 둘 다 기본값은 4입니다. 이 값을 늘리면 특정 InnoDB 작업에 대한 스레드가 늘어납니다. 이를 조정하고 innodb_read_io_threads = 16OLTP  innodb_write_io_threads = 4워크로드가 많은 경우 인스턴스를 유지하는 데 도움이 될 수 있습니다.

대부분의 워크로드는 이에 영향을 받지 않지만 프로덕션 구현 전에 적절한 테스트를 수행하십시오. 때때로 전경 스레드와 이러한 스레드 간에 디스크 리소스에 대한 경합이 발생할 수 있습니다.

innodb_status_output_locks

이 매개변수는 InnoDB 잠금 모니터를 활성화하거나 비활성화합니다. 모니터를 켜려면 1로 설정하고 모니터를 끄려면 0으로 설정하십시오. 기본값은 0입니다. 활성화되면 매개변수는 출력 잠금에 대한 추가 정보를 표시 SHOW ENGINE INNODB STATUS하고 MySQL 오류 로그에 주기적인 출력도 인쇄합니다. 이 매개변수는 MySQL 5.7에서 도입되었습니다. 특히 다른 세션이 잠긴 행에 액세스하지 않아 충돌이 없는 경우 잠금을 식별하는 데 도움이 됩니다.

table_open_cache

이 매개변수는 정의 캐시에 저장할 수 있는 테이블 정의의 수를 정의합니다. 기본값은 2,000입니다.

경험상 이 매개변수를 사용 중인 대부분의 테이블을 항상 열어 둘 수 있을 만큼 충분히 큰 값으로 설정하십시오. 테이블을 열고 닫으면 작업 속도가 크게 느려집니다. max_connections총 테이블 수의 두 배 또는 두 배 값으로 시작한 다음 거기에서 조정할 수 있습니다.

시스템이 가동되어 몇 시간 동안 실행되고 뜨거워지면 다음 명령을 사용하여 할당이 충분한지 확인합니다.

mysql> show global status like 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 200   |
+---------------+-------+
1 row in set (0.04 sec)

mysql> show global status like 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 5000 |
+---------------+-------+
1 row in set (0.00 sec)

의 증가로부터 Opened_tables얼마나 많은 테이블 정의를 추가해야 하는지 추정할 수 있습니다. 초당 열리는 테이블 수가 몇 개 이하가 될 때까지 확인을 반복하는 것이 좋습니다.

귀하의 수가 Opened_tables아직 의 값에 도달하지 않았고 table_open_cache_size서버가 잠시 동안 가동 중이었다면 값을 줄이는 것을 볼 수 있습니다. 일반적으로 1초에 1개 미만의 테이블이 열리길 원합니다.

더 높은 값을 사용할 수 있는지 여부를 평가하려면 table_open_cache서버 가동 시간과 함께 열린 테이블 수를 확인하십시오.

스레드_캐시_크기

이것은 몇 개의 연결 처리 스레드를 해제 및 재할당하는 대신 재사용을 위해 캐시해야 하는지 정의하는 구성 변수입니다. 각 연결 및 연결 해제 시 발생하는 스레드 생성 및 소멸은 비용이 많이 들 수 있습니다.

값 을 사용하여 초당 생성되는 스레드 수를 이해하면 스레드 캐시의 효율성을 예측할 수 있습니다 Threads_Created. 그런 다음 알려진 기간 동안 이를 샘플링할 수 있습니다. 의 기본값 thread_cache_size은 14 입니다. 일반적으로 이 값은 최소 16으로 설정해야 합니다. 응용 프로그램의 동시 연결 수가 크게 Threads_Created증가하고 빠르게 증가하는 경우 이 변수를 더 높은 값으로 늘려야 합니다. 목표는 정상적인 작동에서 스레드가 생성되지 않도록 하는 것입니다.

다음은 예입니다.

mysql> show global status like 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 1     |
| Threads_created   | 20    |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

max_seeks_for_key

이 값은 키를 기반으로 행을 조회할 때 가정되는 최대 탐색 수의 한계입니다. 기본값은 18,446,744,073,709,551,615입니다. 인덱스 스캔이 비용 효율적이고 전체 스캔을 수행하더라도 이 값은 일반적으로 쿼리가 인덱스를 사용하지 않을 때 사용됩니다. 이러한 경우 이 값은 더 낮게 설정되며(예: 100) MySQL이 테이블 스캔 대신 인덱스를 선호하도록 강제할 수 있습니다. 이 값은 모든 쿼리에 적용되므로 전역 수준에서 이 값을 변경하면 다른 쿼리에 예기치 않은 부작용이 발생할 수 있습니다. 따라서 철저히 테스트하십시오.

결론

위의 매개변수는 RDS MySQL 인스턴스에서 최상의 성능을 얻기 위해 주의해야 할 가장 중요한 매개변수입니다. 워크로드와 언급된 모범 사례에 맞게 조정할 수 있습니다.

이 블로그 시리즈 의 다음 부분 에서는 RDS MySQL 복제를 최적화하고 안정화하기 위해 가장 일반적으로 사용되는 매개변수와 이를 조정하기 위한 모범 사례에 대해 설명합니다.