Clickhouse is consuming too much CPU

Site Operators Site Operations Help
,
1

Hi all

I am using Aspects as analytics app for our OpenEdx instance in UAMx. For some time now, we have been experimenting a rapidly growing use of CPU by Clickhouse.

We have a separated instance of Clickhouse in an independent machine along with other critical databases: MySQL, MongoDB and Redis.In approximately one year of use, Clickhouse is consuming 71G of memory, and the disk space is at its 74% capacity…

How can I prevent it from growing even more? Can I delete / rotate some data? In this case, which one?

Thanks in advance for any help
Regards

2

Following this post recommendations How to Fix ‘No free disk space’ Errors in ClickHouse, I’ve ran the following script:

-- Find the largest tables on disk
SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk,
    sum(rows) AS total_rows,
    count() AS part_count
FROM system.parts
WHERE active = 1
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC
LIMIT 20;

Looking at the used space theres 39.19GiB used by the text_log table and all the other bigger tables belong to the system database

image
image1218×254 14.4 KB

How can I prevent this logs from growing? Is it safe to truncate these tables?

3

Hi @Yago . There are a few things you can do. First you probably want to change the Clickhouse log level, which you can do with the Tutor patch clickhouse-server-config . The patch should look like:

<logger>
    <level>warning</level>
</logger>

Then you can clean up the logs, presuming you don’t need them for debugging anything right now. They should be partitioned by month, so you can keep the last X months (and make clean up go a LOT faster) by dropping partitions as documented here.

I can help debug CPU usage, but it will probably be a lot of back and forth while we try to see what’s happening. Maybe we can connect on Slack and then post the findings here when we’re done?

4

@Yago also worth looking at your ASPECTS_DATA_TTL_EXPRESSION.

This setting controls for how long you store analytics data for. Defaults is one year, empty means forever.

5

We had the same problem on our nau.edu.pt service.

I think you need to limit the TTL of internal ClickHouse logs, but by default it won’t delete the old logs. To free the disk space of those old logs, you need to: or truncate the old logs or run a query to delete the old log data. Because it was just system logs, I decided just to truncate them.

This will just free the disk usage and limit the TTL system logs for 10 days:

TRUNCATE TABLE system.asynchronous_metric_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.crash_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.error_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.metric_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.part_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.processors_profile_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.query_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.query_thread_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.query_views_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.text_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.trace_log ON CLUSTER 'logs_cluster' SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.query_metric_log ON CLUSTER logs_cluster SETTINGS max_table_size_to_drop = 0;

ALTER TABLE system.asynchronous_metric_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.crash_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.error_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.metric_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.part_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.processors_profile_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.query_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.query_thread_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.query_views_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.text_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.trace_log MODIFY TTL event_date + INTERVAL 10 DAY;
ALTER TABLE system.query_metric_log MODIFY TTL event_date + INTERVAL 10 DAY;
6

Thanks all, I’ve taken all your recommendations and it looks pretty good, we have reduced the Clickhouse usage to less than 5GiB!

@TyHob Thanks a lot, our Clickhouse instance is natively installed in an external machine, so I added your recommended config to /etc/clickhouse-server/config.d/logging.xml like here

@Felipe Yeah, thanks for the tip, I’ve already checked that it’s in default mode (one year)

@IvoBranco thanks very much for the snippets, I found them very very useful! It saved me a lot of research time.