How to diagnose where that terrible load on your PostgreSQL database server is coming from.
Determining Load
Before continue it's important to understand what heavy load for you is. Also, you may have the kind of system that needs to have large query volumes.
If you've come here because all of a sudden your system is slow and you don't know why, we can help you find the answer.
Resource Consumption
What are the resources that are being consumed the most?
Use free and iotop and htop to see, are you chewing RAM, I/O or CPU.
RAM Usage
If free shows you that you are using swap, you are running out of RAM.
Your best option is to increase the physical memory of the machine.
If this is not possible, try to stop all other non-essential services to reclaim that room.
I/O Usage
To determine if I/O is the issue, look at how much wait time your system has.
In top you can see this as the wa time.
Cpu(s): 0.3%us, 0.0%sy, 0.0%ni, 99.6%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st
So, if iotop is showing high values, find out where they are - in read or write side and for which processes.
iotop iotop -b5 -s1
CPU Usage
Using htop you can see a lot of information at once, if there is a lot of red in the CPU area, that is wait time load.
If the CPUs are being used at high amounts but the bars are green, that means lots of user-space time is being chewed.
PostgreSQL Specific Performance
The tool pg_top is essential, it can show in near real-time the queries and connections.
pg_top pg_top -s2
Query Logging
Enable the Query logger to see which of your queries are the most frequent, then use EXPLAIN ANALYZE to determine their costs.
Make the following adjustments to postgresql.conf.
log_duration = 'yes' log_statement = 'all'
Restart the database server, then use pg-query-log-stat.pl to give you details.
From the queries produced above you can then analyse them, determine which, if any, can be optimised.