Subject: Re: Stats collector performance improvement
From: Hannu Krosing <hannu@skype.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>,
	Jan Wieck <JanWieck@Yahoo.com>, pgsql-hackers@postgreSQL.org
In-Reply-To: <11924.1136233224@sss.pgh.pa.us>
References: <200601021840.k02Ieed21704@candle.pha.pa.us>
	<11924.1136233224@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8
Date: Mon, 02 Jan 2006 23:48:15 +0200
Message-Id: <1136238496.4256.9.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

Ühel kenal päeval, E, 2006-01-02 kell 15:20, kirjutas Tom Lane:
> [ moving to -hackers ]
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I did some research on this because the numbers Tom quotes indicate there
> > is something wrong in the way we process stats_command_string
> > statistics.
> > [ ... proposed patch that seems pretty klugy to me ... ]
> 
> I wonder whether we shouldn't consider something more drastic, like
> getting rid of the intermediate stats buffer process entirely.
> 
> The original design for the stats communication code was based on the
> premise that it's better to drop data than to make backends wait on
> the stats collector.  However, as things have turned out I think this
> notion is a flop: the people who are using stats at all want the stats
> to be reliable.  We've certainly seen plenty of gripes from people who
> are unhappy that backend-exit messages got dropped, and anyone who's
> using autovacuum would really like the tuple update counts to be pretty
> solid too.
> 
> If we abandoned the unreliable-communication approach, could we build
> something with less overhead?

Weell, at least it should be non-WAL, and probably non-fsync, at least
optionally . Maybe also inserts inserts + offline aggregator (instead of
updates) to avoid lock contention. Something that collects data in
blocks of local or per-backend shared memory in each backend and then
gives complete blocks to aggregator process. Maybe use 2 alternating
blocks per backend - 1 for ongoing stats collection and another given to
aggregator. this has a little time shift, but will deliver accurate
starts in the end. Things that need up-to-date stats (like
pg_stat_activity), should look (and lock) also the ongoing satas
collection blocks if needed (how do we know know the *if*) and delay
each backend process momentaryly by looking.

-----------------
Hannu