Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 22C1A9FA433
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 28 Sep 2006 05:44:21 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 74827-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 28 Sep 2006 08:44:11 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from www.axa.it (www.axa.it [194.242.232.5])
	by postgresql.org (Postfix) with ESMTP id 2CC059FB262
	for <pgsql-performance@postgresql.org>;
	Thu, 28 Sep 2006 05:44:08 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by localhost.axa.it (Postfix) with ESMTP id 196D4A50442
	for <pgsql-performance@postgresql.org>;
	Thu, 28 Sep 2006 10:44:07 +0200 (CEST)
Received: from www.axa.it ([127.0.0.1])
	by localhost (www.axa.it [127.0.0.1]) (amavisd-new,
	port 10024) with LMTP
	id 29059-02-11 for <pgsql-performance@postgresql.org>;
	Thu, 28 Sep 2006 10:44:06 +0200 (CEST)
Received: from [192.168.0.71] (adsl-ull-84-199.48-151.net24.it
 [151.48.199.84])
	by www.axa.it (Postfix) with ESMTP id BC42EA50262
	for <pgsql-performance@postgresql.org>;
	Thu, 28 Sep 2006 10:44:05 +0200 (CEST)
Message-ID: <451B8B55.4010207@axa.it>
Date: Thu, 28 Sep 2006 10:44:05 +0200
From: Edoardo Ceccarelli <eddy@axa.it>
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: autovacuum on a -mostly- r/o table
References: <451AA1FE.9050704@axa.it>	<1159374712.46586.47.camel@home>
	<20060927162756.0a9df264.wmoran@collaborativefusion.com>
In-Reply-To: <20060927162756.0a9df264.wmoran@collaborativefusion.com>
Content-Type: multipart/alternative;
	boundary="------------010001020600040606000907"
X-Virus-Scanned: amavisd-new at axa.it
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.215 tagged_above=0 required=5 tests=HTML_MESSAGE,
	HTML_TITLE_EMPTY
X-Spam-Level: 
X-Archive-Number: 200609/379
X-Sequence-Number: 21065

This is a multi-part message in MIME format.
--------------010001020600040606000907
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


>> True,
>> but autovacuum could check load -before- and -during- it's execution and 
>> it could adjust himself automatically to perform more or less 
>> aggressively depending on the difference between those two values.
>> Maybe with a parameter like: maximum-autovacuum-load=0.2
>> that would mean: "never load the machine more than 20% for the autovacuum"
>>     
>
> This is pretty non-trivial.  How do you define 20% load?  20% of the
> CPU?  Does that mean that it's OK for autovac to use 3% cpu and 100% of
> your IO?  Ok, so we need to calculate an average of IO and CPU -- which
> disks?  If your WAL logs are on one disk, and you've used tablespaces
> to spread the rest of your DB across different partitions, it can
> be pretty difficult to determine which IO parameters you want to take
> into consideration.
>
>   
As I said before, it could be done, the main requirement is to find a 
way for pg to check for a value of the system load; of course it has to 
be an average value between disk and cpu, of course the daemon would 
have to collect sample of this values continuously, and of course 
everything would be better if the server it's only running Postgres
Still I think you are right, it wouldn't suit exactly every situations 
but it could be an "emergency" feature:
What happened to me was very clear:
server running pg8 under heavy load, cpu's were 90% idle as usual.
At some point the vacuum started, the server reached 50 of overall load 
and cpu's were 1% idle
I think any test can detect such a situation, regardles if load it's 
more I/O based or CPU based






--------------010001020600040606000907
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<blockquote
 cite="mid20060927162756.0a9df264.wmoran@collaborativefusion.com"
 type="cite">
  <blockquote type="cite">
    <pre wrap="">True,
but autovacuum could check load -before- and -during- it's execution and 
it could adjust himself automatically to perform more or less 
aggressively depending on the difference between those two values.
Maybe with a parameter like: maximum-autovacuum-load=0.2
that would mean: "never load the machine more than 20% for the autovacuum"
    </pre>
  </blockquote>
  <pre wrap=""><!---->
This is pretty non-trivial.  How do you define 20% load?  20% of the
CPU?  Does that mean that it's OK for autovac to use 3% cpu and 100% of
your IO?  Ok, so we need to calculate an average of IO and CPU -- which
disks?  If your WAL logs are on one disk, and you've used tablespaces
to spread the rest of your DB across different partitions, it can
be pretty difficult to determine which IO parameters you want to take
into consideration.

  </pre>
</blockquote>
As I said before, it could be done, the main requirement is to find a
way for pg to check for a value of the system load; of course it has to
be an average value between disk and cpu, of course the daemon would
have to collect sample of this values continuously, and of course
everything would be better if the server it's only running Postgres<br>
Still I think you are right, it wouldn't suit exactly every situations
but it could be an "emergency" feature:<br>
What happened to me was very clear: <br>
server running pg8 under heavy load, cpu's were 90% idle as usual.<br>
At some point the vacuum started, the server reached 50 of overall load
and cpu's were 1% idle<br>
I think any test can detect such a situation, regardles if load it's
more I/O based or CPU based<br>
<br>
<br>
<br>
<br>
<br>
</body>
</html>

--------------010001020600040606000907--