2

I want to better understand how to debug and fix performance issues in PostgreSQL.

I have a query that usually costs 1 second to run. However, for some reason, it started taking 30 seconds to run for a period of time! After some minutes and some explain analyze invocations, it went back to normal. I didn't do anything to fix it, nor did I understand why it got better all of a sudden.

How do I debug a problem like this? What kind of logging and instrumentation should I have in place to understand what caused this and how to fix it?

Improve this question
4
  • 2
    System busy overall? Table(s) undergoing a large update? Large analytic type query being run? The Gods not appreciating your invocations? :-) On a more serious note, this is the reason you should have monitoring software installed before problems arise! Do you have anything like sysstat or nagios or similar?
    – Vérace
    Commented Apr 19, 2018 at 3:14
  • No. Just some simple logs in pg. Was considering using the ELK stack to get all kinds of verbose logs
    – ivarec
    Commented Apr 19, 2018 at 3:15
  • 1
    Have you considered the excellent (and Open Source) sysstat tools?
    – Vérace
    Commented Apr 19, 2018 at 3:34
  • Go this link. It may help you..! dbrnd.com/2016/02/…
    – Ramanna Gunde
    Commented Apr 19, 2018 at 5:41

1 Answer 1

Reset to default
2

It is a very broad question, even more so as you give absolutely no details about your setup and kind of database (volume, type of queries, active connections, size of RAM, dedicated server or not, etc.)

You can start by enabling PostgreSQL to log slow queries, see the log_min_duration_statement in the configuration. That will give you historical data that you would then be able to analyze and maybe correlate with other things (like from the list of @Vérace)

You will then have various tools to help, as described on https://wiki.postgresql.org/wiki/Logging_Difficult_Queries :

  • pgFouine
  • PQA
  • EPQA
  • pgsi
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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