close
프로필 배경
프로필 로고

DBA 킴오리지날

Database/MYSQL ・2022. 10. 19. fullscreen 넓게보기

[MYSQL] 모니터링 쿼리

반응형
모니터링해야하는 주요 값들
https://experiences.tistory.com/24

 

MYSQL 모니터링 툴 종류 정리
https://otsteam.tistory.com/418

 

쿼리

1. DATA

명칭 설명 요구사항 쿼리
Data_Types 테이블별 데이터 타입의 개수 MySQL v5.0+ SELECT
  DATA_TYPE AS Data_Type,
  COUNT(1) AS Number
FROM
  information_schema.COLUMNS
WHERE TABLE_SCHEMA NOT IN (‘information_schema’, ‘mysql’,’performance_schema’)
GROUP BY Data_Type
ORDER BY Number
Database_Size Top 5 데이터베이스의 사이즈 MySQL v5.0+ SELECT
  TABLE_SCHEMA  AS Database,
  SUM(
    (data_length + index_length) / (1024 * 1024)
  ) AS Database_Size
FROM
  information_schema.TABLES
GROUP BY table_schema
ORDER BY Database_Size DESC
LIMIT 5;
Diskinfo 테이블명 및 보유한 레코드 수 MySQL v5.0+ SELECT
  TABLE_SCHEMA AS Database,
  TABLE_NAME AS Table,
  TABLE_ROWS AS Rows
FROM
  information_schema.TABLES;

 

2. Index

명칭 설명 요구사항 쿼리
FullText_Index FULLTEXT 인덱스 목록 MySQL v5.0 SELECT
  TABLE_SCHEMA AS Table_Schema,
  TABLE_NAME AS Table_Name,
  INDEX_NAME AS Index_Name,
  SEQ_IN_INDEX AS Seq_In_Index,
  COLUMN_NAME AS Column_Name,
  CARDINALITY AS Cardinality,
  NULLABLE AS Nullable
FROM
  information_schema.STATISTICS
WHERE INDEX_TYPE LIKE ‘FULLTEXT%’
ORDER BY TABLE_SCHEMA,
  TABLE_NAME;
Schema_Redundant_Index 불필요한 중복되는 인덱스 MySQL 5.7+ or MySQL 5.6+ SELECT *
FROM sys.schema_redundant_indexes LIMIT 5;
Unused_Indexes_In_Schema 테이블에서 사용되지 않은 인덱스 MySQL 5.7+ or MySQL 5.6+ SELECT *
FROM sys.schema_unused_indexes limit 5;
WorstIndex 성능이 가장 떨어지는 상위 10개 인덱스 MySQL v5.0+ SELECT
  t.TABLE_SCHEMA AS Db,
  t.TABLE_NAME AS Table,
  s.INDEX_NAME AS Index_Name,
  s.COLUMN_NAME AS Field_Name,
  s.SEQ_IN_INDEX Seq_In_Index,
  s2.max_columns AS Max_Cols,
  s.CARDINALITY AS Card,
  t.TABLE_ROWS AS Est_rows,
  ROUND(
    (
      (
        s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)
      ) * 100
    ),
    2
  ) AS Sel %
FROM
  INFORMATION_SCHEMA.STATISTICS s
  INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND s.TABLE_NAME = t.TABLE_NAME
  INNER JOIN
    (SELECT
      TABLE_SCHEMA,
      TABLE_NAME,
      INDEX_NAME,
      MAX(SEQ_IN_INDEX) AS max_columns
    FROM
      INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA != ‘mysql’
    GROUP BY TABLE_SCHEMA,
      TABLE_NAME,
      INDEX_NAME) AS s2
    ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA
    AND s.TABLE_NAME = s2.TABLE_NAME
    AND s.INDEX_NAME = s2.INDEX_NAME
WHERE t.TABLE_SCHEMA != ‘mysql’
LIMIT 10 ;

 

3. host

