서버 쪽에서 쿼리 타임아웃 지정(max_connection_time)

지금까지는 클라이언트 쪽에서는 쿼리의 타임아웃을 지정하는 방법이 있었다.

 

예를들면 Connector/J에서는 접속 시에 enableQueryTimeouts 옵션을 활성화하고,
애플리케이션 내에서 쿼리 실행 전에 java.sql.Statement#seQueryTimeout() 메서드를 사용하여 쿼리의 타임아웃을 지정할 수 있었다.

하지만 이 방법은 Connector/J 측에서 시간의 경과를 감지하고 서버로 접근해서 시간이 걸리는 쿼리에 kill 명령어를 실행하는 방식으로, Connector/J 이외에는 사용할 수 없다.

드리이버의 보조 없이 훨씬 쉽게 어떤 프로그래밍 언어로도 쿼리의 타임아웃을 설정하려면 어떻게 하는 것이 좋을까. 그렇게 하기 위해서는 서버 측에서 쿼리의 타임아웃을 지정할 수 있어야 한다. 그래서 Mysql 5.7에는 서버측에서 쿼리를 타임아웃 시키는 구조가 추가되었다.

 

타임아웃의 지정은 max_execution_time 이라는 시스템 변수로 한다. 마일스톤 릴리스나 릴리스 후보판에는 select 구문에서 직접 타임아웃을 지정할 수 있는 장치가 있었지만, 정식판에서는 그기능이 삭제되었다. 타임아웃지정은 max_execution_time 시스템 변수로 한다.

--> 슬로우쿼리로 고생했기 때문에 분명 좋은 기능이긴한데 global 변수라 SET 으로 실시간으로 바꿀 수 있는 것도 아니고 바꾸고 재시작해야하고..

OLAP의 경우 개발자가 실행계획 안보고 코딩하면 어느순간 슬로쿼리가 되어있을 테고..

근데 그게 결제쪽이면 강제로 Kill 시키는 기능이 들어가면 문제가 될 거 같은데...

근데 이걸 session 단위로 힌트로 쓸수 있다는 얘기가 있어서 한번 확인해 보았다.

 

참조 referance:
https://ohgyun.com/767

MySQL: max_execution_time 설정하기

 

 

mysql 레퍼런스를 확인해본다.

dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

MySQL :: MySQL 5.7 Reference Manual :: 8.9.3 Optimizer Hints

 

 

 

#Statement Execution Time Optimizer Hints

MAX_EXECUTION_TIME hint는 단지 select문에서만 허용한다.
서버가 명령문을 종료하기 전에 명렴문을 실행할 수 있는 시간을 N(밀리세컨단위의 시간값)을 지정한다.

MAX_EXECUTION_TIME(N)hint는 실행구문의 timeout을 N 밀리세컨으로 설정한다.
이 옵션이 유효하거나(my.cnf에 설정하였거나) N이 0이면 이 max_execution_time은 시스템 변수의 값이 적용된다.

max_execution_time 힌트를 다음과 같이 적용할 수 있다.

 

For statements with multiple SELECT keywords, such as unions or statements with subqueries,
MAX_EXECUTION_TIME applies to the entire statement and must appear after the first SELECT
Union 또는 subquery가 포함된 문과 같이 복수의 SELECT 키워드가 있는 문에는 MAX_EXECUTION_TIME은 전체 문장에 적용되며 첫 번째 select 후에 나타나야 한다.

--> 첫번째 실행이후에 두번째 실행하게되면 적용된다는 얘기인가?

테스트해보았는데 첫번째 select에서도 바로 실행된다. 첫번째 select 이라는 게 전체 row를 말하는 것이 아니고 1 row를 의미하는지 모르겠다.

 

It applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.
읽기 전용 SELECT 문에도 적용된다. 읽기 전용이 아닌 문장은 side effect로 데이터를 수정하는 stored function을 호출하는 구문이다.

 

It does not apply to SELECT statements in stored programs and is ignored
stored 프로그램이 포함된 select문은 적용되지 않고 무시한다.

--> function, procedure 가 사용된 select문에는 적용되지 않는다.

 

#테스트

2000만건 정도 있는 테이블을 스캔한다. 평상시 풀스캔이면 한도 없이 조회하고 안나온다. 보통 limit 200으로 끊어서 데이터를 보는데 힌트 믿고 한번 날려보자.

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM TESTDB.TB_xxxx_HIST;

error code)
Error occurred during SQL query execution
이유:
 SQL Error [3024] [HY000]: Query execution was interrupted, maximum statement execution time exceeded

--> 와우... 미라클.. ㅎㅎ 슬로우쿼리 파리미터와 같이 적용하면 query 장애는 안녕인가?

 

stored 프로그램이 포함된 select문은 적용되지 않고 무시한다.

--> 이 부분이 맘에 걸려서 함수를 추가해서 테스트해보았다.

--서브쿼리추가하고, 함수추가 후 테스트 해보았다.
select /*+ MAX_EXECUTION_TIME(2000) */  
T1.SEQ_NO ,
T2.CHNL_CD,
TESTDB.CODE_NM('xxxx_CD',T1.xxxx_CD) AS CHANNEL_NM,
(select CHANNEL_CD from TESTDB.TB_xxx_ETC where SEQ_NO = T1.SEQ_NO ) AS CARD
from TESTDB.TB_xxxxx T1
INNER JOIN TESTDB.TB_xxxx_HIST T2
ON T1.SEQ_NO = T2.SEQ_NO;

error code)
Error occurred during SQL query execution
이유:
 SQL Error [3024] [HY000]: Query execution was interrupted, maximum statement execution time exceeded

--> 별문제 없이 잘된다. select문에서는 사용해도 될 거 같다.  함수나 프로시저 단독으로 처리시에 확인이 안되는 거 같은데 프로시저 하나 짜고 다음에 다시 테스트해봐야겠다. 일단 select 문안에 컬럼으로 함수를 쓰는 경우는 사용가능하다.

참고로 테스트 버전은 mysql 5.7.19이다.