Skip site navigation (1) Skip section navigation (2)

pg_autovacuum: short, wide tables

From: mark reid <mail(at)markreid(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_autovacuum: short, wide tables
Date: 2005-07-07 17:33:11
Message-ID: 42CD6757.6050004@markreid.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi,

I've been using pg_autovacuum for a while, and for the most part it's 
been great.  There's one case in my system where it won't run on a 
particular type of table, even though the table apparently needs it.

I have a table called "properties" that has key->value pairs.  Usually 
there are only a handful of rows, some of which are updated relatively 
frequently compared to the number of rows (hundreds or thousands of 
times per day).  The problem is that some of the rows have long strings 
for their value (on the order of a few hundred kilobytes), so if I 
understand correctly, the bulk of the data gets offloaded to a toast 
table. 

What I believe is happening is that the main table doesn't meet the 
minimum activity level for pg_autovacuum based on size / update 
frequency, but the toast table would, though it isn't specifically 
checked by pg_autovacuum.  The result is that the toast table grows 
really big before triggering autovacuum (or until I manually vacuum the 
"properties" table).  Not the end of the world, obviously, but might be 
a "gotcha" for some people with similar situations.

Below is a snippet of output from a run of vacuumdb --full --analyze 
--verbose that should illustrate the problem.

-Mark.

Table Def:
        Table "schema_name.properties"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 name   | character varying |
 value  | character varying |
Indexes:
    "properties_name_key" unique, btree (name)


Vacuum verbose output:

INFO:  vacuuming "schema_name.properties"
INFO:  "properties": found 1361 removable, 8 nonremovable row versions 
in 172 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 63 to 1705 bytes long.
There were 4827 unused item pointers.
Total free space (including removable row versions) is 1376288 bytes.
164 pages are or will become empty, including 0 at the end of the table.
172 pages containing 1376288 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO:  index "properties_name_key" now contains 8 row versions in 15 pages
DETAIL:  1361 index row versions were removed.
8 index pages have been deleted, 8 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "properties": moved 8 row versions, truncated 172 to 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index "properties_name_key" now contains 8 row versions in 15 pages
DETAIL:  8 index row versions were removed.
8 index pages have been deleted, 8 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_10043014"
INFO:  "pg_toast_10043014": found 21052 removable, 24 nonremovable row 
versions in 21100 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 1117 to 2030 bytes long.
There were 63278 unused item pointers.
Total free space (including removable row versions) is 172044376 bytes.
21093 pages are or will become empty, including 0 at the end of the table.
21096 pages containing 172044264 free bytes are potential move destinations.
CPU 0.41s/0.06u sec elapsed 3.63 sec.
INFO:  index "pg_toast_10043014_index" now contains 24 row versions in 
321 pages
DETAIL:  21052 index row versions were removed.
317 index pages have been deleted, 317 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.40 sec.
INFO:  "pg_toast_10043014": moved 24 row versions, truncated 21100 to 6 
pages
DETAIL:  CPU 0.32s/1.04u sec elapsed 5.27 sec.
INFO:  index "pg_toast_10043014_index" now contains 24 row versions in 
321 pages
DETAIL:  24 index row versions were removed.
317 index pages have been deleted, 317 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "schema_name.properties"
INFO:  "properties": 1 pages, 8 rows sampled, 8 estimated total rows

Responses

pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2005-07-07 17:43:57
Subject: Re: BUG #1756: PQexec eats huge amounts of memory
Previous:From: John R PierceDate: 2005-07-07 17:13:58
Subject: Re: Sun inline assembler ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group