어떤 문이 Aurora MySQL의 병렬 쿼리를 사용하는지 확인 - Amazon Aurora

어떤 문이 Aurora MySQL의 병렬 쿼리를 사용하는지 확인

일반적인 작업에서는 병렬 쿼리를 이용하기 위해 어떤 특별한 조치를 수행할 필요가 없습니다. 쿼리가 병렬 쿼리를 위한 필수적 요구 사항을 충족한 후에는, 쿼리 옵티마이저가 각 특정 쿼리에 대하여 병렬 쿼리를 사용할지 여부를 자동으로 결정합니다.

개발 환경 또는 테스트 환경에서 실험을 실행하는 경우, 테이블에 행의 수 또는 전체 데이터 볼륨이 너무 작아서 병렬 쿼리가 사용되지 않을 수도 있습니다. 특히 실험을 수행하기 위해 최근에 만든 테이블의 경우, 테이블의 데이터가 버퍼 풀에서 전부 사용될 수도 있습니다.

클러스터 성능을 모니터링하거나 튜닝할 때, 병렬 쿼리가 적절한 컨텍스트에서 사용되고 있는지 여부를 확인해야 합니다. 이 기능을 활용하기 위해 데이터베이스 스키마, 설정, SQL 쿼리 또는 심지어 클러스터 토폴로지 및 애플리케이션 연결 설정을 조정할 수도 있습니다.

쿼리가 병렬 쿼리를 사용하고 있는지 확인하려면, EXPLAIN 문을 실행하여 쿼리 계획("실행 계획"이라고도 함)을 확인합니다. SQL 문, 절 및 표현식이 병렬 쿼리의 EXPLAIN 출력에 어떠한 영향을 미치는지에 대한 예는 Aurora MySQL의 병렬 쿼리를 위한 SQL 구성를 참조하십시오.

다음 예제는 기존의 쿼리 계획과 병렬 쿼리 계획 간의 차이점을 보여줍니다. 이 설명 계획은 TPC-H 벤치마크의 쿼리 3에서 나온 것입니다. 이 단원의 곳곳에 나오는 샘플 쿼리의 대부분이 TPC-H 데이터 세트의 테이블을 사용합니다. TPC-H 웹 사이트에서 샘플 데이터를 생성하는 dbgen 프로그램, 테이블 정의 및 쿼리를 얻을 수 있습니다.

EXPLAIN SELECT l_orderkey,
  sum(l_extendedprice * (1 - l_discount)) AS revenue,
  o_orderdate,
  o_shippriority
FROM customer,
  orders,
  lineitem
WHERE c_mktsegment = 'AUTOMOBILE'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < date '1995-03-13'
AND l_shipdate > date '1995-03-13'
GROUP BY l_orderkey,
  o_orderdate,
  o_shippriority
ORDER BY revenue DESC,
  o_orderdate LIMIT 10;

기본적으로 쿼리에 다음과 같은 계획이 있을 수 있습니다. 쿼리 계획에 해시 조인이 사용된 것으로 표시되지 않으면 먼저 최적화가 설정되어 있는지 확인합니다.

+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                              |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  1480234 |    10.00 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | orders   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 14875240 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | lineitem | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59270573 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+

Aurora MySQL 버전 3의 경우 다음 문을 실행하여 세션 수준에서 해시 조인을 설정합니다.

SET optimizer_switch='block_nested_loop=on';

Aurora MySQL 버전 2.09 이상에서는 aurora_disable_hash_join DB 파라미터 또는 DB 클러스터 파라미터를 0(비활성화됨)으로 설정합니다. aurora_disable_hash_join을 비활성화하면 optimizer_switch의 값이 hash_join=on이 됩니다.

해시 조인을 활성화한 후 EXPLAIN 문을 다시 실행해 보세요. 해시 조인을 효과적으로 사용하는 방법에 대한 자세한 내용은 해시 조인을 사용하여 대규모 Aurora MySQL 조인 쿼리 최적화 섹션을 참조하세요.

해시 조인이 설정되어 있지만 병렬 쿼리가 해제되어 있으면, 쿼리가 다음과 같은 계획(병렬 쿼리가 아니라 해시 조인을 사용)을 사용할 수 있습니다.

