Let's have a cup of coffee.

개요 : slow_query에서 7일 단위로 느린 SQL 총수행시간(=실행건수 * 평균소요시간 ) 기준으로 추출하기

-- 튜닝 대상 TOP 50 추출

--1. TOP50 저장 테이블 생성

DROP TABLE SLOW_QUERY_TOP50;

CREATE TABLE SLOW_QUERY_TOP50
(
EXTRACT_DATE DATE NOT NULL,
RN DECIMAL NOT NULL,
START_TIME DATETIME NOT NULL,
USER_HOST VARCHAR(100) COLLATE UTF8MB4_0900_BIN NOT NULL,
ROW_SENT DECIMAL NOT NULL,
ROW_EXMAINED DECIMAL NOT NULL,
MIN_QUERY_TIME VARCHAR(10) COLLATE UTF8MB4_0900_BIN NOT NULL,
MAX_QUERY_TIME VARCHAR(10) COLLATE UTF8MB4_0900_BIN NOT NULL,
AVG_QUERY_TIME VARCHAR(10) COLLATE UTF8MB4_0900_BIN NOT NULL,
CNT DECIMAL NOT NULL,
TOTAL_QUERY_TIME VARCHAR(10) COLLATE UTF8MB4_0900_BIN NOT NULL,
SQL_TEXT MEDIUMBLOB NOT NULL,
TUNNING_YN VARCHAR(100),
PRIMARY KEY(EXTRACT_DATE, RN)
)ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE=UTF8MB4_0900_BIN;


--2. 추출 대상 넣기
SET @RN = 0;

INSERT SLOW_QUERY_TOP50
SELECT  NOW(), @RN:=@RN+1 AS RN
      , 최근수행시간, 결과건수, 수행중처리행수, 최소수행시간, 최해대수행시간, 평균수행시간, 수행횟수, 총수행시간, 대상쿼리
 FROM ( 
      SELECT  a.start_time AS 최근수행시간, a.user_hot, a.rows_sent 결과건수
            , a.rows_examined 수행중처리행수, x.min_query_time 최소수행시간, x.max_query_time 최대수행시간
            , x.avg_query_time 평균수행시간, x.cnt 수행횟수
            , SET_TO_TIME(TIME_TO_SEC(x.avg_query_time)*cnt) 총수행시간
            , a.sql_text 대상쿼리
        FROM mysql.slow_log a
       INNER JOIN ( 
                  SELECT  SUBSTRING(INSTR(REGEXP_REPLACE(UPPER(sql_text),'[^[:alpha:]]',''),'SELECT')1,200) SQL_TEXT
                        , MAX(start_time) start_time
                        , MIN(TIME_TO_SEC(query_time)) min_query_time_sec
                        , DATE_FORMAT(MIN(query_time),'%T') min_query_time
                        , DATE_FORMAT(MAX(query_time),'%T') max_query_time
                        , DATE_FORMAT(SEC_TO_TIME(avg(TIME_TO_SEC(query_time))), '%T') avg_query_time
                        , count(*) cnt
                    FROM mysql.slow_log a
                   WHERE user_host not like '%DEV%'
                     AND query_time > '00:00:05' -- 튜닝 대상 시간 선정(5초 이상 수행 쿼리 대상)
                     AND a.sql_text not like '%INSERT%'
                     AND a.sql_text not like '%ApplicationName=DBeaver%'
                     AND a.start_time >= date_sub(now(), INTERVAL 7 day) -- 7일전 부터 실행 쿼리 기준
                     AND LENGTH(REGEXP_REPLACE(sql_text,'[^[:alpha:]]','')) > 50 -- 50자 이상 쿼리
                  GROUP BY SUBSTRING(INSTR(REGEXP_REPLACE(UPPER(a.sql_text),'[^[:alpha:]]',''),'SELECT'),1,200)
                  ) x
                  ON SUBSTRING(INSTR(REGEXP_REPLACE(UPPER(a.sql_text),'[^[:alpha:]]',''),'SELECT'),1,200) = x.sql_text
                 AND a.start_time = x.start_time
                 AND x.min_query_time > '00:00:05'
                 AND x.avg_query_time > '00:00:05'
       WHERE user_host not like '%DEV%'
         AND query_time > '00:00:05'
         AND a.sql_text not like '%INSERT%' -- INSERT 대상 제외
         AND a.sql_text not like '%ApplicationName=DBeaver%'
         AND a.start_time >= date_sub(now(), INTERVAL 7 day) -- STR_TO_DATE('2025-02-05 13:00:00','%Y%m%d %H:%i:%S')
       ORDER BY 총수행시간 DESC
       LIMIT 50
     ) a
    ;


-- 결과 추출 
-- 2회차는 NOT EXISTS 추가
SET @RN = 0;

