6

I upgraded my RDS instance (db.t2.medium) from Postgres 9.5 to 9.6 couple of days back. Since the upgrade, the CPU usage was almost 100% all the time. This brought down my application. Presuming that the problem is with the 9.6 version, I did another upgrade to 10.6. It got better but still considerably higher than the CPU usage for the original 9.5 version.

I haven't changed the application code during the upgrade. How can I check the reason for the increased CPU usage and possibly fix it?

enter image description here

| improve this question | | | | |
  • Don't presume, run a regular "ps -ef" and capture the output to be sure it's Postgres. – Gerard H. Pille Feb 6 '19 at 9:05
  • since it is an RDS instance, AWS would run only Postgres in the VM – Lenin Raj Rajasekaran Feb 6 '19 at 9:10
  • Even so, Postgres consists of multiple processes. – Gerard H. Pille Feb 6 '19 at 14:41
  • Did you execute all steps as in postgresql.org/docs/9.6/pgupgrade.html ? – Gerard H. Pille Feb 6 '19 at 14:57
  • 2
    Did you run ANALYZE after the upgrade (or does RDS do that for you automatically)? – jjanes Feb 6 '19 at 15:04
5
ANALYZE VERBOSE;

You need to run an ANALYZE query on the whole database using the above command.

The issue is that the query plans postgres has generated are optimised for the previous version of postgres, when you do an RDS update it does not implicity regenerate these plans, this needs to be done manually (I'm sure there's a reason why AWS doesn't do this manually but I've really no idea why).

In my case I saw roughly a week of extremely high CPU usage, just as in your case, then after running ANALYZE my cpu dropped back to it's previous baseline. As you can see in the image below the upgrade (in my case from 9.4 - 9.5) was run on 11/27, the analyze query was run on 12/02.

(The VERBOSE is not strictly necessary but it's useful to be able to watch the progress of the command)

Postgres RDS CPU usage over the course of a week

| improve this answer | | | | |
  • 1
    I appreciate you. – Chase Mar 24 at 11:55

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.