Package: postgresql-common
Version: 28
Severity: normal
File: /usr/share/postgresql-common/PgCommon.pm
I have a database which contains tables consisting of millions of rows
which are inserted, then updated once, then left unmodified forever.
The default avac_vac_scale of 2 results in huge amounts of wasted space:
the table has to more than triple in size (live + dead tuple count)
within a single, uninterrupted autovacuum daemon process lifetime,
to meet the default VacuumThreshold. In some tables the rows are on
average 1000 bytes long when inserted and 20 bytes long after update.
The size of the database on disk doubles if only 1% of it is unvacuumed,
so a reasonable value for avac_vac_scale is somewhere around 0.01.
I tried to put the following into /etc/.../autovacuum.conf:
avac_vac_scale=0.01
Unfortunately, this doesn't work. There is a problem with pg_ctlcluster's
get_conf_value routine:
sub get_conf_value {
return 0 unless $_[0] && $_[1];
my $fname = "$confroot/$_[0]/$_[1]/$_[2]";
-e $fname or $fname = "$common_confdir/$_[2]";
if (open F, $fname) {
while (<F>) {
return $1 if /^\s*$_[3]\s*=\s*(\w+)\b/; # simple value
return $1 if /^\s*$_[3]\s*=\s*'([^']*)'/; # string value
}
close F;
}
return undef;
}
The problem is that floating-point numbers don't match "\w+", so they are
silently ignored and the defaults used instead.
Please do one of the following:
1. Use "[\w.]+" instead of "\w+" (or maybe "-?[\w.]+" for negative cases), or
2. Put a comment in autovacuum.conf that says floating-point numbers
(or anything not matching \w+) must be surrounded by ticks (as in
"avac_vac_scale='0.01'").
Thanks in advance.
-- System Information:
Debian Release: 3.1
APT prefers testing
APT policy: (102, 'testing'), (101, 'unstable')
Architecture: i386 (i686)
Kernel: Linux 2.6.12.6-zb5s-s2-nf
Locale: LANG=C, LC_CTYPE=C (charmap=ANSI_X3.4-1968)
Versions of packages postgresql-common depends on:
ii adduser 3.63 Add and remove users and groups
-- no debconf information
--
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
