plausible events db has grown humongous (for a very small site)

[tcurdt]

I just got notified the server was full. After checking the plausible_events_db_data docker volume had 150GB of data. This is for a tiny site though.

I searched for some suggestions. After running

 clickhouse-client -q "SELECT name FROM system.tables WHERE name LIKE '%log%';" | xargs -I{} clickhouse-client -q "TRUNCATE TABLE system.{};"

The size dropped to about 60 GB.
But that still feels huge.

This is still running plausible/analytics:v1.3.0.
I guess it's time to upgrade - but still.

Search did not result in much more than the clickhouse query above.

Any pointers?

[ruslandoga]

👋 @tcurdt

A query like this one can be used to check which tables occupy the most space.

select table, active, formatReadableSize(sum(bytes_on_disk)) from system.parts group by table, active order by sum(bytes_on_disk) desc;

I added active to see which parts are scheduled to be deleted but still reside on disk.

[tcurdt]

Seems like trunc'ing the table is a problem

Received exception from server (version 21.9.4):
Code: 359. DB::Exception: Received from localhost:9000. DB::Exception: Table or Partition in system.query_log was not dropped.
Reason:
1. Size (62.11 GB) is greater than max_[table/partition]_size_to_drop (50.00 GB)
2. File '/var/lib/clickhouse/flags/force_drop_table' intended to force DROP doesn't exist
How to fix this:
1. Either increase (or set to zero) max_[table/partition]_size_to_drop in server config
2. Either create forcing file /var/lib/clickhouse/flags/force_drop_table and make sure that ClickHouse has write permission for it.
Example:
sudo touch '/var/lib/clickhouse/flags/force_drop_table' && sudo chmod 666 '/var/lib/clickhouse/flags/force_drop_table'. (TABLE_SIZE_EXCEEDS_MAX_DROP_SIZE_LIMIT)

[ruslandoga]

Ah, right. That's a ClickHouse safety measure.

[tcurdt]

This did the trick:

# touch '/var/lib/clickhouse/flags/force_drop_table' && chmod 666 '/var/lib/clickhouse/flags/force_drop_table'
# clickhouse-client -q "SELECT name FROM system.tables WHERE name LIKE '%log%';" | xargs -I{} clickhouse-client -q "TRUNCATE TABLE system.{};"
# clickhouse-client -q "select table, active, formatReadableSize(sum(bytes_on_disk)) from system.parts group by table, active order by sum(bytes_on_disk);"
trace_log	1	1.18 KiB
asynchronous_metric_log	1	17.66 KiB
query_thread_log	1	23.24 KiB
query_log	1	38.02 KiB
metric_log	1	61.72 KiB
trace_log	0	336.54 KiB
sessions	1	2.76 MiB
events	1	3.17 MiB
metric_log	0	3.64 MiB
query_log	0	5.87 MiB
query_thread_log	0	37.19 MiB
asynchronous_metric_log	0	104.01 MiB

[tcurdt]

Thanks a lot for the help!

[ruslandoga]

These configs can be mounted into your ClickHouse container to stop it from logging too much.