ClickHouse Operations and Troubleshooting

export CH_HOST="192.0.2.131"
export CH_PORT="9000"
export CH_USER="default"
export CH_PASS="<clickhouse_default_password>"
export CH_CLUSTER="example_3shards_2replicas"

chc() {
  clickhouse-client --host "${CH_HOST}" --port "${CH_PORT}" --user "${CH_USER}" --password "${CH_PASS}" "$@"
}

clickhouse-client --host "${CH_HOST}" --port "${CH_PORT}" --user "${CH_USER}" --password "${CH_PASS}"

SELECT version();

SELECT cluster, shard_num, replica_num, host_name, host_address, port
FROM system.clusters
WHERE cluster = 'example_3shards_2replicas'
ORDER BY shard_num, replica_num;

SELECT database, table, is_leader, is_readonly, queue_size, absolute_delay
FROM system.replicas
ORDER BY database, table;

SELECT count() FROM system.zookeeper WHERE path IN ('/', '/clickhouse');

SELECT hostName(), version()
FROM clusterAllReplicas('example_3shards_2replicas', system.one)
ORDER BY hostName();

SELECT query_id, user, elapsed, read_rows, memory_usage, query
FROM system.processes
ORDER BY elapsed DESC;

SELECT database, table, queue_size, inserts_in_queue, merges_in_queue, log_max_index - log_pointer AS log_lag
FROM system.replicas
ORDER BY queue_size DESC;

SELECT database, table, mutation_id, command, create_time, is_done, latest_failed_part, latest_fail_reason
FROM system.mutations
ORDER BY create_time DESC
LIMIT 20;

SELECT database, table, partition, count() AS parts, sum(rows) AS rows, formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
GROUP BY database, table, partition
ORDER BY rows DESC
LIMIT 30;

systemctl status clickhouse-server --no-pager
systemctl status clickhouse-server-2 --no-pager

ss -lntp | egrep ':8123 |:8124 |:9000 |:9001 |:9009 |:9010 '

journalctl -u clickhouse-server -n 200 --no-pager
journalctl -u clickhouse-server-2 -n 200 --no-pager