JJON's StorY

- 버젼 : Aurora PostgreSQL 13.12

파티션 테이블 관련하여 튜닝을 진행하였는데, 운영에서 이전 보다 오히려 성능이 떨어지는 상황이 발생하였다.

이에 대해 정리 해본다.

대략적인 테이블 구조

CREATE TABLE test_lgct_inrk_test (
	lgct_inrk_no numeric(16) NOT NULL, 
	lgct_inrk_dtm timestamp NOT NULL, 
	lgct_rcpt_no varchar(20) NULL, 
	lgct_rcpt_dtm timestamp NULL, 
	or_no varchar(15) NULL, 
	or_seq numeric(5) NULL, 
	or_cl_no varchar(15) NULL, 
	dlv_fom_cd varchar(2) NULL, 
	ls_updr_id varchar(20) NOT NULL, 
	ls_updt_dtm timestamp NOT NULL, 
	lgct_inrk_rslt_cd varchar(10) NULL, 
	lgct_inrk_rslt_cn text NULL, 
	CONSTRAINT pk_test_lgct_inrk_test PRIMARY KEY (lgct_inrk_no, lgct_inrk_dtm)
)
PARTITION BY RANGE (lgct_inrk_dtm);
CREATE INDEX ix01_test_lgct_inrk_test ON ONLY test_lgct_inrk_test USING btree (or_no, or_seq);
CREATE INDEX ix02_test_lgct_inrk_test ON ONLY test_lgct_inrk_test USING btree (lgct_inrk_dtm, or_no);

-- Partitions
CREATE TABLE test_lgct_inrk_test_pt_202501 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-01-01 00:00:00') TO ('2025-02-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202502 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-02-01 00:00:00') TO ('2025-03-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202503 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-03-01 00:00:00') TO ('2025-04-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202504 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-04-01 00:00:00') TO ('2025-05-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202505 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-05-01 00:00:00') TO ('2025-06-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202506 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-06-01 00:00:00') TO ('2025-07-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202507 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-07-01 00:00:00') TO ('2025-08-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202508 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-08-01 00:00:00') TO ('2025-09-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202509 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-09-01 00:00:00') TO ('2025-10-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202510 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-10-01 00:00:00') TO ('2025-11-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202511 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-11-01 00:00:00') TO ('2025-12-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202512 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-12-01 00:00:00') TO ('2026-01-01 00:00:00');

원본 sql

        update test_lgct_inrk_test
        set ls_updr_id = 'ADMIN'
        , ls_updt_dtm = now()
            , lgct_rcpt_no = '3654573'
            , lgct_rcpt_dtm = now()
            , lgct_inrk_rslt_cd = '9999' 
        where 1=1
        and or_no = '2025033013890'
        and coalesce(or_cl_no,'') = coalesce(null,'')
        and dlv_fom_cd = '01'

update 쿼리를 select 쿼리로 변경하여 test를 진행하였고, 아래와 같이 변경 하였다.

튜닝 전 sql

explain analyze
    select ls_updr_id, 
      ls_updt_dtm,
      lgct_rcpt_no,
      lgct_rcpt_dtm,
      lgct_inrk_rslt_cd
    from test_lgct_inrk_test
        where 1=1
        and or_no = '2025033013890'
        and coalesce(or_cl_no,'') = coalesce(null,'')
        and dlv_fom_cd = '01';

