Message-ID: <53FD6BFE.40601@catalyst.net.nz>
Date: Wed, 27 Aug 2014 17:26:22 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.0
MIME-Version: 1.0
To: Alvaro Herrera <alvherre@2ndquadrant.com>
CC: Amit Kapila <amit.kapila16@gmail.com>,
 Haribabu Kommi <kommi.haribabu@gmail.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: Per table autovacuum vacuum cost limit behaviour strange
References: <20140214213227.GF6342@eldon.alvh.no-ip.org>
 <CAJrrPGcpZ5g5snz0PO4EEKFkTAD=uJi0VF6ruW25Mh0F34rPDw@mail.gmail.com>
 <CAJrrPGdK=nhTHUCsURgXQyG6RH=rJ8b4CdYhzGhwH=UuzaO9Ng@mail.gmail.com>
 <CAA4eK1JALkZOLnGdpqQgKbb+vq0eri1b7ytDfZLA8E9ZM35NHw@mail.gmail.com>
 <CAJrrPGeEwsezwA6+DpA2Y7MBJmEnNWwD5Gi4Ve_EqCxHoyHj3Q@mail.gmail.com>
 <CAA4eK1LwFs9iM-rUdfOrZE5MvjSxMM9ZbPbzv1SeWgGYu-O=VQ@mail.gmail.com>
 <53672F63.3080400@catalyst.net.nz>
 <CAA4eK1KDAUP=g59ku_0ekDoRjaGfEamyadQn5rJVt3j5oErLXg@mail.gmail.com>
 <53686F22.8030300@catalyst.net.nz>
 <20140826161911.GE6343@eldon.alvh.no-ip.org>
 <20140826222700.GA23139@eldon.alvh.no-ip.org>
In-Reply-To: <20140826222700.GA23139@eldon.alvh.no-ip.org>
Content-Type: multipart/mixed; boundary="------------010204090100050307090808"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------010204090100050307090808
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

On 27/08/14 10:27, Alvaro Herrera wrote:
> Alvaro Herrera wrote:
>
>> So my proposal is a bit more complicated.  First we introduce the notion
>> of a single number, to enable sorting and computations: the "delay
>> equivalent", which is the cost_limit divided by cost_delay.
>
> Here's a patch that implements this idea.  As you see this is quite a
> bit more complicated that Haribabu's proposal.
>
> There are two holes in this:
>
> 1. if you ALTER DATABASE to change vacuum delay for a database, those
> values are not considered in the global equiv delay.  I don't think this
> is very important and anyway we haven't considered this very much, so
> it's okay if we don't handle it.
>
> 2. If you have a "fast worker" that's only slightly faster than regular
> workers, it will become slower in some cases.  This is explained in a
> FIXME comment in the patch.
>
> I don't really have any more time to invest in this, but I would like to
> see it in 9.4.  Mark, would you test this?  Haribabu, how open are you
> to fixing point (2) above?
>


I did some testing with this patch applied.

Minimally tweaking autovacuum (naptime of 5s) with a single table 
'cache0' created with a cost limit setting of 10000, running:

$ pgbench -n -c8 -T300 -f volatile0.sql cache

and monitoring the size of 'cache0' table showed a steady state of:


cache=# SELECT pg_relation_size('cache0')/(1024*1024) AS mb;
    mb
------
    85

So far so good. Adding another table 'cache1' similar to the previous 
but lacking any per table autovacuum settings, and running 2 pgbench 
sessions:

$ pgbench -n -c8 -T300 -f volatile0.sql cache
$ pgbench -n -c8 -T300 -f volatile1.sql cache


(volatile1.sql just uses table 'cache1' instead of 'cache0') shows after 
a few minutes:

cache=# SELECT relname,pg_relation_size(oid)/(1024*1024) AS mb
FROM pg_class WHERE relname like 'cache_';
  relname |   mb
---------+--------
  cache0  |    664
  cache1  |   1900

So we are definitely seeing the 'fast' worker being slowed down. Also, 
the growth of 'cache1' was only a bit faster than 'cache0' - so the 
'slow' worker was getting a speed boost was well.

So looks like good progress, but yeah - point (2) is obviously rearing 
its head in this test.

Cheers

Mark


--------------010204090100050307090808
Content-Type: application/sql;
 name="schema.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="schema.sql"

Q1JFQVRFIFRBQkxFIGNhY2hlMChpZCBTRVJJQUwgUFJJTUFSWSBLRVksIHZh
bCBURVhUKSAKV0lUSCAoYXV0b3ZhY3V1bV92YWN1dW1fY29zdF9saW1pdCA9
IDEwMDAwKQo7CgpDUkVBVEUgVEFCTEUgY2FjaGUxKGlkIFNFUklBTCBQUklN
QVJZIEtFWSwgdmFsIFRFWFQpIAo7Cg==

--------------010204090100050307090808
Content-Type: application/sql;
 name="volatile0.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="volatile0.sql"

QkVHSU47ClxzZXQgbnJvd3MgMTAwCklOU0VSVCBJTlRPIGNhY2hlMCAodmFs
KSBTRUxFQ1Qgcy52YWwgRlJPTSAoIFNFTEVDVCBnZW5lcmF0ZV9zZXJpZXMo
MSwgOm5yb3dzKSwgJ3h4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4JyBBUyB2YWwpIEFTIHM7CkRFTEVURSBGUk9N
IGNhY2hlMCBXSEVSRSBpZCBJTiAoU0VMRUNUIGlkIEZST00gY2FjaGUwIE9S
REVSIEJZIGlkIERFU0MgTElNSVQgOm5yb3dzKTsKQ09NTUlUOwo=

--------------010204090100050307090808
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--------------010204090100050307090808--