꿈꾸는 DB 전문가

codebeautify.org/sqlformatter

튜닝대상 sql 추출하면  sql이 포맷되지 않은 상태로 나오는 경우가 있어 불편하다. 

위 사이트를 이용하면 예쁘게 만들어준다.

select * from city ;
select * from country;

 explain format = 'json'

-- explain
select * from city c join country cy
on c.country_id = cy.country_id 
where city_id between 1 and 10
;
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "5.76"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "c",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_fk_country_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "city_id"
          ],
          "key_length": "2",
          "rows_examined_per_scan": 10,
          "rows_produced_per_join": 10,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.26",
            "eval_cost": "1.00",
            "prefix_cost": "2.26",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "city_id",
            "city",
            "country_id",
            "last_update"
          ],
          "attached_condition": "(`c`.`city_id` between 1 and 10)"
        }
      },
      {
        "table": {
          "table_name": "cy",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "country_id"
          ],
          "key_length": "2",
          "ref": [
            "sakila.c.country_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 10,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.50",
            "eval_cost": "1.00",
            "prefix_cost": "5.76",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "country_id",
            "country",
            "last_update"
          ]
        }
      }
    ]
  }
}

-- 아래는 실기2번과 비슷한 유형으로 중요하다고 생각되는 부분만 재현한 쿼리임 

/*

SALES_P  -> 월별 파티션테이블 

ORDER_DATE VARCHAR(8)

*/

CREATE TABLE SALES_P(
    ORDER_DATE VARCHAR2(8),
    ORDER_MODE VARCHAR2(6),
    CUSTOMER_ID VARCHAR2(21),
    EMPLOYEE_ID VARCHAR2(13),
    PRODUCT_ID VARCHAR2(13),
    ORDER_QTY NUMBER,
    ORDER_AMT NUMBER
)
PARTITION BY RANGE(ORDER_DATE)  (
    PARTITION P1 VALUES LESS THAN (DATE '2012-02-01') ,
    PARTITION P2 VALUES LESS THAN (DATE '2012-03-01') ,
    PARTITION P3 VALUES LESS THAN (DATE '2012-04-01') ,
    PARTITION P4 VALUES LESS THAN (DATE '2012-05-01') ,
    PARTITION P5 VALUES LESS THAN (DATE '2012-06-01') ,
    PARTITION P6 VALUES LESS THAN (DATE '2012-07-01') ,
    PARTITION P7 VALUES LESS THAN (DATE '2012-08-01') ,
    PARTITION P8 VALUES LESS THAN (DATE '2012-09-01') ,
    PARTITION P9 VALUES LESS THAN (DATE '2012-10-01') ,
    PARTITION P10 VALUES LESS THAN (DATE '2012-11-01') ,
    PARTITION P11 VALUES LESS THAN (DATE '2012-12-01') ,
    PARTITION PMAX VALUES LESS THAN (MAXVALUE) 
    
)  ;

SELECT '일별' 구분 
      , ORDER_DATE ORDATE
      , SUM(ORDER_AMT) ORAMT 
FROM SALES_P 
WHERE ORDER_DATE BETWEEN 20120301 AND 20120331
GROUP BY ORDER_DATE
UNION ALL
SELECT '월별' 구분 
      , SUBSTR(ORDER_DATE, 1, 6) ORDATE
      , SUM(ORDER_AMT) ORAMT 
FROM SALES_P
WHERE ORDER_DATE BETWEEN 20120301 AND 20120331
GROUP BY SUBSTR(ORDER_DATE, 1, 6) 
ORDER BY 1, 2
; -- 32 

 

SQL_ID  21ndvm228700r, child number 1
-------------------------------------
Plan hash value: 230622046
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |       |  4392 (100)|          |       |       |     32 |00:00:00.30 |   15652 |       |       |          |
|   1 |  SORT ORDER BY         |         |      1 |     32 |   608 |  4391   (1)| 00:00:01 |       |       |     32 |00:00:00.30 |   15652 |  2048 |  2048 | 2048  (0)|
|   2 |   UNION-ALL            |         |      1 |        |       |            |          |       |       |     32 |00:00:00.30 |   15652 |       |       |          |
|   3 |    PARTITION RANGE ALL |         |      1 |     31 |   589 |  2196   (1)| 00:00:01 |     1 |    12 |     31 |00:00:00.15 |    7826 |       |       |          |
|   4 |     HASH GROUP BY      |         |     12 |     31 |   589 |  2196   (1)| 00:00:01 |       |       |     31 |00:00:00.15 |    7826 |  1214K|  1214K| 1374K (0)|
|*  5 |      TABLE ACCESS FULL | SALES_P |     12 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
|   6 |    HASH GROUP BY       |         |      1 |      1 |    19 |  2196   (1)| 00:00:01 |       |       |      1 |00:00:00.15 |    7826 |  1160K|  1160K|  504K (0)|
|   7 |     PARTITION RANGE ALL|         |      1 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
|*  8 |      TABLE ACCESS FULL | SALES_P |     12 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter((TO_NUMBER("ORDER_DATE")>=20120301 AND TO_NUMBER("ORDER_DATE")<=20120331))
   8 - filter((TO_NUMBER("ORDER_DATE")>=20120301 AND TO_NUMBER("ORDER_DATE")<=20120331))
 
  SELECT DECODE(B.NO, 1 ,'일별', 2, '월별') 구분
     , DECODE(B.NO, 1, ORDER_DATE, 2, SUBSTR(ORDER_DATE, 1, 6) ) ORDATE 
     , SUM(ORDER_AMT) ORDER_AMT 
