모니터링용 SQL 모음

-- drop all from plancache; -- 필요시 누적된 plancache 삭제 SELECT last_executed, QUERY_TEXT FROM information_schema.PLANCACHE order by last_executed desc limit 40;

SELECT IP_ADDR, PORT, TYPE, NUM_CPUS, MAX_MEMORY_MB, MAX_TABLE_MEMORY_MB, MEMORY_USED_MB, TABLE_MEMORY_USED_MB, TOTAL_DATA_DISK_MB, AVAILABLE_DATA_DISK_MB, UPTIME, VERSION FROM INFORMATION_SCHEMA.MV_NODES ORDER BY TYPE DESC, PORT;

-- Columnstore table을 보유한 database만 DISK 용량 확인 SELECT DATABASE_NAME, SUM(COMPRESSED_SIZE) AS diskUsage FROM INFORMATION_SCHEMA.COLUMNAR_SEGMENTS GROUP BY DATABASE_NAME ORDER BY diskUsage DESC;

-- 각 테이블별 SELECT DATABASE_NAME, TABLE_NAME, SUM(COMPRESSED_SIZE) AS compressedSize, SUM(UNCOMPRESSED_SIZE) AS uncompressedSize FROM INFORMATION_SCHEMA.COLUMNAR_SEGMENTS GROUP BY DATABASE_NAME, TABLE_NAME ORDER BY 1, 2; -- 각 컬럼별 SELECT DATABASE_NAME, TABLE_NAME, COLUMN_NAME, SUM(COMPRESSED_SIZE) AS compressedSize, SUM(UNCOMPRESSED_SIZE) AS uncompressedSize FROM INFORMATION_SCHEMA.COLUMNAR_SEGMENTS GROUP BY DATABASE_NAME, TABLE_NAME, COLUMN_NAME ORDER BY 1, 2, 3;

SELECT DATABASE_NAME, TABLE_NAME, INDEX_NAME, SUM(COMPRESSED_SIZE) AS totalSize FROM INFORMATION_SCHEMA.MV_COLUMNAR_SEGMENT_INDEX GROUP BY DATABASE_NAME, TABLE_NAME, INDEX_NAME;

SELECT DATABASE_NAME, NUM_PARTITIONS, REMOTE_NAME != '' AS drReplica, IS_SYNC AS syncReplicated FROM INFORMATION_SCHEMA.DISTRIBUTED_DATABASES;

SELECT HOST, PORT, DATABASE_NAME, ROLE, POSITION, STATE, DETAILS FROM INFORMATION_SCHEMA.MV_CLUSTER_STATUS;

SELECT DATABASE_NAME, PIPELINE_NAME, sub.BATCH_STATE AS lastBatchState, FROM_UNIXTIME(sub.BATCH_START_UNIX_TIMESTAMP) AS lastBatchTimestamp, IFNULL(sub.BATCH_ROWS_WRITTEN, 0) AS lastBatchRowsWritten FROM ( SELECT DATABASE_NAME, PIPELINE_NAME, BATCH_STATE, BATCH_START_UNIX_TIMESTAMP, BATCH_ROWS_WRITTEN, ROW_NUMBER() OVER ( PARTITION BY DATABASE_NAME, PIPELINE_NAME ORDER BY BATCH_START_UNIX_TIMESTAMP DESC ) AS r FROM INFORMATION_SCHEMA.PIPELINES_BATCHES_METADATA) sub ORDER BY 1, 2, 4 DESC;

SELECT HOST AS host, PORT AS port, AVAILABILITY_GROUP AS availabilityGroup, PAIR_HOST AS pairHost, PAIR_PORT AS pairPort, STATE AS state, OPENED_CONNECTIONS AS openedConnections, AVERAGE_ROUNDTRIP_LATENCY AS averageLatency FROM INFORMATION_SCHEMA.LEAVES;

SELECT HOST, PORT, DATABASE_NAME, ORDINAL, ROLE, IS_OFFLINE, STATE AS syncState FROM INFORMATION_SCHEMA.DISTRIBUTED_PARTITIONS ORDER BY 1, 3, 4, 5;

SELECT DATABASE_NAME AS databaseName FROM INFORMATION_SCHEMA.DISTRIBUTED_DATABASES;

