Postgres queries intermittently running extremely slowly

Question

We have some queries that are running extremely slowly intermittently in our production environment. These are JSONB intersection queries which normally return in milliseconds, but are taking 30-90 seconds.

We have tried to look at co-occurring server conditions such as RAM, CPU and query load, but there is nothing obvious. This affects a very small minority of queries - probably less than 1%. This does not appear to be a query optimization issue as the affected queries themselves are varied and in some cases very simple.

We've reproduced the same environment as far as possible on a staging server and loaded it heavily and the issue does not occur.

Can anyone suggest possible steps to investigate what is occurring in Postgres when this happens, or anything else we should consider? We have been working on this for over a week and are running out of ideas.

Laurenz Albe · Accepted Answer · 2018-10-29 08:56:22Z

1

It is difficult to guess the cause of that problem; one explanation would be locks.

You should use auto_explain to investigate the problem.

In postgresql.conf, use the following settings:

# log if somebody has to wait for a lock for more than one second
log_lock_waits = on
# log slow statements with their parameters
log_min_duration_statement = 1000
# log the plans of slow statements
shared_preload_libraries = 'auto_explain'
# configuration for auto_explain
auto_explain.log_nested_statements = on
auto_explain.log_min_duration = 1000

Then restart PostgreSQL.

Now all statements that exceed one second will have their plan dumped in the PostgreSQL log, so all you have to do is to wait for the problem to happen again, so that you can analyze it.

You can also get EXPLAIN (ANALYZE, BUFFERS) output if you set

auto_explain.log_buffers = on
auto_explain.log_analyze = on

That would make the log much more valuable, but it will slow down processing considerably, so I'd be reluctant to do it on a production system.

answered Oct 29, 2018 at 8:56

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

see sharper Over a year ago

Great advice. Thank you.

NimChimpsky Over a year ago

you can also reload config without requiring a restart with : pg_ctl reload

Laurenz Albe Over a year ago

@NimChimpsky With most parameters that is true, but shared_preload_libraries requires a restart.

Collectives™ on Stack Overflow

Postgres queries intermittently running extremely slowly

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related