Mysql은 기본적으로 SQL Server와 다르게 Connection Pooling을 자체적으로 제공하지 않는다.
이는 연결 Driver가 SQL Server는 전용 Driver를 사용하기 때문에 그렇지만
Mysql의 첫 시작은 Open Source 진영이었기 때문에 다양한 Driver가 존재가 가능하기 때문이다.
따라서 각 Driver에서 Connection Pooling을 지원하는데
JDBC를 예로 보면 다음과 같은 구조로 처리 된다.
위와 같이 Client 쪽에서 Connection Pool을 관리하는데 따라서 Mysql은 Connection을 새로 생성하거나 제거하는 문제를 어느 정도 해결이 가능합니다.
하지만 CIient의 작업 적인 측면에서 Connection 생성하고 해제하지 않는다거나 여러 Client가 동시 생성되어 과도한 Connection이 발생되게 되었을 때 Mysql에서 제어할 방법이 필요하다.
이 때 사용되는 대표적인 방법이 max_connection 과 wait_timeout, thread_cache_size를 설정하는 방법이다.
Mysql Connection 이 설정된 max_connection 이 초과하게 되면 다음과 같은 오류가 발생된다.
"Too many connections"
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
Too many connections
따라서 Connection 값이 Overflow 되지 않도록 최대 연결 값을 조절하는 것이다.
wait_timeout은 왜 설정해야 하는 것인가?
그것은 한번 연결된 Connection 객체가 delete 되지 않고 Sleep 상태로 지속적으로 존재하게 되었을 때 해당 Process 나 thread가 실행될 때 마다 Connection 수가 증가할 뿐 감소되지 않고 max connection을 넘어버릴 것이기 때문에 사용되지 않는 Connection을 정리해 주는 것이다.
thread_cache_size는 왜 조절해야 하는 것인가?
해당 값을 Connection당 생성된 Thread에 메모리를 할당하게 캐시할 메모리를 미리 생성하여 저장하고 관리하게 하는 변수이다.
해당 값은 용량에 대한 값이 아닌 Connection이 발생될 때 생성된 Thread를 cache에 올려놓을 Thread의 수를 나타내는 값이다.
이 때 Connection이 사용한 Thread가 thread_cache_size보다 작으면 cache 하는 방식이다.
따라서 Connection 수에 따라 메모리 영향을 미치게 되는 것을 설정할 수 있기 때문에 해당 값을 조절하고 고려하여 max_connection 변수를 설정할 수 있을 것이다.
따라서 설정은 어떻게 해야 하는가?
max_connection의 기본값은 151 이다.
show global variables like 'max_connection%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
wait_timeout의 기본값은 28800초(8시간) 이다.
show global variables like 'wait_timeout%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
thread_cache_size의 기본값은 8 + (max_connections / 100) 이다.
show variables like 'thread_cache%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 9 |
+-------------------+-------+
만약 Too many connections 이 발생되었다면 서비스의 상태를 점검해 보아야 몇 가지 상태 정보를 확인하여 가능하다.
show status like 'Connections%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 93 |
+---------------+-------+
show status like '%used_connection%';
+---------------------------+---------------------+
| Variable_name | Value |
+---------------------------+---------------------+
| Max_used_connections | 152 |
| Max_used_connections_time | 2021-10-26 08:35:49 |
+---------------------------+---------------------+
show status like 'Aborted%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_clients | 7 |
| Aborted_connects | 10 |
+------------------+-------+
show status like 'threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 3 |
| Threads_connected | 2 |
| Threads_created | 5 |
| Threads_running | 2 |
+-------------------+-------+
l Connections : Mysql 서버에서 연결 시도한 횟수
l Max_used_connections : 최대 시도 되었던 연결 값
l Max_used_connections_time : 최대 연결 시도 시점
위 값을 최대 연결에 대한 정보를 정리하며 2021-10-26 08:35:49에 152 연결이 되면서 Too many connections 가 발생되었을 것이다.
l Aborted_clients : Client가 비정상적으로 종료된 횟수
l Aborted_connects 연결 실패 횟수
위 값은 Connection의 유실 관련 정보이다.
l Threads_cached : Thread Cache의 Thread 수
l Threads_connected : 현재 연결된 Thread 수
l Threads_created : 접속을 위해 생성된 Thread 수
l Threads_running : Sleeping 되어 있지 않은 Thread 수
Thread 정보로 조절해야 할 Connection 값을 알 수 있다.
그 외에도 더 많은 값들을 고려해서 튜닝되어야 하는데 해당 값들은 다음과 같다.
시스템 전체 상황(실제 어느 정도로 바쁜지에 대한 상대적 수치),
l 초당 connections 수
l 커넥션당 평균 쿼리 요청수
l 커넥션당 생성된 평균 쓰레드 수
l 초당 평균 전송량
l DISK에 생성된 임시 테이블 생성 비율
l Slow_queries
l 한계 도달 N 초 계산
l Connection life time
해당 값들을 기준으로 산출해 볼 수 있는 값은 다음과 같다.
1. Cache Miss Rate(%) = Threads_created / Connections * 100
A. Connection 이 생성될 때 Thread를 얼마나 재사용하지 여부
B. cache에 있는 Thead를 재사용 시 Threads_created 이 증가하지 않는다.
C. 따라서 해당 값이 높을 시에 thread_cache_size를 높여야 한다.
2. Connection Miss Rate(%) = Aborted_connects / Connections * 100
A. Aborted_connects가 높은 수가 나타내는 것이 무조건 max_connections 영향은 아님
B. Connection Miss Rate 높을 시 Max_used_connections을 확인해서 판단
C. 그 외에 문제가 발생된다면 Network, Memory 문제를 살펴보아야 한다.
3. Connection Usage(%) = Threads_connected / max_connections * 100
A. Connection이 생성될 때 생성된 Threads_connected 값이 max_connecions와 같다면 Too Many Connection Error가 발생될 확률이 높음
B. 해당 값을 확인해서 max_connections와 wait_timeout을 조절해야 한다.
C. wait_timeout이 높을 시 낮추어 주고 max_connections를 높여준다.
게임이나 이벤트와 같은 단 기간에 많은 연결 수가 발생될 때는?
단 기간에 Connection이 빠르게 발생되어 Thread가 빠르게 생성되고 사라진다면 back_log 변수 설정에 변화를 주어야 한다.
새 Thread가 발생될 때 약간의 시간의 딜레이가 발생되는데 이 때 back_log가 새 요청에 대한 응답을 일시적으로 쌓아두고 해당 값 이상의 요청이 올 경우 Connection을 일시적으로 중지시킨다.
해당 값은 OS별로 최대값이 다르기 때문에 OS확인해야 하며 OS 제한보다 높을 수 없다.
show variables like 'back_log%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log | 151 |
+---------------+-------+
#Linux의 경우 다음과 같이 확인
sysctl net.core.netdev_max_backlog
net.core.netdev_max_backlog = 1000
아래 그림은 벤치마크 자료로 Connection 수와 요청의 성능 관계를 나타낸다.
위와 같이 Connection이 무작정 많다고 해서 더 많은 쿼리가 처리되는 것이 아니기 때문에 서버 상태와 서비스 상태에 맞는 적정한 조절이 필요해 보인다.
참조
https://codevang.tistory.com/206
https://atl.kr/dokuwiki/doku.php/mysql_max_connection_%EC%84%A4%EC%A0%95
https://m.blog.naver.com/islove8587/221977641268
https://dung-beetle.tistory.com/70
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=jevida&logNo=221121008302
http://www.opencode.co.kr/bbs/board.php?bo_table=mysql_tips&wr_id=59
'Database > Mysql & Mariadb' 카테고리의 다른 글
| MySQL Online DDL(8) [Table Operations] (0) | 2021.11.09 |
|---|---|
| innodb 행 저장 방식(2) 행 저장 방식 (0) | 2021.11.08 |
| innodb 행 저장 방식(1) 저장 파일 형식 (0) | 2021.11.08 |
| MySQL Online DDL(7) [Foreign key Operations] (0) | 2021.11.01 |
| MySQL Online DDL(6) [Generated Column Operations] (0) | 2021.11.01 |
최근댓글