Server load issues after migrating to v3.0.1

[Tony-MF]

Hi,

I'm having load issues after the latest upgrade. It seems after the server reaches some treshold, the CPU utilization increases disproportionally.

My nginx log is full of:

2025/04/16 09:59:13 [error] 1426#1426: *471806619 connect() failed (111: Connection refused) while connecting to upstream, client: 172.68.147.151, server: stats2.indianfunempire.com, request: "POST /api/event HTTP/2.0", upstream: "http://127.0.0.1:8000/api/event", host: "stats2.indianfunempire.com", referrer: "https://www.mydesifun.com/"
2025/04/16 09:59:13 [error] 1426#1426: *471806619 connect() failed (111: Connection refused) while connecting to upstream, client: 172.68.147.151, server: stats2.indianfunempire.com, request: "POST /api/event HTTP/2.0", upstream: "http://127.0.0.1:8000/api/event", host: "stats2.indianfunempire.com", referrer: "https://www.desifun2.com/"
2025/04/16 09:59:13 [error] 1483#1483: *471500450 connect() failed (111: Connection refused) while connecting to upstream, client: 162.158.108.139, server: stats2.indianfunempire.com, request: "POS^C

Top:

The only difference I see is the user for beam.smp. Previously it was systemd+.

My docker-compose.xml:

services:
  plausible_db:
    image: postgres:16-alpine
    restart: always
    volumes:
      - db-data:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=postgres
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      start_period: 1m

  plausible_events_db:
    image: clickhouse/clickhouse-server:24.12-alpine
    restart: always
    volumes:
      - event-data:/var/lib/clickhouse
      - ./clickhouse/clickhouse-config.xml:/etc/clickhouse-server/config.d/logging.xml:ro
      - ./clickhouse/clickhouse-user-config.xml:/etc/clickhouse-server/users.d/logging.xml:ro
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    environment:
      - CLICKHOUSE_SKIP_USER_SETUP=1
    healthcheck:
        test: ["CMD-SHELL", "wget --no-verbose --tries=1 -O - http://127.0.0.1:8123/ping || exit 1"]
        start_period: 1m

  plausible:
    image: ghcr.io/plausible/community-edition:v3.0.1
    restart: always
    command: sh -c "/entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh run"
#    command: sh -c "tail -f /dev/null"
    depends_on:
      plausible_db:
        condition: service_healthy
      plausible_events_db:
        condition: service_healthy
    ulimits:
      nofile:
        soft: 65535
        hard: 65535
    ports:
      - 8000:8000
    env_file:
      - plausible-conf.env

volumes:
  db-data:
    driver: local
  event-data:
    driver: local

Inside the plausible container, the top looks like this:

In the past, I had similar problems but it was because of nginx config, which has not changed:

      location / {
               proxy_pass http://127.0.0.1:8000;
               proxy_set_header X-Forwarded-Ssl on;
               proxy_set_header Host $host;
               proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

               location = /live/websocket {
                       proxy_pass         http://127.0.0.1:8000;
                       proxy_http_version 1.1;
                       proxy_set_header   Upgrade    $http_upgrade;
                       proxy_set_header   Connection "upgrade";
               }
       }

Is anyone else having similar issues?

[slowengine]

Nay, you are not the only one. I did a jump from v2.0 to v3.0.1 today and now the server is eating shit. Now I regret not creating a 500G backup of all the data.

Initially you will have the general load of clickhouse doing some updates, which you can see via:

docker exec -u root -it plausible-plausible_events_db-1 clickhouse-client

SELECT
    database,
    `table`,
    substr(command, 1, 30) AS command,
    sum(parts_to_do) AS parts_to_do,
    anyIf(latest_fail_reason, latest_fail_reason != '')
FROM system.mutations
WHERE NOT is_done
GROUP BY
    database,
    `table`,
    command

Query id: fb9f84c1-07e5-4fbd-9af9-ba7e51c166ac

   ┌─database────────────┬─table───────┬─command────────────────────────┬─parts_to_do─┬─anyIf(latest_fail_reason, notEquals(latest_fail_reason, ''))─┐
