[이슈]
서버 재기동 후 max_user_connection 에러로 인해 to-be 개발서버 어드민 접속 불가능한 상황이 발생했다.
이를 해결하기 위한 과정, 그리고 추가적으로 공부하며 알게 된 내용을 정리하려 한다.
[현황]
legacy DB의 max_user_connection 값이 200으로 설정되어있는데, process 리스트에 200개 이상이 들어오고 있었다.
임시적으로 Sleep 상태인 것들을 kill 했지만 as-is 운영서버에서 커넥션이 들어오고 있는 상황이다.
as-is 운영서버 / to-be 개발서버 / to-be 운영서버에서 모두 같은 DB를 바라보고 있는 그런 끔찍한 상황 🥲
일단은 아래 내용을 공부하면서 찾은 방법인 timeout 설정을 바꾸는 것으로 내일 시도해보려고 한다...
MySQL 실행 프로세스 리스트 확인하기
일단 MySQL 에서 실행중인 프로세스 리스트를 확인해보자.
-- 서버 내에서 실행중인 스레드 집합에서 현재 수행중인 작업을 보여준다.
SHOW PROCESSLIST; -- Info 필드의 100자 까지만 보여준다.
SHOW FULL PROCESSLIST; -- Info 필드의 전체를 보여준다.
-- information_schema 에서도 확인할 수 있다.
SELECT *
FROM information_schema.processlist
ORDER BY id;스키마를 확인해보면 아래와 같다. (아래는 개인공부용 DB)
| Id | User | Host | db | Command | Time | State | Info | Progress |
|---|---|---|---|---|---|---|---|---|
| 1 | system user | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0 | |
| 2 | system user | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0 | |
| 3 | system user | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0 | |
| 4 | system user | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0 | |
| 5 | system user | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0 | |
| 4562 | rdsadmin | 호스트 | mysql | Sleep | 11 | NULL | 0 | |
| 6167 | admin | 호스트 | yrkim_springboot_webservice | Query | 0 | Init | SHOW PROCESSLIST | 0 |
각 필드의 의미는 다음과 같다.
Id : 프로세스 아이디로 MySQL이 관리하는 스레드 번호.
SELECT CONNECTION_ID()와 같다.
User : 스레드에 접속하고 있는 MySQL 유저명
Command : 스레드의 현재 커맨드 상태
Sleep: 클라이언트가 새로운 명령을 보내길 기다리고 있음.Query: 클라이언트의 쿼리 실행.Kill: 이 스레드는 다른 스레드를 kill 하고 있음.
Time : 스레드가 현재 커맨드 상태를 유지한 시간을 seconds 단위로 나타낸 값.
Info : 현재 실행되고 있는 SQL
이때 문제가 되는 게 sleep 세션인데, sleep 세션은 MySQL 서버와 연결 후 다음 쿼리 수행까지 대기중인 상태의 세션을 의미한다.
sleep 세션이 너무 많고 정리가 안되는 경우 connection full로 인해 신규 세션 접속이 불가능해지고,
session별 할당되는 메모리로 인해 메모리 부족 현상이 발생할 수 있다.
timeout 설정
DB의 timeout 설정으로 sleep 세션들을 정리할 수 있다.
interactive_timeout : 활동중인 커넥션이 닫히기 전까지 서버가 대기하는 시간 (mysql command line)
interactive 모드는 'mysql>' 과 같은 프롬프트 있는 콘솔이나 터미널 모드를 말한다.
mysqld 와 mysql client 가 연결을 맺은 다음, 다음 쿼리까지 기다리는 최대 시간을 의미한다.
설정된 interactive_timeout 까지도 아무런 요청(쿼리)이 없으면 연결은 취소되고, 그 이후에 다시 요청이 들어오면 연결은 자동으로 맺어진다.
interactive_timeout 안에 다시 요청이 들어오면 wait time은 0으로 초기화된다. (CLIENT_INTERACTIVE)
ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id: 12002 Current database: xxx이와 같은 연결 취소는 Aborted_clients 에 누계되고, wait_timeout 의 결과도 함께 포함된다.
기본 값은 28800(8시간)초로 설정되어 있는데 상당히 관대한 설정이라고 한다. 권장값은 3600(1시간) 정도로 한다.
-- sleep 상태의 세션을 확인
select * from information_schema.processlist where command='Sleep' order by time;-- interavtive timeout 설정을 확인, value를 보면 해당 초 이후 interactive 세션을 정리하도록 되어있다.
show variables like 'interactive%';| Variable_name | Value |
|---|---|
| interactive_timeout | 28800 |
wait_timeout : 활동하지 않는 커넥션을 끊을 때까지 서버가 대기하는 시간 (php,jdbc 등을 통한 connection)
이 설정이 제일 중요한 파라미터 항목이다.
interactive_timeout 과 마찬가지로 wait_timeout 까지 아무런 요청(쿼리)이 없으면 연결은 취소되고 그 결과는 Aborted_clients 에 누계된다.
-- wait timeout 설정을 확인, value를 보면 해당 초 이후 non-interactive 세션들을 정리하게 설정되어 있다.
show variables like 'wait_timeout';| Variable_name | Value |
|---|---|
| wait_timeout | 28800 |
-- 설정값 변경
set global interactive_timeout=30;
set global wait_timeout=30;
show global variables like 'interactive%';
show global variables like 'wait_timeout';my.cnf 에서 설정 변경을 해주면 된다.
단, 설정 변경 전 존재하는 sleep 세션들에 대해서는 적용이 되지 않고, 신규 세션들에만 적용이 된다.
참고링크
-
Database show processlist
https://johngrib.github.io/wiki/db-processlist/ -
sleep session 정리하기
https://sarc.io/index.php/mariadb/1154-sleep-session -
[MYSQL] 시스템 관련 튜닝]
https://m.blog.naver.com/islove8587/221977641268
