6

We have an UPDATE in our 2am nightly load that has on two occasions pegged CPU at 100% and never completed after many hours. I strongly suspect that this is happening because of inaccurate statistics causing nested loops in a join. I am not able to reproduce it in lower environments with the same hardware specs and postgres configuration.

I need to prove that the nested loops are occurring before I can put in an RFC to address the problem (e.g. adjusting analyze and autovacuum settings). I have setup auto_explain but this only seems to capture plans for queries that actually finish. How can I log plans for queries that do not finish?

In SQL Server I would be able to do this by running sp_whoisactive with @get_plans=1. I am looking for something similar in Postgres. The only idea I've had so far would be to run EXPLAINs using cron, but this seems very hacked together.

  • 1
    What kills them after several hours, before they complete? It seems like that would be the best place to log the plan. – jjanes Feb 10 '17 at 17:26
  • We end up having to kill the pid. We know which query froze, but we don't have the plan. – Andrew Feb 10 '17 at 20:30
  • Unfortunately there is no equivalent on postgres. You can take query text from server logfiles, and run EXPLAIN on it. That's all. – filiprem Sep 22 '17 at 16:36
1

The auto_explain feature seems to fit this use case, if you can take a brief service outage to enable it. It logs a plan for any statement exceeding a time threshold whether or not it has completed yet. It can also log plans before execution starts, so you can even get the plan of a statement that blows out memory!

0

To be honest, logging long-running queries seems via cron does seem like the most KISS solution to me:

psql -tc "select now() as t, pid, usename, query from pg_stat_activity where state != 'idle'" > /tmp/pg.running.txt

Then you can spot the queries that stay there for several hours and run EXPLAIN on them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.