I have two amazon rds postgres instances, one is staging, one is production...

In staging, a table has 21 million rows, and in production that same table has 29 million rows.

If I do select count (*) on staging, it takes approx 3 seconds to get the count.

On the production database, that same call takes around 160 seconds.

Production's total relation size is 16168345600, and relation size is 9859072000 Staging's total relation size is 13933518848, and relation size is 8604483584

Autovacuum is on both databases...

Is there anything I can do to narrow down what is causing the drastic difference in performance?

  • Can you do a restore of prod db in staging and test there? Restore prod and stage in a diff server and test? Are the schemas identical? Index settings, disk io, and usage patterns? Are you pulling data out of memory in stage but do you have memory pressure on prod which means you're doing disk reads? – Ali Razeghi Aug 25 '16 at 18:35
  • Perhaps.. Is there a recommended way to tell about memory between two environments? I only have new relic on production so I don't have the same diagnostic tools available for staging... The boxes are the same aws hardware, staging uname -a says: Linux staging-01 4.4.15-25.57.amzn1.x86_64 #1 SMP Wed Jul 27 22:37:49 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux, and production says: Linux production-01 4.4.15-25.57.amzn1.x86_64 #1 SMP Wed Jul 27 22:37:49 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux – patrick Aug 25 '16 at 19:42
  • you can try new relic on staging too..can try trail version for 30 days if you want. – Spike Aug 26 '16 at 2:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.