Cacti로 MySQL 모니터링 및 튜닝 포인트

출처 : http://www.mimul.com/pebble/default/2012/05/16/1337154545185.html

 

Cacti로 MySQL 모니터링 및 튜닝 포인트

모니터링 도구로 Nagios, Zabbix, Ganglia 등 많지만, 저는 Cacti를 사용해 왔고(많이 사용한 건 아니지만) 최근 들어 많은 기능이 고도화(알림, 템플릿, 다루기 쉬움)되어서 나름 괜찮다고 생각해 Cacti를 가지고 MySQL을 모니터링하고 일부 튜닝 포인트도 잡을 수 있는 내용으로 기술해 봅니다.

모니터링하기 위해 Cacti를 설치하고, Plugin을 등록하자.

기존에 Cacti 설치 매뉴얼은 여기를 참조하면 설치가 가능하다. 이번에는 추가적으로 자주 활용되는 Cacti Plugin을 더 설치하였다.

> wget http://www.cacti.net/downloads/cacti-0.8.7i-PIA-3.1.zip
> unzip cacti-0.8.7i-PIA-3.1.zip
> cd /home/k2/www/cacti
> patch -p1 -N < /logs/src/cacti-0.8.7i-PIA-3.1/cacti-plugin-0.8.7e-PA-v2.6.diff

> cd /home/k2/www/cacti
> wget http://docs.cacti.net/_media/plugin:settings-v0.71-1.tgz -O settings.tgz
> tar zxvf settings*.tgz -C /home/k2/www/cacti/plugins
> chown -R apache:apache /home/k2/www/cacti/plugins/settings
> wget http://docs.cacti.net/_media/plugin:clog-v1.7-1.tgz -O clog.tgz 
> tar zxvf clog*.tgz -C /home/k2/www/cacti/plugins 
> chown -R apache:apache /home/k2/www/cacti/plugins/clog
> wget http://docs.cacti.net/_media/plugin:thold-v0.4.9-3.tgz -O thold.tgz 
> tar zxvf thold*.tgz -C /home/k2/www/cacti/plugins 
> chown -R apache:apache /home/k2/www/cacti/plugins/thold
> wget http://docs.cacti.net/_media/plugin:monitor-v1.3-1.tgz -O monitor.tgz
> tar zxvf monitor*.tgz -C /home/k2/www/cacti/plugins 
> chown -R apache:apache /home/k2/www/cacti/plugins/monitor

MySQL 모니터링을 위한 Cacti 템플릿으로 사용한 것은 better-cacti-templates(percona-monitoring-plugins)이다.
해당 플러그인을 다운로드 받은 다음 cacti 디렉토리의 ss_get_by_ssh.php, ss_get_mysql_stats.php 파일을 Cacti가 설치된 디렉토리의 scripts디렉토리(/home/k2/www/cacti/scripts)에 카피한다.
그리고 아래와 같이 MySQL의 계정 정보를 수정해 준다.

> vi ss_get_mysql_stats.php
$mysql_user = 'cactiuser';
$mysql_pass = 'cactipassword';
$mysql_port = 3306;

그 다음 다운받은 percona-monitoring-plugins Templates를 Cacti UI를 통해 아래와 같이 Import를 해준다.

Console -> Import Templates -> Import Template from Local File 에 cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.0.0.xml 파일을 업로드 한다.
그려면 success라는 응답을 내려주면서 정상적으로 Template이 등록이 완료된다.

그 다음 MySQL 서버가 Cacti 서버와 분리되어 있다면 Create Devices를 통해 호스트 등록을 하면서 Template과 함께 등록한다.
저는 Cacti 서버에 설치된 MySQL을 모니터링하기 때문에 기존 등록된 localhost에 template을 반영하도록 했다.

• Console -> Devices -> Associated Graph Templates 에서 percona 템플릿 추가한다.
• Console -> Data Sources Add 버튼을 클릭해서 데이터 소스 등록한다.
• Console -> Graph Management Graph Add 하고 해당 Data Source 등록한다.

MySQL 성능 모니터링 관련 지표들을 몇가지 살펴보면