QUERY PLAN                                                                                                                                                                                                 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Append  (cost=0.43..238.62 rows=12 width=126) (actual time=5.370..8.010 rows=7 loops=1)                                                                                                                    |
  ->  Index Scan using test_lgct_inrk_test_pt_202501_or_no_or_seq_idx on test_lgct_inrk_test_pt_202501 test_lgct_inrk_test_1  (cost=0.43..47.33 rows=1 width=40) (actual time=3.234..3.234 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202502_or_no_or_seq_idx on test_lgct_inrk_test_pt_202502 test_lgct_inrk_test_2  (cost=0.43..47.26 rows=1 width=41) (actual time=1.627..1.627 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_3  (cost=0.43..50.76 rows=1 width=40) (actual time=0.507..1.068 rows=7 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_4  (cost=0.29..27.87 rows=1 width=41) (actual time=1.983..1.983 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202505_or_no_or_seq_idx on test_lgct_inrk_test_pt_202505 test_lgct_inrk_test_5  (cost=0.14..8.17 rows=1 width=170) (actual time=0.013..0.013 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202506_or_no_or_seq_idx on test_lgct_inrk_test_pt_202506 test_lgct_inrk_test_6  (cost=0.14..8.17 rows=1 width=170) (actual time=0.014..0.014 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202507_or_no_or_seq_idx on test_lgct_inrk_test_pt_202507 test_lgct_inrk_test_7  (cost=0.14..8.17 rows=1 width=170) (actual time=0.010..0.010 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202508_or_no_or_seq_idx on test_lgct_inrk_test_pt_202508 test_lgct_inrk_test_8  (cost=0.14..8.17 rows=1 width=170) (actual time=0.011..0.011 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202509_or_no_or_seq_idx on test_lgct_inrk_test_pt_202509 test_lgct_inrk_test_9  (cost=0.14..8.17 rows=1 width=170) (actual time=0.011..0.011 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202510_or_no_or_seq_idx on test_lgct_inrk_test_pt_202510 test_lgct_inrk_test_10  (cost=0.14..8.17 rows=1 width=170) (actual time=0.012..0.012 rows=0 loops=1)|
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202511_or_no_or_seq_idx on test_lgct_inrk_test_pt_202511 test_lgct_inrk_test_11  (cost=0.14..8.17 rows=1 width=170) (actual time=0.011..0.011 rows=0 loops=1)|
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202512_or_no_or_seq_idx on test_lgct_inrk_test_pt_202512 test_lgct_inrk_test_12  (cost=0.14..8.17 rows=1 width=170) (actual time=0.010..0.010 rows=0 loops=1)|
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
Planning Time: 3.169 ms                                                                                                                                                                                    |
Execution Time: 8.119 ms                                                                                                                                                                                   |

튜닝 후 sql

explain analyze
		select ls_updr_id, 
			ls_updt_dtm,
			lgct_rcpt_no,
			lgct_rcpt_dtm,
			lgct_inrk_rslt_cd
		from test_lgct_inrk_test
        where 1=1
        and or_no = '2025033013890'
        and coalesce(or_cl_no,'') = coalesce(null,'')
        and dlv_fom_cd = '01'
            and lgct_inrk_dtm between to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD')
                                  and to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD') + interval '5 day';

QUERY PLAN                                                                                                                                                                                                                                                                                   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Append  (cost=0.44..161.47 rows=12 width=126) (actual time=0.036..0.070 rows=7 loops=1)                                                                                                                                                                                                      |
  Subplans Removed: 10                                                                                                                                                                                                                                                                       |
  ->  Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_1  (cost=0.43..51.06 rows=1 width=40) (actual time=0.036..0.055 rows=7 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_2  (cost=0.29..27.97 rows=1 width=41) (actual time=0.013..0.013 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
Planning Time: 1.031 ms                                                                                                                                                                                                                                                                      |
Execution Time: 0.112 ms                                                                                                                                                                                                                                                                     |

and lgct_inrk_dtm between to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD')
                                     and to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD') + interval '5 day';

조건을 추가하여 partition pruning 이 동작하도록 하였다.

이렇게 마무리 하였고, 운영에 적용 하였더니 성능이 미세하게 더 나빠졌다.

다시 확인

explain analyze
        update dis01.test_lgct_inrk_test
        set ls_updr_id = 'ADMIN'
        , ls_updt_dtm = now()
            , lgct_rcpt_no = '3654573'
            , lgct_rcpt_dtm = now()
            , lgct_inrk_rslt_cd = '9999' 
        where 1=1
        and or_no = '2025033013890'
        and coalesce(or_cl_no,'') = coalesce(null,'')
        and dlv_fom_cd = '01'
            and lgct_inrk_dtm between to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD')
                                  and to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD') + interval '5 day';

QUERY PLAN                                                                                                                                                                                                                                                                                   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Update on test_lgct_inrk_test  (cost=0.44..161.53 rows=12 width=339) (actual time=0.571..0.573 rows=0 loops=1)                                                                                                                                                                               |
  Update on test_lgct_inrk_test_pt_202501 test_lgct_inrk_test_1                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202502 test_lgct_inrk_test_2                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_3                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_4                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202505 test_lgct_inrk_test_5                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202506 test_lgct_inrk_test_6                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202507 test_lgct_inrk_test_7                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202508 test_lgct_inrk_test_8                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202509 test_lgct_inrk_test_9                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202510 test_lgct_inrk_test_10                                                                                                                                                                                                                             |
  Update on test_lgct_inrk_test_pt_202511 test_lgct_inrk_test_11                                                                                                                                                                                                                             |
  Update on test_lgct_inrk_test_pt_202512 test_lgct_inrk_test_12                                                                                                                                                                                                                             |
  ->  Index Scan using test_lgct_inrk_test_pt_202501_lgct_inrk_dtm_or_no_idx on test_lgct_inrk_test_pt_202501 test_lgct_inrk_test_1  (cost=0.44..8.47 rows=1 width=310) (actual time=0.011..0.011 rows=0 loops=1)                                                                            |
        Index Cond: ((lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)) AND ((or_no)::text = '2025033013890'::text))                                                         |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                                                                                                       |
  ->  Index Scan using test_lgct_inrk_test_pt_202502_lgct_inrk_dtm_or_no_idx on test_lgct_inrk_test_pt_202502 test_lgct_inrk_test_2  (cost=0.44..8.47 rows=1 width=310) (actual time=0.012..0.012 rows=0 loops=1)                                                                            |
        Index Cond: ((lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)) AND ((or_no)::text = '2025033013890'::text))                                                         |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                                                                                                       |
  ->  Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_3  (cost=0.43..51.07 rows=1 width=310) (actual time=0.026..0.042 rows=7 loops=1)                                                                                  |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_4  (cost=0.29..27.98 rows=1 width=310) (actual time=0.018..0.018 rows=0 loops=1)                                                                                  |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202505_or_no_or_seq_idx on test_lgct_inrk_test_pt_202505 test_lgct_inrk_test_5  (cost=0.14..8.19 rows=1 width=354) (actual time=0.012..0.012 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202506_or_no_or_seq_idx on test_lgct_inrk_test_pt_202506 test_lgct_inrk_test_6  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202507_or_no_or_seq_idx on test_lgct_inrk_test_pt_202507 test_lgct_inrk_test_7  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202508_or_no_or_seq_idx on test_lgct_inrk_test_pt_202508 test_lgct_inrk_test_8  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202509_or_no_or_seq_idx on test_lgct_inrk_test_pt_202509 test_lgct_inrk_test_9  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202510_or_no_or_seq_idx on test_lgct_inrk_test_pt_202510 test_lgct_inrk_test_10  (cost=0.14..8.19 rows=1 width=354) (actual time=0.013..0.013 rows=0 loops=1)                                                                                  |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202511_or_no_or_seq_idx on test_lgct_inrk_test_pt_202511 test_lgct_inrk_test_11  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                  |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202512_or_no_or_seq_idx on test_lgct_inrk_test_pt_202512 test_lgct_inrk_test_12  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                  |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
Planning Time: 18.737 ms                                                                                                                                                                                                                                                                     |
Execution Time: 0.962 ms                                                                                                                                                                                                                                                                     |

partition pruning이 되지 않는다.

여기 저기 검색해보니, postgreSQL의 경우 update시 partition pruning 제약 사항이 상당히 있어 보인다.

서브 쿼리 X
함수 사용 X
예약어 X

explain analyze
        update dis01.test_lgct_inrk_test
        set ls_updr_id = 'ADMIN'
        , ls_updt_dtm = now()
            , lgct_rcpt_no = '3654573'
            , lgct_rcpt_dtm = now()
            , lgct_inrk_rslt_cd = '9999' 
        where 1=1
        and or_no = '2025033013890'
        and coalesce(or_cl_no,'') = coalesce(null,'')
        and dlv_fom_cd = '01'
            and lgct_inrk_dtm between '20250330'::timestamp
                                  and '20250330'::timestamp + interval '5 day';

QUERY PLAN                                                                                                                                                                                                                                                                |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Update on test_lgct_inrk_test  (cost=0.43..78.82 rows=2 width=310) (actual time=0.371..0.372 rows=0 loops=1)                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_1                                                                                                                                                                                                           |
  Update on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_2                                                                                                                                                                                                           |
  ->  Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_1  (cost=0.43..50.89 rows=1 width=310) (actual time=0.027..0.040 rows=7 loops=1)                                                               |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                               |
        Filter: ((lgct_inrk_dtm >= '2025-03-30 00:00:00'::timestamp without time zone) AND (lgct_inrk_dtm <= '2025-04-04 00:00:00'::timestamp without time zone) AND ((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))|
  ->  Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_2  (cost=0.29..27.92 rows=1 width=310) (actual time=0.013..0.014 rows=0 loops=1)                                                               |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                               |
        Filter: ((lgct_inrk_dtm >= '2025-03-30 00:00:00'::timestamp without time zone) AND (lgct_inrk_dtm <= '2025-04-04 00:00:00'::timestamp without time zone) AND ((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))|
Planning Time: 0.375 ms                                                                                                                                                                                                                                                   |
Execution Time: 0.407 ms                                                                                                                                                                                                                                                  |

.

1. Aurora MySQL 5.7과 MySQL 5.7 비교

다음 기능은 MySQL 5.7.12에서 지원되지만 Aurora MySQL 5.7에서는 현재 지원되지 않습니다.

그룹 복제 플러그인
페이지 크기 증가
시작 시 InnoDB 버퍼 풀 로딩
InnoDB 풀 텍스트 구문 분석기 플러그인
멀티 소스 복제
온라인 버퍼 풀 크기 조정
암호 확인 플러그인
쿼리 다시 쓰기 플러그인
복제 필터링
CREATE TABLESPACE SQL 문
X 프로토콜

 2. 빠른 입력 기능

빠른 입력 기능은 기본 키에 의해 정렬되는 병렬 입력을 빠르게 처리해 주며, 특히 LOAD DATA 및 INSERT INTO ... SELECT ... 문 사용 시 유용합니다. 빠른 입력 기능은 SQL 구문을 실행할 때 인덱스 순회에서 커서의 위치를 캐싱합니다. 이에 따라 인덱스를 불필요하게 다시 순회하지 않도록 해줍니다.

다음 측정치를 모니터링하면 DB 클러스터에서 빠른 입력 기능의 효과를 확인할 수 있습니다.

• aurora_fast_insert_cache_hits: 캐싱된 커서가 성공적으로 검색 및 확인되면 증가하는 카운터입니다.
• aurora_fast_insert_cache_misses: 캐싱된 커서가 더 이상 유효하지 않고 Aurora가 정상적인 인덱스 순회를 수행하면 증가하는 카운터입니다.

다음 명령을 사용하면 빠른 입력 측정치의 현재 값을 검색할 수 있습니다.

mysql> show global status like 'Aurora_fast_insert%';               


+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| Aurora_fast_insert_cache_hits   | 3598300   |
| Aurora_fast_insert_cache_misses | 436401336 |
+---------------------------------+-----------+

3. 스토리지

Aurora는 Shared Storage를 사용하며 MySQL의 경우 binlog 기반의 replication이 아닌 Storage와 page기반의 replication을 사용합니다.

가장 큰 차이점은 스토리지입니다. 위 그림의 위는 MySQL replica, 아래는 AWS aurora입니다.

MySQL의 경우 자신의 EBS로 데이터를 쌓고 쌓은 데이터를 EBS로 미러링 한 다음 replication을 통해 replica로 데이터를 보내고 replica는 받은 데이터를 자신의 스토리지로 쌓습니다. 데이터베이스는 결국 트랜잭션의 결과물이 input/ooutput 형태로 수행되는 것이기에 before & after 작업들이 WAL(Write-Ahead-Log)를 통해 스토리지에 저장됩니다. 이 방식의 단점은 I/O 대역폭과 IOPS가 제한적이고 이부분에 대해 변경이 필요할 경우 사용자가 직접 튜닝해야 합니다.
반면 aurora의 경우에는 4/6 쿼럼을 사용해서 스토리지에 저장하며 replica로 보내는 것은 frm 및 redo log입니다. 그래서 network badwidth 사용도 적으며 빠르게 변경분을 저장하고 반영할 수 있습니다. 인스턴스와 스토리지의 영역을 나눴기 때문에 이와 같은 아키텍쳐를 그릴 수 있습니다. 스토리지 서브 시스템은 segmented 방식이며 EBS가 아닌 고성능 NVMe SSD 를 사용하고 데이터는 지속적으로 S3에 백업 합니다.

이렇게 보면 writer에 많은 DML이 들어오는 인스턴스의 경우 aurora를 사용하면 적은 replica lag을 가지면서 서비스 운영을 할 수 있다는 것으로 보입니다. 이론상으로만 보면 그렇습니다. 하지만 AWS 스토리지 내부 아키텍쳐를 좀 더 보아야 합니다.

MySQL의 경우 read replica도 binlog를 받아서 처리하기 때문에 read 뿐만아니라 write도 같이 처리해야 하는 단점이 있습니다. AWS aurora MySQL의 경우 read replica가 binlog를 읽어서 싱크를 맞추는 것이 아니라 redo log를 받아서 동기화 하기 때문에 read에만 집중할 수 있습니다. 이점이 aurora MySQL 이 더 뛰어난 점이라고 말 할 수 있습니다.

정리 하자면 가장 큰 차이점은 스토리지 및 관리 주체, read replica의 구성 방식 세가지로 볼 수 있습니다.

• 스토리지 : MySQL은 자체 스토리지로 운영하지만 aurora MySQL은 Shared Storage를 사용한다.
• 관리 주체: MySQL은 관리자가 MySQL 버전을 올리면서 사용 하지만 aurora MySQL은 AWS가 개발해서 버전 업그레이드를 주기적으로 하기 때문에 optional 또는 mandatory가 AWS에 의해 정해질 수 있다. (참조 : https://forums.aws.amazon.com/forum.jspa?forumID=60&start=0 )
• read replica 구성 : MySQL은 standby와 read replica를 만들 때 binlog를 사용하지만 aurora의 경우 내부 storage 및 redo log 전송을 통해 빠른 동기화가 가능하며 bandwidth를 줄일 수 있다

1. 랩 모드 사용

Aurora 랩 모드는 현재 Aurora 데이터베이스 버전에서 사용 가능한 Aurora 기능을 활성화하는 데 사용되지만 기본적으로 비활성화되어 있습니다. 프로덕션 DB 클러스터에서 Aurora 랩 모드 기능을 사용하지 않는 것이 좋지만 개발 및 테스트 환경에서 Aurora 랩 모드를 사용하여 DB 클러스터에 대해 이러한 기능을 활성화할 수 있습니다.

aurora_lab_mode 파라미터는 기본 파라미터 그룹에 속하는 인스턴스 수준 파라미터입니다. 기본 파라미터 그룹에서는 이 파라미터가 0(비활성)으로 설정됩니다. Aurora 랩 모드를 활성화하려면 사용자 지정 파라미터 그룹을 생성하고 사용자 지정 파라미터 그룹에서 aurora_lab_mode 파라미터를 1(활성)로 설정한 후, 사용자 지정 파라미터 그룹을 사용하도록 Aurora 클러스터에서 하나 이상의 DB 클러스터를 수정합니다. 그런 다음, 랩 모드 기능을 시도하기 위해 해당되는 인스턴스 엔드포인트에 연결합니다.

1.1. Aurora 랩 모드 기능

배치화 스캔 :
Aurora MySQL 스캔 배치화는 인 메모리 스캔 지향 쿼리의 속도를 크게 높입니다. 이 기능은 일괄 처리로 테이블 전체 스캔, 인덱스 전체 스캔 및 인덱스 범위 스캔의 성능을 향샹시킵니다.

해시 조인 :
이 기능은 동등 조인을 사용하여 많은 양의 데이터를 조인해야 하는 경우 쿼리 성능을 향상시킬 수 있습니다. Aurora MySQL 버전 1에는 랩 모드가 필요합니다. Aurora MySQL 버전 2에는 랩 모드 없이 이 기능을 사용할 수 있습니다.

빠른 DDL :
이 기능을 사용하면 ALTER TABLE tbl_name ADD COLUMN col_name column_definition 작업을 거의 동시에 실행할 수 있습니다. 이 작업은 테이블을 복사하거나 다른 DML 명령문에 영향을 거의 주지 않고 완료됩니다. 테이블 복사를 위해 임시 스토리지를 사용하지 않으므로 라지 테이블에 대해서도 DDL 문을 쉽게 만듭니다. 현재 빠른 DDL은 테이블 끝에서 기본값 없이 null이 허용된 열에 대해서만 지원됩니다.

2. 비동기식 키 미리 가져오기

Amazon Aurora은 Async Key Prefetch(AKP)를 사용하여 여러 인덱스 사이에 테이블을 조인하는 쿼리 성능을 높일 수 있습니다. 이 기능은 JOIN 쿼리에서 Batched Key Access(BKA) 조인 알고리즘과 Multi-Range Read(MRR) 최적화 기능을 사용해야 하는 쿼리를 실행하면서 필요한 행을 예측하여 성능을 높이는 효과가 있습니다.

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

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

• Using Key Prefetching
• Using join buffer (Batched Key Access with Key Prefetching)

다음은 EXPLAIN 문에 EXTENDED 키워드를 사용하여 AKP를 이용할 수 있는 쿼리의 실행 계획을 확인하는 예제입니다.

mysql> explain extended 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)

3. 해시 조인 작업

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

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

• int, bigint, numeric 및 bit 등과 같은 정확한 숫자 데이터 형식 범주의 모든 항목을 비교할 수 있습니다.
• float 및 double과 같은 대략적인 숫자 데이터 형식 범주의 모든 항목을 비교할 수 있습니다.
• 문자열 유형에 동일한 문자 세트와 콜레이션이 있는 경우 문자열 유형간에 항목을 비교할 수 있습니다.
• 유형이 동일한 경우 날짜 및 타임스탬프 데이터 형식으로 항목을 비교할 수 있습니다.

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

• left/right outer 조인은 지원되지 않습니다.
• 하위 쿼리가 구체화되지 않는 한 하위 쿼리와 같은 Semijoin은 지원되지 않습니다.
• 다중 테이블의 업데이트 또는 삭제는 지원되지 않습니다.
• BLOB 및 공간 데이터 타입 열은 해시 조인의 조인 할 수 없습니다.

해시 조인이 쿼리에 사용할 테이블에 적용되는 경우 이 열에 다음과 비슷한 값이 포함됩니다.

• Using where; Using join buffer (Hash Join Outer table table1_name)
• Using where; Using join buffer (Hash Join Inner table table2_name)

다음은 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은 해시 테이블을 프로브하는 데 사용하는 테이블입니다.

4. Aurora MySQL 힌트

Aurora MySQL 쿼리와 함께 SQL 힌트를 사용하여 성능을 미세 조정할 수 있습니다. 또한 힌트를 사용하여 예기치 않은 조건에 따라 중요한 쿼리에 대한 실행 계획이 변경되는 것을 방지할 수 있습니다.

4.1. HASH_JOIN, NO_HASH_JOIN

쿼리에 해시 조인 최적화 방법을 사용할지 여부를 선택할 수 있는 최적화 프로그램의 기능을 설정하거나 해제합니다. HASH_JOIN을 사용하면 해당 메커니즘이 더 효율적인 경우 최적화 프로그램이 해시 조인을 사용할 수 있습니다. NO_HASH_JOIN은 최적화 프로그램이 쿼리에 해시 조인을 사용하지 못하게 합니다.

다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다.

EXPLAIN SELECT/*+ HASH_JOIN(t2) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;


EXPLAIN SELECT /*+ NO_HASH_JOIN(t2) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;

4.2. HASH_JOIN_PROBING, NO_HASH_JOIN_PROBING

해시 조인 쿼리에서 조인의 프로브 측에 지정된 테이블을 사용할지 여부를 지정합니다. 쿼리는 프로브 테이블의 전체 내용을 읽는 대신 빌드 테이블의 열 값이 프로브 테이블에 있는지 여부를 테스트합니다. HASH_JOIN_PROBING 및 HASH_JOIN_BUILDING을 사용하여 쿼리 텍스트 내에서 테이블을 재정렬하지 않고 해시 조인 쿼리가 처리되는 방법을 지정할 수 있습니다.

다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다. T2 테이블에 HASH_JOIN_PROBING 힌트를 지정하면 T1 테이블에 NO_HASH_JOIN_PROBING을 지정하는 것과 같은 효과가 있습니다.

EXPLAIN SELECT /*+ HASH_JOIN(t2) HASH_JOIN_PROBING(t2) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;


EXPLAIN SELECT /*+ HASH_JOIN(t2) NO_HASH_JOIN_PROBING(t1) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;

4.3. HASH_JOIN_BUILDING, NO_HASH_JOIN_BUILDING

해시 조인 쿼리에서 조인의 빌드 측에 지정된 테이블을 사용할지 여부를 지정합니다. 쿼리는 이 테이블의 모든 행을 처리하여 다른 테이블과 상호 참조할 열 값 목록을 작성합니다. HASH_JOIN_PROBING 및 HASH_JOIN_BUILDING을 사용하여 쿼리 텍스트 내에서 테이블을 재정렬하지 않고 해시 조인 쿼리가 처리되는 방법을 지정할 수 있습니다.

다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다. T2 테이블에 HASH_JOIN_BUILDING 힌트를 지정하면 T1 테이블에 NO_HASH_JOIN_BUILDING을 지정하는 것과 같은 효과가 있습니다.

EXPLAIN SELECT /*+ HASH_JOIN(t2) HASH_JOIN_BUILDING(t2) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;


EXPLAIN SELECT /*+ HASH_JOIN(t2) NO_HASH_JOIN_BUILDING(t1) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;

4.4. JOIN_FIXED_ORDER

쿼리의 테이블이 쿼리에 나열된 순서에 따라 조인되도록 지정합니다. 이는 세 개 이상의 테이블을 포함하는 쿼리에 특히 유용합니다. 이는 MySQL STRAIGHT_JOIN 힌트를 대체하기 위한 것입니다. MySQL JOIN_FIXED_ORDER 힌트에 해당합니다.

다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다.

EXPLAIN SELECT /*+ JOIN_FIXED_ORDER */ f1, f2
  FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);

4.5. JOIN_ORDER

쿼리의 테이블에 대한 조인 순서를 지정합니다. 이는 세 개 이상의 테이블을 포함하는 쿼리에 특히 유용합니다.

다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다.

EXPLAIN SELECT /*+ JOIN_ORDER (t4, t2, t1, t3) */ f1, f2
  FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);

4.6. JOIN_PREFIX

조인 순서에서 먼저 넣을 테이블을 지정합니다. 이는 세 개 이상의 테이블을 포함하는 쿼리에 특히 유용합니다.

다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다.

EXPLAIN SELECT /*+ JOIN_ORDER (t4, t2) */ f1, f2
  FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);

4.7. JOIN_SUFFIX

조인 순서에 마지막으로 넣을 테이블을 지정합니다. 이는 세 개 이상의 테이블을 포함하는 쿼리에 특히 유용합니다.

다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다.

EXPLAIN SELECT /*+ JOIN_ORDER (t1, t3) */ f1, f2
  FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);

1. Aurora 스토리지 개요

Aurora SSD(Solid State Drive)를 사용하는 단일 가상 볼륨인 클러스터 볼륨에 저장됩니다. 클러스터 볼륨은 동일한 AWS 리전에 속한 다중 가용 영역의 데이터 사본으로 구성되어 있습니다. 이러한 가용 영역에서 데이터가 자동으로 복제되기 때문에 데이터 손실 가능성은 줄고 오히려 내구성이 크게 높아집니다. 이러한 복제를 통해 장애 조치 중에도 데이터베이스의 가용성이 높아집니다. 데이터 사본이 이미 나머지 가용 영역에 존재하여 DB 클러스터의 DB 인스턴스에 대한 데이터 요청을 계속 처리할 수 있기 때문입니다. 복제 양은 클러스터의 DB 인스턴스 수와는 관계가 없습니다.

2. 클러스터 볼륨에 포함된 항목

Aurora 클러스터 볼륨에는 모든 사용자 데이터, 스키마 객체, 내부 메타데이터(예: 시스템 테이블 및 이진 로그)가 포함되어 있습니다. 예를 들어 Aurora는 클러스터 볼륨의 Aurora 클러스터에 대한 모든 테이블, 인덱스, BLOB(Binary Large Object), 저장 프로시저 등을 저장합니다.

Aurora 공유 스토리지 아키텍처는 데이터를 클러스터의 DB 인스턴스와 독립적으로 만듭니다. 예를 들어 Aurora가 테이블 데이터의 새 복사본을 만들지 않으므로 DB 인스턴스를 빠르게 추가할 수 있습니다. 대신에 DB 인스턴스는 이미 모든 데이터를 포함하는 공유 볼륨에 연결됩니다. 클러스터에서 기본 데이터를 제거하지 않고 클러스터에서 DB 인스턴스를 제거할 수 있습니다. 전체 클러스터를 삭제하는 경우에만 Aurora가 데이터를 제거합니다.

3. Aurora 스토리지 크기가 자동으로 조정되는 방법

데이터베이스의 데이터 용량이 늘어날수록 Aurora 클러스터 볼륨도 자동 확장됩니다. Aurora 클러스터 볼륨 크기는 최대 128 tebibytes (TiB)까지 증가할 수 있습니다.

이 자동 스토리지 조정은 고성능의 고도로 분산된 스토리지 하위 시스템을 통해 이루어집니다. 따라서 주요 목표가 안정성과 고가용성인 경우 중요한 엔터프라이즈 데이터에 Aurora를 선택하면 좋습니다.

Aurora 데이터가 제거되면 해당 데이터에 할당된 공간이 복원됩니다. 데이터 제거의 예로는 테이블 삭제 또는 자르기 등이 있습니다. 이렇게 스토리지 사용량이 자동으로 줄어들면 스토리지 요금을 최소화할 수 있습니다.

클러스터 볼륨의 최대 크기 및 데이터 삭제 시 자동 크기 조정과 같은 일부 스토리지 기능은 클러스터의 Aurora 버전에 따라 다릅니다. 

4. Amazon Aurora 안정성

Aurora는 안정성, 내구성 및 내결함성을 고려하여 설계되었습니다. Aurora DB 클러스터는 Aurora 복제본을 추가하여 다른 가용 영역에 배포하는 등의 방법으로 가용성을 높이도록 설계할 수 있습니다. 그 밖에도 Aurora에는 안정적인 데이터베이스 솔루션을 위한 자동 기능이 몇 가지 포함되어 있습니다.

4.1. 스토리지 자동 복구

Aurora은 3개의 가용 영역에 여러 개의 복사본을 보관하고 있기 때문에 디스크 결함으로 인한 데이터 손실 가능성이 최소화됩니다. Aurora은 클러스터 볼륨을 구성하고 있는 디스크 볼륨에서 결함을 자동 감지합니다. 예를 들어 디스크 볼륨 세그먼트에 결함이 발생하면 Aurora가 즉시 해당 세그먼트를 복구합니다. Aurora가 디스크 세그먼트를 복구할 때는 동일한 클러스터 볼륨을 구성하는 나머지 디스크 볼륨의 데이터를 사용하기 때문에 복구 세그먼트의 데이터도 이용 가능합니다. 결과적으로 Aurora는 데이터 손실을 방지할 뿐만 아니라 특정 시점으로 복구 기능을 사용해 디스크 결함을 복구할 필요성도 줄어듭니다.

4.2. 유지 가능한 캐시 워밍

Aurora는 전원이 꺼진 데이터베이스를 가동하거나 결함 발생 이후 다시 시작할 때 버퍼 풀 캐시를 "워밍"합니다. 즉, Aurora가 인 메모리 페이지 캐시에 저장된 기존 공통 쿼리 페이지를 이용해 버퍼 풀을 미리 로드합니다. 이 경우 일반적인 데이터베이스 사용과 달리 버퍼 풀의 "웜 업" 필요성을 우회할 수 있기 때문에 성능이 향상되는 이점이 있습니다.

Aurora 페이지 캐시는 데이터베이스가 아닌 별도의 프로세스로 관리되기 때문에 데이터베이스와 상관없이 유지됩니다. 예상과 달리 데이터베이스에 결함이 발생하더라도 페이지 캐시가 메모리에 남아있기 때문에 데이터베이스를 재시작할 때 버퍼 풀이 가장 최신 상태로 워밍됩니다.

4.3. crush 복구

Aurora은 거의 순간적으로 crush에서 복구하고 바이너리 로그없이 애플리케이션 데이터를 계속 제공하도록 설계되었습니다. Aurora은 병렬 스레드에서 비동기 방식으로 crush 복구를 실행하므로 crush 직후에도 데이터베이스를 열어두고 사용할 수 있습니다.

다음은 Aurora MySQL에서의 바이너리 로깅 및 crush 복구 시 고려 사항입니다.

1. Aurora 바이너리 로깅을 활성화하면 DB 인스턴스로 하여금 강제로 바이너리 로그 복구를 수행하도록 하므로 crush 후 복구 시간에 직접적인 영향을 줍니다.

2. 사용되는 bin 로깅 유형은 로깅의 크기와 효율에 영향을 미칩니다. 데이터베이스 활동의 양이 동일하더라도 형식에 따라 bin 로그에서 더 많은 정보가 로깅됩니다. 다음과 같은 binlog_format 파라미터 설정으로 로그 데이터의 양이 달라집니다.

ROW – 최대 로그 데이터
STATEMENT – 최소 로그 데이터
MIXED – 일반적으로 데이터 무결성과 성능의 최상의 조합을 제공하는 적당량의 로그 데이터

bin 로그 데이터의 양은 복구 시간에 영향을 미칩니다. bin 로그에 로깅된 데이터가 더 많은 경우, DB 인스턴스는 복구 도중 더 많은 데이터를 처리해야 하므로 복구 시간이 길어집니다.

3. Aurora은 DB 클러스터 내에서 데이터를 복제하거나 특정 시점 복원(PITR)을 수행하기 위해 바이너리 로그를 필요로 하지 않습니다.

4. 외부 복제(또는 외부 이진 로그 스트림)에 bin 로그가 필요하지 않은 경우, binlog_format 파라미터를 OFF로 설정하여 이진 로깅을 비활성화하는 것이 좋습니다. 이렇게 하면 복구 시간이 단축됩니다.

5. Aurora 스토리지 저장 방식

Aurora는 Shard Storage를 사용하며, MySQL의 binlog 기반의 replication이 아닌 Storage와 page 기반의 replication을 사용합니다.

Aurora 스토리지 내부 아키텍쳐를 이해하기 위해 쿼럼(Quorum)방식을 이해해야 합니다.

Casandra는 세션별로 데이터 저장 , 조회시 몇개의 노드에서 저장, 조회 할지 선택할 수 있습니다. 물론 Global 하게 서버내에서 지정할 수도 있습니다. 일반적으로 노드가 여러개 필요한 NoSQL의 경우 많이 나오는 용어입니다. Aurora는 내부적인 가용성을 확보하기 위해 총 3개의 AZ, 각각의 AZ 별로 2개의 스토리지 영역을 두고 있습니다. 하나의  AZ가 무너져도 다른 AZ에서는 서비스가 가능하고 데이터의 유실이 없게 해줍니다.

Auroroa의 경우 6개의 스토리지 영역이 static하게 설정되어 있기 때문에 write는 4/6, read는 3/6 쿼럼을 사용합니다. 즉 write가 6개 중에 4개의 스토리지에 성공해야 완료이고, read 는 6개중 3개의 스토리지에 성공해야 완료됩니다. Aurora는 내부적으로 스토리지의 latency를 측정하고 있고 응답이 가장 빠르게 오는 스토리지에 write를 시도합니다. 물론 instance와 같은  AZ에 있는 스토리지 영역이 가장 빠르게 응답할 것이고 거기에 데이터가 쌓인 이후 다른  AZ의 스토리지 영역에 데이터가 쌓입니다.

Primary Instance(Master)에서 write가 발생하면 Incoming queue라는 것을 이용해서 비동기 방식으로 처리하게 됩니다. 모든 데이터 저장 스탭이 비동기로 이루어 지기 때문에 insert 가 빠르게 느껴지지만 실제로 스토리지가 움직이는 건 비동기이기 때문에 disk에 어디까지 저장했는지는 Aurora 스토리지만 알고 있습니다. 실제로 Incoming queue가 밀리면 ack가 늦을 수도 있고 장애 났을때 복구 되는 시간도 늘어날 수 있습니다. write시 4개의 쓰기가 성공 할때 까지 대기하지 않고 incoming queue에 들어가고 queue 에 쌓이면 바로 ack 하기 때문에 instance에서는 하나의 스토리지에 반영되면 바로 응답을 받기 때문에 신경 안쓰고 완료 처리할 수 있습니다. 즉 4개에 모두 쌓이는 것을 대기 하지 않습니다.

쿼럼은 요청에 대한 응답을 보내기 위한 최소값입니다. 4/6 쿼럼이 충족되면 스토리지는 VCL이라는 값을 가집니다. VCL은 Volume Completed LSN으로 볼륨에 쓰기가 완료 됐다는 의미 입니다. 관련된 용어로는 CPL(Consustency Point LSN)이 존재합니다. CPL은 다른말로 최근에 커밋된 레코드 입니다. 일관성이 보장된 마지막 포인트가 CPL이기 때문에 장애시에는 최종 CPL 이후의 값은 모두 지우고 복구가 일어납니다.

VCL이 일어난 다음에는 스토리지의 background 작업의 일환으로 나머지 2개의 스토리지에도 카피가 일어납니다. 즉 하나의 write에 대한 쿼럼은 6개의 스토리지중 4개지만 저장은 6개 모두 일어납니다.

이처럼 Aurora의 경우 스토리지 내부의 비동기 방식과 쿼럼 방식이 같이 존재하기 때문에 관리가 어렵지만 운영하는 입장에서 보면 내부적으로 무슨 일이 일어나는지 알수 없습니다.