Aurora MySQL 성능 및 크기 조정에 대한 모범 사례

Aurora MySQL 클러스터의 성능과 확장성을 개선하기 위해 적용할 수 있는 모범 사례는 다음과 같습니다.

개발 및 테스트에 T 인스턴스 클래스 사용

db.t2, db.t3 또는 db.t4g DB 인스턴스 클래스를 사용하는 Amazon Aurora MySQL 인스턴스는 연장된 시간 동안 높은 워크로드를 지원하지 않는 애플리케이션에 가장 적합합니다. T 인스턴스는 중간 정도의 기본 성능을 발휘하면서 워크로드의 필요에 따라 성능을 크게 높이는 버스트 기능을 제공하도록 설계되었습니다. 이러한 인스턴스는 CPU의 최대 성능을 자주 또는 일관적으로 사용하지 않지만 가끔 순간적인 버스트가 필요한 워크로드에 적합합니다. T DB 인스턴스 클래스는 개발 및 테스트 서버 또는 기타 비프로덕션 서버에만 사용하는 것이 좋습니다. T 인스턴스 클래스에 대한 자세한 내용은 버스트 가능 성능 인스턴스를 참조하세요.

Aurora 클러스터가 40TB보다 큰 경우 T 인스턴스 클래스를 사용하지 마세요. 데이터베이스에 많은 양의 데이터가 있는 경우 스키마 개체를 관리하기 위한 메모리 오버헤드가 T 인스턴스의 용량을 초과할 수 있습니다.

MySQL 성능 스키마를 Amazon Aurora MySQL T 인스턴스에서 활성화하지 마세요. 성능 스키마가 활성화된 경우 인스턴스의 메모리가 부족할 수 있습니다.

T 인스턴스를 Aurora MySQL DB 클러스터의 DB 인스턴스로 사용할 때는 다음을 권장합니다.

비동기식 키 프리페치를 사용하여 Aurora MySQL 인덱싱된 조인 쿼리 최적화

Amazon MySQL은 비동기식 키 프리페치(AKP) 기능을 사용하여 여러 인덱스 간에 테이블을 조인하는 쿼리 성능을 높일 수 있습니다. 이 기능은 JOIN 쿼리에서 Batched Key Access(BKA) 조인 알고리즘과 Multi-Range Read(MRR) 최적화 기능을 사용해야 하는 쿼리를 실행하면서 필요한 행을 예측하여 성능을 높이는 효과가 있습니다. BKA 및 MRR에 대한 자세한 정보는 MySQL 설명서에서 Block Nested-Loop and Batched Key Access Joins 및 Multi-Range Read Optimization을 참조하십시오.

쿼리가 AKP 기능을 이용하기 위해서는 BKA와 MRR이 모두 필요합니다. 일반적으로 JOIN 절이 보조 인덱스를 사용하지만 기본 인덱스의 열도 일부 필요할 때 이러한 쿼리가 발생합니다. 예를 들어 JOIN 절이 작은 용량의 외부 테이블과 큰 용량의 내부 테이블 사이에서 인덱스 값을 기준으로 한 등가 조인을 나타내고, 테이블 용량이 커질수록 인덱스 선택의 폭이 매우 제한적일 때 AKP를 사용할 수 있습니다. AKP는 JOIN 절을 평가하는 동안 BKA 및 MRR과 함께 보조-기본 인덱스 조회를 실행합니다. 동시에 쿼리를 실행하는 데 필요한 행까지 식별합니다. 그런 다음 쿼리를 실행하기에 앞서 백그라운드 스레드를 사용하여 식별된 행이 포함된 페이지를 메모리에 비동기식으로 로드합니다.

AKP는 Aurora MySQL 버전 2.10 이상 및 버전 3에서 사용할 수 있습니다. Aurora MySQL 버전에 대한 자세한 내용은 Amazon Aurora MySQL에 대한 데이터베이스 엔진 업데이트 단원을 참조하십시오.

비동기식 키 미리 가져오기(AKP) 활성화

MySQL 서버 변수 aurora_use_key_prefetch를 on으로 설정하여 AKP 기능을 활성화할 수 있습니다. 기본적으로 이 값은 on로 설정됩니다. 하지만 먼저 BKA 조인 알고리즘을 사용 설정하고 비용 기반 MRR 기능을 사용 중지해야만 AKP를 사용 설정할 수 있습니다. 이를 위해서는 optimizer_switch MySQL 서버 변수의 값을 다음과 같이 설정해야 합니다.

현재는 세션 수준에서만 위의 두 값을 설정할 수 있습니다. 다음은 SET 문에서 위의 두 값을 설정하여 현재 세션에 AKP를 활성화하는 방법을 설명한 예제입니다.

mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

마찬가지로 다음 예제와 같이 SET 문을 사용하여 AKP와 BKA 조인 알고리즘을 비활성화한 후 현재 세션에 비용 기반 MRR 기능을 다시 활성화할 수 있습니다.

mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

batched_key_access 및 mrr_cost_based 옵티마이저 스위치에 대한 자세한 정보는 MySQL 설명서에서 Switchable Optimizations를 참조하십시오.

비동기식 키 미리 가져오기를 위한 쿼리 최적화

쿼리의 AKP 기능 사용 여부를 확인할 수 있습니다. 이렇게 하려면 쿼리를 실행하기 전에 EXPLAIN 문을 사용하여 쿼리를 프로파일링하면 됩니다. EXPLAIN 문이 지정한 쿼리에 사용할 실행 계획에 대한 정보를 제공합니다.

Extra 문 출력에서 EXPLAIN 열은 실행 계획에 추가되는 정보에 대한 설명입니다. AKP 기능이 쿼리에 사용할 테이블에 적용되는 경우 이 열에 다음 값 중 하나가 포함됩니다.