명칭 설명 요구사항 쿼리
Host_Hitting_by_File_io 총 파일 I/O를 기반으로 서버에 도달한 호스트 MySQL 5.7+ or MySQL 5.6+ SELECT host
FROM sys.x$host_summary
ORDER BY file_ios DESC LIMIT 5;
Host_Hitting_by_Tablescans 테이블 스캔을 통해 서버에 도달한 호스트 MySQL 5.7+ or MySQL 5.6+ SELECT host
FROM sys.x$host_summary
ORDER BY table_scans DESC LIMIT 5;

 

4. User

명칭 설명 요구사항 쿼리
Users_Connected 현재 연결된 사용자 MySQL v5.0+ SELECT
  SUBSTRING_INDEX(HOST, ‘:’, 1) AS Host_Name,
  GROUP_CONCAT(DISTINCT USER) AS Users,
  COUNT(*) AS No_Of_Connections
FROM
  information_schema.PROCESSLIST
WHERE user != ‘system user’
GROUP BY Host_Name
ORDER BY No_Of_connections DESC ;
Users_Hitting_by_File_io 총 파일 I/O를 기준으로 서버에 접속한 사용자 MySQL 5.7+ or MySQL 5.6+ SELECT user
FROM sys.x$user_summary
ORDER BY file_ios DESC LIMIT 5;
Users_Hitting_by_Tablescans 테이블 스캔으로 서버에 접속한 사용자 MySQL 5.7+ or MySQL 5.6+ SELECT user
FROM sys.x$user_summary
ORDER BY table_scans DESC LIMIT 5;
Users_statements_executed 사용자가 실행한 총 명령문 수를 기반으로 한 사용자 MySQL 5.7+ or MySQL 5.6+ SELECT *
FROM sys.x$user_summary_by_statement_latency
ORDER BY total_latency DESC LIMIT 5;

 

5. Object

명칭 설명 요구사항 쿼리
Non-InnoDB_Tables_Count Innodb가 아닌 테이블 수 MySQL v5.0+ SELECT
COUNT(*) as count
FROM
INFORMATION_SCHEMA.TABLES
WHERE
ENGINE != ‘InnoDB’
AND TABLE_SCHEMA NOT IN (‘mysql’,’performance_schema’,
‘information_schema’);
Object_accessed_the_most 가장 많이 엑세스한 테이블 MySQL 5.7+ or MySQL 5.6+ SELECT table_schema,table_name
FROM sys.x$schema_table_statistics
ORDER BY io_read_latency DESC LIMIT 5;
Storage_Engine 테이블별 엔진 타입 MySQL v5.0+ SELECT
  ENGINE AS Engine,
  COUNT(*) AS Tables,
  CONCAT(
    ROUND(SUM(table_rows) / 1000000, 2),
    ‘M’
  ) AS Rows,
  CONCAT(
    ROUND(
      SUM(data_length) / (1024 * 1024 * 1024),
      2
    ),
    ‘G’
  ) AS Data,
  CONCAT(
    ROUND(
      SUM(index_length) / (1024 * 1024 * 1024),
      2
    ),
    ‘G’
  ) AS Index,
  CONCAT(
    ROUND(
      SUM(data_length + index_length) / (1024 * 1024 * 1024),
      2
    ),
    ‘G’
  ) AS Total_Size,
  ROUND(
    SUM(index_length) / SUM(data_length),
    2
  ) AS Index_Frac
FROM
  information_schema.TABLES
WHERE ENGINE != ‘NULL’
GROUP BY ENGINE
ORDER BY SUM(data_length + index_length) DESC ;
Table_InnoDB_Buffer_Pool InnoDB 버퍼 풀에 가장 많은 페이지를 저장하는 테이블 MySQL 5.7+ or MySQL 5.6+ SELECT object_schema,object_name
FROM sys.x$innodb_buffer_stats_by_table
ORDER BY pages DESC LIMIT 5;
Tables_Size 총 테이블 수와 크기 MySQL v5.0+ SELECT
  COUNT(*) AS Tables,
  CONCAT(
    ROUND(SUM(table_rows) / 1000000, 2),
    ‘M’
  ) AS Rows,
  CONCAT(
    ROUND(
      SUM(data_length) / (1024 * 1024 * 1024),
      2
    ),
    ‘G’
  ) AS Data,
  CONCAT(
    ROUND(
      SUM(index_length) / (1024 * 1024 * 1024),
      2
    ),
    ‘G’
  ) AS Index,
  CONCAT(
    ROUND(
      SUM(data_length + index_length) / (1024 * 1024 * 1024),
      2
    ),
    ‘G’
  ) AS Total_Size,
  ROUND(
    SUM(index_length) / SUM(data_length),
    2
  ) AS Index_Frac
