AkkomaGang/akkoma

Fork 116

[bug] Degraded db performance a couple of days after a reindex #954

New issue

Closed

opened 2025-07-28 01:55:47 +00:00 by blacklight · 6 comments

blacklight commented

2025-07-28 01:55:47 +00:00

Your setup

OTP

Extra details

Arch Linux package

Version

3.15.2

PostgreSQL version

17.5

What were you trying to do?

The performance of the database is heavily degraded, with several queries timing out, just a few days after doing a full reindex.

I run a single user instance, so I wouldn't expect this to happen so often.

I usually optimize the db through:

akkoma@localhost:akkoma> vacuum full; reindex database akkoma;

Which usually improves performance once the timeouts make the instance nearly unusable.

I used to run those commands once every 2-3 months before. But now I've noticed that already after 7-10 days performance is so bad that I have to vacuum and reindex everything again (which usually takes ~4-5h).

What did you expect to happen?

db performance shouldn't degrade so fast

What actually happened?

7-10 days after a full vacuum+reindex the db performance is bad again and many queries time out

Logs

No response

Severity

I cannot use it as easily as I'd like

Have you searched for this issue?

I have double-checked and have not found this issue mentioned anywhere.

### Your setup OTP ### Extra details Arch Linux package ### Version 3.15.2 ### PostgreSQL version 17.5 ### What were you trying to do? The performance of the database is heavily degraded, with several queries timing out, just a few days after doing a full reindex. I run a single user instance, so I wouldn't expect this to happen so often. I usually optimize the db through: ``` akkoma@localhost:akkoma> vacuum full; reindex database akkoma; ``` Which usually improves performance once the timeouts make the instance nearly unusable. I used to run those commands once every 2-3 months before. But now I've noticed that already after 7-10 days performance is so bad that I have to vacuum and reindex everything again (which usually takes ~4-5h). ### What did you expect to happen? db performance shouldn't degrade so fast ### What actually happened? 7-10 days after a full vacuum+reindex the db performance is bad again and many queries time out ### Logs _No response_ ### Severity I cannot use it as easily as I'd like ### Have you searched for this issue? - [x] I have double-checked and have not found this issue mentioned anywhere.

blacklight added the

bug

label

2025-07-28 01:55:47 +00:00

floatingghost commented

2025-07-28 14:38:29 +00:00

Owner

hm, are you using a high amount of relays? this can happen if you try to pull in a lot of posts via the relay system

blacklight commented

2025-07-28 15:14:16 +00:00

Author

Only two relays, https://relay.101010.pl/actor and https://relay.fedi.agency/actor. They're quite high-volume in terms of published content, but they are also two popular solutions used by many of the Mastodon instances I know of.

floatingghost commented

2025-07-28 15:52:47 +00:00

Owner

a poast relay... ew

relays can cause very sparse indices due to the volume of posts
you also didn't include your instance resources in your initial issue description
if you're using high-volume relays and are running on, say, a raspberry pi, it'll die pretty fast

a poast relay... ew relays can cause very sparse indices due to the volume of posts you also didn't include your instance resources in your initial issue description if you're using high-volume relays and are running on, say, a raspberry pi, it'll die pretty fast

blacklight commented

2025-07-28 15:58:21 +00:00

Author

a poast relay... ew

I protected myself by blocking poa.st and anything that looks like it (which I guess should also prevent the creation of db records from those high-volume/low-quality instances...?)

I'm running Akkoma on an Intel miniPC with 16GB of RAM, and both the application and Postgres are on the same SSD drive. And my account has also >2k followers and many interactions - not sure if that can also be a reason for frequent index updates?

I mean, I'd definitely expect a RPi with an SD card to choke with this traffic, but my machine is more mid-tier, so that puzzles me a bit.

If blocked instances can't create db records could it be a solution to block a bunch of high-volume instances on those relays that are known to create a lot of content that I'm not interested in?

> a poast relay... ew I protected myself by blocking poa.st and anything that looks like it (which I guess should also prevent the creation of db records from those high-volume/low-quality instances...?) I'm running Akkoma on an Intel miniPC with 16GB of RAM, and both the application and Postgres are on the same SSD drive. And my account has also >2k followers and many interactions - not sure if that can also be a reason for frequent index updates? I mean, I'd definitely expect a RPi with an SD card to choke with this traffic, but my machine is more mid-tier, so that puzzles me a bit. If blocked instances can't create db records could it be a solution to block a bunch of high-volume instances on those relays that are known to create a lot of content that I'm not interested in?

blacklight commented

2025-07-28 22:18:06 +00:00

Author

Ok I think I can close this issue.

I had dragged those two relays with me since my instance was still on Mastodon because they were two popular options a few years ago, but I never bothered to read the summary on the relay.fedi.agency endpoint - I thought that it only provided poast content among others (so I could only block that and a few other instances), but now I see that it's actively managed by poast.

Removing that crap has actually sliced both my db fragmentation and my CPU and memory usage - no idea of how much crap they were pushing down that pipe.

I see that performance is better now, and I guess it'll get even better after a last round of vacuum+reindex.

Ok I think I can close this issue. I had dragged those two relays with me since my instance was still on Mastodon because they were two popular options a few years ago, but I never bothered to read the summary on the relay.fedi.agency endpoint - I _thought_ that it only provided poast content among others (so I could only block that and a few other instances), but now I see that it's _actively_ managed by poast. Removing that crap has actually sliced both my db fragmentation and my CPU and memory usage - no idea of how much crap they were pushing down that pipe. I see that performance is better now, and I guess it'll get even better after a last round of vacuum+reindex.

blacklight closed this issue

2025-07-28 22:18:06 +00:00

Oneric commented

2025-07-29 18:50:58 +00:00

Owner

btw, you mentioned vacuuming and reindexing, but do you also occasionally prune old remote data? If not your db and its indexes will continue to grow, becoming more expensive to process eventhough you likely do not care about the vast majority of the stored data

Also, to my knowledge since Postgresql 9.0 (so for quite a while already), reindexing after a VACUUM FULL is but a waste of time. Unless you want to return freed up space to the OS, there likely is also no need to use FULL (but then you need a separate reindex).

And in general, while it worked out this time it might be a good idea to set up Prometheus metric scraping. This will allow you and us to know what your server is actually doing and spending time on; else “performance degrades” is too vague to do anything but blindly guess

btw, you mentioned vacuuming and reindexing, but do you also occasionally [prune old remote data](https://docs.akkoma.dev/develop/administration/CLI_tasks/database/#prune-old-remote-posts-from-the-database)? If not your db and its indexes will continue to grow, becoming more expensive to process eventhough you likely do not care about the vast majority of the stored data Also, to my knowledge since Postgresql 9.0 (so for quite a while already), reindexing after a `VACUUM FULL` is but a waste of time. Unless you want to return freed up space to the OS, there likely is also no need to use `FULL` (but then you need a separate reindex). And in general, while it worked out this time it might be a good idea to set up Prometheus metric scraping. This will allow you and us to know what your server is actually doing and spending time on; else “performance degrades” is too vague to do anything but blindly guess

👍 1

No milestone

No project

No assignees

3 participants

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

AkkomaGang/akkoma#954

No description provided.

Rows
Columns