0. 들어가며..
프로젝트를 진행하며 부스 운영을 통해 각 팀의 진행상황을 공유하고있습니다.
부스 운영 중 갑작스레 DB에서 Too many connections.. 에러가 발생했고 부스 운영을 더 이상 진행 할 수 없는 상황을 경험했습니다.
본 문서에는 위 문제를 해결하는 과정을 담았습니다.
1. 원인 파악
(1) Too many connections
MySQL 공식문서 - Too many connections
mysqld서버에 연결하려 시도할 때 client에Too many connections에러가 발생한다면, 다른 client가 사용 가능한 모든connection들을 사용중인 것입니다.
max_connections시스템 변수를 통해 허용 가능한connection의 수를 제어할 수 있습니다.
connection을 더 제공하기 위해서는max_connections를 더 큰 값으로 설정해야합니다.
mysqld는 실제로max_connections+ 1 개의 clientconnection들을 허용합니다. 추가적인connection은CONNECTION_ADMIN권한을 가진 계정에게 예약되어있습니다. 일반 사용자가 아닌 관리자에게 권한을 부여하여 권한이 없는 사용자가 최대로connection을 생성해도 관리자는 서버에 연결하고SHOW PROCESSLIST를 사용하여 문제를 진단할 수 있습니다.
(2) 에러 당시 상태
1) 허용 가능 연결 수 -> 151
mysql> show variables like '%max_connection%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set (0.00 sec)2) 동시에 사용된 최대 연결 수 -> 152
mysql> show status like '%Max_used_connections%';
+---------------------------+---------------------+
| Variable_name | Value |
+---------------------------+---------------------+
| Max_used_connections | 152 |
| Max_used_connections_time | 2024-02-16 10:46:08 |
+---------------------------+---------------------+
2 rows in set (0.00 sec)실제로는 152보다 더 많은 connection을 연결하려했지만 차단된 것으로 보인다.
(3) 원인
허용 가능한
connection수를 초과하고connection이wait_timeout의 값만큼 서버에 그대로 연결을 유지한 채로 남아있는 것이Too many connections에러의 원인 중 하나입니다.
2. Hot fix
mysql> set global max_connections=500;
mysql> set global wait_timeout=60;위 command로 급한 불은 끌 수 있지만 만능은 아닙니다. 상황에 따라 값을 늘리거나 줄이는 것이 더 좋을 수 있습니다.
(1) 고민해볼 해결책
1) Connection Pool
2) 쿼리 최적화
3) JPA 및 querydsl을 사용함에 있어 루프 찾기
3. 주요 변수
(1) mysql 접속
1) ec2 instance에 접속합니다.
ssh -i {pem key}.pem ubuntu@{ip address}2) 위 command로 mysql에 접속합니다.
mysql -u root -p(2) 변수 확인
1) variables
show variables like '%{variable}%';-
max_connections동시 클라이언트 연결의 최대 허용 수입니다.
-
wait_timeout서버가
noninteracttive connection을 닫기 전에 활동을 기다리는 시간(초)입니다.
API를 통한 client 프로그램 상에서 최대 연결시간입니다.- 비대화형 세션이란?
클라이언트가 연결을 맺고 쿼리를 실행한 후에 즉시 종료되는 경우를 말합니다. 이는 주로 배치 작업이나 백그라운드 프로세스와 관련된 연결에 적용됩니다.
-
interactive_timeout서버가
interactive connection을 닫기 전에 활동을 기다리는 시간(초)입니다.
mysqld와 client가 연결을 맺은 후 요청을 기다리는 최대시간입니다.
interactive 세션의 대기 시간을 정의합니다.- 대화형 세션이란?
대화형 세션은 클라이언트가 연결을 맺고 오랫동안 활성화된 상태로 유지되는 경우를 말합니다. 예를 들어, 사용자가 쿼리를 실행하고 결과를 기다리거나 연결을 유지한 채로 클라이언트를 닫지 않는 경우에 해당합니다.
-
thread_cache_size재사용을 위해 서버가 캐시해야 하는 스레드 수입니다. client의 연결이 끊어지면 해당 스레드가
thread_cache_size보다 적은 경우 클라이언트의 스레드가 캐시에 저장됩니다. 스레드에 대한 요청은 가능하면 캐시에서 가져온 스레드를 재사용하여 충족되며, 캐시가 비어있는 경우에만 새 스레드가 생성됩니다. 새로운connection이 많은 경우 성능 향상을 위해 이 변수를 늘릴 수 있습니다. 일반적으로는 성능 향상이 크지 않습니다. 그러나 서버가 초당 수백 개의 연결을 확인하는 경우 대부분의 새 연결이 캐시된 스레드를 사용할 수 있도록thread_cache_size를 충분히 높게 설정해야합니다.Connections와Thread_created의 차이점을 조사하면 스레드 캐시의 효율성을 확인할 수 있습니다.
2) status
show status like '%{status}%';Aborted_connectsMySQL server에 접속하려 시도했지만 실패한 수입니다.
Max_used_connectionsserver 시작 이후 동시에 사용된 최대 연결 수입니다.
Threads_connected현재 열려있는 연결 수입니다.
Threads_created연결을 처리하기 위해 생성된 스레드 수입니다.
Threads_created가 큰 경우thread_cache_size값을 늘릴 수 있습니다.Cache miss rate는Threads_created/Connections로 계산됩니다.
4. Tuning
(1) Cache Miss Rate
1) 계산
Threads_created / Connetions * 100
2) 개선
Cache Miss Rate가 높다면 thread_cache_size를 기본값(8)보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected를 Peak-time인 경우 보다 약간 낮은 수치로 설정하는 것이 좋습니다.
(2) Connection Miss Rate
1) 계산
Aborted_connects / Connetions * 100
2) 개선
DB 접속이 많은 경우 wait_timeout을 최대한 적게(10~20 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다. 그러나 Connection Miss Rate가 1% 이상이라면 wait_timeout을 조금 더 길게 설정하는 것이 좋습니다.
(3) Connection Usage
1) 계산
Threads_connected / max_connections * 100
2) 개선
100%라면 max_connections를 증가시킵니다. Connection 수가 부족하면 Too Many Connection error가 발생합니다.
5. 실제 확인
운영 중.. (2024.02.18)
(1) Cache Miss Rate
mysql> show status like '%Threads_created%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 195 |
+-----------------+-------+
1 row in set (0.00 sec)mysql> show status like '%Connections%';
+-----------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------+---------------------+
| Connection_errors_max_connections | 0 |
| Connections | 1233 |
| Max_used_connections | 55 |
| Max_used_connections_time | 2024-02-16 10:46:08 |
| Mysqlx_connections_accepted | 0 |
| Mysqlx_connections_closed | 0 |
| Mysqlx_connections_rejected | 0 |
+-----------------------------------+---------------------+
7 rows in set (0.00 sec)
- 계산 결과
195 / 1233 * 100=15.815 %
(2) Connection Miss Rate
mysql> show status like '%Aborted_connects%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_connects | 1 |
+------------------+-------+
1 row in set (0.01 sec)mysql> show status like '%Connections%';
+-----------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------+---------------------+
| Connection_errors_max_connections | 0 |
| Connections | 1233 |
| Max_used_connections | 55 |
| Max_used_connections_time | 2024-02-16 10:46:08 |
| Mysqlx_connections_accepted | 0 |
| Mysqlx_connections_closed | 0 |
| Mysqlx_connections_rejected | 0 |
+-----------------------------------+---------------------+
7 rows in set (0.00 sec)
- 계산 결과
1 / 1233 * 100=0.081 %
(3) Connection Usage
mysql> show status like '%Threads_connected%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 22 |
+-------------------+-------+
1 row in set (0.00 sec)mysql> show variables like '%max_connection%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set (0.00 sec)
- 계산 결과
22 / 151 * 100=14.570 %
운영 중.. - 2024.02.20
(1) Cache Miss Rate : 15.815 % -> 14.963 %
(2) Connection Miss Rate : 0.081 % -> 0.393 %
(3) Connection Usage : 14.570 % -> 20.530 %
