27 June, 2016
Every slow query situation has its own quirks. Here’s my generic template for troubleshooting them.
First, I start with the holy trinity of IT Troubleshooting Questions:
- What are you trying to do?
- What data do you want?
- What is the end result you want?
- How does your result differ from what you expected?
- “I thought I could do a 7-way join on unindexed fields in tables with over 3 million records each in less than 2 seconds. I’m pretty disappointed with Postgres’s performance.”
- What changed?
- Has it always been this slow?
- Did this happen suddenly, or did it slowly degrade?
- If suddenly, what changed? (“This started right after our last deploy.”)
20 June, 2016
Last year I commented that “autovacuum log messages are still missing in [RDS] 9.4“.
Amazon fixed that for versions 9.4.5 and up with the custom GUC rds.force_autovacuum_logging_level.
I discovered this week that it’s also available on earlier versions of RDS – I have it enabled on a 9.3.10 instance. The default value in the config is one of the debug levels; just change it to ‘log’. No restart required (assuming you already have logging configured… and you do, don’t you?)
Works as expected:
2016-06-16 18:36:41 UTC::@::LOG: automatic vacuum of table "my_db.public.my_dimension": index scans: 1
pages: 0 removed, 29457 remain
tuples: 3454 removed, 429481 remain, 0 are dead but not yet removable
buffer usage: 64215 hits, 8056 misses, 22588 dirtied
avg read rate: 1.018 MB/s, avg write rate: 2.855 MB/s
system usage: CPU 0.10s/0.88u sec elapsed 61.80 sec
This makes me very happy – no more cron job to monitor vacuums!