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에서는 더욱 더 안정적인 성능구조로 가져갈 수 있을 것이다.
참고로 이 포스트는 테스트의 일환으로 개념 이해를 위해서 만든 상황이므로 정확한 튜닝 값이라고 단정 지으면 안되고 운영 환경에 맞게 테스트를 해서 최적의 값을 찾는 것이 최선의 튜닝방법입니다. 클라이언트도 실제 사용되는 쿼리와 언어 등 실환경과 비슷한 구조에서 테스트하는 것이 가장 유효한 성능 테스트가 될 것입니다.
추측하지 마시고 데이터를 보고 계산된 예측을 하시기 바랍니다.
[참조 사이트]