EXPLAIN을 사용하여 AKP를 이용할 수 있는 쿼리의 실행 계획을 확인하는 예는 다음과 같습니다.

mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

EXPLAIN 출력 형식에 대한 자세한 내용은 MySQL 설명서의 확장된 EXPLAIN 출력 형식을 참조하세요.

해시 조인을 사용하여 대규모 Aurora MySQL 조인 쿼리 최적화

동등 조인을 사용하여 많은 양의 데이터를 조인해야 하는 경우 해시 조인을 통해 쿼리 성능을 향상시킬 수 있습니다. Aurora MySQL의 해시 조인을 활성화할 수 있습니다.

해시 조인 열은 복잡한 표현식이 될 수 있습니다. 해시 조인 열에서 다음과 같은 방식으로 데이터 유형을 비교할 수 있습니다.

Aurora MySQL의 해시 조인에는 다음의 제한 사항이 적용됩니다.

해시 조인 활성화

해시 조인 활성화 방법:

해시 조인은 Aurora MySQL 버전 3에서 기본적으로 활성화되어 있으며 Aurora MySQL 버전 2에서 기본적으로 비활성화되어 있습니다. 다음은 Aurora MySQL 버전 3에서 해시 조인을 활성화하는 방법을 설명한 예입니다. 먼저 select @@optimizer_switch 문을 발행하여 다른 설정이 SET 파라미터 문자열에 있는지 확인합니다. optimizer_switch 파라미터에서 설정 하나를 업데이트하면 다른 설정을 지우거나 수정하지 못합니다.

mysql> SET optimizer_switch='block_nested_loop=on';

이 설정을 사용하면 옵티마이저는 비용, 쿼리 특성 및 리소스 가용성을 기반으로 해시 조인을 사용하도록 선택합니다. 비용 견적이 정확하지 않으면 옵티마이저가 해시 조인을 선택하게 할 수 있습니다. 그렇게 하려면 MySQL 서버 변수 hash_join_cost_based를 off으로 설정합니다. 다음은 옵티마이저가 해시 조인을 선택하도록 하는 방법을 설명한 예제입니다.

mysql> SET optimizer_switch='hash_join_cost_based=off';

해시 조인에 대한 쿼리 최적화

쿼리가 해시 조인을 활용할 수 있는지 확인하려면 EXPLAIN 문을 사용하여 쿼리를 먼저 프로파일링하십시오. EXPLAIN 문이 지정한 쿼리에 사용할 실행 계획에 대한 정보를 제공합니다.

Extra 문 출력에서 EXPLAIN 열은 실행 계획에 추가되는 정보에 대한 설명입니다. 해시 조인이 쿼리에 사용할 테이블에 적용되는 경우 이 열에 다음과 비슷한 값이 포함됩니다.

다음은 EXPLAIN을 사용하여 해시 조인 쿼리의 실행 계획을 확인하는 예제입니다.

mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

출력에서 Hash Join Inner table은 해시 테이블을 작성하는 데 사용하는 테이블이며 Hash Join Outer table은 해시 테이블을 프로브하는 데 사용하는 테이블입니다.

확장된 EXPLAIN 출력 형식에 대한 자세한 정보는 MySQL 제품 설명서에서 Extended EXPLAIN Output Format을 참조하십시오.

Aurora MySQL 2.08 이상에서는 SQL 힌트를 사용하여 쿼리에서 해시 조인을 사용하는지 여부와 조인의 빌드 및 프로브 측에 사용할 테이블에 영향을 줄 수 있습니다. 세부 정보는 Aurora MySQL 힌트을 참조하세요.

Amazon Aurora를 사용하여 MySQL 데이터베이스 읽기 조정

MySQL DB 인스턴스에서 Amazon Aurora를 사용하여 Amazon Aurora의 읽기 조정 기능을 활용하고 MySQL DB 인스턴스에 대한 읽기 작업을 확장할 수 있습니다. Aurora을 사용하여 MySQL DB 인스턴스에 대한 읽기 조정을 수행하려면 Aurora MySQL DB 클러스터를 생성한 후 이 클러스터를 MySQL DB 인스턴스의 읽기 전용 복제본으로 설정합니다. 그런 다음 Aurora MySQL 클러스터에 연결하여 읽기 쿼리를 처리합니다. 이러한 설정은 RDS for MySQL DB 인스턴스 또는 Amazon RDS 외부에서 실행 중인 MySQL 데이터베이스에 적용됩니다. 자세한 내용은 Amazon Aurora를 사용하여 MySQL 데이터베이스 읽기 규모 조정 단원을 참조하십시오.

타임스탬프 작업 최적화

시스템 변수 time_zone의 값이 SYSTEM으로 설정되면 시간대 계산이 필요한 각 MySQL 함수 호출이 시스템 라이브러리를 호출합니다. 이러한 TIMESTAMP 값을 거의 동시에 반환하거나 변경하는 SQL 문을 실행하면 지연 시간, 잠금 경합 및 CPU 사용량이 증가할 수 있습니다. 자세한 내용은 MySQL 설명서의 time_zone을 참조하세요.

이러한 동작을 방지하려면 time_zone DB 클러스터 파라미터의 값을 UTC로 변경하는 것이 좋습니다. 자세한 내용은 Amazon Aurora에서 DB 클러스터 파라미터 그룹의 파라미터 수정 단원을 참조하십시오.

time_zone 파라미터는 동적이지만(데이터베이스 서버를 다시 시작할 필요가 없음), 새 값은 최신 연결에만 사용됩니다. 모든 연결이 새 time_zone 값을 사용하도록 업데이트하려면 DB 클러스터 파라미터를 업데이트한 후 애플리케이션 연결을 해제했다가 다시 연결하는 것이 좋습니다.