Обсуждение: Help understanding VACUUM info on 7.4.5
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.
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
\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)
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
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
\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)
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
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
"Chris White (cjwhite)" <cjwhite@cisco.com> writes:
> 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?
That means there's an open transaction that is old enough to be able to
see those rows under MVCC rules, so VACUUM dare not remove them.
Check for clients that are issuing a BEGIN and then sitting with the
transaction open. Older versions of JDBC did that behind your back,
IIRC.
regards, tom lane
Does this mean it could be any transaction, even one that has not done
anything with large objects, but one that started prior to the large objects
being deleted?
All access to the DB is done via JDBC, so has this JDBC issue been fixed in
7.4.5?
Chris
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Friday, April 01, 2005 1:07 PM
To: cjwhite@cisco.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Help understanding VACUUM info on 7.4.5
"Chris White (cjwhite)" <cjwhite@cisco.com> writes:
> 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?
That means there's an open transaction that is old enough to be able to see
those rows under MVCC rules, so VACUUM dare not remove them.
Check for clients that are issuing a BEGIN and then sitting with the
transaction open. Older versions of JDBC did that behind your back, IIRC.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
"Chris White \(cjwhite\)" <cjwhite@cisco.com> writes:
> Does this mean it could be any transaction, even one that has not done
> anything with large objects, but one that started prior to the large objects
> being deleted?
Exactly.
> All access to the DB is done via JDBC, so has this JDBC issue been fixed in
> 7.4.5?
You'd have to ask the JDBC guys ...
regards, tom lane
Thanks, for the input.
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Friday, April 01, 2005 1:49 PM
To: cjwhite@cisco.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Help understanding VACUUM info on 7.4.5
"Chris White \(cjwhite\)" <cjwhite@cisco.com> writes:
> Does this mean it could be any transaction, even one that has not done
> anything with large objects, but one that started prior to the large
> objects being deleted?
Exactly.
> All access to the DB is done via JDBC, so has this JDBC issue been
> fixed in 7.4.5?
You'd have to ask the JDBC guys ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster