[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

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※

 

 

Designed by JB FACTORY