Skip to content

Instantly share code, notes, and snippets.

@loganlinn
Last active September 19, 2020 01:13
Show Gist options
  • Save loganlinn/ce428b1168e10f239ed2d839ebaeb190 to your computer and use it in GitHub Desktop.
Save loganlinn/ce428b1168e10f239ed2d839ebaeb190 to your computer and use it in GitHub Desktop.
ClickHouse Queries
SELECT table,
name,
formatReadableSize(c) compressed,
formatReadableSize(u) uncompressed,
floor((c/u) * 100, 4) percent
FROM (
SELECT table,
name,
sum(data_compressed_bytes) c,
sum(data_uncompressed_bytes) u
FROM system.columns
WHERE database = currentDatabase()
GROUP BY table, name
ORDER BY table ASC, name ASC
)
SELECT toStartOfMinute(event_time) AS time,
sum(ProfileEvent_UserTimeMicroseconds) AS user_time,
bar(user_time, 0, 60000000, 80) AS bar
FROM system.metric_log
WHERE event_date = today()
GROUP BY time ORDER BY time
SELECT database, formatReadableSize(sum(bytes_on_disk)) database_size
FROM system.parts
GROUP BY database
SELECT database, table, partition, name part_name, active, formatReadableSize(bytes_on_disk) bytes_on_disk
FROM system.parts
ORDER BY database, table, partition, name
SELECT
formatReadableSize(filesystemCapacity()) capacity,
formatReadableSize(filesystemFree()) free,
formatReadableSize(filesystemCapacity() - filesystemFree()) used,
round(((filesystemCapacity() - filesystemFree()) / filesystemCapacity())*100, 2) `%used`,
bar(((filesystemCapacity() - filesystemFree()) / filesystemCapacity())*100, 0, 100, 20) `used_bar`;
SELECT
name,
path,
formatReadableSize(free_space) AS free,
formatReadableSize(total_space) AS total,
formatReadableSize(keep_free_space) AS reserved
FROM system.disks;
SELECT user,
client_hostname AS host,
client_name AS client,
formatDateTime(query_start_time, '%T') AS started,
query_duration_ms / 1000 AS sec,
round(memory_usage / 1048576) AS MEM_MB,
result_rows AS RES_CNT,
toDecimal32(result_bytes / 1048576, 6) AS RES_MB,
read_rows AS R_CNT,
round(read_bytes / 1048576) AS R_MB,
written_rows AS W_CNT,
round(written_bytes / 1048576) AS W_MB,
query
FROM system.query_log
WHERE type = 2
ORDER BY query_duration_ms DESC
LIMIT 10
SELECT table, formatReadableSize(sum(bytes_on_disk)) table_size
FROM system.parts
WHERE database=currentDatabase()
GROUP BY table
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment