MySQL의 max_connections과 thread_cache에 대해

앞의 MySQL Connection Manager와 Thread Manager의 소스분석에서 살펴보았듯이 연결 설정자, 스레드 설정자에 대해서 알면 도움이 될 거 같아서 몇자 정리해 봅니다.

BMT 도구 마련

BMT의 도구로는 super-smack을 사용해 보기로 한다. 주요한 특징은 아래와 같다.

• MySQL과 PostgreSQL에서 동작함.
• C++로 만들어져 추가 라이브러리(드라이버 등)가 필요없고 수정이나 확장이 용이함
• 시나리오 파일(smack파일)로 다양한 쿼리를 구사하게 함.
• 복수의 클라이언트를 가상 fork()로 생성하고 각 클라이언트(자식 프로세스)가 쿼리를 실행함.
• 데이터(영문숫자)를 생성하는 툴도 존재함
• 쿼리는 mysql_query()와 PQexec()를 실행. stored procedure는 사용하지 않음.

super-smack의 설치과정은 아래와 같다.

> wget http://vegan.net/tony/supersmack/super-smack-1.3.tar.gz
> tar xvfz super-smack-1.3.tar.gz
> ./configure --prefix=/database/server/super-smack \
--with-mysql=/database/server/mysql-5.5.25 \
--with-mysql-lib=/database/server/mysql-5.5.25/lib \
--with-mysql-include=/database/server/mysql-5.5.25/include \
--with-smacks-dir=/database/server/super-smack/smacks \
--with-datadir=/database/server/super-smack/data

컴파일 하다보면 64bit 호환성 문제가 생겨서 query.cc과 src/Makefile 수정이 필요하다.
query.cc는 200번째 라인, 219번째 라인에서 unsigned long로 수정

> vi query.cc <- unsigned long
> make;make install

실행을 하기 위해 먼저 /database/server/super-smack/smacks/select-key.smack 설정 파일 중에 DB 관련 정보를 현행화 한다.
그리고 실행 옵션은 아래와 같다.
./super-smack "구성 파일" "스레드" "하나의 스레드에 대해 쿼리 수"의 순서로 지정한다.

실행 샘플 예제는 아래와 같다.

> ./super-smack ../smacks/select-key.smack 10 10
Error running query select count(*) from http_auth:Table 
 'test.http_auth' doesn't exist
Creating table 'http_auth'
Populating data file '/database/server/super-smack/data/words.dat' 
 with shell command 'gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d'
Loading data from file '/database/server/super-smack/data/words.dat' 
 into table 'http_auth'
Table http_auth is now ready for the test
Query Barrel Report for client smacker1
connect: max=2ms  min=0ms avg= 0ms from 10 clients 
Query_type    num_queries    max_time     min_time     q_per_s
select_index    200     0       0       7682.85

MySQL에서 연결 정보 확인

max_connections는 MySQL 서버가 허용하는 MySQL 클라이언트 동시 연결 최대값을 설정하는 파라미터이다. 이 값은 MySQL 클라이언트에서 무제한으로 연결요청으로 MySQL 서버의 부하가 증가하여 결국 서비스가 중단되는 사태를 방지하기 위해 MySQL 클라이언트의 동시 접속자 수를 제한하는 것이 목적이다.
실제로 연결할 수 있는 숫자는 "max_connections +1"로 SUPER 권한을 가진 사용자(예 : root)에 +1의 연결이 예약되어 있다. 이렇게 함으로써 max_connections에 도달했다하더라도 관리자는 우선적으로 MySQL 서버에 연결하여 원인 조사가 가능한 구조로 되어 있다.

1) max_connections 정보

> mysqladmin -u root -p variables | grep max_connection
Enter password: 
| max_connections                          | 450    

2) 현재 연결 수 정보

> mysqladmin -u root -p extended-status |egrep '(Max|Threads_)'
Enter password: 
| Max_used_connections                     | 1           |
| Threads_cached                           | 0           |
| Threads_connected                        | 1           |
| Threads_created                          | 1           |
| Threads_running                          | 1           |

3) 파라미터 설명

파라미터	설명
Max_used_connections	지금까지 기록된 동시 연결 최대 수
Threads_connected	현재 열려있는 연결 수
Threads_created	연결을 처리하기 위해 생성된 스레드
Threads_running	작동중인 스레드

max_connections=450에서 부하 테스트

max_connections=450과 나머지 설정은 디폴트에서 부하테스트를 하는 이유는 thread_cache_size의 튜닝과의 성능 차이를 비교하기 위해서 진행을 한다.

1) 가정
- my.cnf의 내용.

max_connections = 450
thread_cache_size = 8
//MySQL 리스타트 함.

- 동시 연결 수를 10,20,30 ... 늘려 400까지 측정하고 Queries_per_sec를 수집.
- 1 스레드 당 쿼리 수는 100으로 고정.
- 측정 동안 10 초 간격을 유지함.

2) 테스트를 위한 test database 권한 허가 주기

mysql> grant all on test.* to 'testuser'@'localhost' 
 identified by 'testadmin';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3) 실행 스크립트

/database/server/super-smack/bin/super-smack ./select-key.smack 10 100\
 >> /database/samples/mysql_max_connection/data/log.txt
sleep 10
/database/server/super-smack/bin/super-smack ./select-key.smack 20 100\
 >> /database/samples/mysql_max_connection/data/log.txt
sleep 10
/database/server/super-smack/bin/super-smack ./select-key.smack 30 100\
 >> /database/samples/mysql_max_connection/data/log.txt
sleep 10
....
/database/server/super-smack/bin/super-smack ./select-key.smack 400 100\
 >> /database/samples/mysql_max_connection/data/log.txt

