But in the meantime: Hey, #mastoadmin, anybody else having trouble going from Postgres 9.6 (Stretch) to 11 (Buster)? We're seeing high rates of 502s, a whole lot of long-running CPU eating queries, and no real idea why this would happen due to a DB upgrade.
Ran vacuum and reindex, and the queries that seem to be hanging look fairly normal.
This seems to be a thing that's coming in waves – we're ok for a while, then suddenly CPU load across all cores spikes, leading to plenty of 502s.
I'm particularly confused that postgres would do this (misconfig on our part?), and that there always a couple of *idle* queries among the CPU hogs. How come?
Any hints or experiences on your side would be very appreciated – either on here or via r at rixx.de (email) or rixx at jabber.shackspace.de, or messenger pidgeon, I suppose. #mastoadmin
@rixx Our little instance's PgHero has shown “4 duplicate indexes” right from the start. We started on Debian Buster.
Not sure about unused indexes. They just went from 11(?) to 10 after I clicked “with a migration”.
Alas, our in-house competence with PostgreSQL is limited. Formerly, we used MySQL for everything, but lots of projects seem to prefer psql these days.
@chpietsch We're back again, so all is well :)
@rixx Have you tried to export and re-import it, again? Without index.
Have you tried to set the (error) logging on the highest level?
@rixx I specialise in Postgres if you still need a hand with anything.
I'd turn the query logs on (log_min_duration_statement = 0, just needs a reload) and run a query analyzer such as pgfouine on it.
Then get an example of the slower queries and EXPLAIN ANALYZE.
@rixx if something is showing as "idle" in pg_stat_activity it's not running anything. The statement shown is the last one it executed.
@russss Thanks, we found we didn't run the analyze script (which seems to do more than a plain analyze, which we ran). All good now, was just weird to figure out what was going on (especially with late-night brain on).
@russss But it's good to know you specialise in postgres. *files notes*
Is there anything you don't specialise in, though.
@rixx with most things I just know enough to be useful, usually :)
@rixx you might be interested in this view which tidies up pg_stat_activity https://github.com/russss/postgres_dba_views/blob/master/ps.sql
@rixx (although I just spotted that's broken on postgres 11 so I shall fix it now!)
@rixx I wrote this a few years ago - I think it's still mostly correct. https://russ.garrett.co.uk/2015/10/02/postgres-monitoring-cheatsheet/
...and that just reminded me about pg_stat_statements which I'd completely forgotten about!
The social network of the future: No ads, no corporate surveillance, ethical design, and decentralization! Own your data with Mastodon!