From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Qingqing Zhou <zhouqq@cs.toronto.edu>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Stats collector performance improvement
Date: Tue, 03 Jan 2006 09:40:53 +0000
Message-ID: <1136281253.5052.113.camel@localhost.localdomain> (raw)
In-Reply-To: <12503.1136238525@sss.pgh.pa.us>
References: <200601021840.k02Ieed21704@candle.pha.pa.us>
<11924.1136233224@sss.pgh.pa.us>
<dpc4cg$lm6$1@news.hub.org>
<12503.1136238525@sss.pgh.pa.us>
On Mon, 2006-01-02 at 16:48 -0500, Tom Lane wrote:
> The two compromises that were made in the original stats design to make
> it fast were (1) stats updates lag behind reality, and (2) some updates
> may be missed entirely. Now that we have a couple of years' field
> experience with the code, it seems that (1) is acceptable for real usage
> but (2) not so much.
We decided that the stats update had to occur during execution, in case
the statement aborted and row versions were not notified. That means we
must notify things as they happen, yet could use a reliable queuing
system that could suffer a delay in the stats becoming available.
But how often do we lose a backend? Could we simply buffer that a little
better? i.e. don't send message to stats unless we have altered at least
10 rows? So we would buffer based upon the importance of the message,
not the actual size of the message. That way singleton-statements won't
generate the same stats traffic, but we risk losing a buffers worth of
row changes should we crash - everything would still work if we lost a
few small row change notifications.
We can also save lots of cycles on the current statement overhead, which
is currently the worst part of the stats, performance-wise. That
definitely needs redesign. AFAICS we only ever need to know the SQL
statement via the stats system if the statement has been running for
more than a few minutes - the main use case is for an admin to be able
to diagnose a rogue or hung statement. Pushing the statement to stats
every time is just a big overhead. That suggests we should either have a
pull or a deferred push (longer-than-X-secs) approach.
Best Regards, Simon Riggs
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, tgl@sss.pgh.pa.us, zhouqq@cs.toronto.edu, pgsql-hackers@postgresql.org
Subject: Re: Stats collector performance improvement
In-Reply-To: <1136281253.5052.113.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox