출처 : 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로 나누면 비적중률 계산 가능. 비적중률이 높다면 키 버퍼를 늘려야 하는 등의 튜닝을 가해야 함.

출처 : http://toybsd.tistory.com/900

FreeBSD 8.2에서 APM환경을 구축해보자!

출처 : http://visualnet.tistory.com/334

FreeBSD + APM + cacti 설치

#vi .cshrc 수정

setenv  LANG ko_KR.UTF-8
setenv  LC_ALL ko_KR.UTF-8
(로케이션 한글 설정)
set prompt = "%B`whoami`%b@`hostname`[%~]#"

#source .cshrc

port 업데이트

cvsup -g -L 2 -h cvsup.jp.freebsd.org /usr/share/examples/cvsup/ports-supfile

1.apache22 설치

whereis apache22

cd /usr/ports/www/apache22
make WITH_MPM=worker install clean (2코어 활성)

선택 옵션에서 mysql 선택하고 탭ok 누르면 된다.

apache22 셋팅은 mysql55-server 와.. php5 를 설치 하고나서 한번에 셋팅 한다..

2. mysql55-server 설치

cd /usr/ports/databases/mysql55-server

#make WITH_CHARSET=utf8 WITH_COLLATION=utf8_general_ci install clean
* EUC-KR을 사용하기 위한 옵션 설치
 # > make WITH_CHARSET=euckr WITH_COLLATION=euckr_korean_ci install clean

utf8로 설치 한다..

#make WITH_CHARSET=utf8 WITH_COLLATION=utf8_general_ci install clean

 특별히 설정할껀 없다.

 * mysql 설정파일 복사
 # > cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf

3. php5 설치

apache22 와 연동하기 위하여..

cd /usr/ports/lang/php5 && make config

설정 화면에서 Build Apache module 선택후.. ok 버튼으로 넘김

cd /usr/ports/lang/php5-extensions

make config install 로 설치

설치 옵션에서 FTP GD MYSQL SOCKETS SNMP 선택후 OK 눌러 설치

설치중 나오는 옵션은 전부 탭눌러서 넘긴다.
설정

1. apache22 설정

cd /usr/local/etc/apache22

vi httpd.conf

AddType 로 검색하여 적당한 곳에..
AddType application/x-httpd-php .php .inc .html
AddType application/x-httpd-php-source .phps

추가한다.

    AddType application/x-compress .Z
    AddType application/x-gzip .gz .tgz
    AddType application/x-httpd-php .php .inc .html
    AddType application/x-httpd-source .phps
이런식으로 적어 주면 된다.

DirectoryIndex 를 검색하여.

DirectoryIndex index.html index.php  추가해준다.

# Virtual hosts
Include etc/apache22/extra/httpd-vhosts.conf

Include 앞에 주석 해제 위와 같이 변경
vi /etc/rc.conf
apache22_enable="YES"

2. mysql 설정

cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf
vi /etc/rc.conf
mysql_enable="YES" 추가후 리붓

3.php 설정
cd /usr/local/etc

cp  php.ini-development php.ini 로 카피 한다.

리붓후
sockstat -4 로 mysql 과 httpd 작동을 확인 한다..

root     sshd       1174  4  tcp4   *:22                  *:*
root     httpd      1147  3  tcp4 6 *:80                  *:*
root     httpd      1147  4  tcp4   *:*                   *:*
mysql    mysqld     1081  11 tcp4 6 *:3306                *:*

4. mysql 나머지 설정..

* 기본 사용자가 mysql로 사용하는 기본 관리 DB 설치
/usr/local/bin/mysql_install_db --user=mysql

---------------------------------------------------------------------------------------

FATAL ERROR: Could not find ./bin/my_print_defaults

If you compiled from source, you need to run 'make install' to

copy the software into the correct location ready for operation.

If you are using a binary release, you must either be at the top

level of the extracted archive, or pass the --basedir option

pointing to that location.

---------------------------------------------------------------------------------------

위와 같은 오류때문에.. mysql_install_db 가 설치가 안된다고 하면 아래 방법으로 설치

/usr/local/bin/mysql_install_db --user=mysql --basedir=/usr/local

