오늘은 MySQL Database Parameter 중 Connection 관련 파라미터에 대해 글을 써보겠습니다.
MySQL 은 서버에 동시에 접근하는 최대 클라이언트 수를 제어할 수 있다. 서비스를 하다 보면 느려지는 경우가 있는데 그중에 하나로 서버 스펙에 비해 과도하게 클라이언트가 붙게 되어 발생하는 경우도 있다. 이를 방지하기 위해서는 서버의 스펙에 맞게 동시에 접근하는 클라이언트 수를 제한할 필요가 있다.
1. max_connections 파라미터는 최대 동시 클라이언트 연결 수를 제한하는 파리미터 이다.
- dynamic parameter
- Default Value : 151
- Min : 1
- Max Value : 100000
2. wait_timeout 파라미터는 지정된 시간 동안 아무런 요청 없이 대기하는 경우 해당 시간이 지난 Connection은 강제 종료한다.
- dynamic parameter
- Default Value : 28800 sec ( 8 hour )
- Min : 1
- Max Value : 2147483(Window) / 31536000(Other)
3. interactive_timeout : 활동 중인 커넥션이 닫히기 전까지 서버가 대기하는 시간
- dynamic parameter
- Default Value : 28800
- Min : 1
** 1. 현재 max_connections 값 확인 **
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
** 2. max_connections 값 변경 **
mysql> set global max_connections=200;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 200 |
+-----------------+-------+
** /etc/my.cnf 설정 **
재부팅시에도 적용시키기 위해서는 /etc/my.cnf 환결설정 파일에도 추가해주어야한다.
[root:LEE:/root > cat /etc/my5.7.cnf | grep max
max_connections=200
상태 변수를 통해 Connection에 관련된 여러 정보들을 수집할 수 있다.
| Global Status Name |
Description |
Category |
| Threads_connected |
현재 열려있는 커넥션 |
Server status variable |
| Threads_running |
현재 실행 중인 커넥션 |
Server status variable |
| Connection_errors_internal |
서버오류에 의해 거절된 커넥션 |
Server status variable |
| Aborted_connects |
서버에 연결시도가 실패한 수 |
Server status variable |
| Connection_errors_max_connections |
max_connections의 제한 때문에 거절된 커넥션의 수 |
Server status variable |
| Max_used_connections |
startup이후 최대 사용 커넥션 수 |
Server status variable |
mysql> select * from information_schema.global_status where variable_name in ('Threads_connected','Threads_running','Connection_errors_internal','Aborted_connects','Connection_errors_max_connections','Max_used_connections');
+-----------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------------------+----------------+
| ABORTED_CONNECTS | 0 |
| CONNECTION_ERRORS_INTERNAL | 0 |
| CONNECTION_ERRORS_MAX_CONNECTIONS | 0 |
| MAX_USED_CONNECTIONS | 1 |
| THREADS_CONNECTED | 1 |
| THREADS_RUNNING | 1 |
+-----------------------------------+----------------+
6 rows in set, 1 warning (0.00 sec)
**실제 운영 서버에서 실행시**
mysql> select * from information_schema.global_status where variable_name in ('Threads_connected','Threads_running','Connection_errors_internal','Aborted_connects','Connection_errors_max_connections','Max_used_connections');
+-----------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------------------+----------------+
| ABORTED_CONNECTS | 70 |
| CONNECTION_ERRORS_INTERNAL | 0 |
| CONNECTION_ERRORS_MAX_CONNECTIONS | 0 |
| MAX_USED_CONNECTIONS | 516 |
| THREADS_CONNECTED | 494 |
| THREADS_RUNNING | 4 |
+-----------------------------------+----------------+
아래 Query를 통해서 max_connections 파라미터에 비해 현재 Connection / 가장 많은 Connection이 있었을 때의 Conneciton 사용률을 계산할 수 있다.
mysql> select
-> round(P0.variable_value/P2.variable_value*100,2) as "Current Usage(%)",
-> round(P1.variable_value/P2.variable_value*100,2) as "Max Used Usage(%)"
-> from
-> information_schema.global_status P0,
-> information_schema.global_status P1,
-> information_schema.global_variables P2
-> where P0.variable_name='THREADS_CONNECTED'
-> and P1.variable_name='MAX_USED_CONNECTIONS'
-> and P2.variable_name='MAX_CONNECTIONS'
-> ;
+------------------+-------------------+
| Current Usage(%) | Max Used Usage(%) |
+------------------+-------------------+
| 4.96 | 5.16 |
+------------------+-------------------+
1 row in set (0.00 sec)
- Connection 수를 변경할 때는 서버 스펙에 맞게 설정하는 것이 제일 중요하다. MySQL 서버에 Client 가 붙게 되면 해당 Client가 Sleep 상태일 때에도 Memory를 사용하기 때문에 한 번에 올리기보다는 점차적으로 올리는 것이 좋다고 생각된다.
