I'm using postgreSQL 9.5 on Ubuntu 16.04
About an hour ago, I realized my site was super slow and unusable. I checked my DigitalOcean console, and realized CPU usage jumped to 100%. When I ran top in console, postgres was the culprit.
I did some troubleshooting but couldn't figure out the issue. So I decided to turn off the server and see if the CPU usage went down. 5 minutes after shutting down the server, the CPU usage finally crashed from 100% to 0%.
After restarting the server, the site had the same problem and was unusable. I was on the verge of tears when 2 minutes after the server restart, everything suddenly went back to normal without me doing anything.
So what caused this? I've experienced a similar issue in the past, where postgres would cause huge spikes and make the website unusable, only to fix itself an hour later
EDIT: It is back at 100% again.
Best Answer
Most likely an sql query that uses too many resources or retrieves a very large amount of data (swap to temporary files). To check you can enable log_min_duration_statement setting to make the server log beyond the specified duration of query exection. To do this simply add (or uncomment) this parameter in postgresql.conf file with appropriate duration (2s is a good start), then reload (no need to restart). When the problem occur next time check the log file content. If this is the problem you'll find the text of your erroneous request (s) and even how offen they run.
For further details (see 18.8.2. When To Log):PostgreSQL 9.5 doc.