FROM
  information_schema.TABLES ;
Tables_Without_PK_UK Primary Key / Unique Key가 없는 테이블 MySQL v5.0+ SELECT
      t.TABLE_SCHEMA,
      t.TABLE_NAME,
      ENGINE
FROM
      information_schema.TABLES t
      INNER JOIN information_schema.COLUMNS c
            ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
            AND t.TABLE_NAME = c.TABLE_NAME
            AND t.TABLE_SCHEMA NOT IN (
                  ‘performance_schema’,
                  ‘information_schema’,
                  ‘mysql’
            );
Tables_Without_Constraints Primary Key / Unique Key가 없는 테이블 수 MySQL v5.0+ SELECT
  COUNT(1) AS COUNT
FROM INFORMATION_SCHEMA.TABLES A
  LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
    ON A.TABLE_NAME=B.TABLE_NAME
    AND A.TABLE_SCHEMA =B.TABLE_SCHEMA
WHERE A.TABLE_SCHEMA NOT IN (‘mysql’,’information_schema’,’performance_schema’)
    AND B.TABLE_NAME IS NULL
    AND B.TABLE_SCHEMA is NULL;
Tables_FK_Constraints FK 제약 조건 테이블 찾기 MySQL v5.0+ SELECT referenced_table_name parent, table_name child, constraint_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE referenced_table_name IS NOT NULL
ORDER BY referenced_table_name;

 

6. Performance

명칭 설명 요구사항 쿼리
Performance_Schema_Events Performance_Schema 메트릭 MySQL v5.5+ SELECT
  EVENT_NAME,
  MAX_TIMER_READ,
  AVG_TIMER_READ,
  MAX_TIMER_WRITE,
  AVG_TIMER_WRITE,
  MAX_TIMER_MISC,
  AVG_TIMER_MISC
FROM performance_schema.file_summary_by_event_name;
Primary_Key_Ratio Primary Key Ratio MySQL v5.0+ SELECT
  @tblWithoutPk :=
  (SELECT
    COUNT()   FROM     information_schema.TABLES AS t     LEFT JOIN information_schema.KEY_COLUMN_USAGE AS c       ON t.TABLE_NAME = c.TABLE_NAME       AND c.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA   WHERE t.TABLE_SCHEMA NOT IN (‘information_schema’, ‘mysql’,’performance_schema’)     AND constraint_name IS NULL) AS Tables_Without_PK,   @tblwithPk :=   (SELECT     COUNT()
  FROM
    information_schema.TABLES AS t
    LEFT JOIN information_schema.KEY_COLUMN_USAGE AS c
      ON t.TABLE_NAME = c.TABLE_NAME
      AND c.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA
  WHERE t.TABLE_SCHEMA NOT IN (‘information_schema’, ‘mysql’,’performance_schema’)
    AND constraint_name = ‘PRIMARY’) AS Tables_With_PK,
  @tblwithPk / (@tblWithoutPk + @tblwithPk) * 100 AS Ratio ;