SELECT EXTRACT_DATE||'_'||LPAD(@RN:@RN+1,2,'0') EXTRACT_RN, RN, EXTRACT_DATE, USER_HOST
     , ROW_EXAMINED, ROW_SENT, MIN_QUERY_TIME, AVG_QUERY_TIME, MAX_QUERY_TIME, CNT
     , TOTAL_QUERY_TIME, SQL_TEXT, TUNNING_YN
  FROM SLOW_QUERY_TOP50 A
 WHERE EXTRACT_DATE = '2025-02-05'
   AND NOT EXISTS(SELECT 1 
                    FROM SLOW_QUERY_TOP50 X 
                   WHERE EXTRACT_DATE !='2025-02-05'
                     AND SUBSTRING(INSTR(REGEXP_REPLACE(UPPER(X.SQL_TEXT),'[^[:alpha:]]',''),'SELECT',1,200) = SUBSTRING(INSTR(REGEXP_REPLACE(UPPER(A.SQL_TEXT),'[^[:alpha:]]',''),'SELECT'),1,200))
;


-- 정규식 시간제한으로 에러 시 방안
-- 1. SQL_TEXT 변경 쿼리 : 좀 더 부 정확함 
SUBSTRING(INSTR(REPLACE(UPPER(sql_text),' ',''),'SELECT'),1,200) SQL_TEXT

-- 2. 중간 테이블 추가하여 추출 (추후)

개요 : 병렬 쿼리가 실행되지 않는 문제점 발생하여 원인을 추적하니 Aurora I/O-Optimized DB 클러스터 스토리지 구성이었다.

MySQL 병렬 쿼리 기능의 제한사항

• 병렬 쿼리는 Aurora I/O-Optimized DB 클러스터 스토리지 구성에서 지원되지 않는다.
• db.t2 또는 db.t3 인스턴스 클래스에는 병렬 쿼리는 사용할 수 없다. 병렬 쿼리에 사용하려는 테이블에는 COMPACT 또는 DYNAMIC행 형식을 사용한다.
• ....
• AWS > Amazon Aurora 사용 설명서 참고

조치 사항 : I/O-Optimized DB 클러스터 구성 -> Standard 전환 요청 

https://aws.amazon.com/ko/blogs/korea/new-amazon-aurora-i-o-optimized-cluster-configuration-with-up-to-40-cost-savings-for-i-o-intensive-applications/

Standard -> I/O Optimized 전환 : 30일에 한 번

I/O Optimized -> Standard 전환 : 언제든지 

Aurora I/O-Optimized는 I/O 비용이 청구되지 않으므로 Standard와 비교하여 컴퓨팅 및 스토리지에 대해 정해진 가격이 청구된다.

I/O-Optimized의 경우 가격 대비 성능이 향상되고 비용을 최대 40% 절감할 수 있다.

위와 같은 I/O 요금 절감으로 인해서 I/O Optimized DB 클러스터 구성으로 되어 있었다. Standard로 변경 요청하고 병렬 쿼리를 실행한다.

SET SESSION aurora_parallel_query = ON;
SET SESSION aurora_pq_force = ON;
SET SESSION innodb_parallel_read_threads = 16; -- 4, 8, 16, 32, ....

EXPLAIN ANALYZE
SELECT /*+ PQ(A) */ COUNT(*) FROM IV_TDMR_BYDY_MD_S A;

-> Aggregate: count(int_mux(1)) [Using parallel query(0 group-bys, 1 aggrs)] (cost=79031804.24 rows=341928662) (actual time=151080.384..151080.385 rows=1 loops=1)
  -> Table scan on A [Using parallel query(0 columns)] (cost=44838938.04 rows=341928662) (actual time=163.610..150852.497 rows=330009 loops=1) [parallel query actual (total time= 151011.460, rows returned= 3300009)]

개요 : MySQL은 NOT NULL 컬럼에 '' 과 ' ' 입력을 허용하여서 정제되지 않은 데이터의 경우나 개발자들이 '' 을 입력하고 조회에 조건으로 사용하는 경우가 있으므로 아래와 같이 조회되는 방법과 차이점을 알아두자.

'' : 공백 , ' ' : 스페이스 라고 하자.

create table test(
col1 varchar(20),
col2 varchar(20));

insert into test values('', '111');
insert into test values(' ', '112');
insert into test values('  ', '113');
insert into test values('  a a ', '114');
insert inot test values('a a', '115');
insert into test values('222', '116');

select * from test; -- 임의로 '(싱글쿼테이션) 사용해서 결과 표시
col1      | col2 |
------------------
''        |  111 |
' '       |  112 |
'  '      |  113 |
'  a a '  |  114 |
'a a'     |  115 |
'222'     |  116 |
------------------

select ascii(''), ascii(' ');  -- 공백: 0,  스페이스 : 32
select * from test where col1 = '';
col1      | col2 |
------------------
''        |  111 |
select * from test where ascii(col1) = 0;
col1      | col2 |
------------------
''        |  111 |
select a.*, ascii(col1), ascii(trim(col1)), ascci('a'), instr(col1,' ') from test a where ascii(col1) = 32; -- 첫 문자 스페이스 있는 데이터 모두 출력
col1      | col2 | ascii(col1) | ascii(trim(col1)) | ascii('a') | instr(col1,' ') |
-----------------------------------------------------------------------------------
' '       |  112 |          32 |                 0 |         97 |              1  |
'  '      |  113 |          32 |                 0 |         97 |              1  |
'  a a '  |  114 |          32 |                97 |         97 |              1  |
select * from test where col1 = ' ';
col1      | col2 |
------------------
' '       |  112 |
select * from test where trim(col1) = '';
col1      | col2 |
------------------
''        |  111 |
' '       |  112 |
'  '      |  113 |