mysql Prepared_stmt_count 줄이기

# mariadb(또는mysql) 에서 stmt 가 정상종료되지 않고 있다.
# https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Prepared_stmt_count
# Com_stmt_prepare, Com_stmt_close, Com_stmt_fetch 는
# 각각 PREPARE, EXECUTE, DEALLOCATE PREPARE 를 실행한 카운트이다.
# Prepared_stmt_count 는 현재 prepared statement 사용중인 개수이다.
show global status like '%stmt%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Binlog_stmt_cache_disk_use | 0     |
| Binlog_stmt_cache_use      | 0     |
| Com_stmt_execute           | 18116 |
| Com_stmt_close             | 12216 |
| Com_stmt_fetch             | 0     |
| Com_stmt_prepare           | 33749 |
| Com_stmt_reset             | 0     |
| Com_stmt_send_long_data    | 0     |
| Com_stmt_reprepare         | 0     |
| Prepared_stmt_count        | 9991  |
+----------------------------+-------+

# 테스트를 위해서 db 를 재시작해서 variable 를 초기화 한다.
sudo systemctl restart mariadb

# 로컬에서 root 로 접속(root 패스워드 설정은 mariadb 설치 포스팅 참고)
mysql -u root -pysoftman

# 다음과 같이 prepare stmt1, stmt2, ... stmtN 로 이름을 달리 계속 생성할 수 있다.
# 같은 stmt 이름을 사용하게 이전 stmt 를 닫고(Com_stmt_close 증가) 새로 만든다.
# Com_stmt_prepare, Prepared_stmt_count 증가
prepare stmt1 from 'select * from mysql.help_category where name = ?';

# stmt1 에 name 으로 전달된 파라미터 설정
set @help_name = 'functions';

# prepare stmt1 를 실행한다.
# Com_stmt_execute 증가
execute stmt1 using @help_name;

# prepare stmt1 를 종료한다.
# Com_stmt_close 증가, Prepared_stmt_count 감소
deallocate prepare stmt1;

# Prepared_stmt_count 줄이기
# flush 를 사용해봤지만 영향이 없다.
# flush 열려 있는 테이블들을 닫는다.(캐시된 쿼리를 삭제하는데 사용할 수는 있다.)
# https://dev.mysql.com/doc/refman/5.5/en/flush.html#flush-tables
flush tables;

# 현재 db 커넥션을 종료하거나 사용중인 프로세스를 종료시켜도
# 해당 프로세스와 연결된 Prepared_stmt_count 줄어든다.
# 다음 쿼리로 KILL id; 들로 실행할 쿼리를 만든다.
select concat('KILL ',id,';') from information_schema.processlist where db='ysoftman';

# 해당 프로세스 종료
KILL 1;
KILL 2;
KILL 3;

# 참고
# max_prepared_stmt_count 설정 값은 파악해본다.
show variables like '%stmt%';

# max_prepared_stmt_count 를 2로 줄여준다.
set global max_prepared_stmt_count=2;

# Prepared_stmt_count 는 max_prepared_stmt_count 변수로 설정된 개수를
# 넘어가면 더이상 stmt 를 생성할수 없다는 에러가 발생한다.
prepare stmt3 from 'select * from mysql.help_category where name = ?';
ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 2)