Re: [BUGS] Postgresql 'eats' all mi data partition

#1

On Thu, 25 Sep 2003, Javier Carlos wrote:

Quote:

================================================== ==========================
POSTGRESQL BUG REPORT TEMPLATE
================================================== ==========================

Your name : Javier Carlos Rivera
Your email address : fjcarlos ( at ) correo ( dot ) insp ( dot ) mx

System Configuration
----------------------
Architecture (example: Intel Pentium) : Intel Pentium 4

Operating System (example: Linux 2.0.26 ELF) : Debian GNU/Linux 3.0 2.4.21

RAM : 256 MB

PostgreSQL version (example: PostgreSQL-6.3.2) : PostgreSQL-7.3.4

Compiler used (example: gcc 2.7.2) : 2.95.4

Please enter a FULL description of your problem:
-------------------------------------------------
On Thursday Bruce Momjian was at Mexico; I saw him and asked about
this problem. He told me to write to this e-mail.

When I do a simple 'UPDATE' PostgreSQL 'eats' all my partition space
of my data directory. For example:

***** My data directory is in /var
***** BEFORE I do the UPDATEs I got this from df:
OPORTUNIDADES:~# df
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda2 2885812 14372 2724848 1% /
/dev/hda1 14421344 1195132 12493652 9% /var
/dev/hda3 7692908 888560 6413568 13% /usr
/dev/hda6 12491804 22704 11834536 1% /javier
/dev/hda7 1494204 23936 1394364 2% /home

***** Then I do the UPDATEs:
**** The updates are of this type :
UPDATE tbl_personas SET "PIDFOLIO"=-2 WHERE "PIDFOLIO" IS NULL;
UPDATE tbl_personas SET "P5_1"=-2 WHERE "P5_1" IS NULL;
UPDATE tbl_personas SET "P4PAQ"=-2 WHERE "P4PAQ" IS NULL;
UPDATE tbl_personas SET "P5_4"=-2 WHERE "P5_4" IS NULL;
UPDATE tbl_personas SET "P5_5"=-2 WHERE "P5_5" IS NULL;
UPDATE tbl_personas SET "P36_4"=-2 WHERE "P36_4" IS NULL;
..
UPDATE table_name SET column_name = -2 WHERE column_name IS NULL;
..

If you're not vacuuming, you're going to have a potentially large
number of dead rows. Does a vacuum between updates or a vacuum full at
the end bring the space usage down to something reasonable?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#2

On Thu, 25 Sep 2003, Javier Carlos wrote:

Quote:

Quoting Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com>:

On Thu, 25 Sep 2003, Javier Carlos wrote:

================================================== ==========================
POSTGRESQL BUG REPORT TEMPLATE

================================================== ==========================

Your name : Javier Carlos Rivera
Your email address : fjcarlos ( at ) correo ( dot ) insp ( dot ) mx

System Configuration
----------------------
Architecture (example: Intel Pentium) : Intel Pentium 4

Operating System (example: Linux 2.0.26 ELF) : Debian GNU/Linux
3.0
2.4.21

RAM : 256 MB

PostgreSQL version (example: PostgreSQL-6.3.2) : PostgreSQL-7.3.4

Compiler used (example: gcc 2.7.2) : 2.95.4

Please enter a FULL description of your problem:
-------------------------------------------------
On Thursday Bruce Momjian was at Mexico; I saw him and asked about
this problem. He told me to write to this e-mail.

When I do a simple 'UPDATE' PostgreSQL 'eats' all my partition space
of my data directory. For example:

***** My data directory is in /var
***** BEFORE I do the UPDATEs I got this from df:
OPORTUNIDADES:~# df
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda2 2885812 14372 2724848 1% /
/dev/hda1 14421344 1195132 12493652 9% /var
/dev/hda3 7692908 888560 6413568 13% /usr
/dev/hda6 12491804 22704 11834536 1% /javier
/dev/hda7 1494204 23936 1394364 2% /home

***** Then I do the UPDATEs:
**** The updates are of this type :
UPDATE tbl_personas SET "PIDFOLIO"=-2 WHERE "PIDFOLIO" IS NULL;
UPDATE tbl_personas SET "P5_1"=-2 WHERE "P5_1" IS NULL;
UPDATE tbl_personas SET "P4PAQ"=-2 WHERE "P4PAQ" IS NULL;
UPDATE tbl_personas SET "P5_4"=-2 WHERE "P5_4" IS NULL;
UPDATE tbl_personas SET "P5_5"=-2 WHERE "P5_5" IS NULL;
UPDATE tbl_personas SET "P36_4"=-2 WHERE "P36_4" IS NULL;
..
UPDATE table_name SET column_name = -2 WHERE column_name IS NULL;
..

