Appwrite 2.4.0 self-hosted - High CPU usage by Clickhouse

I've just updated my self-hosted instance from 2.1.0 to 2.4.0. Clickhouse is still using much CPU (100-200%) and stats does not load in Appwrite console. I get:
{
"message": "Usage storage is not ready. Please retry after the usage schema has been initialized.",
"code": 503,
"type": "general_usage_not_ready",
"version": "2.4.0"
}

Does it mean that Clickhouse is still initializing itself in the background and I need to wait?

Update: Restart of all containers helped and now the stats work. However, CPU usage is still high

@Ibaraki Douji For the test purposes, I've disabled all the traffic to Appwrite in my external nginx and clickhouse usage is still high, so it still seems to be the system logging.
Does the Clickhouse sh script run once or at every startup? Maybe it did not succeeded during the upgrade process?

the sh should run on every start with the env ALWAYS_RUN=1.
maybe check the clickhouse logs , maybe ye, it didn't ran proprely

It seems that it run and there is no error message

then maybe it's just cleaning up the system ingestion

It's been around 30min and I see no improvement. Also, the disk usage is rising instead of falling 😄

Disabling usage stats in .env also didn't decrease cpu usage

i'm looking in a dev env, but i think it's because they disabled some system ingestion but not everything

as a test for your setup can you check :

# is appwrite.xml mounted?
docker exec appwrite-clickhouse ls -la /etc/clickhouse-server/config.d/
docker exec appwrite-clickhouse head -30 /etc/clickhouse-server/config.d/appwrite.xml

# ---

# system log tables: removed ones should be absent (or orphan + TTL); kept ones should show TTL
docker exec appwrite-clickhouse clickhouse-client \
  --user appwrite --password "$(_pw(){ grep '^_APP_USAGE_PASS=' .env | cut -d= -f2-; }; _pw)" \
  -q "
SELECT name,
       if(position(engine_full, ' TTL ') > 0, 'yes', 'no') AS has_ttl,
       total_rows
FROM system.tables
WHERE database = 'system'
  AND name IN (
    'asynchronous_metric_log','metric_log','histogram_metric_log','query_metric_log',
    'processors_profile_log','trace_log','text_log',
    'asynchronous_insert_log','query_log','query_views_log','query_thread_log',
    'part_log','background_schedule_pool_log','error_log','crash_log'
  )
ORDER BY name
"

also if your not in the same folder as the .env replace "$(_pw(){ grep '^_APP_USAGE_PASS=' .env | cut -d= -f2-; }; _pw)" with the real value of _APP_USAGE_PASS

other thing you can do to see growth:

# run twice, ~30s apart
docker exec appwrite-clickhouse clickhouse-client \
  --user appwrite --password "$(grep '^_APP_USAGE_PASS=' .env | cut -d= -f2-)" \
  -q "SELECT name, total_rows FROM system.tables WHERE database='system' AND name IN ('metric_log','trace_log','query_metric_log','asynchronous_metric_log','query_log') ORDER BY name"

here is the output for first query

The growth is in query_log, around 1000/minute. I have many functions executions (around 200 a minute). Are the executions logged in query_log?

But, when I disabled usage stats in Appwrite, the Clickhouse had finally reduced the CPU usage after ~20min. Now I enabled stats again, restarted Appwrite and it is high again

so ye apparently it's backlog data that are doing merge TLL + deletes..

The query_log trigger only on requests and yes, logs like timings and stats about the query.

can you also run the following command twice with 60s interval to see if the stopped ones are still writing :

docker exec appwrite-clickhouse clickhouse-client \
  --user appwrite --password "$(grep '^_APP_USAGE_PASS=' .env | cut -d= -f2-)" \
  -q "SELECT name, total_rows FROM system.tables WHERE database='system' AND name IN (
    'asynchronous_metric_log','metric_log','histogram_metric_log','query_metric_log',
    'processors_profile_log','trace_log','text_log'
  ) ORDER BY name"

you can also force cleanup the ones that should be cleaned with :

docker exec -it appwrite-clickhouse clickhouse-client \
  --user appwrite --password "$(grep '^_APP_USAGE_PASS=' .env | cut -d= -f2-)" \
  --multiquery "
TRUNCATE TABLE IF EXISTS system.asynchronous_metric_log;
TRUNCATE TABLE IF EXISTS system.metric_log;
TRUNCATE TABLE IF EXISTS system.histogram_metric_log;
TRUNCATE TABLE IF EXISTS system.query_metric_log;
TRUNCATE TABLE IF EXISTS system.processors_profile_log;
TRUNCATE TABLE IF EXISTS system.trace_log;
TRUNCATE TABLE IF EXISTS system.text_log;
"

There are many rows in that tables, but they do not increase

ok so that's good, you can jsut remove them with the last command i sent

yeah, I did that

is the cpu better ?

not really 😄 Still much higher than when I disabled usage stats

like how much is the CPU using ?

50-150%, sometimes up to 200%. My VPS has 3 Intel E5-2680 cores

and disabled, does it still use a lot ? to know if now it's jsut appwrite

will check that

2-3 mins after restart, it is also high with stats disabled. Don't know if I have to wait again for 30-50 mins for it to drop and stabilize

Ok, it dropped significantly after 5min. Now the usage is 5-60%