Tables_with_full_table_scans 쿼리 실행 시 전체 테이블 스캔을 수행하는 테이블 확인 MySQL 5.7+ or MySQL 5.6+ SELECT *
FROM sys.x$schema_tables_with_full_table_scans
ORDER BY rows_full_scanned DESC,latency DESC LIMIT 5;
Tables_Fragmentation 테이블 Fragmentation 확인 MySQL v5.0+ SELECT TABLE_NAME, (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS sizeMb,DATA_FREE / 1024 / 1024 AS data_free_MB
FROM information_schema.TABLES
WHERE ENGINE LIKE ‘InnoDB’
AND TABLE_SCHEMA = ‘db_name’
AND DATA_FREE > 100 * 1024 * 1024;

 

7. Cluster

명칭 설명 요구사항 쿼리
Cluster_Data_Free 사용 가능한 데이터 메모리의 백분율 MySQL Cluster v7.1.3 SELECT
    @ total_data_memory : = (
      SELECT
          SUM(total) AS Total_Memory
        FROM
          ndbinfo.memoryusage
        WHERE
          memory_type = ‘Data memory’
    ) AS Total_Data_Memory
    ,@ used_data_memory : = (
      SELECT
          SUM(used) Used_Memory
        FROM
          ndbinfo.memoryusage
        WHERE
          memory_type = ‘Data memory’
    ) AS Used_Data_Memory
    ,(@ total_data_memory – @ used_data_memory) / (@ total_data_memory) * 100 AS Percentage_Of_Free_Data_Memory;
Cluster_Nodes 실행되지 않는 노드의 수를 반환하고 모든 노드가 실행 중인 경우 0을 반환 MySQL Cluster v7.1.3 SELECT
  @total :=
  (SELECT
    VARIABLE_VALUE
  FROM
    information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = ‘NDB_NUMBER_OF_DATA_NODES’) AS Total,
  @running :=
  (SELECT
    VARIABLE_VALUE
  FROM
    information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = ‘NDB_NUMBER_OF_READY_DATA_NODES’) AS Running,
  @total – @running AS Nodes_Not_Running ;
Cluster_RedoBuffer 사용 가능한 로그 버퍼 크기의 백분율 MySQL Cluster v7.1.3 SELECT
  @total :=
  (SELECT
    SUM(total)
  FROM
    ndbinfo.logbuffers) AS Total,
  @used :=
  (SELECT
    SUM(used)
  FROM
    ndbinfo.logbuffers) AS Used,
  ((@total – @used) / @total) * 100 AS Free_Redo_Buffer;
Cluster_RedoLogspace 사용 가능한 로그 공간 크기의 백분율 MySQL Cluster v7.1.3 SELECT
  @total :=
  (SELECT
    SUM(total)
  FROM
    ndbinfo.logspaces) AS Total,
  @used :=
  (SELECT
    SUM(used)
  FROM
    ndbinfo.logspaces) AS Used,
  ((@total – @used) / @total) * 100 AS Free_Redo_Log_Space ;

 

8. Percona

명칭 설명 요구사항 쿼리
Percona_Active_Tables 읽은 행과 변경된 행을 기반으로 한 상위 5개의 활성 테이블 Percona based MySQL server v5.5 + and userstat=ON SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  ROWS_READ,
  ROWS_CHANGED,
  ROWS_CHANGED_X_INDEXES
FROM
  INFORMATION_SCHEMA.TABLE_STATISTICS
ORDER BY ROWS_CHANGED DESC
LIMIT 5 ;
Percona_Unused_Indexes 사용되지 않은 인덱스 목록 Percona based MySQL server v5.5+ SELECT DISTINCT
  s.TABLE_SCHEMA AS Table_Schema,
  s.TABLE_NAME AS Table_Name,
  s.INDEX_NAME AS Index_Name
FROM
  information_schema.STATISTICS s
  LEFT JOIN information_schema.INDEX_STATISTICS INDXS
    ON (
      s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA
      AND s.TABLE_NAME = INDXS.TABLE_NAME
      AND s.INDEX_NAME = INDXS.INDEX_NAME
    )
WHERE INDXS.TABLE_SCHEMA IS NULL
  AND s.TABLE_SCHEMA != ‘mysql’ ;

 

※참고사이트

https://nomadlee.com/mysql-monitoring-query/
반응형

'Database > MYSQL' 카테고리의 다른 글

[MYSQL] PMM 모니터링  (0) 2022.10.21
[MYSQL] MYSQL 아키텍처  (0) 2022.10.21
[MYSQL] Cold backup, Hot backup, Logical backup, Physical Bakcup  (0) 2022.10.12
[MYSQL] InnoDB vs MyISAM  (0) 2022.10.12
[MYSQL] Dump기능을 사용하여 백업, 복구  (0) 2022.10.12
Database/MYSQL 관련 글
더 보기
댓글

티스토리툴바