MySQL 성능분석도구 이야기(PERFORMANCE_SCHEMA)

• 개요
• 성능스키마란?
• 성능 스키마 수집 구조
• 성능스키마 사용방법

개요

데이터베이스에 있어서 성능은 포기할 수 없는 부분이다.
아무리 사용하기 편리하고 기능이 많더라도 성능이 나오지 않는 데이터베이스는 현업에서 절대 쓰여질 수 없다. 만족스런 성능의 기준은 DBMS 가 사용되는 시스템의 인입 부하량에 따라 많이 다를 것이다. 어떤 시스템의 경우는 MySQL 의 기본 설정만으로도 충분히 성능 요구치를 만족할 수 있을 것이고, 어떤 시스템은 장시간에 걸친 튜닝 노력을 통해서만 달성할 수 있는 성능수치를 요구할 수도 있다.
때에 따라서는 Disk DBMS 의 성능 최대치로도 부족하여 In-Memory RDBMS 도입을 고려할 수도 있고, Relational DBMS 의 ACID 를 다 만족시키지 않아도 좋으니 그 이상의 극한 성능을 바란다면 적합한 NoSQL 제품을 고려할 수도 있을 것이다.
어쨌건 성능은 데이터 관리를 위해 DBMS 를 사용하는 본질적인 목적 중의 하나라는 것은 너무나 자명하다.

만족스런 성능 결과를 얻기 위해 수많은 튜닝요소들을 반영하기 마련인데, 이러한 튜닝요소를 반영하기 전에 가장 먼저 해야하는 일은 당연히 현재의 DBMS 상태를 분석하는 일이다.

• 클라이언트 세션들은 얼마나 연결되어 있고 무슨 일을 하고 있는가?
• 각 세션들은 어느 정도의 메모리를 사용 중인가?
• DBMS 내부 Thread 들은 무엇을 처리하고 있는가?
• 혹시 트랜잭션 간에 서로 Lock 경합을 일으키고 있는가?
• 어떤 트랜잭션이 오래 수행되고 있는가?
• DBMS 전체 Memory 사용 량은 어느 정도이고 증가량은 어떤가?
• 인덱스나 테이블 접근 시 IO wait 이 발생하는 정도는 어떠한가?
• 이중화(Replication) 관련 Thread 들의 상태는 정상인가?
• 시스템, 세션의 변수들 설정 상황은 어떤가?
• 통신 소켓 별로 전송량은 얼마나 되는가?
• ...

위에 열거한 것 이외에도 수많은 분석 요소들이 존재한다. 이러한 분석을 위해 정보 수집은 어떻게 해야할까?

성능스키마란?

위와 같은 정보들을 여기저기 찾아다니며 사용자가 직접 정보를 수집하는 것은 너무나 어려운 일이다. 이러한 정보 수집 활동을 대신 수행하여 보기 좋게 한 곳에 모아주는 기능이 바로 PERFORMANCE_SCHEMA 라고 생각하면 된다.

DBMS 의 곳곳에 숨어 있는 여러 수집 도구 용 코드들을 통해 성능 데이터를 수집하여 메모리에 쌓아두고, 이를 사용자가 SQL 을 통해 쉽게 조회, 집계해 볼 수 있도록 만들어 주는 도구

Oracle 류의 많은 RDBMS 들은 performance view 라는 형태로 성능 도구를 지원한다. 주로 'v$뷰이름' 형태로 제공하며 이러한 뷰는 데이터베이스 생성 시 자동으로 생성되기도 하지만, 어떤 DBMS 는 performance view 를 생성하기 위한 스크립트를 패키지에 포함하여 사용자가 직접 생성하도록 유도하기도 한다.
어쨌건 performance view 이든 performance schema 이든 성능 분석을 위한 도구인 것은 동일하다.
(수집 방법은 다를 수 있다.)

참고로 성능 스키마(Performance Schema)의 스키마라는 용어에 대해 잠깐 짚고 넘어가는 것이 좋겠다.
스키마라는 것은 파일시스템의 디렉토리와 유사하다고 생각하면 된다. DBMS 의 각종 오브젝트들을 파일이라고 생각하면 스키마는 그 파일들을 담아두기 위한 디렉토리와 같은 개념이다. 그런데, 주의할 것은 이러한 스키마에 대한 개념이 DBMS 마다 좀 다르다는 것이다.
보통 스키마라라는 것이 아래의 3 가지 개념 정도로 사용되는데 MySQL 은 3 번째 경우에 해당된다.

1. 스키마는 유저가 만들 수 있는 오브젝트 중의 하나이다.
   - 한 유저가 다수의 스키마를 만들 수 있고(1:N), 그 안에 테이블 등의 오브젝트를 만들 수 있다. (디렉토리처럼)
   - 따라서, DB 엔진이 오브젝트 이름만을 가지고 실제 오브젝트에 접근하려면, 마치 리눅스에서 실행파일을 찾기 위해 PATH 환경변수에 등록된 디렉토리를 차례로 검색하듯이 내부적으로 스키마 path 를 검색해 들어가야 한다.
