[MySQL] 쿼리 성능 확인하기 - Performance 스키마 & Sys 스키마

MySQL에서 제공하는 Performance 스키마와 Sys 스키마를 활용하면 현재 데이터베이스의 상태를 확인하고, 성능을 향상시킬 수 있는 튜닝 요소들을 찾을 수 있다.

마침 진행중인 프로젝트에서 E2E 테스트를 위해 실행 중인 MySQL 서버가 있어서 쿼리 성능을 확인해보기로 했다.

이번 시간에는 서버에서 자주 실행되는 쿼리 목록과 실행 시간이 긴 쿼리 목록을 확인해 볼 것이다.

자주 실행되는 쿼리 목록 확인

Sys 스키마의 statement_analysis 뷰를 통해 확인할 수 있다.

어느 데이터베이스에서 실행된 쿼리인지(db), 실행 횟수(exec_count), 그리고 쿼리문(query)을 실행 횟수 내림차순으로 정렬하여 출력하도록 쿼리를 작성했다.

프로젝트 관련한 쿼리만 조회하기 위해 WHERE db='lesser'라는 조건을 추가했다.

SELECT db, exec_count, query
FROM sys.statement_analysis
WHERE db='lesser'
ORDER BY exec_count DESC;

쿼리 실행 결과는 아래와 같았다. E2E 테스트를 할 때 테스트 간 영향을 미치지 않도록 새로 생긴 레코드를 삭제하고 다음 테스트를 진행하기 때문에 DELETE 쿼리가 많이 실행되는 것을 확인할 수 있었다. 

