ClickHouse Show partitions for database
SELECT DISTINCT
table,
partition
FROM system.parts
WHERE database = 'db'
Show partitions on cluster
SELECT DISTINCT
hostName(),
database,
table,
partition
FROM cluster('cluster', 'system', 'parts')
Show partition sizes
SELECT
database,
table,
partition,
formatReadableSize(sum(bytes)) AS size
FROM system.parts
GROUP BY
database,
table,
partition
ORDER BY sum(bytes) DESC
LIMIT 10
Show partition sizes on cluster
SELECT
hostName(),
database,
table,
partition,
formatReadableSize(sum(bytes)) AS size
FROM remote('ch-{1..100}.example.net', 'system.parts')
WHERE database = 'db' AND table = 'table_sharded'
GROUP BY
hostName(),
database,
table,
partition
ORDER BY sum(bytes) DESC
Show column sizes
SELECT
column,
formatReadableSize(size)
FROM
(
SELECT
column,
sum(column_bytes_on_disk) AS size
FROM system.parts_columns
WHERE (database = 'db') AND (table = 'table_sharded') AND (partition = '202010')
GROUP BY column
ORDER BY size ASC
)
Show max_part_count_for_partition metric
SELECT
hostName(),
*
FROM remote('ch-{1..100}.example.net', 'system.asynchronous_metrics')
WHERE metric = 'MaxPartCountForPartition'
ORDER BY hostName() ASC
Merges in tables with ETA
SELECT
hostName(),
database,
table,
round(elapsed, 0) AS time,
round(progress, 4) AS percent,
formatReadableTimeDelta((elapsed / progress) - elapsed) AS ETA,
num_parts,
result_part_name
FROM clusterAllReplicas('mycluster', 'system.merges')
ORDER BY (elapsed / percent) - elapsed ASC
Lookup executing query by substring
SELECT substring(query, position(query, 'interesting query part'), 20)
FROM system.processes
WHERE (query LIKE '%INSERT%') AND (user = 'production')
Drop many custom partitions
SELECT partition
FROM system.parts
WHERE database='{db:String}'
AND table='{table:String}'
GROUP BY partition
ORDER BY partition INTO outfile '{table:String}.tsv'
FORMAT TSVRaw
clickhouse-client --param_db='db' --param_table='t'
FORMAT TSVRaw prevents escaping because drop partition query doesn’t need it.
Edit file and leave only partitions to drop.
Drop via bash loop
$ for p in $(cat table.tsv);do echo "$p"; clickhouse-client -h ch-1.example.net --query="alter table db.t on cluster c drop partition $p" ;done
Tail logs
Tail useful part of logs. Excludes stack traces, empty lines, version string. This will not work with trace level logs.
tail -F /var/log/clickhouse-server/clickhouse-server.log | grep -vP '\d\d?\. ' | grep -vP '^ *$' | grep -v 'version '
Distinct errors from log
grep '<Error>' clickhouse-server.log | cut -d' ' -f 7- | sort | uniq -c > errors.uniq.log
Distinct query errors from system.text_log
SELECT
replaceAll(replaceRegexpAll(replaceRegexpOne(message, '\\(from \\[.*\\)', ''), '\n\\d+\\.[^\n].*', ''), '\n', '') AS m,
count() AS freq,
any(query_id) as query_id
FROM remote('ch-{1..100}.example.net', 'system.text_log')
WHERE (level = 'Error') AND (logger_name = 'executeQuery') AND (event_date >= '2021-08-31') AND (event_time >= '2021-08-31 14:45:00') AND (message NOT LIKE '%Received from%')
GROUP BY m
ORDER BY freq DESC