Excessive resource usage from clickhouse

[RealOrangeOne]

Bug report

Describe the bug

Clickhouse is using a crazy large amount of resources, when the plausible is getting little to no traffic.

Looking at /etc/clickhouse-server/clickhouse-server.log (crazy large file BTW), there are lots of logs stating it's querying, when nothing is happening:

<Debug> executeQuery: (from xxx.xxx.xxx.xxx:xxx) SELECT 1 FORMAT JSONCompact

These are run once every 2 seconds or so, for each connection in the pool (10 by default).

If I stop plausible, the usage goes down to nothing, so it's definitely something coming from plausible itself causing the usage, and I suspect it's these strange queries.

Expected behavior

Clickhouse only jumps up when it's doing things, and likely not to this height.

Screenshots (If applicable)

N/A

Environment (If applicable):

• OS: Docker on Ubuntu
• Browser: N/A
• Version: N/A

[tckb]

@RealOrangeOne this is expected, I suggest you rotate the log file and reduce the log level to warn or error (I wouldn't recommend going higher than warn on prod environment) to prevent the disk overflow. The query that you see is from the health check, see https://github.com/plausible/analytics/blob/master/lib/plausible_web/controllers/api/external_controller.ex#L38

[RealOrangeOne]

The log file is capped at 1000M anyway, so it's not the end of the world. But yes I'm just changing the log level and mounting the log location on a tempfs to help with that.

Changing that doesn't change the resource usage though, as i'd kinda expect. Even if I make the application completely unroutable, and so the healthcheck endpoint isn't being called, usage is unchanged.

On a side note, wouldn't running that query in the healthcheck only result in it being run once, rather than for each connection in the pool?

[ukutaht]

We don't control Clickhouse's resource consumption. I'll keep this issue open for a while for discussion but unless we identify an issue related to our codebase, I will close it.

On a side note, wouldn't running that query in the healthcheck only result in it being run once, rather than for each connection in the pool?

Yeah, the healthcheck should only run once and only when you actually hit the /api/health endpoint.

I'm not sure what's going on with your setup but my 2 cents is that when I run Clickhouse with Docker on my dev machine, I tend to see excessive resource usage as well. However, in our deployment I've installed it on Ubuntu directly with no Docker and it runs much better.

So my (very uninformed) guess is that CH just doesn't run very efficiently on Docker. Maybe the authors over at https://github.com/ClickHouse/ClickHouse/ can shed light on this

[RealOrangeOne]

Yeah, the healthcheck should only run once and only when you actually hit the /api/health endpoint.

My thought too, which I think means there is a bug here in plausible, even if it's not the entire story of this issue?

when I run Clickhouse with Docker on my dev machine, I tend to see excessive resource usage as well

Well it's comforting to know it's not just me!

I'll try looking at either an alternative clickhouse container, or running it on the host OS and see if that makes a noticeable difference. Usage looks fine when plausible is turned off, so it's definitely query overhead rather than simply idle usage.

[RealOrangeOne]

After doing a load of research, I found some issues in the default container configuration which make Clickhouse super inefficient. They're also there in the Debian package, so not sure why it's so much better on Plausible's production environment, but might just be hidden by regular user traffic 🤷.

Either way i'm pretty confident i've fixed the glaring issues with Clickhouse, although I do think it'd be worth checking into the multiple queries issue, even if just to check. But definitely not super urgent!

@ukutaht BTW if your local setup using excessive resources, I wrote up my findings which may be of interest to you! https://theorangeone.net/posts/calming-down-clickhouse/

[ukutaht]

@RealOrangeOne Thanks! I only got around to reading it now. I will definitely refer back to your post when I look at our production setup next time.

I think I've also figured out what's causing these SELECT 1 calls you're seeing. The database library we're using (https://github.com/clickhouse-elixir/clickhousex) uses DBConnection which itself has a mechanism to ping the database every 1000ms if the connection is idle. Check the idle_interval connection parameter here: https://hexdocs.pm/db_connection/DBConnection.html#start_link/2

I'm not sure how much sense it makes to ping every second. Could also do every 5 seconds, 10?

I intend to try out a different database library (https://github.com/CatTheMagician/pillar). The main reason is that Pillar has support for migrations. I don't know the internals of that library but it might not ping automatically.

If I end up sticking with the current library, we can default to a longer ping interval for database connections to reduce idle resource usage.

[RealOrangeOne]

If the ping happens once per connection in the pool, that'd definitely do it! I guess if it's in the library there's not much we can do about it, besides make it a huge amount of time and rely on the healthcheck endpoint. Or making it user configurable depending on the scale of the deployed instance (or how much the user cares about the ping)

Tabling this until you've migrated to the alternative library is probably a good idea, as you say that might ping in a different way, or not ping at all (background pinging is a weird thing to see having worked with other DB libraries).

Migrations are definitely a thing worth having!

[ukutaht]

Couple of updates:

1. I decided to stick with the current CH connection library. I'm thinking we should probably increase the default ping interval to reduce load generated from excessive pinging.

2. Saturday night, the Clickhouse instance backing our cloud instance crashed with out of memory error. I found the underlying issue: Suspected memory leak ClickHouse/ClickHouse#7207

Basically it seems that Clickhouse needs special configuration when running with <32GB memory. I suspect most self-hosters are running on something like 1-4GB memory. So it seems to be that we should add a Clickhouse configuration file in our self-hosting documentation. It would configure CH for low-memory environment and also reduce logging as per your blog post @RealOrangeOne

[RealOrangeOne]

1. Decreasing the ping sounds great!

2. wow that's unfortunate!

I know some self-hosters may be running on rigs with more than 32GB, but even then they probably don't want Clickhouse using all of it!

Shipping a basic config is probably a good idea, although obviously want it to be small and simple enough so it doesn't need constant maintenance. Happy to test any config changes to have!

[ukutaht]

@RealOrangeOne did you edit the configuration files directly in the running container? Or did you mount some extra configuration files to be merged with the default config?

I've been trying to figure out how keep using the default CH configuration and apply your override. I want to add the overrides to our hosting template.

After 1.5h I give up, I can't get the config to load properly. Couldn't manage to figure out the interactions between how clickhouse loads configuration and the details of how docker mounts single files (not directories).

[RealOrangeOne]

@ukutaht I mounted some additional config files into the container, so they'd persist between restarts.

Sorry that's not clear from the article.

You can take a look at the compose file I'm using for my Plausible instance here: https://github.com/RealOrangeOne/infrastructure/tree/master/ansible/roles/plausible/files. Note that the files are copied into the right places by some external ansible scripts, but specifically take a look at the volumes: for the clickhouse container, and you'll see how the mounting works. I forget where I found the documentation on this, but it does work.

Let me know if that works 😄

[ukutaht]

Thanks! This is what I get:

WARNING: Found orphan containers (hosting_geoip_1) for this project. If you removed or renamed this service in your compose file, you can run this command with the --remove-orphans flag to clean it up.
hosting_plausible_db_1 is up-to-date
Starting hosting_plausible_events_db_1 ...
Starting hosting_plausible_events_db_1 ... error

ERROR: for hosting_plausible_events_db_1  Cannot start service plausible_events_db: error while creating mount source path '/host_mnt/Users/ukutaht/dev/plausible/hosting/clickhouse/config.xml': mkdir /host_mnt/Users/ukutaht/dev/plausible/hosting/clickhouse/config.xml: file exists

ERROR: for plausible_events_db  Cannot start service plausible_events_db: error while creating mount source path '/host_mnt/Users/ukutaht/dev/plausible/hosting/clickhouse/config.xml': mkdir /host_mnt/Users/ukutaht/dev/plausible/hosting/clickhouse/config.xml: file exists
ERROR: Encountered errors while bringing up the project.

[RealOrangeOne]

That's, very strange. If you can share your compose file i'm happy to give it a try locally and debug, but yeah looks like the mounts are the issue. Mine are just bare files mounted directly into the container, read-only just in case:

volumes:
      - /mnt/tank/dbs/clickhouse/plausible:/var/lib/clickhouse
      - /mnt/tank/dbs/clickhouse/docker_related_config.xml:/etc/clickhouse-server/config.d/docker_related_config.xml:ro
      - /mnt/tank/dbs/clickhouse/docker_related_user_config.xml:/etc/clickhouse-server/users.d/docker_related_user_config.xml:ro

[bokub]

Basically it seems that Clickhouse needs special configuration when running with <32GB memory

Woah, that's huge!

That would explain why my micro server (550MB or RAM, 1 vCPU) has been struggling since I switched to Plausible... My server is regularly down, I often have 500 errors when trying to access my dashboards, the average response time for the tracking script is above 1000ms...

From what I understand, it's not possible to use Plausible without Clickhouse, right ?