MariaDB 5.5 Analyzing DBMS v1.0
|
13.08.26 |
v1.0 |
|
|
|
|
|
|
|
|
|
1. MariaDB Analyze DBMS
MariaDB 도 ORACLE 과 마찬가지로 성능을 분석할 수 있는 지표 및 DICTIONARY 가 있다.
대표적인 분석 자료는 다음과 같다.
n information_schema.GLOBAL_VARIABLES
à System
Variables 의 현재 정보를 담고 있다.
n information_schema.GLOBAL_STATUS
à System 의 각종 통계정보를 담고 있다.
n information_schema.PROCESSLIST
à 조회 시점의 processlist 정보를 담고 있다.
2. Collecting Data
앞서 언급한 정보들에 대해서는 한계점이 있다.
n 공통사항으로 이력관리가 안 된다.
n GLOBAL_STATUS 의 경우 (STATUS 에 따라 다를 수 있음) 값이 보통 누적값이다.
이러한 한계점 때문에 데이터를 DB 에 저장해야 한다.
뒷 부분의 분석 부분들은 앞서 언급한 DICTIONARY 정보를 저장 한 테이블이 있음을 전제로 하고 있다.
데이터 수집에 대해서는 여기서 언급하지 않겠다.
또 수집한 데이터를 사용한다는 것은 현재 상황 조회용이 아니라는 이야기며 수집한 데이터를 이용해 특정 시점을 분석하겠다는
의미이다.
3. Analysis (OUTLINE)
무엇을 분석 할 수 있는지 그 대상을 먼저 보면 다음과 같다.
n Active Session 의 변화 추이
n 특정 SQL 추적
n System Variables 의 변경이력 조회
n DB 의 일량 분석
4. Analysis (DETAIL)
n Active Session 의 변화 추이
Active Session 의 수를 파악하는 것은 DBMS 의 안정적인 관리를 위해 필수적이다.
분석의 후속조치로 max connections 의 수정이나 WAS 에서 사용하는 connection pool 을 조정한다.
processlist 의 snapshot 이기 때문에 본래 구조의 컬럼을 다 가져간다.
검색에 유용하게 사용할 수 있는 컬럼은 다음과 같다.
a. ctime à 원본 테이블에는 없는 컬럼으로 snapshot 을 찍은 시각을 기록
b. user à 계정 user@host 의 user 부분
c. host à 계정 user@host 의 host 부분
d. db à 연결 후 사용하는 DB (Schema)
e. time à 수행타임
특정 시점의 3개 이상의 세션이 있었던 시각을 찾는다면 다음과 같이 조회한다.
|
MariaDB [dbstat]> select ctime, count(ctime) from processlist -> where ctime between '2013-08-21 00:00:00' and '2013-08-22 00:00:00' -> group by ctime having count(ctime) > 2; +---------------------+--------------+ | ctime | count(ctime) | +---------------------+--------------+ | 2013-08-21 00:00:01 | 3 | | 2013-08-21 06:00:01 | 3 | +---------------------+--------------+ 2 rows in set (0.00 sec) |
특정 계정에 대해 찾는다면 user 나 host 조건을 사용해 조회하면 된다.
|
MariaDB [dbstat]> select * from processlist -> where ctime between '2013-08-21 00:00:00' and '2013-08-22 00:00:00' -> and user = 'monitor' and host = 'localhost' -> limit 1\G *************************** 1. row *************************** pid: 4941 ctime: 2013-08-21 00:00:01 id: 4664 user: monitor host: localhost db: dbstat command: Query time: 0 state: executing info: insert into dbstat.processlist (ctime, id, user, host, db, command, time, state, info, time_ms, stage, max_stage, progress) select date_format(now(),'%Y%m%d%H%i%s'), id, user, host, db, command, time, state, info, time_ms, stage, max_stage, progress from information_schema.PROCESSLIST time_ms: 0.821 stage: 0 max_stage: 0 progress: 0.000 1 row in set (0.02 sec) |
위 컬럼 정보들을 보면 왜 processlist 의 정보를 수집하는 지 알 수 있다.
user, host 정보를 이용하여 장비나 사용자의 특정이 가능하며,
info 정보를 이용하여 어떤 동작을 수행했는지 알 수 있다.
더불어 time 정보를 이용하여 위 동작을 얼마나 수행했는지 알 수 있다.
n 특정 SQL 추적
특히 장애와 연관하여 어떤 특정 쿼리를 찾아야 할 상황이 있다.
쿼리 뿐만이 아니라 DDL 의 경우도 마찬가지이다.
위와 같은 경우 processlist 의 info 정보로 추적이 가능하며, user 와 host 정보로 주체를 특정지을 수 있다.
다만 processlist 는 그 때 그 순간의 정보라는 점과 이 정보를 기록하는 인터벌에 따라
유효한 정보를 놓칠 수 있다.
n System Variables 의 변경이력 조회
성능에 영향을 미치는 System Variables 에 대해 언제 변경되었는지 그 시점을 찾아야 하는 경우가 있다.
이 경우 다음과 같이 이력테이블을 조회하여 확인할 수 있다.
박스의 쿼리는 8/23 일 0시 이후에 대해 max_connections 의 값을 조회한 것이다.
|
MariaDB [dbstat]> select ctime, v_name, v_value from global_variables -> where ctime > '2013-08-23 00:00:00' and v_name = 'MAX_CONNECTIONS'; +---------------------+-----------------+---------+ | ctime | v_name | v_value | +---------------------+-----------------+---------+ | 2013-08-23 00:00:01 | MAX_CONNECTIONS | 300 | | 2013-08-23 06:00:01 | MAX_CONNECTIONS | 300 | | 2013-08-23 12:00:01 | MAX_CONNECTIONS | 300 | | 2013-08-23 13:40:09 | MAX_CONNECTIONS | 200 | +---------------------+-----------------+---------+ |
해당 테이블의 유효한 정보는 ctime 과 v_name, v_value 3개 뿐이다.
ctime 의 경우 앞서 살펴 본 processlist 와 마찬가지로 기록시점 정보를 남긴다.
(원본 테이블인 information_schema.processlist 에는 시각정보가 없다.)
v_name 과 v_value 는 variables 이름과 셋팅 된 값에 해당한다.
ctime 정보를 보면 6시간 간격마다 수집했으나 변경 된 결과를 보여주기 위해 지표를 수동으로 수집하였다.
n DB 의 일량 분석
MariaDB 5.5 의 경우 Global Status 가 411 개나 된다.
이 중에 의미있는 걸 추려낸다면 다음과 같다.
l BYTES_RECEIVED : 클라이언트로부터 수신한 bytes 수
l BYTES_SENT : 클라이언트로 송신한 bytes
l COM_COMMIT : Commit 한 횟수 (누적치)
l COM_ROLLBACK : Rollback 한 횟수
l COM_DELETE : Delete 한 횟수 (누적치)
l COM_INSERT : Insert 한 횟수
l COM_INSERT_SELECT : Insert - Select 한 횟수
l COM_SELECT : Select 한 횟수
l COM_UPDATE : update 한 횟수
l PREPARED_STMT_COUNT : 현재의 prepared statements 수를 가리킨다.
l QUERIES : 수행 된 쿼리 수를 가리킨다. (STORED PROCEDURE 에서 수행 된 쿼리 포함)
l PREPARED_STMT_COUNT : 현재의 prepared statements 수를 가리킨다.
위 지표에서도 특히 트래픽에 대해 가늠할 때 다음을 사용한다.
COM_COMMIT, COM_INSERT, COM_UPDATE, COM_DEETE, COM_SELECT
위 지표의 값을 이용하여 delta 값을 구할 수 있으며 이를 이용해 사용량의 변화 추이 및
경향을 분석할 수 있다.
이를 테면 com_commit 을 이용해 다음과 같이 데이터를 뽑을 수 있다.
|
MariaDB [dbstat]> select date_format(ctime, '%Y%m%d'), v_name, min(v_value) min_val, max(v_value) max_val, max(v_value) - min(v_value) diff -> from global_status -> where v_name in ('COM_COMMIT') -> and ctime between '2013-08-20 00:00:00' and '2013-08-23 00:00:00' -> group by date_format(ctime, '%Y%m%d'); +------------------------------+------------+---------+---------+-------+ | date_format(ctime, '%Y%m%d') | v_name | min_val | max_val | diff | +------------------------------+------------+---------+---------+-------+ | 20130820 | COM_COMMIT | 237013 | 253781 | 16768 | | 20130821 | COM_COMMIT | 254510 | 271283 | 16773 | | 20130822 | COM_COMMIT | 272012 | 288796 | 16784 | +------------------------------+------------+---------+---------+-------+ |
5. Future topics
n Table size 에 대한 추적
select sum(data_length), sum(index_length) from tables where table_schema = 'dbstat' and table_name = 'global_status';
테이블 사이즈 정보는 위와 같이 information_schema 의 tables 테이블을 이용해 조회한다.
테이블 자체에 대한 사이즈와 인덱스 사이즈를 조회할 수 있으며, 인덱스의 경우엔 Secondary index 가 없으면 0 으로 조회된다.
위 데이터를 Permanent Table 에 넣으면 테이블 사이즈에 대해 추적이 가능하다.
6. References