2. 스키마는 유저 당 하나만 자동으로 만들어지고 유저와 동일한 이름으로 동격으로 사용된다.
   - 스키마를 추가로 만들 수 없다(1:1).
3. 스키마 = 데이터베이스이다.

데이터베이스라고 하면 보통 다른 RDBMS 에서는 모든 것을 아우르는 가장 큰 개념인데, MySQL 에서는 데이터베이스라는 것이 오브젝트를 담을 수 있는 그릇 정도로 취급되어 너무나도 쉽게 생성, 삭제할 수 있다. 데이터베이스와 스키마가 같은 개념으로 사용되기 때문에 그렇다. (다른 DB 에 적응된 사람에게는 좀 혼동스럽기는 하지만...)
show databases 명령을 통해 볼 수 있는 mysql, information_schema, performance_schema, sys 등이 모두 사실은 스키마인 것이다.
유저에게 'CREATE' 권한이 있다면 이러한 스키마(데이터베이스)를 쉽게 생성 또는 삭제할 수 있다. 심지어 기본적으로 생성되어 있는 데이터베이스인 performance_schema, sys, mysql 조차도 쉽게 삭제할 수 있다(information_schema 는 삭제안됨). 하지만, 이런건 혼자 docker container 안에서만 실습해보고 실 서버에서는 진짜 되는지 해보겠다는 호기심을 버리자.
(참고로 performance_schema 데이터베이스의 경우는 실수로 삭제했다면 mysql_upgrade 명령어를 통해 복구할 수 있다.)
PERFORMANCE_SCHEMA 는 이렇게 오브젝트를 담을 수 있는 스키마이고, 그 안에는 각종 성능 관련 테이블 오브젝트들이 생성되어 있다.

스키마를 아래처럼 쉽게 삭제할 수 있다.

root@localhost:information_schema 20:01:46> use information_schema;
Database changed
root@localhost:information_schema 20:01:49> select * from user_privileges where privilege_type like '%CREATE%'...;
+--------------------+---------------+-------------------------+--------------+
| GRANTEE            | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+--------------------+---------------+-------------------------+--------------+
| 'root'@'localhost' | def           | CREATE                  | YES          |
+--------------------+---------------+-------------------------+--------------+

root@localhost:(none) 20:11:16> drop database sys;
Query OK, 101 rows affected (0.02 sec)

root@localhost:(none) 20:11:37> drop database mysql;
Query OK, 31 rows affected, 2 warnings (0.05 sec)

root@localhost:(none) 20:11:45> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

root@localhost:(none) 20:11:20> drop database information_schema;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

MySQL 에서 스키마는 데이터베이스와 동일한 개념이므로 show databases 와 show schemas 명령의 결과가 정확히 같은 것을 볼 수 있다.

root@localhost:information_schema 20:20:10> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

root@localhost:information_schema 20:20:12> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

INFORMATION_SCHEMA 의 TABLES 테이블을 통해 MySQL 5.7 에 존재하는 각 스키마의 모든 테이블들을 조회해보면 아래와 같은 결과가 나온다. 조회 건수가 많기 때문에 스키마 당 몇 건씩만 남겼다.
table_type 항목을 주의 깊게 살펴보면 각 스키마 별로 생성되어 있는 테이블들의 type 이 다르다는 것을 알 수 있다.

root@localhost:information_schema 20:28:48> select table_schema, table_name, table_type from tables;
+--------------------+------------------------------------------------------+-------------+
| table_schema       | table_name                                           | table_type  |
+--------------------+------------------------------------------------------+-------------+
| information_schema | CHARACTER_SETS                                       | SYSTEM VIEW |
| information_schema | COLLATIONS                                           | SYSTEM VIEW |
...
| information_schema | INNODB_SYS_COLUMNS                                   | SYSTEM VIEW |
| information_schema | INNODB_SYS_FOREIGN                                   | SYSTEM VIEW |
| information_schema | INNODB_SYS_TABLESTATS                                | SYSTEM VIEW |
| mysql              | columns_priv                                         | BASE TABLE  |
| mysql              | db                                                   | BASE TABLE  |
...
| mysql              | time_zone_transition_type                            | BASE TABLE  |
| mysql              | user                                                 | BASE TABLE  |
| performance_schema | accounts                                             | BASE TABLE  |
| performance_schema | cond_instances                                       | BASE TABLE  |
...
| performance_schema | users                                                | BASE TABLE  |
| performance_schema | variables_by_thread                                  | BASE TABLE  |
| sys                | host_summary                                         | VIEW        |
| sys                | host_summary_by_file_io                              | VIEW        |
...
| sys                | sys_config                                           | BASE TABLE  |
...
| sys                | x$waits_by_user_by_latency                           | VIEW        |
| sys                | x$waits_global_by_latency                            | VIEW        |
+--------------------+------------------------------------------------------+-------------+

