Unusually high CPU usage

[supermathie (Michael Brown)]

@char

I´d hypothesize having not enough memory: queries go into swap.

(not swap, but they’re reading from disk)

this fits with my earlier observations:

@supermathie

there’s only around 6GB taken up by applications. There’s a lot of reading happening, which at a guess I’d say (along with sidekiq being busy) maybe postgres is doing a lot of reading of old data from disk, maybe to do some stats jobs. If the database doesn’t fit in RAM, it’s potentially going to do a lot of reading if that older data is being accessed.

@char

pg_total_relation_size

Note this is the size of the relation and indexes. Compare with pg_relation_size.

@char

  2501 | 2026-02-07 11:25:01.028892+00 | active | IO              | DataFileRead | UPDATE posts                                                                                        +
       |                               |        |                 |              | SET percent_rank = X.percent_rank                                                                   +
       |                               |        |                 |              | FROM (                                                                                              +
       |                               |        |                 |              |   SELECT posts.id, Y.percent_rank                                                                   +
       |                               |        |                 |              |   FROM posts

This is from ScoreCalculator, part of PeriodicalUpdates.

@char

many statistics recalculations are hogging up resources (ie. Jobs::DestroyOldDeletionStubs - 419 seconds; Jobs::AboutStats - 472 seconds; Jobs::EnsureDbConsistency 2083 seconds; Jobs::TopRefreshOlder - 2727 seconds

This is your finding that needs to be solved. By comparison, here on meta Jobs::EnsureDbConsistency takes <2min and Jobs::TopRefreshOlder takes <10s:

Postgres needs more memory. Give it as much as you can.

You might also see benefit from a VACUUM ANALYZE or VACUUM ANALYZE FULL. Doing the first never hurts.

I’d probably do, in order:

• vacuum analyze
• pause sidekiq then vacuum analyze full (this freezes the tables to rewrite them fully, may incur some failures while it runs)
• more memory to postgres