pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris White (cjwhite) <cjwhite@cisco.com>
To: pgsql-admin@postgresql.org
Subject: Help understanding VACUUM info on 7.4.5
Date: Fri, 1 Apr 2005 11:40:29 -0800
Message-ID: <200504011940.j31JeT3S006624@sj-core-4.cisco.com> (raw)

Hi,
 
I am running Postgres 7.4.5 and am storing binary objects in the largeobject
table. We want to keep the size of the database and especially the large
object table to a minimum, so we vacuum it (not full) on a regular basis.
However, what we have seen is that even after deleting entries from the
table and vacuuming the free cells don't seem to be being reused. So when we
add a new entry to the table which is smaller in size to a previously
deleted object the DB grows.
 
Below I have info which shows a DB which has had all objects deleted and
then vacuumed. As can been seen it say it has "9014 dead rows that can't
been removed". What does that mean? If the table is empty why can't they be
removed? Then when I add a new object of ~500k, the DB grows and when I
vacuum again, there are still 9104 dead rows. When will these dead row
become free and available for reuse, so the DB doesn't keep growing?
 
***** empty pg_largeobject, before vacuum******
bash-2.05b# du -k sql
4472    sql/base/1
4472    sql/base/17141
9060    sql/base/17142
18008   sql/base
136     sql/global
12      sql/pg_clog
32812   sql/pg_xlog
51000   sql
bash-2.05b# !p
psql -d aesop -U xxxxx
Welcome to psql 7.4.5, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
 
aesop=# \lo_list
  Large objects
 ID | Description 
----+-------------
(0 rows)
 
aesop=# vacuum verbose pg_largeobject;
INFO:  vacuuming "pg_catalog.pg_largeobject"
INFO:  index "pg_largeobject_loid_pn_index" now contains 9014 row versions
in 45 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO:  "pg_largeobject": found 0 removable, 9014 nonremovable row versions
in 381 pages
DETAIL:  9014 dead row versions cannot be removed yet.
There were 6230 unused item pointers.
0 pages are entirely empty.
CPU 0.06s/0.04u sec elapsed 0.10 sec.
VACUUM
aesop=# \q
 
*****store new object in pg_largeobject, before vacuum*****

bash-2.05b# du -k sql                 
4472    sql/base/1
4472    sql/base/17141
9108    sql/base/17142
18056   sql/base
136     sql/global
12      sql/pg_clog
32812   sql/pg_xlog
51048   sql
bash-2.05b# !p
psql -d aesop -U xxxxx
Welcome to psql 7.4.5, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
 
aesop=# \lo_list
    Large objects
  ID   | Description 
-------+-------------
 35509 | 
(1 row)
 
aesop=# vacuum verbose pg_largeobject;
INFO:  vacuuming "pg_catalog.pg_largeobject"
INFO:  index "pg_largeobject_loid_pn_index" now contains 9148 row versions
in 45 pages
DETAIL:  38 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.03u sec elapsed 0.06 sec.
INFO:  "pg_largeobject": removed 38 row versions in 36 pages
DETAIL:  CPU 0.01s/0.09u sec elapsed 0.10 sec.
INFO:  "pg_largeobject": found 38 removable, 9148 nonremovable row versions
in 387 pages
DETAIL:  9014 dead row versions cannot be removed yet.
There were 6079 unused item pointers.
0 pages are entirely empty.
CPU 0.08s/0.16u sec elapsed 0.23 sec.
VACUUM
aesop=# \q
 
***** after vacuum*****

bash-2.05b# du -k sql
4472    sql/base/1
4472    sql/base/17141
9108    sql/base/17142
18056   sql/base
136     sql/global
12      sql/pg_clog
32812   sql/pg_xlog
51048   sql

view thread (5+ messages)  latest in thread

Message-ID: <200504011940.j31JeT3S006624@sj-core-4.cisco.com>
Permalink:  ../200504011940.j31JeT3S006624@sj-core-4.cisco.com/
Also on:    postgresql.org/message-id/200504011940.j31JeT3S006624@sj-core-4.cisco.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: cjwhite@cisco.com
  Subject: Re: Help understanding VACUUM info on 7.4.5
  In-Reply-To: <200504011940.j31JeT3S006624@sj-core-4.cisco.com>
  References: <200504011940.j31JeT3S006624@sj-core-4.cisco.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox