Merlin, 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 >
