20

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

1 Answer 1

Reset to default
45 
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
8
  • 4
    I appreciate you.
    – Chase Finch
    Commented Mar 24, 2020 at 11:55
  • 6
    'God bless you' This saved us millions!
    – Monku
    Commented Jul 25, 2020 at 5:20
  • 3
    I wish I could upvote more than once.
    – nil
    Commented Dec 21, 2020 at 4:44
  • came here because my materialized views were dead slow after upgrading from postgres 11
    – barnacle.m
    Commented Oct 27, 2021 at 17:56
  • 1
    Observed the same issue while upgrading from v9.6 to v13.3 on AWS RDS. Post the Analyse query, the CPU stabilized to the normal baseline.
    – Srikanta
    Commented Nov 25, 2021 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.