X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 898EE9DC9BC
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sun,  8 Jan 2006 08:18:05 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 74697-05
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sun,  8 Jan 2006 08:18:08 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.skype.net (mail.skype.net [195.215.8.149])
	by postgresql.org (Postfix) with ESMTP id 584C39DC833
	for <pgsql-hackers@postgresql.org>;
	Sun,  8 Jan 2006 08:18:03 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mail.skype.net (Postfix) with ESMTP id 10AA34DD80;
	Sun,  8 Jan 2006 13:18:05 +0100 (CET)
Received: from [192.168.1.102] (80-235-57-196-dsl.trt.estpak.ee
	[80.235.57.196])
	by mail.skype.net (Postfix) with ESMTP id 9724F4DD7B;
	Sun,  8 Jan 2006 13:18:02 +0100 (CET)
Subject: Re: Stats collector performance improvement
From: Hannu Krosing <hannu@skype.net>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	Qingqing Zhou <zhouqq@cs.toronto.edu>, pgsql-hackers@postgresql.org
In-Reply-To: <1136281253.5052.113.camel@localhost.localdomain>
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>
	<1136281253.5052.113.camel@localhost.localdomain>
Content-Type: text/plain; charset=utf-8
Date: Tue, 03 Jan 2006 23:42:53 +0200
Message-Id: <1136324574.4256.17.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.2.3 (2.2.3-2.fc4) 
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.906 required=5 tests=[AWL=-0.666,
	DATE_IN_PAST_96_XX=1.572]
X-Spam-Score: 0.906
X-Spam-Level: 
X-Archive-Number: 200601/253
X-Sequence-Number: 78357

Ühel kenal päeval, T, 2006-01-03 kell 09:40, kirjutas Simon Riggs:

> 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. 

Interestingly I use pg_stat_activity view to watch for stuck backends,
"stuck" in the sense that they have not noticed when client want away
and are now waitin the TCP timeout to happen. I query for backends which
have been in "<IDLE>" state for longer than XX seconds. I guess that at
least some kind of indication for this should be available.

Of course this would be much less of a problem if there was a
possibility for sime kind of keepalive system to detect when
client/frontend goes away.

> Pushing the statement to stats
> every time is just a big overhead. That suggests we should either have a
> pull 

I could live with "push", where pg_stat_activity would actually ask each
live backend for its "current query". This surely happens less often
than queries are performed (up to few thousand per sec)

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