+--------+------------+-------------------------------------------------------------------+
| db     | exec_count | query                                                             |
+--------+------------+-------------------------------------------------------------------+
| lesser |        312 | START TRANSACTION                                                 |
| lesser |        312 | COMMIT                                                            |
| lesser |        216 | DELETE FROM `login_member`                                        |
| lesser |        216 | DELETE FROM `temp_member`                                         |
| lesser |        216 | DELETE FROM `member`                                              |
| lesser |        216 | DELETE FROM `project_to_member`                                   |
| lesser |        216 | DELETE FROM `project`                                             |
| lesser |        144 | SELECT `VERSION` ( ) AS `version`                                 |
| lesser |         96 | SELECT `Member` . `id` AS `Mem ... Member` . `id` = ? ) ) LIMIT ? |
| lesser |         90 | SELECT `Member` . `id` AS `Mem ...  . `github_id` = ? ) ) LIMIT ? |
| lesser |         84 | SELECT `TempMember` . `uuid` A ...  . `github_id` = ? ) ) LIMIT ? |
| lesser |         78 | SELECT `TempMember` . `uuid` A ... HERE `TempMember` . `uuid` = ? |
| lesser |         78 | INSERT INTO `temp_member` ( `u ... ( ?, ... , DEFAULT , DEFAULT ) |
| lesser |         78 | SELECT `TempMember` . `uuid` A ... `TempMember` . `uuid` IN (...) |
| lesser |         72 | ALTER TABLE `member` ADD UNIQU ... 518bfc2544125` ( `github_id` ) |
| lesser |         72 | DROP INDEX `IDX_f29502fbd8f92518bfc2544125` ON `member`           |
| lesser |         72 | SELECT SCHEMA ( ) AS `db_name`                                    |
| lesser |         72 | SELECT `TempMember` . `uuid` A ... mber` . `uuid` = ? ) ) LIMIT ? |
| lesser |         72 | SELECT `TABLE_SCHEMA` , `TABLE ... HEMA` = ? AND `TABLE_NAME` = ? |
| lesser |         72 | SELECT * FROM `INFORMATION_SCH ... HEMA` = ? AND `TABLE_NAME` = ? |
| lesser |         72 | SELECT * FROM ( SELECT * FROM  ... u` WHERE `CONSTRAINT_NAME` = ? |
| lesser |         72 | SELECT `SCHEMA_NAME` , `DEFAUL ... FORMATION_SCHEMA` . `SCHEMATA` |
| lesser |         72 | SELECT `s` . * FROM ( SELECT * ... ABLE_NAME` = ? ) `rc` ON `s` . |
| lesser |         72 | SELECT `kcu` . `TABLE_SCHEMA`  ... _NAME` = ? UNION SELECT * FROM |
| lesser |         72 | SELECT * FROM `INFORMATION_SCH ... HEMA` = ? AND `TABLE_NAME` = ? |
| lesser |         72 | DROP INDEX `IDX_1945f9202fcfbce1b439b47b77` ON `member`           |
| lesser |         72 | ALTER TABLE `member` ADD UNIQU ... bce1b439b47b77` ( `username` ) |
| lesser |         66 | SELECT `LoginMember` . `member ... nMember` . `member_id` = ? ) ) |
| lesser |         60 | SELECT `Member` . `id` AS `Mem ... ber` WHERE `Member` . `id` = ? |
| lesser |         60 | SELECT `LoginMember` . `member ... LoginMember` . `member_id` = ? |
| lesser |         60 | INSERT INTO `login_member` ( ` ... ( ?, ... , DEFAULT , DEFAULT ) |
| lesser |         60 | INSERT INTO `member` ( `id` ,  ... , ?, ... , DEFAULT , DEFAULT ) |
| lesser |         24 | DELETE FROM `login_member` WHERE `member_id` = ?                  |
| lesser |         18 | INSERT INTO `project` ( `id` , ... , ?, ... , DEFAULT , DEFAULT ) |
| lesser |         18 | SELECT `Project` . `id` AS `Pr ... ct` WHERE `Project` . `id` = ? |
| lesser |         18 | INSERT INTO `project_to_member ... , DEFAULT , DEFAULT , ?, ... ) |
| lesser |         18 | SELECT `ProjectToMember` . `id ... E `ProjectToMember` . `id` = ? |
| lesser |         12 | SELECT `Member` . `id` AS `Mem ... ` . `username` = ? ) ) LIMIT ? |
| lesser |         12 | UPDATE `login_member` SET `ref ...  = ? AND `refresh_token` = ? ) |
| lesser |          6 | SELECT `Project` . `id` AS `Pr ... 100ccc9660eeffb2727d` . `id` = |
| lesser |          6 | UPDATE `login_member` SET `ref ...  WHERE ( ( `member_id` = ? ) ) |
| lesser |          6 | SELECT `LoginMember` . `member ... LoginMember` . `member_id` = ? |
| lesser |          6 | UPDATE `temp_member` SET `temp ... t` = NOW WHERE `uuid` IN (...) |
+--------+------------+-------------------------------------------------------------------+

실행 시간이 긴 쿼리 목록 확인

Sys 스키마의 x$statement_analysis 뷰를 통해 확인할 수 있다.

💡 statement_analysis 와 x$statement_analysis 차이점?
Sys 스키마의 뷰는 사람이 읽기 쉽게 포맷팅된 Formatted-View와 데이터가 원본 형태로 저장된 Raw-View가 있다.
Raw-View는 “x$” 접두사가 붙는 걸로 구분할 수 있다.
따라서 statement_analysis는 사람이 읽기 쉽게 포맷팅 된 뷰, x$statement_analysis는 원본 형태의 데이터로 보여주는 뷰이다.

Formatted-View인 statement_analysis에서 avg_latency를 조회하면 '111.34 us’, '1.31 ms’ 처럼 문자열로 조회된다. 따라서 실행 시간으로 정렬하기 위해 avg_latency가 원본 형태(bigint unsigned)로 저장되어 있는 x$statement_analysis로 조회하였다.

쿼리문(query)과 평균 실행 시간(avg_latency)을 실행 시간 내림차순으로 정렬하여 출력하도록 쿼리를 작성했다.

SELECT query, sys.format_time(avg_latency) as 'formatted_avg_latency'
FROM sys.x$statement_analysis
WHERE db='lesser'
ORDER BY avg_latency DESC LIMIT 4;

실행시간 기준 상위 4개의 레코드를 조회한 결과 다음과 같았다. 인덱스를 만들고 삭제하는 작업이 오래걸리는 것을 알 수 있었다.

+----------------------------------------------------------------------------------------+-----------------------+
| query                                                                                  | formatted_avg_latency |
+----------------------------------------------------------------------------------------+-----------------------+
| DROP INDEX `IDX_1945f9202fcfbce1b439b47b77` ON `member`                                | 14.92 ms              |
| DROP INDEX `IDX_f29502fbd8f92518bfc2544125` ON `member`                                | 10.21 ms              |
| ALTER TABLE `member` ADD UNIQUE INDEX `IDX_f29502fbd8f92518bfc2544125` ( `github_id` ) | 10.12 ms              |
| ALTER TABLE `member` ADD UNIQUE INDEX `IDX_1945f9202fcfbce1b439b47b77` ( `username` )  | 9.44 ms               |
+----------------------------------------------------------------------------------------+-----------------------+

Raw-View에서 쿼리문이 너무 길게 출력되어서 보기 어렵다면 쿼리 종료할 때 ; 대신 \G를 사용해 결과를 세로로 출력할 수 있다.

SELECT query, sys.format_time(avg_latency) as 'formatted_avg_latency'
FROM sys.x$statement_analysis
WHERE db='lesser'
ORDER BY avg_latency DESC LIMIT 5\G

실행시간 기준 상위 5개의 레코드를 세로로 출력한 결과는 다음과 같다.

*************************** 1. row ***************************
                query: DROP INDEX `IDX_1945f9202fcfbce1b439b47b77` ON `member`
formatted_avg_latency: 14.92 ms
*************************** 2. row ***************************
                query: DROP INDEX `IDX_f29502fbd8f92518bfc2544125` ON `member`
formatted_avg_latency: 10.21 ms
*************************** 3. row ***************************
                query: ALTER TABLE `member` ADD UNIQUE INDEX `IDX_f29502fbd8f92518bfc2544125` ( `github_id` )
formatted_avg_latency: 10.12 ms
*************************** 4. row ***************************
                query: ALTER TABLE `member` ADD UNIQUE INDEX `IDX_1945f9202fcfbce1b439b47b77` ( `username` )
formatted_avg_latency: 9.44 ms
*************************** 5. row ***************************
                query: SELECT `TABLE_SCHEMA` , `TABLE_NAME` , `TABLE_COMMENT` FROM `INFORMATION_SCHEMA` . `TABLES` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? UNION SELECT `TABLE_SCHEMA` , `TABLE_NAME` , `TABLE_COMMENT` FROM `INFORMATION_SCHEMA` . `TABLES` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? UNION SELECT `TABLE_SCHEMA` , `TABLE_NAME` , `TABLE_COMMENT` FROM `INFORMATION_SCHEMA` . `TABLES` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? UNION SELECT `TABLE_SCHEMA` , `TABLE_NAME` , `TABLE_COMMENT` FROM `INFORMATION_SCHEMA` . `TABLES` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? UNION SELECT `TABLE_SCHEMA` , `TABLE_NAME` , `TABLE_COMMENT` FROM `INFORMATION_SCHEMA` . `TABLES` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?
formatted_avg_latency: 6.29 ms

마무리

위에서는 자주 쓰는 쿼리와 실행 시간이 긴 쿼리 목록만 확인해봤지만 풀 테이블 스캔을 실행하는 쿼리를 확인하거나, 테이블 별 작업량 통계를 확인하는 등 쿼리 성능을 튜닝하기 위한 다양한 정보들을 Performance 스키마 & Sys 스키마에서 확인할 수 있다는 것을 알게되었다. 이러한 통계 정보들을 이용해 데이터베이스 성능을 개선하는 작업을 지속적으로 해봐야겠다.

참고자료

Real MySQL 8.0 - 18장 Performance 스키마 & Sys 스키마