* 관리자 비밀번호 지정 하기
/usr/local/bin/mysqladmin -u root password '패스워드' (패스워드 앞에 '은 엔터옆에 '임)

* 캐릭터셋 점검
 # > mysql -u root -p
      Enter password:
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 9 to server version: 5.1.34-log

      Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

      mysql> use test;
      Database changed
      mysql> show variables like 'c%';
      +--------------------------+----------------------------------+
      | Variable_name            | Value                            |
      +--------------------------+----------------------------------+
      | character_set_client     | utf8                            |
      | character_set_connection | utf8                            |
      | character_set_database   | utf8                            |
      | character_set_results    | utf8                            |
      | character_set_server     | utf8                            |
      | character_set_system     | utf8                            |
      | character_sets_dir       | /usr/local/share/mysql/charsets/ |
      | collation_connection     | utf8_general_ci                  |
      | collation_database       | utf8_general_ci                  |
      | collation_server         | utf8_general_ci                  |
      | concurrent_insert        | ON                              |
      | connect_timeout          | 5                                |
      +--------------------------+----------------------------------+
      12 rows in set (0.00 sec)
     
      mysql >

mysql > quit
- 서버설정에 타라 위의 내용이 다를 수 있습니다.
quit 로 빠져 나온다..

mysql utf8 적용

vi /etc/my.cnf

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8

[mysql]
dafault-character-set=utf8

mysql> use test;
Database changed
mysql> show variables like 'c%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
| collation_connection     | utf8_general_ci                  |
| collation_database       | utf8_general_ci                  |
| collation_server         | utf8_general_ci                  |
| completion_type          | NO_CHAIN                         |
| concurrent_insert        | AUTO                             |
| connect_timeout          | 10                               |
+--------------------------+----------------------------------+
14 rows in set (0.01 sec)

mysql>

apm 설치및 셋팅이 끝났다..

mysql> status

--------------

mysql  Ver 14.14 Distrib 5.1.60, for portbld-freebsd8.2 (amd64) using  5.2

Connection id: 1048

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: more

Using outfile: ''

Using delimiter: ;

Server version: 5.1.60-log FreeBSD port: mysql-server-5.1.60

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8

Db     characterset: utf8

Client characterset: utf8

Conn.  characterset: utf8

UNIX socket: /tmp/mysql.sock

Uptime: 1 day 16 hours 37 min 20 sec

Threads: 1  Questions: 109705  Slow queries: 0  Opens: 1107  Flush tables: 1  Open tables: 54  Queries per second avg: 0.750

--------------

cacti 를 설치 합니다.

기본적으로 apm 이 설치 되어 있어야 한다. php 설치 옵션중 snmp

test# whereis cacti
cacti: /usr/ports/net-mgmt/cacti

cacti: /usr/ports/net-mgmt/cacti
test# cd /usr/ports/net-mgmt/cacti && make install clean

mysql 의 db와 계정을 셋팅한다.

# mysql -u root -p

Enter password: **********

---------------------------------------------------

mysql> create database cacti;

mysql> use mysql;

mysql> GRANT ALL ON cacti.* TO cacti@localhost IDENTIFIED BY 'password';

mysql> FLUSH privileges;

mysql> quit

mysql -u root -p cacti < /usr/local/share/cacti/cacti.sql

php.ini timezone 수정

vi /usr/local/etc/php.ini

date.timezone = Asia/Seoul

로 수정

# vi /usr/local/share/cacti/include/config.php
--------------->modify<------------------

#database_type = "mysql";

#database_default = "cacti";

$database_hostname = "127.0.0.1";

$database_hostname = "localhost";

$database_username = "cacti";

$database_password = "password";

$database_port = "3306";

------------------------------------------

vi /etc/rc.conf

snmpd_enable="YES"
snmptrapd_enable="YES"
linux_enable="YES"

cd /usr/local/share/snmp/

cp snmpd.conf.example snmpd.conf

vi snmpd.conf

#trap2sink    localhost public

#rocommunity public  localhost

주석 제거 해 준다.

vi /etc/crontab

*/5 * * * * cacti /usr/local/bin/php /usr/local/share/cacti/poller.php > /dev/null 2>&1

추가 해 준다.

