Your message dated Thu, 22 Sep 2005 08:54:55 +0200
with message-id <[EMAIL PROTECTED]>
and subject line Fwd: Re: [BUGS] autovacuum doesn't seem to vacuum pg_attribute
has caused the attached Bug report to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(NB: If you are a system administrator and have no idea what I am
talking about this indicates a serious mail system misconfiguration
somewhere. Please contact me immediately.)
Debian bug tracking system administrator
(administrator, Debian Bugs database)
--------------------------------------
Received: (at submit) by bugs.debian.org; 6 Oct 2004 16:47:56 +0000
>From [EMAIL PROTECTED] Wed Oct 06 09:47:56 2004
Return-path: <[EMAIL PROTECTED]>
Received: from 100-12.dsl.otton1.isp.cyberus.ca (mokona.furryterror.org)
[209.195.100.12] (meow)
by spohr.debian.org with esmtp (Exim 3.35 1 (Debian))
id 1CFExI-0005QQ-00; Wed, 06 Oct 2004 09:47:56 -0700
Received: from zblaxell by mokona.furryterror.org with local (Exim 3.36 #1
(Debian))
id 1CFEx3-0004Jd-00; Wed, 06 Oct 2004 12:47:41 -0400
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
From: Zygo Blaxell <[EMAIL PROTECTED]>
To: Debian Bug Tracking System <[EMAIL PROTECTED]>
Subject: postgresql-contrib: autovacuum doesn't seem to vacuum pg_attribute
X-Mailer: reportbug 2.63
Date: Wed, 06 Oct 2004 12:47:41 -0400
Message-Id: <[EMAIL PROTECTED]>
Delivered-To: [EMAIL PROTECTED]
X-Spam-Checker-Version: SpamAssassin 2.60-bugs.debian.org_2004_03_25
(1.212-2003-09-23-exp) on spohr.debian.org
X-Spam-Status: No, hits=-8.0 required=4.0 tests=BAYES_00,HAS_PACKAGE
autolearn=no version=2.60-bugs.debian.org_2004_03_25
X-Spam-Level:
Package: postgresql-contrib
Version: 7.4.5-3
Severity: normal
I have a postgresql server which runs a query that generates a temporary
table once per minute. This creation and deletion of a table seems to
cause the pg_attribute table to fill up with deleted entries, but the
n_tup_del and n_tup_upd values in pg_stat_all_tables do not seem to
reflect this:
emokona=# select * from pg_stat_all_tables where relname = 'pg_attribute';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del
-------+------------+--------------+----------+--------------+----------+---------------+-----------+-----------+-----------
1249 | pg_catalog | pg_attribute | 12208 | 619174 | 1928209 |
4182563 | 755387 | 85 | 220
(1 row)
Time: 46.274 ms
mokona=# select count(*) from pg_attribute;
count
-------
1554
(1 row)
Time: 54.020 ms
Because n_tup_upd and n_tup_del are always small, pg_autovacuum never
vacuums this table--pg_autovacuum by design only operates on tables with
high numbers in these fields. The index files grow to many megabytes
until I eventually run 'vacuum pg_attribute' manually:
mokona=# vacuum verbose pg_attribute;
INFO: vacuuming "pg_catalog.pg_attribute"
INFO: index "pg_attribute_relid_attnam_index" now contains 1554 row versions
in 8980 pages
DETAIL: 13363 index row versions were removed.
8914 index pages have been deleted, 8712 are currently reusable.
CPU 2.09s/0.28u sec elapsed 48.56 sec.
INFO: index "pg_attribute_relid_attnum_index" now contains 1577 row versions
in 2074 pages
DETAIL: 13363 index row versions were removed.
2035 index pages have been deleted, 1985 are currently reusable.
CPU 0.54s/0.19u sec elapsed 11.47 sec.
INFO: "pg_attribute": removed 13363 row versions in 220 pages
DETAIL: CPU 0.14s/0.08u sec elapsed 1.18 sec.
INFO: "pg_attribute": found 13363 removable, 1554 nonremovable row versions in
245 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 2.78s/0.56u sec elapsed 61.23 sec.
VACUUM
Time: 61915.693 ms
As you can see, this takes a long time, and frees a lot of space, so it
is quite useful to vacuum this table...but pg_autovacuum won't, because it
never sees the need.
I'm not sure if this is a pg_autovacuum problem (pg_autovacuum might need
to consider counting index row versions as well as table row versions?) or
a postgresql statistics collection problem (is pg_attribute an exceptional
case for n_tup_* statistics collection?), so I'll just submit a bug report
for now and let the turf wars begin. ;-)
-- System Information:
Debian Release: 3.0
APT prefers testing
APT policy: (102, 'testing'), (101, 'unstable')
Architecture: i386 (i686)
Kernel: Linux 2.4.26-zb-586-smp-nf1.2.9
Locale: LANG=C, LC_CTYPE=C
Versions of packages postgresql-contrib depends on:
ii libc6 2.3.2.ds1-16 GNU C Library: Shared libraries an
ii libdbd-pg-perl 1.32-2 a PostgreSQL interface for Perl 5
ii libkrb53 1.3.4-4 MIT Kerberos runtime libraries
ii libpam0g 0.76-22 Pluggable Authentication Modules l
ii libpg-perl 1:2.0.2-3 Perl modules for PostgreSQL
ii libpq3 7.4.5-3 Shared library libpq.so.3 for Post
ii libreadline4 4.3-11 GNU readline and history libraries
ii libssl0.9.7 0.9.7d-5 SSL shared libraries
ii libxml2 2.6.11-3 GNOME XML library
ii postgresql 7.4.5-3 Object-relational SQL database, de
ii zlib1g 1:1.2.1.1-7 compression library - runtime
-- no debconf information
---------------------------------------
Received: (at 275207-done) by bugs.debian.org; 22 Sep 2005 06:55:27 +0000
>From [EMAIL PROTECTED] Wed Sep 21 23:55:27 2005
Return-path: <[EMAIL PROTECTED]>
Received: from box79162.elkhouse.de [213.9.79.162]
by spohr.debian.org with esmtp (Exim 3.36 1 (Debian))
id 1EIKzP-00028g-00; Wed, 21 Sep 2005 23:55:27 -0700
Received: by box79162.elkhouse.de (Postfix, from userid 1000)
id 5A418196604; Thu, 22 Sep 2005 08:54:55 +0200 (CEST)
Date: Thu, 22 Sep 2005 08:54:55 +0200
From: Martin Pitt <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: Fwd: Re: [BUGS] autovacuum doesn't seem to vacuum pg_attribute
Message-ID: <[EMAIL PROTECTED]>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
protocol="application/pgp-signature"; boundary="Q68bSM7Ycu6FN28Q"
Content-Disposition: inline
User-Agent: Mutt/1.5.9i
Delivered-To: [EMAIL PROTECTED]
X-Spam-Checker-Version: SpamAssassin 2.60-bugs.debian.org_2005_01_02
(1.212-2003-09-23-exp) on spohr.debian.org
X-Spam-Level:
X-Spam-Status: No, hits=-3.0 required=4.0 tests=BAYES_00 autolearn=no
version=2.60-bugs.debian.org_2005_01_02
--Q68bSM7Ycu6FN28Q
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
Hi!
This seems to have been fixed long ago, in both 7.4 and 8.0.
Thanks for your report,
Martin
----- Forwarded message from Tom Lane <[EMAIL PROTECTED]> -----
To: Martin Pitt <[EMAIL PROTECTED]>
Cc: PostgreSQL Bugs <[email protected]>
Subject: Re: [BUGS] autovacuum doesn't seem to vacuum pg_attribute=20
Date: Wed, 21 Sep 2005 22:59:37 -0400
=46rom: Tom Lane <[EMAIL PROTECTED]>
X-Spam-Status: No, score=3D0.4 required=3D4.0 tests=3DAWL,BAYES_50 autolear=
n=3Dno=20
version=3D3.0.3
Martin Pitt <[EMAIL PROTECTED]> writes:
> While cleaning up the postgresql bug reports in Debian, I stumbled
> over the following gem. Do you know about this problem, and is it
> still an issue for 8.0 and 8.1?
Probably fixed here:
2005-03-31 18:20 tgl
* src/backend/postmaster/: pgstat.c (REL7_4_STABLE), pgstat.c
(REL8_0_STABLE), pgstat.c: Flush any remaining statistics counts
out to the collector at process exit. Without this, operations
triggered during backend exit (such as temp table deletions) won't
be counted ... which given heavy usage of temp tables can lead to
pg_autovacuum falling way behind on the need to vacuum pg_class and
pg_attribute. Per reports from Steve Crawford and others.
regards, tom lane
----- End forwarded message -----
--=20
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntulinux.org
Debian Developer http://www.debian.org
--Q68bSM7Ycu6FN28Q
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
iD8DBQFDMlU/DecnbV4Fd/IRAlbtAKCldr5z7nT6iTRZD+8WJPY9EZKm3gCcD7cR
V5DowRWv26zjUmWK8cYjzmw=
=jKCH
-----END PGP SIGNATURE-----
--Q68bSM7Ycu6FN28Q--
--
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
