Hi, I had a quick question about scaling the database (clickhouse), I've already sharded it so that it can handle the ingestion load, but now the ingest is way better but reads are slow, because the cpu and memory is always high on there. is there a way to setup read only replicas of the shards and make the query service pointed at those? cc: @Srikanth Chekuri

How much CPU, memory is provisioned and how much is it currently using?

Each shard is provisioned 32g ram and 8cpu, cou is usually above 90% and memory hovers between 20-30g

Roughly what volume of your of you data in terms of records/s? If a total of 90% is used for ingesting then it's important to understand the ingest scale. In ClickHouse all replicas are equal and do all the things so you can't have just the read only replica.

I see, I am not sure exactly how much of it is used for ingestion. But I’ve had trouble with sudden spikes in ingestion earlier because it used to get killed due to spike in memory and cpu during high ingestion load.

Do you have any alerts set? What is the usage when you are not issuing any requests to query-service

Yes, I do. I setup alerts to know when we’re not ingesting any traces spans. I haven’t noticed how much the usage is when no queries are being sent out, but I could try that out for a period and get a baseline if that’d be useful

collector-metrics.json

perfect !i imported the dashboard, what numbers would be useful?

Accepted traces/metrics/logs per second

These are for the last hour

Can you share for last 6 hours and 1 day? It will give better picture.

apologies, yea this is for the last day

This kind of explains the resource usage. What does the graph Exporter DB writes/s show?

This is what it looks like

This is good. The number of inserts/s should be single digit for tables.

interesting, so that sounds good that these look good, just unsure how the clickhouse cluster. The reason is like i said, having clickhouse overwhelmed during spikes in our system (which we plan to address by having kafka in the mix), but the application is sluggish now, which we believe is due to clickhouse not having enough resources to respond to queries from the query service

Can you exec into ClickHouse and share the output of this in DMs?

SELECT
    normalized_query_hash,
    any(query),
    count(),
    sum(query_duration_ms) / 1000 AS QueriesDuration,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'RealTimeMicroseconds')]) / 1000000 AS RealTime,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')]) / 1000000 AS UserTime,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')]) / 1000000 AS SystemTime,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'DiskReadElapsedMicroseconds')]) / 1000000 AS DiskReadTime,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'DiskWriteElapsedMicroseconds')]) / 1000000 AS DiskWriteTime,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'NetworkSendElapsedMicroseconds')]) / 1000000 AS NetworkSendTime,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'NetworkReceiveElapsedMicroseconds')]) / 1000000 AS NetworkReceiveTime,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'ZooKeeperWaitMicroseconds')]) / 1000000 AS ZooKeeperWaitTime,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSIOWaitMicroseconds')]) / 1000000 AS OSIOWaitTime,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSCPUWaitMicroseconds')]) / 1000000 AS OSCPUWaitTime,
    sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')]) / 1000000 AS OSCPUVirtualTime,
    sum(read_rows) AS ReadRows,
    formatReadableSize(sum(read_bytes)) AS ReadBytes,
    sum(written_rows) AS WrittenTows,
    formatReadableSize(sum(written_bytes)) AS WrittenBytes,
    sum(result_rows) AS ResultRows,
    formatReadableSize(sum(result_bytes)) AS ResultBytes
FROM system.query_log
WHERE (event_date >= today()) AND (event_time > (now() - INTERVAL 1 HOUR)) AND type in (2,4)
GROUP BY normalized_query_hash
    WITH TOTALS
ORDER BY UserTime DESC
LIMIT 30
FORMAT Vertical

i get the following exception

Received exception from server (version 24.1.2):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Missing columns: 'OSCPUWaitMicroseconds' 'RealTimeMicroseconds' 'OSCPUVirtualTimeMicroseconds' 'SystemTimeMicroseconds' 'OSIOWaitMicroseconds' 'UserTimeMicroseconds' 'ZooKeeperWaitMicroseconds' 'NetworkSendElapsedMicroseconds' 'DiskReadElapsedMicroseconds' 'NetworkReceiveElapsedMicroseconds' 'DiskWriteElapsedMicroseconds' while processing query: 'SELECT normalized_query_hash, any(query), count(), sum(query_duration_ms) / 1000 AS QueriesDuration, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, RealTimeMicroseconds)]) / 1000000 AS RealTime, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, UserTimeMicroseconds)]) / 1000000 AS UserTime, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, SystemTimeMicroseconds)]) / 1000000 AS SystemTime, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, DiskReadElapsedMicroseconds)]) / 1000000 AS DiskReadTime, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, DiskWriteElapsedMicroseconds)]) / 1000000 AS DiskWriteTime, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, NetworkSendElapsedMicroseconds)]) / 1000000 AS NetworkSendTime, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, NetworkReceiveElapsedMicroseconds)]) / 1000000 AS NetworkReceiveTime, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, ZooKeeperWaitMicroseconds)]) / 1000000 AS ZooKeeperWaitTime, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, OSIOWaitMicroseconds)]) / 1000000 AS OSIOWaitTime, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, OSCPUWaitMicroseconds)]) / 1000000 AS OSCPUWaitTime, sum(ProfileEvents.Values[indexOf(ProfileEvents.Names, OSCPUVirtualTimeMicroseconds)]) / 1000000 AS OSCPUVirtualTime, sum(read_rows) AS ReadRows, formatReadableSize(sum(read_bytes)) AS ReadBytes, sum(written_rows) AS WrittenTows, formatReadableSize(sum(written_bytes)) AS WrittenBytes, sum(result_rows) AS ResultRows, formatReadableSize(sum(result_bytes)) AS ResultBytes FROM system.query_log WHERE (event_date >= today()) AND (event_time > (now() - toIntervalHour(1))) AND (type IN (2, 4)) GROUP BY normalized_query_hash WITH TOTALS ORDER BY UserTime DESC LIMIT 30', required columns: 'normalized_query_hash' 'ProfileEvents.Values' 'DiskWriteElapsedMicroseconds' 'event_date' 'NetworkReceiveElapsedMicroseconds' 'event_time' 'DiskReadElapsedMicroseconds' 'NetworkSendElapsedMicroseconds' 'written_bytes' 'type' 'result_rows' 'ProfileEvents.Names' 'ZooKeeperWaitMicroseconds' 'UserTimeMicroseconds' 'result_bytes' 'query_duration_ms' 'OSIOWaitMicroseconds' 'read_bytes' 'SystemTimeMicroseconds' 'written_rows' 'query' 'OSCPUVirtualTimeMicroseconds' 'read_rows' 'RealTimeMicroseconds' 'OSCPUWaitMicroseconds', maybe you meant: 'normalized_query_hash', 'event_date', 'event_time', 'written_bytes', 'type', 'result_rows', 'ProfileEvents', 'event_time_microseconds', 'result_bytes', 'query_duration_ms', 'read_bytes', 'written_rows', 'query' or 'read_rows'. (UNKNOWN_IDENTIFIER)

ok this is for old version. let me share a new query