리눅스 에뮬레이터 설치
메모리 부분 스크립트가 linux 전용으로 되어 있기 때문에 linux에뮬레이터를 설치 해야 정상적으로 작동한다.
위에 rc.conf 수정에서 linux_enable="YES" 로 한후 리붓을 해야 정상적으로 설치가 가능하다.

rc.conf 를 수정 하지 않고 설치 했을경우

root@cacti.test.com[/usr/ports/emulators/linux_base-f10]#make install clean
===>  linux_base-f10-10_4 linuxulator is not (kld)loaded.
*** Error code 1

Stop in /usr/ports/emulators/linux_base-f10.

와 같은 오류 메시지를 볼수 있다
리눅스 에뮬레이터 사용시 시작스크립트인 rc.conf 에서 리눅스 모듈? 을 불러와야 정상적으로 설치가 된다.

리붓후 다시 설치 하도록 한다.

root@test.crois.net[~]#cd /usr/ports/emulators/linux_base-f10/
root@test.crois.net[~]#make install clean

cacti 메모리 부분 스크립트 변경
리눅스 용으로 작성이 되어 있기때문에 freebsd 에서 구동시키면 정상 작동이 되질 않습니다.
 test# cd /usr/local/share/cacti/scripts/
 test# vi linux_memory.pl

#!/usr/bin/perl

$mem = `cat /compat/linux/proc/meminfo | grep -w "$ARGV[0]"`;
$mem =~ s/($ARGV[0].*\s)(.*[0-9])( kB)//;

print $2;

-------------------------------------------------

linux 에뮬레이터 사용을 위해.
fstab 부분에 설정을 합니다.

 vi /etc/fstab
 linproc   /compat/linux/proc linprocfs rw 0 0

vi /etc/crontab

*/5 * * * * cacti /usr/local/bin/php /usr/local/share/cacti/poller.php > /dev/null 2>&1

---------------------------------------

root@cacti.test.com[/usr/local/share/cacti/scripts]#cd /usr/local/etc/apache22/
vi httpd.conf

apache 설정

Alias /cacti /usr/local/share/cacti
<Directory "/usr/local/share/cacti">
    AllowOverride None
    Order Allow,deny
    Allow from all
</Directory>

root@cacti.test.com[~]#/etc/rc.d/cron restart

--------------------------------------------------------

01/09/2012 10:35:02 AM - PCOMMAND: Poller[0] Host[1] WARNING: Recache Event Detected for Host

오류가 뜨면
php -q /usr/local/share/cacti/cli/poller_reindex_hosts.php -id=2 -d

php -q /usr/local/share/cacti/cli/poller_reindex_hosts.php -id=All

삭제 해 줘야 한다.
오류 관련 설정 페이지

http://docs.cacti.net/manual:087:6_reference.4_cli_script.2_reindex_hosts

정상적으로 snmpd 가 작동 하는걸 보기 위해.. 아래 명령어를 실행해 본다.

snmpwalk -v 2c -c public localhost

-----------------------------------------------------------

cacti 설정

admin:admin 으로 로그인 하고 로그인 후에 비번을 만들어 주면 된다.

Devices 부분

Host Template 부분을 ucd/net SNMP Host 로 바꿔 준다.

SNMP Version 을 Version 2 로 바꿔준다.

Associated Graph Templates 에서

2) ucd/net - CPU Usage  Not Being Graphed   
3) ucd/net - Load Average  Not Being Graphed   
4) ucd/net - Memory Usage  Not Being Graphed 

위와 같이 추가를 해준다.

Associated Data Queries 부분에서

1) SNMP - Interface Statistics 추가

그리고 저장.

Management 부분에서

Graph Management

기본으로 되어 있던 설정을 전부 삭제 한다.

다시
Create
New Graphs  부분에서 그래프를 만들어 준다.

Collection Methods
Data Queries

부분에서 SNMP - Interface Statistics 를 추가

전부 설치 하고 나면 5분 후 아래와 같은 그래프를 볼수 있다.

----------------------------------------------
설명이 너무 부실하여 도움이 될지는 모르겠지만..
차후좀더 수정 보완하겠습니다.
어설픈글 읽어 주셔서 감사합니다 _(__)_