If you're not vacuuming, you're going to have a potentially large
number of dead rows. Does a vacuum between updates or a vacuum full at
the end bring the space usage down to something reasonable?

I did a vacuumbdb after the updates, and the space usage didn't down to
something reasonable. For example, I had a 250MB database, then I did about
300 query updates, and mi partition growed up until fill all mi data partition
space of 15GB. After that I did an vacuumdb and only the space down 100MB.
After that I DROPPED the database, and the space down ALL the 15GB; It's very
weird, don't you think?

Did you use -f on the vacuumdb? If not, it did a normal vacuum (which
isn't likely to help) not a full vacuum.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#3

fjcarlos (AT) correo (DOT) insp.mx (Javier Carlos) writes:

Quote:

*** When I make a 'DROP DATABASE' to the database where that table belongs to,
mi /var partition returns to its original size (in this example to 9%).

VACUUM ANALYZE is your friend, here.

PostgreSQL uses MVCC, wherein each update results in the creation of a
new tuple which coexists with the old one in the database. If you
update a particular row in the database 10 times, then there will be
11 copies, 10 of them being obsolete.

If you VACUUM the table or the database, the rows that are no longer
accessible will be cleared out, thus diminishing the amount of space
in use.

Things are somewhat more complicated than that; VACUUM looks at the
data page-by-page, and won't _always_ reclaim all free space. The
more often you VACUUM, the more likely that such reclaimation will be
possible.

There is a program called pg_autovacuum that will vacuum automatically
when specific tables look like they need it.

ANALYZE does something different but related; it updates the
statistics used by the query optimizer. When you make major changes
to the table, it may be necessary to ANALYZE it in order for queries
to be efficient.
--
"cbbrowne","@","libertyrms.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#4

Quoting Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com>:

Quote:

On Thu, 25 Sep 2003, Javier Carlos wrote:

================================================== ==========================
POSTGRESQL BUG REPORT TEMPLATE

================================================== ==========================

Your name : Javier Carlos Rivera
Your email address : fjcarlos ( at ) correo ( dot ) insp ( dot ) mx

System Configuration
----------------------
Architecture (example: Intel Pentium) : Intel Pentium 4

Operating System (example: Linux 2.0.26 ELF) : Debian GNU/Linux
3.0
2.4.21

RAM : 256 MB

PostgreSQL version (example: PostgreSQL-6.3.2) : PostgreSQL-7.3.4

Compiler used (example: gcc 2.7.2) : 2.95.4

Please enter a FULL description of your problem:
-------------------------------------------------
On Thursday Bruce Momjian was at Mexico; I saw him and asked about
this problem. He told me to write to this e-mail.

When I do a simple 'UPDATE' PostgreSQL 'eats' all my partition space
of my data directory. For example:

***** My data directory is in /var
***** BEFORE I do the UPDATEs I got this from df:
OPORTUNIDADES:~# df
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda2 2885812 14372 2724848 1% /
/dev/hda1 14421344 1195132 12493652 9% /var
/dev/hda3 7692908 888560 6413568 13% /usr
/dev/hda6 12491804 22704 11834536 1% /javier
/dev/hda7 1494204 23936 1394364 2% /home

***** Then I do the UPDATEs:
**** The updates are of this type :
UPDATE tbl_personas SET "PIDFOLIO"=-2 WHERE "PIDFOLIO" IS NULL;
UPDATE tbl_personas SET "P5_1"=-2 WHERE "P5_1" IS NULL;
UPDATE tbl_personas SET "P4PAQ"=-2 WHERE "P4PAQ" IS NULL;
UPDATE tbl_personas SET "P5_4"=-2 WHERE "P5_4" IS NULL;
UPDATE tbl_personas SET "P5_5"=-2 WHERE "P5_5" IS NULL;
UPDATE tbl_personas SET "P36_4"=-2 WHERE "P36_4" IS NULL;
..
UPDATE table_name SET column_name = -2 WHERE column_name IS NULL;
..

If you're not vacuuming, you're going to have a potentially large
number of dead rows. Does a vacuum between updates or a vacuum full at
the end bring the space usage down to something reasonable?

I did a vacuumbdb after the updates, and the space usage didn't down to
something reasonable. For example, I had a 250MB database, then I did about
300 query updates, and mi partition growed up until fill all mi data partition
space of 15GB. After that I did an vacuumdb and only the space down 100MB.
After that I DROPPED the database, and the space down ALL the 15GB; It's very
weird, don't you think?

Cheers,

Javier

-------------------------------------------------
http://www.insp.mx

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#5

Quoting Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com>:

Quote:

On Thu, 25 Sep 2003, Javier Carlos wrote:

Quoting Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com>:

On Thu, 25 Sep 2003, Javier Carlos wrote:

================================================== ==========================
POSTGRESQL BUG REPORT TEMPLATE

================================================== ==========================

Your name : Javier Carlos Rivera
Your email address : fjcarlos ( at ) correo ( dot ) insp ( dot ) mx

System Configuration
----------------------
Architecture (example: Intel Pentium) : Intel Pentium 4

Operating System (example: Linux 2.0.26 ELF) : Debian GNU/Linux
3.0
2.4.21

RAM : 256 MB

PostgreSQL version (example: PostgreSQL-6.3.2) : PostgreSQL-7.3.4

Compiler used (example: gcc 2.7.2) : 2.95.4

Please enter a FULL description of your problem:
-------------------------------------------------
On Thursday Bruce Momjian was at Mexico; I saw him and asked about
this problem. He told me to write to this e-mail.

When I do a simple 'UPDATE' PostgreSQL 'eats' all my partition space
of my data directory. For example:

***** My data directory is in /var
***** BEFORE I do the UPDATEs I got this from df:
OPORTUNIDADES:~# df
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda2 2885812 14372 2724848 1% /
/dev/hda1 14421344 1195132 12493652 9% /var
/dev/hda3 7692908 888560 6413568 13% /usr
/dev/hda6 12491804 22704 11834536 1% /javier
/dev/hda7 1494204 23936 1394364 2% /home

***** Then I do the UPDATEs:
**** The updates are of this type :
UPDATE tbl_personas SET "PIDFOLIO"=-2 WHERE "PIDFOLIO" IS NULL;
UPDATE tbl_personas SET "P5_1"=-2 WHERE "P5_1" IS NULL;
UPDATE tbl_personas SET "P4PAQ"=-2 WHERE "P4PAQ" IS NULL;
UPDATE tbl_personas SET "P5_4"=-2 WHERE "P5_4" IS NULL;
UPDATE tbl_personas SET "P5_5"=-2 WHERE "P5_5" IS NULL;
UPDATE tbl_personas SET "P36_4"=-2 WHERE "P36_4" IS NULL;
..
UPDATE table_name SET column_name = -2 WHERE column_name IS NULL;
..

If you're not vacuuming, you're going to have a potentially large
number of dead rows. Does a vacuum between updates or a vacuum full at
the end bring the space usage down to something reasonable?

I did a vacuumbdb after the updates, and the space usage didn't down to
something reasonable. For example, I had a 250MB database, then I did
about
300 query updates, and mi partition growed up until fill all mi data
partition
space of 15GB. After that I did an vacuumdb and only the space down 100MB.
After that I DROPPED the database, and the space down ALL the 15GB; It's
very
weird, don't you think?

Did you use -f on the vacuumdb? If not, it did a normal vacuum (which
isn't likely to help) not a full vacuum.

Many Thanks!,

I used the -f option and it worked!!

I really appreciate your help.

Best regards,

Javier

-------------------------------------------------
http://www.insp.mx

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#6

Quote:

[sszabo (AT) megazone (DOT) bigpanda.com]

Did you use -f on the vacuumdb? If not, it did a normal vacuum (which
isn't likely to help) not a full vacuum.

There are scenarios where VACUUM FULL is not an option because
of its resource-hungriness and plain VACUUM just doesn't seem
to help.

We have a production database that happens to receive several
thousand row updates per minute. We VACUUM ANALYZE every four
hours with max_fsm_pages set to 2100000, and it's no use.

The only way to prevent the system from filling up the data
partition seems to be to regularly schedule downtime to dump
and restore the whole db (the dump is ~150 MiB gzipped).

--
Tomas Szepe <szepe (AT) pinerecords (DOT) com>

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#7

On Fri, 26 Sep 2003, Tomas Szepe wrote:

Quote:

[sszabo (AT) megazone (DOT) bigpanda.com]

Did you use -f on the vacuumdb? If not, it did a normal vacuum (which
isn't likely to help) not a full vacuum.

There are scenarios where VACUUM FULL is not an option because
of its resource-hungriness and plain VACUUM just doesn't seem
to help.

We have a production database that happens to receive several
thousand row updates per minute. We VACUUM ANALYZE every four
hours with max_fsm_pages set to 2100000, and it's no use.

Hmm, what does vacuum verbose say?

One other thing is to find where the space is going. Some of that might
be ending up in indexes which (unfortunately) on 7.3 and earlier aren't
going to get cleaned up by vacuum and will instead need a reindex.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#8

Tomas Szepe <szepe (AT) pinerecords (DOT) com> writes:

Quote:

We have a production database that happens to receive several
thousand row updates per minute. We VACUUM ANALYZE every four
hours with max_fsm_pages set to 2100000, and it's no use.

Have you spent any time determining exactly where the problem is?
I'm suspicious that it's an index-bloat issue.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#9

Quote:

[sszabo (AT) megazone (DOT) bigpanda.com]

On Fri, 26 Sep 2003, Tomas Szepe wrote:

[sszabo (AT) megazone (DOT) bigpanda.com]

Did you use -f on the vacuumdb? If not, it did a normal vacuum (which
isn't likely to help) not a full vacuum.

There are scenarios where VACUUM FULL is not an option because
of its resource-hungriness and plain VACUUM just doesn't seem
to help.

We have a production database that happens to receive several
thousand row updates per minute. We VACUUM ANALYZE every four
hours with max_fsm_pages set to 2100000, and it's no use.

Hmm, what does vacuum verbose say?

(postgres is 7.3.4 on x86 Linux)

INFO: --Relation pg_catalog.pg_description--
INFO: Pages 12: Changed 0, Empty 0; Tup 1390: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: --Relation pg_toast.pg_toast_16416--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Skipping "pg_group" --- only table or database owner can VACUUM it
INFO: --Relation pg_catalog.pg_proc--
INFO: Pages 58: Changed 0, Empty 0; Tup 1492: Vac 0, Keep 0, UnUsed 165.
Total CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: --Relation pg_toast.pg_toast_1255--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_rewrite--
INFO: Pages 4: Changed 0, Empty 0; Tup 27: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16410--
INFO: Pages 4: Changed 0, Empty 0; Tup 16: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_type--
INFO: Pages 4: Changed 0, Empty 0; Tup 178: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_attribute--
INFO: Pages 16: Changed 0, Empty 0; Tup 914: Vac 0, Keep 0, UnUsed 4.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_class--
INFO: Pages 4: Changed 0, Empty 0; Tup 138: Vac 0, Keep 0, UnUsed 44.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_inherits--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_index--
INFO: Pages 3: Changed 0, Empty 0; Tup 69: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_operator--
INFO: Pages 13: Changed 0, Empty 0; Tup 643: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_opclass--
INFO: Pages 1: Changed 0, Empty 0; Tup 51: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_am--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_amop--
INFO: Pages 1: Changed 0, Empty 0; Tup 180: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_amproc--
INFO: Pages 1: Changed 0, Empty 0; Tup 57: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_language--
INFO: Pages 1: Changed 0, Empty 0; Tup 3: Vac 0, Keep 0, UnUsed 3.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_largeobject--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_aggregate--
INFO: Pages 1: Changed 0, Empty 0; Tup 60: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_trigger--
INFO: Pages 1: Changed 0, Empty 0; Tup 2: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_listener--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_cast--
INFO: Pages 2: Changed 0, Empty 0; Tup 174: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_namespace--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Skipping "pg_shadow" --- only table or database owner can VACUUM it
INFO: --Relation pg_catalog.pg_conversion--
INFO: Pages 2: Changed 0, Empty 0; Tup 114: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_depend--
INFO: Pages 20: Changed 0, Empty 0; Tup 2834: Vac 0, Keep 0, UnUsed 66.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_attrdef--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16384--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_constraint--
INFO: Pages 1: Changed 0, Empty 0; Tup 5: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16386--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Skipping "pg_database" --- only table or database owner can VACUUM it
INFO: --Relation pg_catalog.pg_statistic--
INFO: Index pg_statistic_relid_att_index: Pages 4; Tuples 189: Deleted 187.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Removed 187 tuples in 15 pages.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Pages 18: Changed 9, Empty 0; Tup 189: Vac 187, Keep 0, UnUsed 259.
Total CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO: --Relation pg_toast.pg_toast_16408--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.contract_ips--
INFO: Index contract_ips_pkey: Pages 430; Tuples 743: Deleted 37893.
CPU 0.03s/0.13u sec elapsed 0.16 sec.
INFO: Removed 37893 tuples in 609 pages.
CPU 0.01s/0.07u sec elapsed 0.07 sec.
INFO: Pages 1113: Changed 24, Empty 0; Tup 743: Vac 37893, Keep 0, UnUsed 36763.
Total CPU 0.08s/0.20u sec elapsed 0.28 sec.
INFO: Truncated 1113 --> 110 pages.
CPU 0.05s/0.00u sec elapsed 0.41 sec.
INFO: --Relation pg_toast.pg_toast_50107070--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.authinfo--
INFO: Index authinfo_pkey: Pages 733; Tuples 930: Deleted 47430.
CPU 0.04s/0.18u sec elapsed 0.44 sec.
INFO: Removed 47430 tuples in 717 pages.
CPU 0.02s/0.07u sec elapsed 0.09 sec.
INFO: Pages 1380: Changed 29, Empty 0; Tup 930: Vac 47430, Keep 0, UnUsed 45290.
Total CPU 0.10s/0.27u sec elapsed 0.60 sec.
INFO: --Relation public.stats_min--
INFO: Index stats_min_start: Pages 34445; Tuples 1985464: Deleted 404651.
CPU 1.19s/2.41u sec elapsed 17.86 sec.
INFO: Index stats_min_pkey: Pages 76501; Tuples 1986938: Deleted 404651.
CPU 3.98s/5.47u sec elapsed 217.07 sec.
INFO: Removed 404651 tuples in 6118 pages.
CPU 0.83s/0.77u sec elapsed 13.52 sec.
INFO: Pages 25295: Changed 4615, Empty 0; Tup 1985464: Vac 404651, Keep 0, UnUsed 468220.
Total CPU 7.19s/8.78u sec elapsed 252.67 sec.
INFO: --Relation public.stats_hr--
INFO: Index stats_hr_start: Pages 57654; Tuples 10811294: Deleted 348991.
CPU 3.09s/5.27u sec elapsed 63.67 sec.
INFO: Index stats_hr_pkey: Pages 78301; Tuples 10814527: Deleted 348991.
CPU 5.11s/6.39u sec elapsed 152.78 sec.
INFO: Removed 348991 tuples in 8333 pages.
CPU 1.09s/0.92u sec elapsed 36.46 sec.
INFO: Pages 217213: Changed 1362, Empty 0; Tup 10810476: Vac 348991, Keep 0, UnUsed 352822.
Total CPU 17.09s/13.98u sec elapsed 284.52 sec.
INFO: --Relation public.stats_hr_old--
INFO: Pages 60984: Changed 0, Empty 0; Tup 3232113: Vac 0, Keep 0, UnUsed 0.
Total CPU 2.76s/0.45u sec elapsed 13.90 sec.
VACUUM

Quote:

One other thing is to find where the space is going. Some of that might
be ending up in indexes which (unfortunately) on 7.3 and earlier aren't
going to get cleaned up by vacuum and will instead need a reindex.

That's very likely happening in our case I'm afraid.

Hmm, you seem to suggest that we might expect a change in this regard
as 7.4 ships. Is that right?

Thanks for your interest in this problem,
--
Tomas Szepe <szepe (AT) pinerecords (DOT) com>

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#10

Quote:

[tgl (AT) sss (DOT) pgh.pa.us]

Tomas Szepe <szepe (AT) pinerecords (DOT) com> writes:
We have a production database that happens to receive several
thousand row updates per minute. We VACUUM ANALYZE every four
hours with max_fsm_pages set to 2100000, and it's no use.

Have you spent any time determining exactly where the problem is?
I'm suspicious that it's an index-bloat issue.

I'm afraid so. The weird thing is, we once tried running
VACUUM FULL followed by REINDEX and although the process
took ages to complete, it didn't seem to help either.

I'll post whatever debug data I'm asked for, just don't make
me run VACUUM FULL or REINDEX again please. (A full redump
is 10-20 times faster. :O)

Thanks,
--
Tomas Szepe <szepe (AT) pinerecords (DOT) com>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly