Ibaraki DoujiOriginal post
Rank 5: Hypervisor
After some time with 2.X, i saw the Clickhouse container was growing in size very fast despite not having that much stats going on.
After some lookup with it i saw it was that by default the Clickhouse system adds stats without TTL, so it just accumulate over time.
To fix that you can remove the current system stats, and adds a TTL to them so it grows but not too much :
SQL
-- Truncate (optional one-shot cleanup)TRUNCATE TABLE IF EXISTS system.text_log;TRUNCATE TABLE IF EXISTS system.trace_log;TRUNCATE TABLE IF EXISTS system.asynchronous_metric_log;TRUNCATE TABLE IF EXISTS system.metric_log;TRUNCATE TABLE IF EXISTS system.part_log;TRUNCATE TABLE IF EXISTS system.processors_profile_log;TRUNCATE TABLE IF EXISTS system.query_log;TRUNCATE TABLE IF EXISTS system.histogram_metric_log;TRUNCATE TABLE IF EXISTS system.query_views_log;TRUNCATE TABLE IF EXISTS system.query_metric_log;TRUNCATE TABLE IF EXISTS system.background_schedule_pool_log;TRUNCATE TABLE IF EXISTS system.asynchronous_insert_log;TRUNCATE TABLE IF EXISTS system.error_log;
-- Retention TTLsALTER TABLE system.text_log MODIFY TTL event_date + INTERVAL 3 DAY DELETE;ALTER TABLE system.trace_log MODIFY TTL event_date + INTERVAL 3 DAY DELETE;ALTER TABLE system.asynchronous_metric_log MODIFY TTL event_date + INTERVAL 7 DAY DELETE;ALTER TABLE system.metric_log MODIFY TTL event_date + INTERVAL 7 DAY DELETE;ALTER TABLE system.part_log MODIFY TTL event_date + INTERVAL 7 DAY DELETE;ALTER TABLE system.query_log MODIFY TTL event_date + INTERVAL 7 DAY DELETE;ALTER TABLE system.histogram_metric_log MODIFY TTL event_date + INTERVAL 7 DAY DELETE;ALTER TABLE system.query_views_log MODIFY TTL event_date + INTERVAL 7 DAY DELETE;ALTER TABLE system.query_metric_log MODIFY TTL event_date + INTERVAL 7 DAY DELETE;ALTER TABLE system.background_schedule_pool_log MODIFY TTL event_date + INTERVAL 7 DAY DELETE;ALTER TABLE system.asynchronous_insert_log MODIFY TTL event_date + INTERVAL 7 DAY DELETE;ALTER TABLE system.error_log MODIFY TTL event_date + INTERVAL 14 DAY DELETE;ALTER TABLE system.processors_profile_log MODIFY TTL event_date + INTERVAL 7 DAY DELETE;also maybe the appwrite team could add some sort of init script to run this once and won't make a 10GB volume full in two weeks 🙂 (actual appwrite data is ~1.5GB)
Summary
Clickhouse container size growing due to accumulating default system stats without TTL. Solution: Remove current system stats and add TTL to prevent excessive growth. Suggested SQL queries for truncating tables and setting retention TTLs. Recommend appwrite team to consider adding an init script in the usage-setup command to automate this process.