On Thu, Nov 15, 2012 at 11:50 AM, Vlad <marchenko@gmail.com> wrote:
> there is no big spike of queries that cause that, queries come in relatively
> stable pace. It's just when the higher rate of queries coming, the more
> likely this to happen. yes, when stall happens , the active queries pile up
> - but that's the result of a stall (the server reacts slow on a keypress,
> not to mention queries execution), not the cause.
>
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu-----
> r b swpd free buff cache si so bi bo in cs us sy id
> wa st
> 1 0 0 279240 12016 14431964 0 0 32 0 197852 4299 15 9
> 76 0 0
> 4 0 0 225984 12024 14419696 0 0 0 64 197711 5158 11 9
> 79 1 0
> 0 0 0 260112 12024 14413636 0 0 48 0 196708 4618 17 10
> 73 0 0
> 6 0 0 233936 12024 14375784 0 0 104 0 179861 4884 19 17
> 64 0 0
> 30 0 0 224904 12024 14354812 0 0 8 0 51088 1205 9 86
> 5 0 0
> 72 0 0 239144 12024 14333852 0 0 144 0 45601 542 2 98
> 0 0 0
> 78 0 0 224840 12024 14328536 0 0 0 0 38732 481 2 94
> 5 0 0
> 22 1 0 219072 12032 14250652 0 0 136 100 47323 1231 9 90
> 1 0 0
hm. well, we can definitely rule out i/o. I reviewed your last
posting, and you said:
"Out of the top 50 processes in top, 48 of them are postmasters, one
is syslog, and one is psql. Each of the postmasters have a high %CPU,
the top ones being 80% and higher, the rest being anywhere between 30%
- 60%. Would postmaster 'queries' that are running attribute to the
sys CPU usage, or should they be under the 'us' CPU usage?"
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