Clickhouse backups

[vitobotta]

Hi! I set up Plausible but I am having trouble backing up the Clickhouse database. I'm using https://github.com/AlexAkulov/clickhouse-backup for this since I couldn't find a better tool.
The problem with this tool is that is only support MergeTree tables, so the schema_migration table, being of type TinyLog, is ignored, meaning than after restoring that table is created and left empty. So when the Plausible container starts, it tries to re-run the migrations which will fail because those tables etc already exist.

So the question is.... how can I back up and restore the Clickhouse database used by Plausible?

Thanks!

[ukutaht]

Good question. Currently I still use full OS image based backups so it's not really an issue for me.

I don't see a quick and easy way to solve this. Maybe switching from TinyLog to MergeTree would work even though TinyLog is a much more appropriate table engine for migrations. This would require some work on the clickhouse_ecto library and changing existing schema_migrations tables.

[ruslandoga]

👋 @vitobotta

ClickHouse migrations are defined in https://github.com/plausible/analytics/tree/master/priv/clickhouse_repo/migrations

[vitobotta]

Thanks @ruslandoga

[schnerring]

The new location for future versions is: https://github.com/plausible/analytics/tree/master/priv/ingest_repo/migrations

Version v1.5.1 migrations can be found at the old location: https://github.com/plausible/analytics/tree/v1.5.1/priv/clickhouse_repo/migrations

[ruslandoga]

Yes, the migrations have been moved. Thank you for the update!

[schnerring]

Additionally, setting USE_EMBEDDED_BACKUP_RESTORE=true with clickhouse-backup v2.0.0+ now supports backing up non-MergeTree tables.

However, it requires ClickHouse v23+ and is still experimental, see: Altinity/clickhouse-backup#238 (comment)

[bradkane]

@ukutaht I appreciate your input on this and understand that Q4 2021 is the infrastructure quarter. I really like your idea of using DO's Managed POSTGRES solution. And a clickhouse backup option to object storage makes perfect sense. We are using that same strategy on another project (we send the objects to Amazon S3 rather than DO Spaces). When we established this system, DO Spaces was very new and limited. In the future, it might be helpful for others to share some metrics on production database sizes (POSTGRES, Clickhouse) for a given number of recorded events (assuming that is the right proxy for size). Currently we have only a dev Plausible server (and yes, all on one droplet), but with the upcoming release of city level geo tracking, we will spin up a self-hosted production server as well.

[ukutaht]

I've actually hit very different bottlenecks with our system so far and for us it's quite a bit more complex than just giving some specs for certain number of events.

Early on some of our queries crashed the whole database because they would attempt to use more memory than was available on the machine. We gave it way more memory now so it doesn't happen. I believe Clickhouse also added some safeguards to make sure it runs OK on machines with <64GB ram.

Storage is obviously an ongoing concern. Luckily Clickhouse is very good at compression.

Most recently we started having problems with high CPU utilization which increased query latency to unacceptable territory.

I believe CPU util is not a huge concern for most self-hosters. See, it's very different running a single site with 100m monthly events vs 10,000 sites with 100m monthly events combined. This is because all the weekly reports, spike notifications and regular dashboard views add up to a lot of queries. If you're just self-hosting, you probably don't have the problem of trying to run 10,000 email reports on a Monday, each triggering at least 5 heavy DB queries :)

And because we run it very differently than self-hosters, it's not easy for me to provide specs that are relevant to you. Our requirements are quite different and I don't have much experience operating Plausible like self-hosters do.

[ukutaht]

I do run a small instance for self-hosting setup testing purposes. It's the $10/mo DO droplet with 1vCPU and 2GB ram. It cannot report one year's worth of stats from our live demo, for example, Clickhouse just crashes. I guess I can try bumping that up to see what sort of specs are needed to run a self-hosted instance that can handle our live demo.

[bradkane]

Thinking about this a little. Are the heavy DB queries (when creating reports to be emailed) to the Clickhouse DB, POSTGRES DB or both? Would separating Plausible into 3 droplets (Plausible core, Clickhouse, POSTGRES) help this situation? As you know, droplets are very reasonable. The POSTGRES droplet component could be DO's Managed POSTGRES for many benefits. Can large accounts (2M monthly events) reside in the same self-hosted instance as 10 very small websites with perhaps 1000 monthly events each?

[ukutaht]

Are the heavy DB queries (when creating reports to be emailed) to the Clickhouse DB, POSTGRES DB or both?

Just Clickhouse

Would separating Plausible into 3 droplets (Plausible core, Clickhouse, POSTGRES) help this situation?

This is how I have it set up already.

Can large accounts (2M monthly events) reside in the same self-hosted instance as 10 very small websites with perhaps 1000 monthly events each?

I think if you're provisioned for a site with 2M monthly events then adding 10 tiny sites will not be a problem at all. The 'many small sites' problem we have is dealing with >10k sites. 10 is nothing :). In this scenario your bottleneck will be the one big site.

[vitobotta]

Hi all, has anything changed / become easier since I started this discussion a while ago? Is there an easy way to properly back up the data now? Thanks @ukutaht