[테스트] stored function cache 설정

1. 목적

상용서비스에 슬로우 쿼리 이슈가 있어 확인해보니 select ~ from 안에 function을 사용했는데

이게 문제가 되어 응답시간이 느려지고 서비스 지연이 발생하였다.

근데 이게 원래는 슬로우 쿼리가 아니었는데 왜 갑자기 튀어나왔는지 보다보니

지난주에 대용량 테이블에 인덱스 추가 3개를 하고 나서 사이드 이펙트로 튀어나왔다. ㅠ

인덱스 추가하고 나서 아무래도 fucntion의 로컬 메모리(connection 당) 사용이 어느부분에서 MAX를 넘어 디스크를 사용하게 되었고 이게 Disk I/O를 치는 이유로 보인다.

--> mysql은 오라클과 달리 function이나 procedure가 global memory 영역을 사용하지 못하고 connection당 local 메모리가 my.cnf에 정의되어 있고 그 이상 사용할 경우 Disk로 넘어가 스토리지 엔진의 핸들러가 바뻐지게 되고 이게 Disk I/O를 발생시킨다.

 

슬로우쿼리 설정값은 2초이고 인덱스 3개 추가 후 현재는 14초 이상이 소요되고 있다.

대용량 참조하는 fucntion의 로직을 개선하려고 보니 쓰는 곳이 너무 많아서 수정하게 되면 테스트할 곳이 많다고

개발, 기획파트에서 얘기가 많다.ㅠ

고민중 fucntion에 캐시를 쓸 수 있는 DETERMINISTIC 옵션이 있는 것을 확인하고 테스트를 진행하였다.

mysql function에 DETERMINISTIC, NOT DETERMINISTIC 이라는 설정이 있다.

 

#장비사항

DB version : mysql 5.7.19

MEM : 16G ( innodb buffer pool :16G =테스트 용도로 Max로 잡음)

 

2.옵션 정의

characteristic
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
SQL SECURITY { DEFINER | INVOKER }
COMMENT 'string'

 

1) DETERMINISTIC
- 입력값이 같은면 결과는 늘 같다.

- 쿼리가 실행되면 그때 한번 호출하고 그 쿼리 안에서 상수값을 돌려줌
다시 쿼리가 호출되면 다시 실행됨

2) NOT DETERMINISTIC
- 입력값이 같아도 시간에 따라 결과는 다르다.

- 디펄트가 늘 변함 임으로 속도가 느려질수 있다 꼭 구분해서 사용하자

--> 따로 설정하지 않으면 NOT DETERDETERMINISTIC 이다

 

3. 추가 설명

DETERMINISTIC and NOT DETERMINISTIC 특성은 함수가 주어진 입력 값에 대해
항상 동일한 결과를 만드는지 아닌지를 나타낸다. 어떤 특성도 주어지지 않으면,
디폴트는 NOT DETERMINISTIC이며, 따라서 함수를 확정적인 것으로 선언하기 위해서는
DETERMINISTIC를 확실하게 지정해 주어야한다.

 

NOW() 함수(또는 동일 기능함수) 또는 RAND()의 사용은 함수를 반드시 non-deterministic하게
만들어 주는 것은 아니다. NOW()의 경우, 바이너리 로그는 타임스탬프와 복사본은
올바르게 포함한다. 또한 RAND()도 함수내에서 일단 한번 호출되어 지면 정확하게 복사본을 만들게 된다.
(함수 실행 타임스탬프 및 무작위 수는 마스터 서버 및 슬레이브 상에 있는
동일한 암시적 입력(implicit input)으로 간주할 수 있다.)

--> function 안에 now() 있는데 넣다 뺐다 둘다 테스트 해봐야할 거 같다.

 

contains sql, no sql, reads sql data, modifies sql data
특성은 함수가 데이터를 읽거나 또는 스는 정보를 제공한다. NO SQL 또는 Read sql data는 함수가 데이터를
변경하지 않는다는 것을 나타내는 것이다. 하지만 어떠한 특성도 주어지지 않으면 디폴트가 CONTAIN SQL이
되기 때문에 반드시 이러한 것 중에 하나를 명확히 지정해 주어야한다

--> 이렇게 얘기하긴 하는데 찾아보니 별로 중요하지 않다는 얘기가 더 많다.

 

1) CONTAINS SQL : 데이터를 읽거나 쓰는 명령문을 갖지 않음.
2) NO SQL : 아무런 SQL명령문이 없음.
3) READS SQL DATA : 데이터를 읽는 명령문.
4) MODIFIES SQL DATA : 데이터를 쓰는 있는 명령문
5) CONTAINS SQL : DEFAULT

6) SQL SECURITY : 사용자가 생성/수정 권한인지, 호출 권한인지 명시 

 

4. 테스트

4-1) 쿼리 캐시 OFF 일떄

DEFAULT 설정

-> 수행시간 13초

DETERMINISTIC  설정
-> 수행시간 13초 (똑같네;;)

 

4-1) 쿼리 캐시 ON 일떄

mysql> set global query_cache_type=1;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

#my.cnf 설정하고 재시작한다.
mysql> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.00 sec)

#쿼리캐시 설정 옵션설명

1) Query_Cache_Type = ON
– 예외사항을 제외한 모든 SQL결과를 메모리에 캐싱하겠다.
2) Query_Cache_Type = OFF
– Query Cache를 사용하지 않겠다.
3) Query_Cache_Type = DEMAND

 

DEFAULT 설정

-> 수행시간 14초

DETERMINISTIC 설정
-> 수행시간 14초 (더 느리다;;)

 

캐시를 쓰는지 확인해본다.

 

mysql> show global status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091584 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 18       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

--> Qcache_hits  0 캐시 안타네;; now() 때문에 그런가 now() 빼고 테스트해본다.

 

4-1) 쿼리 캐시 ON + function안에 now() 제거

DEFAULT 설정

-> 수행시간 14초

DETERMINISTIC 설정
-> 수행시간 14초 (똑같다;;)

mysql> show global status like 'qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091584 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 54       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

--> 역시나 Qcache_hits 0 캐시 안쓴다.

 

음 mysql 매뉴얼을 좀 더보니 쿼리캐시를 사용하지 못하는 경우라는 게 있다.;

 

 

[쿼리 캐시를 사용하지 못하는 경우]

1) 임시 테이블에 대한 쿼리
2) 사용자 변수의 사용(프리페어 스테이트먼트와 동일하게 작용)
3) 칼럼 기반의 권한 설정
4) LOCK IN SHARE MODE 힌트
5) FOR UPDATE 힌트
6) UDF(User Define Function)사용
7) 독립적인 SELECT문장이 아닌 일부분의 서브 쿼리
8) 스토어드 루틴(Procedure, Function, Trigger)에서 사용된 쿼리 --> 아....
9) SQL_NO_CACHE 힌트

 

5.결론

쿼리캐시 사용하고 function 에 DETERMINISTIC 을 설정하더라도
스토어드 루틴(Procedure, Function, Trigger)에서 사용된 쿼리는 캐시를 쓰지 않는다.
--> function 튜닝은 결국 한 세션에 로컬 메모리를 적게 사용(table cache를 줄임) 옵티마이져 cost를 개선하는 방법 말곤 없는 거 같다.

function 로직 개선을 위해 개발, 기획파트랑 다시 얘기좀 해봐야겠다.