I've read @Mike answer to How to tell if database tuning is fruitful (PostgreSQL), and that's the kind of approach I'm thinking about for monitoring the performance of my database (of a web application, many connections, heavy writes and reads all the time, very dynamic data).

The approach sums up like this: I want to know if my database can keep up with the workload, if it has the same readiness over time, and ultimately if my tuning and cleaning is useful. So for a general wellbeing indicator, monitoring the query time (for representative queries used by the web application and data flows) seams sensible and compact to me (query time increases without substantial changes => performance is degrading). But my question is, is it? Besides the question linked I've found no other source for such an approach, and I have still some holes on how to implement all this, so knowing more from other examples/experiences or manuals would be very useful. Even knowing that this approach is not worth doing would be interesting.

And FYI, I've read about pgbench, but it seams so aseptic to me, I mean, it surely gives a reference point, but what it says about my tables, my indexes, about the alive environment that is my database? (I hope it makes sense to you what I mean with that) Maybe I'm looking at pgbench in the wrong way, but that's exactly what I want to find out!

Note: I work with Postgres, so I Postgres specific answer would be awesome... but not necessary!

  • 3
    This is way too broad. You define what "performance" and "wellbeing" mean to you, determine metrics that reflect those criteria, and monitor them. You will have to collect also lower-level metrics, such as provided by pg_stat* views, which will give you the necessary troubleshooting information when your basic metrics go out of the acceptable range. – mustaccio Oct 20 '20 at 20:17

In general I agree somewhat: if the customers are happy, don't start tuning.

But there are certainly things that can go wrong without you noticing before it is too late. Here is a random list of things that should cause an alert, without a claim to completeness:

  • Is there enough free disk space?
  • Is the CPU overloaded?
  • Is the I/O subsystem overloaded?
  • Does the database grow unreasonably?
  • Are there problems with WAL archiving?
  • Are there too many client connections?
  • Are there long running queries?
  • Are there transactions that remain open for a long time?
  • Are there databases that are in danger of suffering data loss from transaction wraparound?
  • Can you connect to the database and run a simple query?
  • Is replication delayed too much?
  • Are there any tables with too many dead tuples?
  • Did you get errors from data corruption?
  • Are there any stale prepared transactions?

pgbench is more useful than you think: you can use it with custom scripts that even support a simple scripting language to simulate different workloads.

But of course it is essentially a tool to generate artificial load to test the performance impact of certain changes and to make sure you didn't misconfigure PostgreSQL or the operating system.

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.