문서번호 : 11-729581
Document Information
•
최초 작성일 : 2022.06.20
•
최종 수정일 : 2022.06.20
•
이 문서는 아래 버전을 기준으로 작성되었습니다.
◦
SinglestoreDB : 7.8
Goal
SingleStore Studio 주요 화면에서 사용하는 SQL을 수집하는 방법과 SQL을 모아 정리한다.
Solution
주의
본 문서는 참고용으로 작성된 것이며, 특히 발췌된 SQL은 작성일 이전의 것이므로, 현재 버전에 맞지 않을 수 있습니다.
수집방법
1) 2개 창에 다음 화면을 연다.
1번 창: Studio 에서 [Active-Queries] 화면
2번 창: Studio 에서 SQL을 확인하려는 화면 (예, [Hosts])
2) 1번창에서 현재 Active Query 목록을 확인한다.
3) 2번창에서 오른쪽 위 다시읽기 아이콘을 클릭한다.
4) 1번창에서 새롭게 추가된 Active Query의 “Query Text”를 발췌한다.
5) 발췌한 Query Text에서 변수부가 있다면, 관련 값으로 바꾼다.
1. Studio - Hosts
1.1. Studio - Hosts 첫 화면
WITH cte_cpu AS
(
SELECT
IP_ADDR,
ANY_VALUE(TOTAL_USED_CUMULATIVE_NS) AS TOTAL_USED_CUMULATIVE_NS,
TIMESTAMP_NS,
NUM_CPUS
FROM INFORMATION_SCHEMA.MV_SYSINFO_CPU
GROUP BY IP_ADDR
),
cte_sleep AS (SELECT SLEEP(1) AS r)
SELECT
node.IP_ADDR AS hostAddress,
node.nodes,
cpu.NUM_CPUS AS CPUCores,
cpu.cpuUsage,
ROUND(mem.HOST_USED_B / mem.HOST_TOTAL_B * 100, 2) AS memoryUsage,
CONCAT(ROUND(mem.HOST_USED_B / 1073741824, 1), 'GB/', ROUND(mem.HOST_TOTAL_B / 1073741824, 1), 'GB') AS memoryUsageD,
ROUND(disk.MOUNT_USED_B / disk.MOUNT_TOTAL_B * 100, 2) AS diskUsage,
CONCAT(ROUND(disk.MOUNT_USED_B / 1073741824, 1), 'GB/', ROUND(disk.MOUNT_TOTAL_B / 1073741824, 1), 'GB') AS diskUsageD
FROM
(
SELECT
IP_ADDR,
CONCAT(SUM(IF(TYPE = 'MA', 1, 0)), ' MA, ', SUM(IF(TYPE = 'CA', 1, 0)), ' CA, ', SUM(IF(TYPE = 'LEAF', 1, 0)), ' LEAF') AS nodes
FROM INFORMATION_SCHEMA.MV_NODES
GROUP BY IP_ADDR
) AS node
INNER JOIN (SELECT IP_ADDR, HOST_TOTAL_B, HOST_USED_B FROM INFORMATION_SCHEMA.MV_SYSINFO_MEM GROUP BY IP_ADDR) AS mem ON node.IP_ADDR=mem.IP_ADDR
INNER JOIN (SELECT IP_ADDR, MOUNT_TOTAL_B, MOUNT_USED_B FROM INFORMATION_SCHEMA.MV_SYSINFO_DISK GROUP BY IP_ADDR) AS disk ON node.IP_ADDR=disk.IP_ADDR
INNER JOIN
(
SELECT
a.IP_ADDR,
a.NUM_CPUS,
ROUND((a.TOTAL_USED_CUMULATIVE_NS - b.TOTAL_USED_CUMULATIVE_NS) / (a.TIMESTAMP_NS - b.TIMESTAMP_NS) / a.NUM_CPUS * 100, 2) AS cpuUsage
FROM
(SELECT t.* FROM cte_cpu AS t LEFT JOIN cte_sleep AS s ON s.r = 0) AS a
LEFT JOIN cte_cpu AS b ON a.IP_ADDR = b.IP_ADDR
) AS cpu ON node.IP_ADDR=cpu.IP_ADDR
ORDER BY node.IP_ADDR;
SQL
복사
1.2. Studio - Hosts -> Host 선택
-- Studio - Nodes 첫 화면과 비슷함(Partition Instance Status 미포함)
WITH cte_cpu AS
(
SELECT
IP_ADDR,
PORT,
MEMSQL_TOTAL_CUMULATIVE_NS,
TIMESTAMP_NS,
NUM_CPUS
FROM INFORMATION_SCHEMA.MV_SYSINFO_CPU
),
cte_sleep AS (SELECT SLEEP(1) AS r)
SELECT
CONCAT(node.IP_ADDR, ':', node.PORT) AS nodeAddress,
node.STATE,
node.TYPE AS role,
cpu.cpuUsage,
ROUND(mem.MEMSQL_B / mem.HOST_TOTAL_B * 100, 2) AS memoryUsage,
CONCAT(ROUND(mem.MEMSQL_B / 1073741824, 1), 'GB/', ROUND(mem.HOST_TOTAL_B / 1073741824, 1), 'GB') AS memoryUsageD,
ROUND(disk.MOUNT_USED_B / disk.MOUNT_TOTAL_B * 100, 2) AS diskUsage,
CONCAT(ROUND(disk.MOUNT_USED_B / 1073741824, 1), 'GB/', ROUND(disk.MOUNT_TOTAL_B / 1073741824, 1), 'GB') AS diskUsageD
FROM INFORMATION_SCHEMA.MV_NODES AS node
INNER JOIN INFORMATION_SCHEMA.MV_SYSINFO_MEM AS mem ON node.IP_ADDR=mem.IP_ADDR AND node.PORT=mem.PORT
INNER JOIN INFORMATION_SCHEMA.MV_SYSINFO_DISK AS disk ON node.IP_ADDR=disk.IP_ADDR AND node.PORT=disk.PORT
INNER JOIN
(
SELECT
a.IP_ADDR,
a.PORT,
ROUND((a.MEMSQL_TOTAL_CUMULATIVE_NS - b.MEMSQL_TOTAL_CUMULATIVE_NS) / (a.TIMESTAMP_NS - b.TIMESTAMP_NS) / a.NUM_CPUS * 100, 2) AS cpuUsage
FROM
(SELECT t.* FROM cte_cpu AS t LEFT JOIN cte_sleep AS s ON s.r = 0) AS a
LEFT JOIN cte_cpu AS b ON a.IP_ADDR = b.IP_ADDR AND a.PORT=b.PORT
) AS cpu ON node.IP_ADDR=cpu.IP_ADDR AND node.PORT=cpu.PORT
ORDER BY CASE node.TYPE WHEN 'MA' THEN 1 WHEN 'CA' THEN 2 WHEN 'LEAF' THEN 3 ELSE 9 END, node.IP_ADDR, node.PORT;
SQL
복사
1.3. Studio - Hosts -> Host 선택 (HOST INFO: Network I/O)
WITH cte_net AS
(
SELECT
IP_ADDR,
INTERFACE,
ANY_VALUE(RECEIVED_CUMULATIVE_B) AS RECEIVED_CUMULATIVE_B,
TRANSMITTED_CUMULATIVE_B,
TIMESTAMP_NS
FROM
(
SELECT
IP_ADDR,
INTERFACE,
FIRST_VALUE(RECEIVED_CUMULATIVE_B) OVER w AS RECEIVED_CUMULATIVE_B,
FIRST_VALUE(TRANSMITTED_CUMULATIVE_B) OVER w AS TRANSMITTED_CUMULATIVE_B,
FIRST_VALUE(TIMESTAMP_NS) OVER w AS TIMESTAMP_NS
FROM INFORMATION_SCHEMA.MV_SYSINFO_NET
WHERE TRANSMITTED_CUMULATIVE_B > 0
WINDOW w AS (PARTITION BY IP_ADDR, INTERFACE ORDER BY TIMESTAMP_NS DESC)
)
GROUP BY IP_ADDR, INTERFACE
),
cte_sleep AS (SELECT SLEEP(1) AS r)
SELECT
a.IP_ADDR,
ROUND(SUM((a.RECEIVED_CUMULATIVE_B - b.RECEIVED_CUMULATIVE_B) / ((a.TIMESTAMP_NS - b.TIMESTAMP_NS) / 1000000000)), 2) AS received,
ROUND(SUM((a.TRANSMITTED_CUMULATIVE_B - b.TRANSMITTED_CUMULATIVE_B) / ((a.TIMESTAMP_NS - b.TIMESTAMP_NS) / 1000000000)), 2) AS transmitted
FROM
(SELECT t.* FROM cte_net AS t LEFT JOIN cte_sleep AS s ON s.r = 0) AS a
LEFT JOIN cte_net AS b ON a.IP_ADDR = b.IP_ADDR AND a.INTERFACE = b.INTERFACE
GROUP BY a.IP_ADDR;
SQL
복사
1.4. Studio - Hosts -> Host 선택 (HOST INFO: Disk I/O)
WITH cte_disk AS
(
SELECT
IP_ADDR,
PORT,
ANY_VALUE(READ_CUMULATIVE_B) AS READ_CUMULATIVE_B,
WRITE_CUMULATIVE_B,
TIMESTAMP_NS
FROM
(
SELECT
IP_ADDR,
PORT,
FIRST_VALUE(READ_CUMULATIVE_B) OVER w AS READ_CUMULATIVE_B,
FIRST_VALUE(WRITE_CUMULATIVE_B) OVER w AS WRITE_CUMULATIVE_B,
FIRST_VALUE(TIMESTAMP_NS) OVER w AS TIMESTAMP_NS
FROM INFORMATION_SCHEMA.MV_SYSINFO_DISK
WINDOW w AS (PARTITION BY IP_ADDR, PORT ORDER BY TIMESTAMP_NS DESC)
)
GROUP BY IP_ADDR, PORT
),
cte_sleep AS (SELECT SLEEP(1) AS r)
SELECT a.IP_ADDR,
ROUND(SUM((a.READ_CUMULATIVE_B - b.READ_CUMULATIVE_B) / ((a.TIMESTAMP_NS - b.TIMESTAMP_NS) / 1000000000)), 2) AS readB,
ROUND(SUM((a.WRITE_CUMULATIVE_B - b.WRITE_CUMULATIVE_B) / ((a.TIMESTAMP_NS - b.TIMESTAMP_NS) / 1000000000)), 2) AS writeB
FROM
(SELECT t.* FROM cte_disk AS t LEFT JOIN cte_sleep AS s ON s.r = 0) AS a
LEFT JOIN cte_disk AS b ON a.IP_ADDR = b.IP_ADDR AND a.PORT = b.PORT
GROUP BY a.IP_ADDR;
SQL
복사
2. Studio - Nodes
2.1. Studio - Nodes 첫 화면
WITH cte_cpu AS
(
SELECT
IP_ADDR,
PORT,
MEMSQL_TOTAL_CUMULATIVE_NS,
TIMESTAMP_NS,
NUM_CPUS
FROM INFORMATION_SCHEMA.MV_SYSINFO_CPU
),
cte_sleep AS (SELECT SLEEP(1) AS r)
SELECT
CONCAT(node.IP_ADDR, ':', node.PORT) AS nodeAddress,
node.STATE,
IF(ISNULL(cst.offCnt), 'online', 'offline') AS PartitionInstanceStatus,
node.TYPE AS role,
cpu.cpuUsage,
ROUND(mem.MEMSQL_B / mem.HOST_TOTAL_B * 100, 2) AS memoryUsage,
CONCAT(ROUND(mem.MEMSQL_B / 1073741824, 1), 'GB/', ROUND(mem.HOST_TOTAL_B / 1073741824, 1), 'GB') AS memoryUsageD,
ROUND(disk.MOUNT_USED_B / disk.MOUNT_TOTAL_B * 100, 2) AS diskUsage,
CONCAT(ROUND(disk.MOUNT_USED_B / 1073741824, 1), 'GB/', ROUND(disk.MOUNT_TOTAL_B / 1073741824, 1), 'GB') AS diskUsageD
FROM INFORMATION_SCHEMA.MV_NODES AS node
INNER JOIN INFORMATION_SCHEMA.MV_SYSINFO_MEM AS mem ON node.IP_ADDR=mem.IP_ADDR AND node.PORT=mem.PORT
INNER JOIN INFORMATION_SCHEMA.MV_SYSINFO_DISK AS disk ON node.IP_ADDR=disk.IP_ADDR AND node.PORT=disk.PORT
LEFT JOIN
(
SELECT
HOST,
PORT,
COUNT(*) AS offCnt
FROM INFORMATION_SCHEMA.MV_CLUSTER_STATUS
WHERE ROLE = 'master' AND STATE <> 'online'
) AS cst ON node.IP_ADDR=cst.HOST AND node.PORT=cst.PORT
INNER JOIN
(
SELECT
a.IP_ADDR,
a.PORT,
ROUND((a.MEMSQL_TOTAL_CUMULATIVE_NS - b.MEMSQL_TOTAL_CUMULATIVE_NS) / (a.TIMESTAMP_NS - b.TIMESTAMP_NS) / a.NUM_CPUS * 100, 2) AS cpuUsage
FROM
(SELECT t.* FROM cte_cpu AS t LEFT JOIN cte_sleep AS s ON s.r = 0) AS a
LEFT JOIN cte_cpu AS b ON a.IP_ADDR = b.IP_ADDR AND a.PORT=b.PORT
) AS cpu ON node.IP_ADDR=cpu.IP_ADDR AND node.PORT=cpu.PORT
ORDER BY CASE node.TYPE WHEN 'MA' THEN 1 WHEN 'CA' THEN 2 WHEN 'LEAF' THEN 3 ELSE 9 END, node.IP_ADDR, node.PORT;
SQL
복사
2.2. Studio - Nodes - Node 선택
-- where절 제거시 모든 Node의 partition별 정보
SELECT
HOST,
PORT,
IF(METADATA_ROLE='Reference', 'Reference Partition Instance', 'Data Partition Instance') AS type,
STATE,
DATABASE_NAME AS partitions,
ROLE,
POSITION
FROM INFORMATION_SCHEMA.MV_CLUSTER_STATUS
WHERE HOST='node1' AND PORT='3308'
ORDER BY 1, 2, 3, 4, 5;
SQL
복사
3. Studio - Databases
3.1. Studio - Databases 첫 화면
SELECT
dd.DATABASE_NAME,
IF(dd.NUM_PARTITIONS = mcs.DISTCNT AND mcs.REFCNT = 1, 'online', IF(OFFCNT > 0, 'offline', 'replicating')) AS status,
IFNULL(ts.TABLECOUNT, 0) AS tableCount,
dd.NUM_PARTITIONS AS partitionCount,
IFNULL(ts.memoryUsage, 0) AS memoryUsage,
IFNULL(cs.diskUsage, 0) AS diskUsage
FROM INFORMATION_SCHEMA.DISTRIBUTED_DATABASES AS dd
INNER JOIN
(
SELECT
DATABASE_NAME,
SUM(IF(STATE='online' AND TYPE>0, 1, 0)) AS DISTCNT,
SUM(IF(STATE='online' AND TYPE=0, 1, 0)) AS REFCNT,
SUM(IF(STATE='offline', 1, 0)) AS OFFCNT,
SUM(IF(STATE='replicating', 1, 0)) AS REPLICNT
FROM
(
SELECT
REGEXP_REPLACE(DATABASE_NAME, '_[0-9]*$', '') AS DATABASE_NAME,
REGEXP_INSTR(DATABASE_NAME, '_[0-9]*$') AS TYPE,
STATE
FROM INFORMATION_SCHEMA.MV_CLUSTER_STATUS
WHERE ROLE = 'master'
)
GROUP BY DATABASE_NAME
) AS mcs ON dd.DATABASE_NAME = mcs.DATABASE_NAME
LEFT JOIN
(
SELECT
DATABASE_NAME,
SUM(MEMORY_USE) AS memoryUsage,
COUNT(DISTINCT TABLE_NAME) AS tableCount,
COUNT(DISTINCT HOST, PORT) AS nodesUsed
FROM INFORMATION_SCHEMA.TABLE_STATISTICS
GROUP BY DATABASE_NAME
) AS ts ON dd.DATABASE_NAME = ts.DATABASE_NAME
LEFT JOIN
(
SELECT
DATABASE_NAME,
SUM(COMPRESSED_SIZE) AS diskUsage
FROM INFORMATION_SCHEMA.COLUMNAR_SEGMENTS
GROUP BY DATABASE_NAME
) AS cs ON dd.DATABASE_NAME = cs.DATABASE_NAME;
SQL
복사
3.2.Studio - Databases -> database 선택: 상단 database 요약 정보
-- where절 제거시 모든 database의 요약정보
SELECT
dd.DATABASE_NAME,
IFNULL(ts.memoryUsage, 0) AS memoryUsage,
IFNULL(cs.diskUsage, 0) AS diskUsage,
dd.NUM_PARTITIONS AS partitionCount,
IFNULL(ts.tableCount, 0) AS tableCount,
IF(dd.IS_SYNC=0, 'Asynchronous', 'Synchronous') AS replicationType,
IF(dd.REMOTE_NAME='', 'Database is not a secondary replica', REMOTE_NAME)AS drReplica,
CONCAT(IFNULL(ts.nodesUsed, 0), ' / ', mn.totalNodes) AS nodesUsed
FROM INFORMATION_SCHEMA.DISTRIBUTED_DATABASES AS dd
LEFT JOIN (SELECT COUNT(*) AS totalNodes FROM INFORMATION_SCHEMA.MV_NODES WHERE TYPE='LEAF') AS mn ON 1 = 1
LEFT JOIN
(
SELECT
DATABASE_NAME,
SUM(MEMORY_USE) AS memoryUsage,
COUNT(DISTINCT TABLE_NAME) AS tableCount,
COUNT(DISTINCT IF(NODE_TYPE<>'Leaf', NULL, HOST), PORT) AS nodesUsed
FROM INFORMATION_SCHEMA.TABLE_STATISTICS
-- WHERE DATABASE_NAME='sem'
GROUP BY DATABASE_NAME
) AS ts ON dd.DATABASE_NAME = ts.DATABASE_NAME
LEFT JOIN
(
SELECT
DATABASE_NAME,
SUM(COMPRESSED_SIZE) AS diskUsage
FROM INFORMATION_SCHEMA.COLUMNAR_SEGMENTS
-- WHERE DATABASE_NAME='sem'
GROUP BY DATABASE_NAME
) AS cs ON dd.DATABASE_NAME = cs.DATABASE_NAME
WHERE dd.DATABASE_NAME='sem';
SQL
복사
3.3. Studio - Databases -> database 선택: 하단 Schema 정보
-- where절 제거시 모든 database의 table별 상세정보
SELECT
ts.TABLE_NAME,
IF(isRef, CONCAT(ts.STORAGE_TYPE, '(Ref)'), ts.STORAGE_TYPE) AS STORAGE_TYPE,
-- ts.numReplicas_or_Partitions,
IF(isRef, trunc(ts.rowCount/ts.numReplicas_or_Partitions), ts.rowCount) AS rowCount,
ts.memoryUse,
IFNULL(cs.compressedSize, '-') AS diskUsage,
-- IFNULL(cs.uncompressedSize, '-') AS uncompressedSize,
IF(ISNULL(cs.compressedSize), '-', ROUND((1 - cs.compressedSize/cs.uncompressedSize) * 100, 2)) AS compressionRatio
FROM
(
SELECT
TABLE_NAME, IF(PARTITION_TYPE = 'Reference', 1, 0) AS isRef,
SUBSTRING_INDEX(STORAGE_TYPE, '_', -1) AS STORAGE_TYPE, COUNT(*) AS numReplicas_or_Partitions,
SUM(MEMORY_USE) AS memoryUse, SUM(ROWS) AS rowCount
FROM INFORMATION_SCHEMA.TABLE_STATISTICS
WHERE DATABASE_NAME = 'sem'
GROUP BY TABLE_NAME
) AS ts
LEFT JOIN
(
SELECT
TABLE_NAME,
SUM(COMPRESSED_SIZE) AS compressedSize,
SUM(UNCOMPRESSED_SIZE) AS uncompressedSize
FROM INFORMATION_SCHEMA.COLUMNAR_SEGMENTS
WHERE DATABASE_NAME = 'sem'
GROUP BY TABLE_NAME
) AS cs
ON ts.TABLE_NAME = cs.TABLE_NAME
ORDER BY 1;
SQL
복사
3.4. Studio - Databases -> database -> table 선택
-- where절 제거시 모든 table의 column별 상세정보
SELECT
col.TABLE_SCHEMA AS DATABASE_NAME,
col.TABLE_NAME,
-- col.ORDINAL_POSITION,
col.COLUMN_NAME,
col.COLUMN_TYPE,
col.computed,
cst.memoryUsage,
IFNULL(seg.compressedSize, '0') AS diskUsage,
IF(ISNULL(seg.compressedSize), '-', ROUND((1 - seg.compressedSize/seg.uncompressedSize) * 100, 2)) AS compressionRatio,
col.COLUMN_DEFAULT
FROM
(
SELECT
TABLE_SCHEMA,
TABLE_NAME,
ORDINAL_POSITION,
COLUMN_NAME,
COLUMN_TYPE,
IF(EXTRA = 'computed', 'Yes', 'No') AS computed,
IFNULL(COLUMN_DEFAULT, '-') AS COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'sem' AND TABLE_NAME = 'SEM_SRC_DATA'
) AS col
LEFT JOIN
(
SELECT
DATABASE_NAME,
TABLE_NAME,
COLUMN_NAME,
IFNULL(SUM(MEMORY_USE), '0') AS memoryUsage
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE DATABASE_NAME = 'sem' AND TABLE_NAME = 'SEM_SRC_DATA'
GROUP BY 1, 2, 3
) AS cst
ON col.TABLE_SCHEMA = cst.DATABASE_NAME
AND col.TABLE_NAME = cst.TABLE_NAME
AND col.COLUMN_NAME = cst.COLUMN_NAME
LEFT JOIN
(
SELECT
DATABASE_NAME,
TABLE_NAME,
COLUMN_NAME,
SUM(COMPRESSED_SIZE) AS compressedSize,
SUM(UNCOMPRESSED_SIZE) AS uncompressedSize
FROM INFORMATION_SCHEMA.COLUMNAR_SEGMENTS
WHERE DATABASE_NAME = 'sem' AND TABLE_NAME = 'SEM_SRC_DATA'
GROUP BY 1, 2, 3
) AS seg
ON col.TABLE_SCHEMA = seg.DATABASE_NAME
AND col.TABLE_NAME = seg.TABLE_NAME
AND col.COLUMN_NAME = seg.COLUMN_NAME
ORDER BY 1, 2, col.ORDINAL_POSITION;
SQL
복사
References
History
일자 | 작성자 | 비고 |
2022.06.20 | kkh | studio 화면용 10종 정리 별도 페이지로 분리 |