프로젝트 오픈 전에는 log-slow-queries, log_queries_not_using_indexes 등을 설정하여 인덱스를 사용하지 않는 쿼리, 인덱스를 사용해도 전체 Scan하는 쿼리를 모두 기록하고 그것을 튜닝한다. 그런데 전수 조사하기에는 분명 빠지는 부분이 있을 것이다.
그러나 오픈 후에는 성능상의 이유로 이런 기능을 disable시켜 놓게 된다. 하지만 문제되는 쿼리들은 분명 나오게 되는데 이를 해결하기 위해서는 모니터링 도구를 통해 즉각 대응할 수 있어야 한다. 그래서 Template에 사용되는 MySQL의 모니터링 도구에 표시되는 그래프를 통해 튜닝 포인트를 찾아내는 방법에 대해서 알아본다.

1) 트랜잭션 상황을 파악하는 MySQL Transaction Handler이 그래프는 트랜젝션 완료 갯수와 롤백 개수를 나타낸다.


롤백의 현황을 통해 쿼리의 오류 정도를 확인 가능하고 Deadlock을 예측해 볼 수도 있다.

2) SQL의 종류를 확인하는 MySQL Command Counters
이 그래프는 어떤 종류의 SQL 문장이 실행되었는지를 나타내주는 그래프다. 단위 간격(기본 5분)마다 횟수이다 m는 밀리, 즉 1/1000이다. 예를 들면 100m라는 것은 5분 동안 발생 횟수가 0.1회하는 것으로, 50분만에 1번 발생했다는 것을 나타낸다.


Com은 Command의 약자로 Com Select/Delete/Insert/Update/Replace는 이름 그대로 SQL 실행 횟수이다. Com xxx Multi는 여러 테이블을 일괄적으로 Update하는 수를 나타낸다.
Questions은 MySQL 서버 시작 후 지금까지 요청된 쿼리 수.

3) SELECT의 실행 계획을 확인하는 MySQL Select Types
이 그래프는 개별 쿼리에 대해 EXPLAIN을함으로써 어떤 실행 계획을 사용하는지 알 수 있다.


인덱스가 없는 칼럼을 조회하는 Select Full Join에 표시되고 범위 지정도 역시 인덱스를 타지 않고 Join을 했을 경우 Select Full Range Join을 보면 된다.
이런 값들이 발생할 경우 튜닝의 대상으로 삼아야 한다.

4) 쿼리의 I/O 동작을 알수 있는 MySQL Handlers
MySQL 쿼리 실행 후 스토리지 엔진 API를 통해 일어나는 파일이나 디스크 I/O를 볼 수 있게 해 준다. show session status 값.


- Handler Read First : 테이블이나 인덱스의 Full Index Scan 시의 먼저 첫 번째 레코드 수. -> 튜닝 대상.
- Handler Read Key : 인덱스 키값에 따라 점프하여 읽는 횟수.
- Handler Read Next : 키 값에 따라 레코드를 확인한 후 후속 행을 읽은 횟수.
- Handler Read Prev : 키 값에 따라 이전 레코드 읽는 횟수.
- Handler Read Rnd : Random Read 값.
- Handler Read Rnd Next : Random Read 후속 읽기 횟수. -> 튜닝 대상.

5) 쿼리 캐시 감시하는 MySQL Query Cache
이 그래프는 캐시의 사용 변화 추이를 살펴 보면서 캐시를 효율적으로 사용하는지 파악하는 데 도움을 준다.


- Qcache_hits : 캐시에서 질의를 가져올 때마다 값이 증가.
- Qcache_inserts : 질의가 들어올 때마다 증가. inserts를 hits로 나누면 비적중률을, 1에서 비적중률을 빼면 적중률 계산이 가능함.
- Qcache_lowmem_prunes : 캐시를 위한 메모리가 부족해져 더 많은 질의를 위한 공간을 확보하기 위해 정리되어야 하는 횟수. 증가 추세에 있다면 단편화가 심각하거나 메모리가 부족하다는 징후.
- Qcache_not_cached : 일반적으로 SELECT 구문이 아니기 때문에 캐시 후보에서 제외된 질의 숫자.

6) 키 효율성 확인하는 MyISAM Indexes
키 버퍼는 MyISAM 테이블을 위한 인덱스 블록을 저장한다. 이상적인 경우 이 블록에 대한 요청은 디스크가 아니라 메모리에서 일어나야 한다는점을 알아두자.


- Key_reads : 디스크에서 요청한 숫자.
- Key_read_requests : 전체 숫자. Key_reads를 Key_read_requests로 나누면 비적중률 계산 가능. 비적중률이 높다면 키 버퍼를 늘려야 하는 등의 튜닝을 가해야 함.