Skip site navigation (1) Skip section navigation (2)

Re: Stats collector performance improvement

From: Hannu Krosing <hannu(at)skype(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>,Jan Wieck <JanWieck(at)Yahoo(dot)com>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Stats collector performance improvement
Date: 2006-01-02 21:48:15
Message-ID: 1136238496.4256.9.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patchespgsql-performance
Ühel kenal päeval, E, 2006-01-02 kell 15:20, kirjutas Tom Lane:
> [ moving to -hackers ]
> 
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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



In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2006-01-02 21:48:45
Subject: Re: Stats collector performance improvement
Previous:From: Qingqing ZhouDate: 2006-01-02 21:03:20
Subject: Re: Stats collector performance improvement

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-01-02 21:48:45
Subject: Re: Stats collector performance improvement
Previous:From: Qingqing ZhouDate: 2006-01-02 21:03:20
Subject: Re: Stats collector performance improvement

pgsql-patches by date

Next:From: Tom LaneDate: 2006-01-02 21:48:45
Subject: Re: Stats collector performance improvement
Previous:From: Tom LaneDate: 2006-01-02 21:40:19
Subject: Re: TODO item: list prepared queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group