Re: More activity in pg_stat_activity

Erik Jones <erik(at)myemma(dot)com> writes:
> Before migrating to 8.2, even during peak times, unless queries were
> seriously stacking (not completing in a timely manner), we'd see at most
> 50 - 100 queries active at any given time (we did have
> stats_command_string = on). Since the migration, during peak times it's
> not uncommon to see the query count vary radically between around 80 and
> the upper 400s (we have max_connections set to 512). This variation is
> per second and when the count is high, the vast majority listed are
> sub-second. It would seem that this is due to some fine tuning
> somewhere on th backside of 8.2 versus previous versions. Was there
> previously a more limited precision to the query lengths that would be
> picked up by the pg_stat_activity view?

Hmm ... 8.2 has a completely rewritten pg_stat_activity implementation,
but I'm not sure why it would show more active queries. The old code
essentially showed you snapshots taken every half second, whereas the
new code gives you The Truth as of the start of your transaction.
So it should be more up-to-date but I'm not seeing why the old method
would have capped the number of active entries to less than what you see
now. Maybe there was some weird scheduling interaction before? (As in,
the stats collector was more likely to be able to run and push out its
snapshot when there were fewer active backends?) Or maybe the stats
subsystem is just reflecting reality, and some other change(s) elsewhere
in 8.2 allow it to achieve higher concurrency than you got before.
I dunno, but it would be worth looking closer to try to figure out
what the story is. Do you have numbers from before from other
monitoring tools such as vmstat, that you could compare to 8.2?

regards, tom lane