SELECT TABLE_SCHEMA as databaseName, STATISTICS.TABLE_NAME, STATISTICS.INDEX_NAME, SUM(MEMORY_USE) AS memoryUse, INDEX_TYPE AS indexType, COLUMN_NAME, SEQ_IN_INDEX, ANY_VALUE(NON_UNIQUE) AS nonUnique FROM information_schema.STATISTICS, information_schema.INDEX_STATISTICS WHERE STATISTICS.TABLE_SCHEMA = INDEX_STATISTICS.DATABASE_NAME AND STATISTICS.TABLE_NAME = INDEX_STATISTICS.TABLE_NAME AND STATISTICS.INDEX_NAME = INDEX_STATISTICS.INDEX_NAME GROUP BY STATISTICS.TABLE_SCHEMA, STATISTICS.TABLE_NAME, STATISTICS.INDEX_NAME, STATISTICS.COLUMN_NAME, STATISTICS.INDEX_TYPE ORDER BY 1, 2, 3, seq_in_index;

SELECT NUM_CPUS, MAX_MEMORY_MB, MAX_TABLE_MEMORY_MB, MEMORY_USED_MB, TABLE_MEMORY_USED_MB, TOTAL_DATA_DISK_MB, AVAILABLE_DATA_DISK_MB, UPTIME, VERSION FROM INFORMATION_SCHEMA.LMV_NODES; SELECT IP_ADDR, PORT, TYPE, NUM_CPUS, MAX_MEMORY_MB, MAX_TABLE_MEMORY_MB, MEMORY_USED_MB, TABLE_MEMORY_USED_MB, TOTAL_DATA_DISK_MB, AVAILABLE_DATA_DISK_MB, UPTIME, VERSION FROM INFORMATION_SCHEMA.MV_NODES ORDER BY TYPE DESC, PORT;

-- 주의) 모든 파티션 (Master/Slaver) 합산 : 클러스터 전체에 대한 현황 SELECT DATABASE_NAME, TABLE_NAME, STORAGE_TYPE, COUNT(*) AS numReplicas_or_Partitions, SUM(IFNULL(MEMORY_USE, 0)) AS memoryUse, SUM(CASE WHEN PARTITION_TYPE != '' THEN IFNULL(ROWS, 0) ELSE 0 END) AS rowCount FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE `DATABASE_NAME` NOT IN ('') GROUP BY DATABASE_NAME, TABLE_NAME ORDER BY 1, 2; -- 테이블 타입에 따른 row 계산 추가 예시 SELECT T.TABLE_NAME, T.STORAGE_TYPE, REPLACE(TS.PARTITION_TYPE, 'Master', '-') AS PARTITION_TYPE, TRUNC(SUM(TS.ROWS)/ (CASE TS.PARTITION_TYPE WHEN 'Reference' THEN COUNT(*) ELSE 1 END), 0) AS ROWS FROM INFORMATION_SCHEMA.TABLES T LEFT JOIN INFORMATION_SCHEMA.TABLE_STATISTICS TS ON T.TABLE_SCHEMA = TS.DATABASE_NAME AND T.TABLE_NAME = TS.TABLE_NAME WHERE T.TABLE_SCHEMA ='database_name' AND T.TABLE_NAME LIKE 'table_%' AND TS.PARTITION_TYPE <> 'Slave' GROUP BY T.TABLE_NAME, T.STORAGE_TYPE, TS.PARTITION_TYPE ORDER BY 1;

SELECT DATABASE_NAME, TABLE_NAME, FLOOR(AVG(ROWS)) AS avg_rows, ROUND(STDDEV(ROWS)/AVG(ROWS),3) * 100 AS row_skew, FLOOR(AVG(MEMORY_USE)) AS avg_memory, ROUND(STDDEV(MEMORY_USE)/AVG(MEMORY_USE),3) * 100 AS memory_skew FROM INFORMATION_SCHEMA.TABLE_STATISTICS GROUP BY 1, 2 HAVING SUM(ROWS) > 10000 ORDER BY row_skew DESC; -- 특정 테이블에 대한 상세 정보 SELECT DATABASE_NAME, TABLE_NAME, ORDINAL AS PARTITION_ID, ROWS, MEMORY_USE FROM INFORMATION_SCHEMA. WHERE TABLE_NAME = 'domains'; -- 특정 테이블의 파티션별 rows 조회 SELECT PARTITION_ID(), COUNT(*) FROM domains GROUP BY 1;