각 스키마 별로 기본적으로 생성된 table 들의 table_type 을 정리해 보면 아래와 같다.
information_schema 의 경우 drop database 명령으로 삭제가 안된다고 했는데 이는 SYSTEM VIEW 타입이기 때문이다. 가장 중요한 Meta 정보들을 보여주는 View 이다. 일반 사용자에게 all privileges on *.* 권한을 주더라도 삭제할 수가 없다.
오늘의 주제인 PERFORMANCE_SCHEMA 은 BASE TABLE(보통 우리가 생각하는 물리적인 테이블이라고 생각하면 된다)들로 구성되어 있고 이러한 BASE TABLE 들은 빠르게 구축, 조회할 수 있어야 하므로 Memory 에 저장된다.

• information_schema : SYSTEM VIEW
• mysql : BASE TABLE
• performance_schema : BASE TABLE
• sys : VIEW or BASE TABLE

그러면 PERFORMANCE_SCHEMA 는 Memory Storage Engine 에 구축이 되는 것일까?
그렇지 않다.
아래 쿼리 결과에서 engine 항목을 보면 PERFORMANCE_SCHEMA 로 나오는데, 이는 performance_schema 내의 table 들을 구축하기 위해 PERFORMANCE_SCHEMA 라는 별도의 전용 storage engine 을 사용한다는 것을 알 수 있다. (스키마 이름과 storage engine 이름이 동일하다.)

root@localhost:information_schema 20:53:49> select table_schema, table_name, table_type, engine from tables where table_schema = 'performance_schema' limit 2;
+--------------------+----------------+------------+--------------------+
| table_schema       | table_name     | table_type | engine             |
+--------------------+----------------+------------+--------------------+
| performance_schema | accounts       | BASE TABLE | PERFORMANCE_SCHEMA |
| performance_schema | cond_instances | BASE TABLE | PERFORMANCE_SCHEMA |
+--------------------+----------------+------------+--------------------+

show engines 명령어로 확인해봐도 PERFORMANCE_SCHEMA 라는 엔진이 어엿하게 하나의 별도 엔진으로 등록되어 있다. (select * from information_schema.engines 로도 똑같이 확인할 수 있다.)
Transactions 항목을 보면 NO 로 되어 있으므로 SQL 을 사용할 수는 있지만 InnoDB 와는 다르게 Transaction 은 지원하지 않는다는 것을 알 수 있다. (재기동되면 리셋되고 복구도 할 수 없다.)

root@localhost:information_schema 20:53:50> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

information_schema 와 performance_schema 의 차이점은 무엇일까?
테이블을 통해 여러 종류의 정보들을 조회할 수 있다는 점에서는 서로 유사하지만 그 용도와 처리 구조는 완전히 다르다.
다음 표를 보자. (출처 : MySQL 5.7 완전분석)

위의 표를 통해 중요한 사실들 몇 가지를 정리해볼 수 있다.

• information_schema 는 표준에 정의되어 일정한 기준이 있는 반면, performance_schema 는 DBMS 독자적으로 자기 방식대로 제공할 수 있다.
• performance schema 는 별도의 스토리지 엔진을 사용하여 데이터를 저장해두는 구조이고, information schema 는 API 호출로 실시간 처리되는 구조이다.
• performance schema 는 정보의 수집을 평상 시 수집 대상 코드가 수행 될 때 마다 수행하여 Base Table 로 쌓는데 반해, information schema 는 테이블에 접근 시 그 때 그 때 API 에 의해 메타 데이터가 수집되어 View 로 제공된다.
  따라서, 평상 시에 performance schema 는 오버헤드가 발생할 수 있는데 반해 information schema 는 조회하지 않으면 오버헤드가 발생할 일이 없다.
• performance schema 는 이미 정보가 평상 시에 Base Table 에 축적되어 있으므로 출력할 때는 Memory Table 을 조회하는 비용 정도가 소요되는 반면, information schema 는 조회 시에 API 내부에서 여기 저기 정보들을 수집하는 행위를 할 것이므로 출력 시의 오버헤드가 때에 따라 커질 수 있다.

성능 스키마 수집 구조

--- 작성 중 ---

성능 스키마 사용방법

기본적인 이론 배경은 쌓았으니 이제 사용법을 알아보자.
performance schema 와 그 테이블들은 기본적으로 설치 시에 생성이 되고 기동할 때 자동으로 활성화된다. 따라서, 별도로 활성화를 위해 무언가를 할 필요는 없다.
다만 기본 설정 상태에서 수집되는 데이터는 일부분만 활성화되어 있다.
일부분만 활성화되어 있다? 그렇다면 사용자가 수집되는 데이터의 종류를 마음대로 설정할 수 있다는 말인가?
결론적으로 그렇다.

전체 데이터를 수집하게 설정한다면 오버 헤드때문에 문제가 될 수 있지만, 기본 설정에 추가적으로 필요하다고 생각되는 요소들에 대해서는 활성화를 고려해야 한다.

--- 작성 중 ---

References
개발자와 DBA 를 위한 Real MySQL
MySQL 5.7 완벽분석
MySQL 5.7 Reference Manual
MySQL 8.0 Reference Manual
MySQL performance-schema-instruments 사용에 따른 성능 영향 실험
MySQL Performance Schema in Action: the Complete Tutorial