[Percona Toolkit] pt-table-usage

pt-table-usage는 쿼리가 테이블을 어떻게 사용하는지를 분석하는 프로그램이다. 

사용법 

다음과 같이 사용한다. 

pt-table-usage [OPTIONS] [FILES]

pt-table-usage는 로그로 분석할 쿼리를 읽고, 어떻게 테이블을 사용했는지를 분석한다. 파일명이 명시되지 앟는다면 STDIN으로 읽고, 각 쿼리에 대해 리포트를 출력한다. pt-table-usage가 사용하는 로그는 MySQL이 생성하는 slow log query와 같은 포맷이어야 한다. 

Table usage는 쿼리를 읽고 쓰는 작업을 간단하게 표현하는 것 이상의 역할을 수행하는데, 그것은 바로 데이터의 흐름을 보여주는 것이다. 이 툴은 테이블에 표시되는 문맥에 맞게 데이터의 흐름을 파악하여 그 흐름 정보를 보여준다. 그리고, 개별 컬럼 수준으로 해당 정보를 분석하기 때문에 정확히 파악하는 것이 가능하다. 만약, 쿼리가 복수의 테이블에 접근하여 처리하는 경우 이름이 다르다면 EXPLAIN EXTENDED를 실행한 후에 SHOW WARNINGS 명령어를 실행하여 해당 정보를 자세히 보는 게 필요할 수도 있다. 

만약, 프로그램이 기본 스키마를 알지 못하는 경우 explain extended 실행이 실패 할수가 있다. 그래서 이 경우에 --database 옵션을 통해 지정하여 처리할 수 있다. 또한 , --create-table-definations 옵션을 통해 모호성을 해결 할 수도 있다. 

OUTPUT 분석

프로그램이 실행하고 나면 다음과 같이 결과가 출력된다. 

 

 

첫번째 라인은 쿼리의 ID를 나타낸다. 기본적으로 이것은 pt-query-digest를 통해 표기되는 것과 동일한 것이다. 이 값은 쿼리의 MD5 checksum 값에 대한 fingerprint 값이다. 쿼리 ID는 2부분으로 나뉘는데, 쿼리 ID와 테이블의 숫자 이다. 만약, 해당 쿼리가 다른 값을 가지기를 원한다면 --id-attribute를 사용하면 된다. 

앞에서 보여준 예제는 2개의 쿼리의 결과를 보여준 것이 아니라. 하나의 쿼리에 대한 분석자료 2개를 보여준 것이다. 하지만, 쿼리 ID는 2개가 다른 값으로 나타나고 있는 것을 확인할 수 있다. 이것은 바로 테이블을 나타내는 숫자값이 다르기 때문에 나타나는 현상이다. 테이블에 대한 숫자값이 각각 테이블에 대해 1씩 증가함을 확인할 수 있다. 

해당 분석 데이터에 대한 원래 쿼리는 다음과 같다. 

 

 

첫번째 라인 이후의 정보는 다음과 같다. 

SELECT : 하나 또는 그 이상의 이유로 테이블로 부터 데이터를 추출한다는 것을 의미한다. 첫번째 이유는 데이터를 조회하기 위함이다. 단순 조회를 위해 조회가 진행되면 SELECT로 표시된다. 두번째 이유는 DML이다. 데이터를 입력하거나 수정할때 필요한 경우 SELECT가 진행되는데, 이때에는 다음과 같이 SELECT DUAL로 표시된다. 

 

 

이것은 다음을 가르킨다. 

 

 

여기서는 테이블에서 찾은 값이 아닌 bar, bat으로 값을 수정하게 되어있다. 이와 같이 사용하는 경우 DUAL이라는 키워드를 사용한다. 

Any other verb : 해당 쿼리에서 진행되는 INSERT, UPDATE, DELETE 같은 것을 말하는 것으로 해당 단어는 쿼리 내에서 변경이 발생했음을 의미한다. INSERT ... SELECT 나 UPDATE와 같은 쿼리가 실행되는 경우 발견할 수 있으며, 현재 SET, LOAD, multi-table DELETE는 지원되지 않는다. 

JOIN : 여기서는 조인되는 정보를 보여준다. 명시적, 또는 암묵적으로 이루어지는 조인 내용이 보여진다. 

WHERE : 이 부분은 WHERE 구문에 작성된 필터 조건을 보여준다. 여기서는 조인에서 사용되는 암묵적인 조건은 포함되지 않는다.  예를 들어 다음과 같은 조회 조건이라면...

 

 

다음과 같이 보일 것이다. 

 

 

TLIST : 여기에는 쿼리에서 접근하는 테이블 목록이 보여진다. 그 외의 다른 정보는 보이지 않는다. 이 테이블들은 일반적으로 암묵적인 cartesian join을 한다. 즉, SELECT * FROM t1, t2 테이블의 결과라고 했을때 다음과 같이 보여진다. 

 

 

먼저, 2개의 SELECT 내용이 보여지는데, 이것은 t1, t2 테이블에 대한 조회 내용인것이고, 두번째로 암묵적으로 발생한 조인 내용이 보여지는데, 이 내용을 cartesian join으로  TLIST로 보여주고 있는것이다. 

  

EXIT STATUS

pt-table-usage는 에러가 하나라도 있으면 1이라는 결과값을 나타내고, 에러가 하나도 없으면 0이라는 결과값을 보여준다. 

OPTIONS

--ask-pass

--charset

--config

--constant-data-value

--[no]continue-on-error

--create-table-definitions

--daemonize

--database

--defaults-file

--explain-extended

--filter

--help

--host

--id-attribute

--log

--password

--pid

--port

--progress

--query

--read-timeout

--run-time

--set-vars

--socket

--user

--version

 

테스트~~~

 

[mysql@oraclelinux6 bin]$ ./pt-table-usage /data1/mysqllog/slowqueries.log
Query_id: 0xE3A3649C5FAC418D.1
SELECT DUAL

Query_id: 0x389FF9DA2DF3DF62.1
SELECT DUAL

Query_id: 0xFB38455BF7035609.1
SELECT DUAL

Query_id: 0x9D753E354B552E3B.1
SELECT City
SELECT Country
TLIST City
JOIN Country

 

 

[mysql@oraclelinux6 bin]$ ./pt-table-usage --query="select * from user join db on (user.user = db.user)"

Query_id: 0x20E9099E3AA67BDD.1
SELECT user
SELECT db
JOIN user
JOIN db