MySQL 모니터링 쿼리 Monitoring Query

MySQL 모니터링 쿼리 Monitoring Query

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_SizeTop 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;


Index

명칭설명요구사항쿼리
FullText_IndexFULLTEXT 인덱스 목록MySQL v5.0SELECT
  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 ;


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;

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;


Object

명칭설명요구사항쿼리
Non-InnoDB_Tables_CountInnodb가 아닌 테이블 수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_PoolInnoDB 버퍼 풀에 가장 많은 페이지를 저장하는 테이블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_UKPrimary 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_ConstraintsPrimary 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_ConstraintsFK 제약 조건 테이블 찾기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;


Performance

명칭설명요구사항쿼리
Performance_Schema_EventsPerformance_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_RatioPrimary Key RatioMySQL 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;


Cluster

명칭설명요구사항쿼리
Cluster_Data_Free사용 가능한 데이터 메모리의 백분율MySQL Cluster v7.1.3SELECT
    @ 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.3SELECT
  @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.3SELECT
  @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.3SELECT
  @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 ;


Percona

명칭설명요구사항쿼리
Percona_Active_Tables읽은 행과 변경된 행을 기반으로 한 상위 5개의 활성 테이블Percona based MySQL server v5.5 + and userstat=ONSELECT
  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’ ;

MySQL Monitoring Query 참고 URL : https://www.mysql.com/