1. │ plausible_events_db │ sessions_v2 │ UPDATE utm_medium = multiIf((r │          63 │                                                              │
2. │ plausible_events_db │ events_v2   │ UPDATE referrer_source = trans │         285 │                                                              │
3. │ plausible_events_db │ events_v2   │ UPDATE utm_medium = multiIf((r │          95 │                                                              │
4. │ plausible_events_db │ sessions_v2 │ UPDATE referrer_source = trans │         189 │                                                              │
   └─────────────────────┴─────────────┴────────────────────────────────┴─────────────┴──────────────────────────────────────────────────────────────┘

4 rows in set. Elapsed: 0.013 sec.

After the data migration is done which can take quite some time, the overall load will fall.
You will be able to access the "?period=realtime" pages of your sites, but when you try to view historic data, it starts eating shit like no other. Anything past a weeks range results in /api/stats/*******/entry-pages/?period=28d&date=2025-04-17&filters=[]&with_imported=true&limit=9 and others to error out with {"error":"internal server error"} and clickhouse goes ham on all the CPUs.

I was just a few hours too late to see your post, otherwise I would have stayed on the version I was.

[slowengine]

Whatever v2 was doing, it had no issues chewing through 2,917,181,075 requests per month. Now I am wondering if I can roll any of the clickhouse data back.

[slowengine]

And whenever plausible does queries that cause the clickhouse to go haywire, nginx writing stats go haywire, which indicates that plausible is unable to process the requests at time of querying the clickhosue. Which also causes other DB services to slow down due to load.

You can see when I try to get anything >today via history and when it tries to reload.

[slowengine]

Just said fuck it and deployed the previous version plausible/analytics:v2.0. A few issues like:

00:18:24.898 [error] #PID<0.3913.0> running PlausibleWeb.Endpoint (connection #PID<0.3912.0>, stream id 1) terminated
Server: **********:80 (http)
Request: POST /api/event
** (exit) an exception was raised:
    ** (RuntimeError) could not find persistent term for endpoint PlausibleWeb.Endpoint. Make sure your endpoint is started and note you cannot access endpoint functions at compile-time
        (plausible 0.0.1) lib/phoenix/endpoint.ex:536: PlausibleWeb.Endpoint.persistent!/0
        (plausible 0.0.1) lib/phoenix/endpoint.ex:576: PlausibleWeb.Endpoint.script_name/0
        (plausible 0.0.1) lib/plausible_web/endpoint.ex:1: PlausibleWeb.Endpoint.call/2
        (plug_cowboy 2.6.1) lib/plug/cowboy/handler.ex:11: Plug.Cowboy.Handler.init/2
        (cowboy 2.10.0) /app/deps/cowboy/src/cowboy_handler.erl:37: :cowboy_handler.execute/2
        (cowboy 2.10.0) /app/deps/cowboy/src/cowboy_stream_h.erl:306: :cowboy_stream_h.execute/3
        (cowboy 2.10.0) /app/deps/cowboy/src/cowboy_stream_h.erl:295: :cowboy_stream_h.request_process/3
        (stdlib 4.2) proc_lib.erl:240: :proc_lib.init_p_do_apply/3

But frankly it still works and what is of more interest, is that the querying into the history is as speedy an non-problematic as it was.

So whatever is going on, it is on plausible side querying clickhouse. What they did? Don't know, don't care.
If someone wants some queries run, I will be checking this thread every other day, but will probably just stay on v2.0.

[Tony-MF]

Thanks for pushing me. I figured out if I keep the new docker-compose.xml (v3.0.1) and just change the plausible image to 2.1.5, it also works. Load dropped from 120 to 15ish.

[slowengine]

Well, personally I would suggest opening an issue, as this seems to be a plausible issue and not a clickhouse issue. I won't be doing that, as I will stay on 2.0 and ignore the future updates for now.

[slowengine]

Yeah, no. Forget that suggestion. Seems to be a running issue since version 2.1.0:
#4285
#4360

Just stay on v2.0.0 and ignore any updates.