this is not my report, probably from a thread that I've referenced as having a common symptoms. Here is info about my db:
Postgresql 9.1.6.
Postgres usually has 400-500 connected clients, most of them are idle.
Database is over 1000 tables (across 5 namespaces), taking ~150Gb on disk.
We have mostly select statements (joins across few tables), using indexes and resulting in a small number of records returned.
So mostly small lookups across few tables joining by a primary key / index.
Also, I just perform an experiment - I switched our app over to using hot PITR instead of master and it experienced the same problem. So since PITR db can only perform read-only queries, there is no write-locks (except maybe when pitr is playing wal records from the master?), nevertheless SYS CPU jumped sky.
-- Vlad
Is this still true? Can we capture strace from one of the high %
postmasters to see if there's any clues there. Maybe we've uncovered
some type of weird spinlock contention issue. How large is your
database (or at least the portion of it that's commonly used)? Would
you characterize your queries as mostly small lookups, scans, or a
mix?
merlin
