[MySQL] Connection 튜닝
■ Connection 관련 파라미터와 MySQL 상태 변수
컨넥션에 관련된 파라미터와 상태 확인 변수들입니다. 상태 변수 확인 후 관련 파리미터를 설정하여 성능을 개선시킬 수 있습니다.
파라미터미터와 상태 변수의 상호 관계와 어떤 부분을 모니터링하고 확인해야 하는지 알아봅니다.
1. Variables
(예 : show variables like '%max_connection%';)
• max_connections
허용되는 최대 동시 클라이언트 연결 수입니다.
접속 최대수를 초과해 접속 시도를 하는 경우 "too many connections error"이 발생 합니다.
max_connection 수가 늘어 나면 innodb 의 thread별 메모리 사용량이 늘어나 같이 고려해야 합니다.
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.01 sec)
• wait_timeout
지정된 시간(초) 동안 아무런 요청 없이 대기하는 경우 MySQL 서버는 해당 커넥션을 강제로 종료해 버립니다.
기본값을 두는것보단 시스템 환겨엥 맞게 설정하는것을 추천드립니다. (기본값은 28800초(8시간))
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)
• thread_cache_size
클라이언트와 서버와의 연결 그 자체를 의미하며 스레드는 해당 커넥션으로부터 오는 작업 요청을 처리하는 주체입니다.
최초 클라이언트로부터 접속 요청이 오면 MySQL 서버는 스레드를 준비해 그 커넥션에 작업 요청을 처리해 줄 스레드를 매핑하는 형태입니다.
클라이언트가 종료되면 MySQL은 스레드를 스레드풀에 보관합니다. 재사용을 위해 설정 변수는 최대 몇 개까지의 스레드를 스레드 풀에 보관할지 결정한다.
밑에 상태변수인 Threads_created가 많이 늘어난다면 이 파라미터를 늘려야 합니다.
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 9 |
+-------------------+-------+
1 row in set (0.01 sec)
2. Status
(예 : show status like '%clients%';)
• Aborted_clients
클라이언트가 연결을 올바르게 닫지 않고 접속이 끊겨서 중단된 연결 수입니다.
mysql_close()를 호출하지 않고 종료시키거나 wait_timeout파라미터등에 의해 강저적으로 종료된 경우 발생됩니다.
어플리케이션도 병행해서 확인해봐야 합니다.
mysql> show global status like 'Aborted_clients';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Aborted_clients | 0 |
+-----------------+-------+
1 row in set (0.00 sec)
• Aborted_connects : MySQL 서버에 접속이 실패된 수
MySQL 서버 연결에 실패한 횟수입니다. 클라이언트가 데이터베이스에 접근할 권한이 없는 경우나 접속시 사용자 비밀번호를 틀렸을 경우 발생합니다.
mysql> show global status like 'Aborted_connects';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_connects | 0 |
+------------------+-------+
1 row in set (0.00 sec)
• Max_used_connections
서버가 시작된 이후 동시에 사용중인 최대 연결 수입니다.
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 128 |
+----------------------+-------+
1 row in set (0.01 sec)
• Threads_cached
Thread Cache의 Thread 수입니다.
mysql> show global status like 'Threads_cached';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| Threads_cached | 8 |
+----------------+-------+
1 row in set (0.00 sec)
• Threads_connected
현재 열려있는 연결 수입니다.
mysql> show global status like 'Threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 1 |
+-------------------+-------+
1 row in set (0.00 sec)
• Threads_created
접속을 위해 생성된 Thread 수 입니다. 이 값이 비정상적으로 올라갈 시 thread_cache_size를 늘려줍니다.
mysql> show global status like 'Threads_created';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 128 |
+-----------------+-------+
1 row in set (0.00 sec)
• Threads_running
Sleeping 되어 있지 않은 Thread 수 입니다. 활성화 된 Thread 수입니다.
mysql> show global status like 'Threads_running';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 10 |
+-----------------+-------+
1 row in set (0.00 sec)
■ Connection 튜닝
컨넥션에 관련된 점검 사항들입니다. 위에서 확인한 상태값을 이용해서 계산합니다.
• Aborted connection usage(%) = (aborted_clients / connections) * 100
강제로 연결이 취소된 비율인데, 특정 세션에서 일정 시간이상동안 요청이 없다면 MySQL서버는 강제로 연결을 해제 합니다.
또한 mysql이 비정상적으로 종료가 되었거나 접속시도시 권한이 없어서 강제로 종료될때 증가합니다.
이 비율이 높다면 wait_timeout 값의 조정 및 어플리케이션 점검이 필요할 수 있습니다.
• Cache Miss Rate(%) = Threads_created / Connections * 100
Cache Miss Rate(%) 가 높다면 thread_cache_size를 높일 필요가 있습니다.
일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.
• Connection Miss Rate(%) = Aborted_connects / Connections * 100
DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다.
그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.
• Connection Usage(%) = Threads_connected / max_connections * 100
Connection Usage(%)가 100% 라면 max_connections 파라미터 수를 늘려야 합니다. Connection 수가 부족할 경우 Too Many Connection Error가 발생합니다.
• skip-name-resolve
MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다.
skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.
Reference
https://jhyutno.tistory.com/entry/MySQL-Aborted-Connections