Slow Sidekiq + Postmaster using 95%+ CPU (32 cores) after Postgresql Version Upgrade

Installation
19

Ok so to follow up, everything still looks good, postmaster has calmed down drastically and sidekiq is moving really fast again.

To summarise on what worked in my scenario (with default db name of ‘discourse’):

cd /var/discourse/
./launcher enter app
sudo -u postgres psql
\c discourse

Then from the postgres console, each of these below were run. Each one takes a bit of time to complete, depending on DB size, heavy CPU usage for the first two also:

VACUUM FULL VERBOSE;

REINDEX DATABASE discourse;

VACUUM VERBOSE ANALYZE;

Note: I didn’t notice a difference until VACUUM VERBOSE ANALYZE; was ran as per @Falco’s recommendation, so the first two might not have been necessary. Though, the first two seemed to be key to fixing this issue in the past on the previous version of Postgresql.

If you get an error like ‘ERROR: deadlock detected’ during REINDEX DATABASE discourse; - just try running it again until it works. This happened to me last time (previous Postgresql version).

There are some recommendations to run a concurrent reindex instead of the reindex above: PostgreSQL 12 update

However, note that @eboehnisch above got an error from the concurrent reindex, see above.

8 Likes