[MariaDB] MySQL 성능 모니터링

* MySQL 운영 환경에서 성능 저하를 방지하고 리소스를 효율적으로 모니터링하기 위한 다양한 명령어들이 있습니다.

* 각 명령어는 서버의 상태를 특정 측면에서 분석하며, 주로 트랜잭션 처리량, 메모리 사용량, 잠금 현황 등 성능에 직접적인 영향을 미치는 요소를 모니터링하는 데 사용됩니다.

MySQL 성능 모니터링을 위한 주요 명령어

• SHOW STATUS
  SHOW STATUS 명령어는 MySQL 서버의 일반적인 상태를 확인할 수 있게 해줍니다. 예를 들어 Connections(연결 수), Uptime(운영 시간), Questions(질의 수) 등의 지표를 제공하여 서버의 전체 상태를 파악하는 데 유용합니다. 특히 쿼리 수와 같은 요청 처리 빈도를 확인하여, 특정 시점에서의 부하 정도를 파악할 수 있습니다.

MariaDB [(none)]> SHOW STATUS LIKE 'Connections';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Connections   | 4668962 |
+---------------+---------+
1 row in set (0.012 sec)

MariaDB [(none)]> SHOW STATUS LIKE 'Uptime';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Uptime        | 40520620 |
+---------------+----------+
1 row in set (0.001 sec)

MariaDB [(none)]> SHOW STATUS LIKE 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 7     |
+---------------+-------+
1 row in set (0.001 sec)

 

• SHOW VARIABLES
  SHOW VARIABLES 명령어는 MySQL의 서버 설정 변수들을 조회할 수 있습니다. 서버의 동작을 결정하는 중요한 설정 값들을 조정하는 데 유용하며, key_buffer_size, innodb_buffer_pool_size, max_connections와 같은 성능 관련 변수들을 확인하고 최적화할 수 있습니다.

MariaDB [(none)]> show variables like 'innodb_buffer_pool_size';
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 26843545600 |
+-------------------------+-------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 20000 |
+-----------------+-------+
1 row in set (0.001 sec)

 

• SHOW PROCESSLIST
  SHOW PROCESSLIST는 현재 실행 중인 쿼리와 연결 상태를 보여줍니다. 각 연결의 상태를 확인할 수 있어서, 특정 쿼리가 지연되는지, 혹은 잠금 상태에 있는지 확인하는 데 유용합니다. 특정 사용자가 실행하는 장기 실행 쿼리를 감지하여 필요시 강제 종료할 수 있습니다.

  MariaDB [(none)]> show processlist;
  +---------+-------------+----------------------+---------------+---------+------+--------------------------+------------------+----------+
  | Id      | User        | Host                 | db            | Command | Time | State                    | Info             | Progress |
  +---------+-------------+----------------------+---------------+---------+------+--------------------------+------------------+----------+
  |       2 | system user |                      | NULL          | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
  |       1 | system user |                      | NULL          | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
  |       3 | system user |                      | NULL          | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
  |       4 | system user |                      | NULL          | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
  |       5 | system user |                      | NULL          | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |

   