4) 측정 스크립트

HOME=`/bin/echo $HOME`
DIR=/database/samples/mysql_max_connection/data
HOST=`/bin/hostname`

SOCKET=/tmp/mysql.sock
COMMAND="/database/server/mysql-5.5.25/bin/mysqladmin \
 -u root -ppwd extended-status"

while true
DATE=`/bin/date "+%Y/%m/%d %H:%M:%S"`
do
if [ -S $SOCKET ];then
   $COMMAND | /bin/awk '{print $2,$4}' | /bin/egrep -v \
   '(^ |Variable_name)' | \
   while read LABEL DATA
   do
      /bin/echo $DATE $DATA >> $DIR/$HOST.$LABEL
   done
else
   /bin/echo "MySQL is not running."
fi
sleep 10
done

5) 테스트 결과 측정 그래프
아래 그래프에 대한 설명을 간략하게 첨부한다.
- X 축 : 경과 시간 (hh : mm).
- 왼쪽 Y 축 : Max_used_connections, Threads_created 수.
- 오른쪽 Y 축 : Queries_per_sec.

아래는 max_connections=450이고 나머진 디폴트 설정을 한 MySQL 부하테스트 후 연결 정보하고 스레드 정보를 프린트한 내용이다. Connections 8611건에 Threads_created 5575건으로 엄청난 쓰레드 생성이 일어났다.

mysql> show status like '%conn%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Aborted_connects     | 0     |
| Connections          | 8611  |
| Max_used_connections | 396   |
| Threads_connected    | 1     |
+----------------------+-------+

mysql> show status like '%thr%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threads_cached                           | 7     |
| Threads_connected                        | 1     |
| Threads_created                          | 5575  |
| Threads_running                          | 1     |
+------------------------------------------+-------+

테스트 결과 측정 차트는 아래와 같다.

 
먼저 Queries_per_sec가 안정되어 있는 구간은 Max_used_connections은 215까지 연결수가 증가했다. 이 구간이 최고의 고른 성능(QPS)을 보여주었다. 하지만, 215이상 넘어가면 QPS는 떨어지고 있다.
이때, Threads_created는 지속적으로 증가해서 5575까지 갔다. MySQL 서버 자체의 스레드 생성 횟수 많았다는 반증이 되고 이는 성능 저하의 원인이 되었다.
max_connections값으로는 튜닝의 한계가 있어 커넥션 재사용을 활용한다면 성능이 올라갈 수 있다는 개연성이 강해 thread_cache의 튜닝도 같이 가져가야 한다는 결론에 도달한다.

max_connections과 thread_cache_size 튜닝

1) thread_cache_size 조정
thread_cache_size 한 번에 생성한 스레드를 유지하고 재사용함으로써 쓰레드 생성에 걸리는 부하를 저감하는 것이다.

[mysqld] 
max_connections = 450 
thread_cache_size = 450

2) thread_cache 튜닝 후의 측정 결과 그래프
위의 경우와 같이 MySQL 부하테스트 후 연결 정보하고 스레드 정보를 프린트한 내용이다. Threads의 재사용 흔적이 보인다. Threads_created는 31번밖에 발생하지 않았다.

mysql> show status like '%conn%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Aborted_connects     | 0     |
| Connections          | 8937  |
| Max_used_connections | 31    |
| Threads_connected    | 1     |
+----------------------+-------+
4 rows in set (0.00 sec)

mysql> show status like '%thr%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threads_cached                           | 30    |
| Threads_connected                        | 1     |
| Threads_created                          | 31    |
| Threads_running                          | 1     |
+------------------------------------------+-------+

Thread_cache 튜닝 후, 테스트 결과 측정 차트는 아래와 같다.



앞의 경우 Max_used_connections만 튜닝했을 때는 Max_used_connections의 동시 연결 수가 계속 증가했고, 이에 따른 Thread_created도 계속 증가했다. 그러나 여기서는 Threads_created가 격감했고(31개 정도) Queries_per_sec(4만 4천 정도)도 지속적으로 안정된 구간을 유지하고 있다.
위 그래프를 보면 연결수(커넥션)가 추가된 스레드들은 모두 재사용된 것으로 보인다. Max_used_connections와 Threads_created가 거의 비슷하게 증가하는 것을 볼 수 있다. 클라이언트에서 BMT할때 커멕션을 10개부터 400개까지 늘려서 테스트를 해도 Queries_per_sec가 4만을 일정하게 유지해 주고 있고, Thread_created도 크게 증가하지 않고 31개 정도에서 멈췄다.
초기의 max_connections만 늘렸을때는 성능의 굴곡이 있었던 것과 달리 max_connextions과 thread_cache_size를 테스트하면서 적정값으로 튜닝해주니 성능도 일정하게 유지하고 최적의 가용성 포인트를 찾을 수 있게 되었다.
그리고 클라이언트에서 커넥션 풀을 사용해서 접근한다면 MySQL에서는 스레드 생성을 최소화할 수 있는 구조가 되므로 MySQL에서는 더욱 더 안정적인 성능구조로 가져갈 수 있을 것이다. 

참고로 이 포스트는 테스트의 일환으로 개념 이해를 위해서 만든 상황이므로 정확한 튜닝 값이라고 단정 지으면 안되고 운영 환경에 맞게 테스트를 해서 최적의 값을 찾는 것이 최선의 튜닝방법입니다. 클라이언트도 실제 사용되는 쿼리와 언어 등 실환경과 비슷한 구조에서 테스트하는 것이 가장 유효한 성능 테스트가 될 것입니다.

추측하지 마시고 데이터를 보고 계산된 예측을 하시기 바랍니다.

[참조 사이트]

• max_connections, thread_cache 값 조정