ClickHouse

Show partitions for database

SELECT DISTINCT 
    table, 
    partition
FROM system.parts
WHERE database = 'db'

Show partitions on cluster

SELECT DISTINCT 
    hostName(), 
    table, 
    partition
FROM cluster('cluster', 'system', 'parts')

Show partition sizes

SELECT 
    table,
    partition,
    formatReadableSize(sum(bytes)) AS size
FROM system.parts
GROUP BY 
    table,
    partition
ORDER BY sum(bytes) DESC
LIMIT 10

Show partition sizes on cluster

SELECT 
    hostName(),
    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(),
    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

SELECT 
    database,
    table,
    count()
FROM remote('ch-{1..100}.example.net', 'system.merges')
GROUP BY 
    database,
    table
ORDER BY 
    database ASC,
    table 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

  1. Export via
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.

  1. Edit file and leave only partitions to drop.

  2. 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 '