0

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.

30 minutes of agony

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.

2
  • Have you checked the logs? Oct 4, 2018 at 0:29
  • How vulnerable and secure is your website? Disable your website and keep the database running temporarily if there's a time pattern. See if it still occurs with no traffic. If it does not you could be vulnerable to XSS or SQL injection and someone or bots are attacking you. Another possibility is that your site has a poorly written query under a particular function causing the spike. And of course if you upgrade to more resources through DigitalOcean it can handle the load, provided it's enough to not get bottlenecked.
    – Brad
    Oct 13, 2018 at 15:09

1 Answer 1

1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .