Question:
Tell me what indicators of performance and health of the database should be monitored in postgresql?
Answer:
- First of all, of course, you need to monitor the main parameters of the OS. Loading the CPU, Memory, Swap, Disks. In the case of Linux, utilities will help (top, htop, iostat, free)
- Next, you need to look at which requests take the most CPU time, and of course try to optimize them. Pg_stat_statements will help with this, powa ( http://dalibo.github.io/powa/ )
- Next, it is very important to monitor table statistics such as index_scan, sequential_scan and locks (ideally, there should be fewer seq_scans). This data can be taken from the pg_stat_user_tables, pg_locks tables.
- If connection polling is used, then you need to monitor the number of connections.
Well, for good reason, you need to screw up the monitoring framework for collecting and storing statistics. Personally, I use sensu (http_s: //sensuapp.org/) already with community plugins (http_s: //github.com/sensu-plugins/sensu-plugins-postgres). And of course powa. In case of problems, you can of course still have a lot to see, but these are the main parameters and basically there are enough of them.