+----+-------------+----------+...+-----------+-----------------------------------------------------------------+
| id | select_type | table    |...| rows      | Extra                                                           |
+----+-------------+----------+...+-----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | customer |...|   5798330 | Using where; Using index; Using temporary; Using filesort       |
|  1 | SIMPLE      | orders   |...| 154545408 | Using where; Using join buffer (Hash Join Outer table orders)   |
|  1 | SIMPLE      | lineitem |...| 606119300 | Using where; Using join buffer (Hash Join Outer table lineitem) |
+----+-------------+----------+...+-----------+-----------------------------------------------------------------+

병렬 쿼리가 설정된 후에는 이 쿼리 계획의 두 단계가 Extra 출력의 EXPLAIN 열 아래에 표시된 대로 병렬 쿼리 최적화를 사용할 수 있습니다. 이 두 단계를 위한 I/O 집약적이고 CPU 집약적인 처리는 스토리지 계층으로 푸시 다운됩니다.

+----+...+--------------------------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                                          |
+----+...+--------------------------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using index; Using temporary; Using filesort                                                                      |
|  1 |...| Using where; Using join buffer (Hash Join Outer table orders); Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra)   |
|  1 |...| Using where; Using join buffer (Hash Join Outer table lineitem); Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra) |
+----+...+--------------------------------------------------------------------------------------------------------------------------------+

병렬 쿼리 및 병렬 쿼리가 적용될 수 있는 SQL 문의 부분에 대한 EXPLAIN 츨력을 해석하는 방법에 대한 내용은 Aurora MySQL의 병렬 쿼리를 위한 SQL 구성을 참조하십시오.

다음 예제 출력은 콜드 버퍼 풀이 있는 db.r4.2xlarge 인스턴스에서 이전 쿼리를 실행한 결과를 보여줍니다. 병렬 쿼리 사용 시 쿼리가 상당히 더 빠르게 실행됩니다.

참고

타이밍은 많은 환경 요인에 의존하기 때문에 결과가 다를 수 있습니다. 본인의 고유한 환경, 워크로드 등에서의 결과를 확인하기 위해 항상 본인의 성능 테스트를 수행하십시오.

-- Without parallel query
+------------+-------------+-------------+----------------+
| l_orderkey | revenue     | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
|   92511430 | 514726.4896 | 1995-03-06  |              0 |
.
.
|   28840519 | 454748.2485 | 1995-03-08  |              0 |
+------------+-------------+-------------+----------------+
10 rows in set (24 min 49.99 sec)

-- With parallel query
+------------+-------------+-------------+----------------+
| l_orderkey | revenue     | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
|   92511430 | 514726.4896 | 1995-03-06  |              0 |
.
.
|   28840519 | 454748.2485 | 1995-03-08  |              0 |
+------------+-------------+-------------+----------------+
10 rows in set (1 min 49.91 sec)

이 단원의 곳곳에 나오는 샘플 쿼리의 대부분은 이 TPC-H 데이터 세트의 테이블을 사용합니다(특히, 2천만 개의 행과 다음 정의가 포함된 PART 테이블).

+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| p_partkey     | int(11)       | NO   | PRI | NULL    |       |
| p_name        | varchar(55)   | NO   |     | NULL    |       |
| p_mfgr        | char(25)      | NO   |     | NULL    |       |
| p_brand       | char(10)      | NO   |     | NULL    |       |
| p_type        | varchar(25)   | NO   |     | NULL    |       |
| p_size        | int(11)       | NO   |     | NULL    |       |
| p_container   | char(10)      | NO   |     | NULL    |       |
| p_retailprice | decimal(15,2) | NO   |     | NULL    |       |
| p_comment     | varchar(23)   | NO   |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+

개별 SQL 문이 병렬 쿼리를 활용할 수 있는지 가늠하기 위해 본인의 워크로드로 시험하십시오. 그런 다음, 시간이 지남에 따라 실제 워크로드에서 병렬 쿼리가 얼마나 자주 사용되는지 확인할 수 있도록 다음 모니터링 기법을 사용합니다. 실제 워크로드의 경우, 동시성 한도와 같은 추가 요인이 적용됩니다.