• SHOW TABLE STATUS
  SHOW TABLE STATUS는 특정 데이터베이스에 있는 테이블의 상태를 확인할 수 있게 해줍니다. 각 테이블의 크기, 행 수, 자동 증가 값, 업데이트 시간 등을 확인할 수 있어 데이터베이스의 성능 최적화 및 용량 관리에 도움이 됩니다.

  SHOW TABLE STATUS FROM [데이터베이스명];
  
  SHOW TABLE STATUS FROM storage;
  MariaDB [(none)]> SHOW TABLE STATUS FROM storage;
  +--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+-----------------------------------+---------------------------------------------+------------------+-----------+
  | Name         | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options                    | Comment                                     | Max_index_length | Temporary |
  +--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+-----------------------------------+---------------------------------------------+------------------+-----------+
  | file_info    | InnoDB |      10 | Dynamic    |  9050205 |            245 |  2223259648 |               0 |   3605168128 | 500170752 |       35700973 | 2022-12-31 21:14:17 | 2024-08-20 18:35:57 | NULL       | utf8mb4_general_ci |     NULL | row_format=DYNAMIC                | 파일 정보                                   |                0 | N         |
  | file_log     | InnoDB |      10 | Compressed | 79370895 |             62 |  4935507968 |               0 |   2050572288 |  11534336 |           NULL | 2022-12-31 21:14:17 | 2024-08-20 18:35:57 | NULL       | utf8mb4_general_ci |     NULL | row_format=COMPRESSED partitioned | 파일 로그                                   |                0 | N         |
  | link_once    | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |        65536 |  23068672 |         815996 | 2022-12-31 21:14:17 | 2023-08-31 03:20:19 | NULL       | utf8mb4_general_ci |     NULL | row_format=DYNAMIC                | 일회용 파일 다운로드 링크 정보              |                0 | N         |
  | relay_info   | InnoDB |      10 | Compressed |        0 |              0 |        8192 |               0 |        32768 |   3145728 |         192625 | 2022-12-31 21:14:17 | NULL                | NULL       | utf8mb4_general_ci |     NULL | row_format=COMPRESSED             | 릴레이 정보                                 |                0 | N         |
  | thumbnail    | InnoDB |      10 | Dynamic    |  6166253 |            255 |  1575141376 |               0 |   1554448384 |  14680064 |       25909524 | 2022-12-31 21:14:17 | 2024-08-20 18:07:19 | NULL       | utf8mb4_general_ci |     NULL | row_format=DYNAMIC                | 기본 썸네일 정보                            |                0 | N         |
  | user_storage | InnoDB |      10 | Dynamic    |  9894310 |            252 |  2493628416 |               0 |   5185028096 | 114294784 |           NULL | 2022-12-31 21:14:17 | 2024-08-20 18:35:57 | NULL       | utf8mb4_general_ci |     NULL | row_format=DYNAMIC partitioned    | 사용자 스토리지 정보                        |                0 | N         |
  +--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+-----------------------------------+---------------------------------------------+------------------+-----------+
  6 rows in set (0.001 sec)

   

   
• Performance Schema
  Performance Schema는 MySQL에서 정교한 성능 모니터링과 분석을 가능하게 하는 스키마입니다. 쿼리 실행 계획, 잠금, 트랜잭션, 메모리 사용량 등에 대한 세부 데이터를 자동으로 수집하여 성능 문제를 심층적으로 분석할 수 있게 해줍니다. Performance Schema는 SQL 문법을 통해 데이터베이스처럼 질의할 수 있습니다.
   
  SELECT * FROM performance_schema.events_statements_current;
  SELECT * FROM performance_schema.threads;

 

• EXPLAIN
  EXPLAIN 명령어는 특정 쿼리의 실행 계획을 확인하여, 효율적으로 쿼리를 최적화할 수 있습니다. 예를 들어, 인덱스가 제대로 사용되고 있는지, 조인이 필요한지 등의 정보를 제공하여 쿼리 성능을 개선할 수 있습니다.
   
  EXPLAIN SELECT * FROM your_table WHERE [조건문];

 

📌 성능 저하를 예방하는 팁

• 주기적인 모니터링: SHOW STATUS, SHOW PROCESSLIST 같은 명령어로 시스템 상태를 주기적으로 확인하여 성능 문제를 사전에 감지합니다.
• 쿼리 최적화: 자주 실행되는 쿼리는 EXPLAIN으로 실행 계획을 분석하고, 인덱스를 사용하거나 쿼리 구조를 조정하여 최적화합니다.
• 시스템 설정 조정: SHOW VARIABLES로 확인한 서버 설정 변수들을 필요에 맞게 조정하여 메모리 및 연결 성능을 최적화합니다.