Re: [HACKERS] Per table autovacuum vacuum cost limit behaviour strange

[Mark Kirkwood]

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.



$ 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



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




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





Cheers

Mark

Minimally tweaking autovacuum (naptime of 5s) with a single table 'cache0' created with a cost limit setting of 10000, running: So far so good. Adding another table 'cache1' similar to the previous but lacking any per table autovacuum settings, and running 2 pgbench sessions: (volatile1.sql just uses table 'cache1' instead of 'cache0') shows after a few minutes: 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.

schema.sql
Description: application/sql

volatile0.sql
Description: application/sql

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