FROM (
        SELECT /*+ FULL(S)*/ORDER_DATE 
             , ORDER_AMT 
        FROM SALES_P S
        WHERE ORDER_DATE BETWEEN '20120301' AND '20120331' --> 형변환되지 않게 문자열처리 (날짜가 varchar2 타입이므로) -> 파티션푸루닝 유도 
        ) A , ( SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 2) B --> 같은테이블을 반복으로 읽지않기 위해 복제이용) 
GROUP BY DECODE(B.NO, 1 ,'일별', 2, '월별')
       , DECODE(B.NO, 1, ORDER_DATE, 2, SUBSTR(ORDER_DATE, 1, 6) ) 
ORDER BY 1, 2
; -- 32

SQL_ID  21ndvm228700r, child number 1
-------------------------------------
Plan hash value: 230622046
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |       |  4392 (100)|          |       |       |     32 |00:00:00.30 |   15652 |       |       |          |
|   1 |  SORT ORDER BY         |         |      1 |     32 |   608 |  4391   (1)| 00:00:01 |       |       |     32 |00:00:00.30 |   15652 |  2048 |  2048 | 2048  (0)|
|   2 |   UNION-ALL            |         |      1 |        |       |            |          |       |       |     32 |00:00:00.30 |   15652 |       |       |          |
|   3 |    PARTITION RANGE ALL |         |      1 |     31 |   589 |  2196   (1)| 00:00:01 |     1 |    12 |     31 |00:00:00.15 |    7826 |       |       |          |
|   4 |     HASH GROUP BY      |         |     12 |     31 |   589 |  2196   (1)| 00:00:01 |       |       |     31 |00:00:00.15 |    7826 |  1214K|  1214K| 1374K (0)|
|*  5 |      TABLE ACCESS FULL | SALES_P |     12 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
|   6 |    HASH GROUP BY       |         |      1 |      1 |    19 |  2196   (1)| 00:00:01 |       |       |      1 |00:00:00.15 |    7826 |  1160K|  1160K|  504K (0)|
|   7 |     PARTITION RANGE ALL|         |      1 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
|*  8 |      TABLE ACCESS FULL | SALES_P |     12 |    104K|  1936K|  2191   (1)| 00:00:01 |     1 |    12 |    104K|00:00:00.14 |    7826 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 

5.7

SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2

WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2

WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;

SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...

FROM (SELECT /*+ QB_NAME(qb2) */ ... FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

SELECT /*+ BKA(@`my hint name`) */ ... FROM (SELECT /*+ QB_NAME(`my hint name`) */ ...) ...

SELECT /*+ BKA(@"my hint name") */ ... FROM (SELECT /*+ QB_NAME("my hint name") */ ...) ...

docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.BestPractices.html#Aurora.BestPractices.HashJoin

적용할 수 없는 MySQL 파라미터 및 상태 변수

Aurora MySQL과 MySQL 간의 아키텍처 차이 때문에 일부 MySQL 파라미터와 상태 변수는 Aurora MySQL에 적용되지 않습니다.

다음 MySQL 파라미터는 Aurora MySQL에 적용되지 않습니다.

• innodb_adaptive_flushing

• innodb_adaptive_flushing_lwm

• innodb_change_buffering

• innodb_checksum_algorithm

• innodb_doublewrite

• innodb_flush_method

• innodb_flush_neighbors

• innodb_io_capacity

• innodb_io_capacity_max

• innodb_log_buffer_size

• innodb_log_file_size

• innodb_log_files_in_group

• innodb_max_dirty_pages_pct

• innodb_use_native_aio

• innodb_write_io_threads

• thread_cache_size

다음 MySQL 상태 변수는 Aurora MySQL에 적용되지 않습니다.

• innodb_buffer_pool_bytes_dirty

• innodb_buffer_pool_pages_dirty

• innodb_buffer_pool_pages_flushed

오로라 사용 설명서 

docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.html#AuroraMySQL.Reference.Parameters.Inapplicable

downloads.percona.com/downloads/percona-toolkit/3.3.0/binary/tarball/percona-toolkit-3.3.0_x86_64.tar.gz

www.percona.com/blog/2018/09/13/analyzing-amazon-aurora-slow-logs-pt-query-digest/

MySQL 서버의 Max Connection 고려사항

1. Connection Usage(%)
= Threads_connected / max_connection *100

2.오전 /오후 , 주중/주말 비교 확인
, was 커넥션풀 최대동시커넥션 개수보다 크게

max connections 설정 이후 필수 모니터링
연결취소율(%) = Aborted_clients * 100 / Connections
0% 에 가까울 수록 좋음