X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 42CAE53226 for ; Fri, 1 Apr 2005 20:40:39 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36740-02 for ; Fri, 1 Apr 2005 19:40:35 +0000 (GMT) Received: from sj-iport-4.cisco.com (sj-iport-4.cisco.com [171.68.10.86]) by svr1.postgresql.org (Postfix) with ESMTP id 7B94253AD0 for ; Fri, 1 Apr 2005 20:40:33 +0100 (BST) Received: from sj-core-4.cisco.com (171.68.223.138) by sj-iport-4.cisco.com with ESMTP; 01 Apr 2005 11:40:31 -0800 Received: from cjwhitew2k02 ([128.107.172.206]) by sj-core-4.cisco.com (8.12.10/8.12.6) with ESMTP id j31JeT3S006624 for ; Fri, 1 Apr 2005 11:40:29 -0800 (PST) Message-Id: <200504011940.j31JeT3S006624@sj-core-4.cisco.com> Reply-To: From: "Chris White (cjwhite)" To: Subject: Help understanding VACUUM info on 7.4.5 Date: Fri, 1 Apr 2005 11:40:29 -0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_005B_01C536AF.9B36BE40" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 thread-index: AcU28qjWitQPTyNeQwKdDrlXMK+bGA== X-PMX-Version: 4.7.0.111621 X-from-outside-Cisco: [128.107.172.206] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.307 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200504/19 X-Sequence-Number: 17032 This is a multi-part message in MIME format. ------=_NextPart_000_005B_01C536AF.9B36BE40 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_005B_01C536AF.9B36BE40 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hi,
 
I am = running=20 Postgres 7.4.5 and am storing binary objects in the largeobject table. = We want=20 to keep the size of the database and especially the large object table = to a=20 minimum, so we vacuum it (not full) on a regular basis. However, what we = have=20 seen is that even after deleting entries from the table and vacuuming = the free=20 cells don't seem to be being reused. So when we add a new entry to the = table=20 which is smaller in size to a previously deleted object the DB = grows.
 
Below = I have info=20 which shows a DB which has had all objects deleted and then vacuumed. As = can=20 been seen it say it has "9014 dead rows that can't been removed". What = does that=20 mean? If the table is empty why can't they be removed? Then when I add a = new=20 object of ~500k, the DB grows and when I vacuum again, there are still = 9104 dead=20 rows. When will these dead row become free and available for reuse, so = the DB=20 doesn't keep growing?
 
***** = empty=20 pg_largeobject, before vacuum******
bash-2.05b# du -k sql
4472    sql/base/1
4472    = sql/base/17141
9060    = sql/base/17142
18008  =20 sql/base
136    =20 sql/global
12      = sql/pg_clog
32812  =20 sql/pg_xlog
51000   sql
bash-2.05b# !p
psql -d aesop=20 -U xxxxx
Welcome to psql = 7.4.5,=20 the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution = terms
       \h for help with SQL=20 commands
       \? for help on internal = slash=20 commands
       \g or terminate with = semicolon=20 to execute query
       \q to = quit
 
aesop=3D# \lo_list
  Large=20 objects
 ID | Description
----+-------------
(0=20 rows)
 
aesop=3D# vacuum verbose=20 pg_largeobject;
INFO:  vacuuming=20 "pg_catalog.pg_largeobject"
INFO:  index = "pg_largeobject_loid_pn_index"=20 now contains 9014 row versions in 45 pages
DETAIL:  0 index = pages have=20 been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed = 0.01=20 sec.
INFO:  "pg_largeobject": found 0 removable, 9014 = nonremovable row=20 versions in 381 pages
DETAIL:  9014 dead row versions cannot be = removed=20 yet.
There were 6230 unused item pointers.
0 pages are entirely=20 empty.
CPU 0.06s/0.04u sec elapsed 0.10 sec.
VACUUM
aesop=3D#=20 \q
 
*****store new object in pg_largeobject, before=20 vacuum*****

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

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

------=_NextPart_000_005B_01C536AF.9B36BE40--