Re: Question about DB VACUUM - Mailing list pgsql-admin

Tom,

I found my source of the not removing all objects. Now however, when I
rerun my tests I am still seeing the pg_largeobject table grow even
though there are no entries in the table.

I started with any empty pg_largeobject table and added and then deleted
6 large objects of 80K.

Database info after first set of adds and deletes:
aesop=# \lo_list
  Large objects
 ID | Description
----+-------------
(0 rows)

aesop=# vacuum verbose pg_largeobject;
NOTICE:  --Relation pg_largeobject--
NOTICE:  Index pg_largeobject_loid_pn_index: Pages 2818; Tuples 227:
Deleted 84.

        CPU 0.22s/0.03u sec elapsed 0.24 sec.
NOTICE:  Removed 84 tuples in 14 pages.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Pages 15: Changed 0, Empty 0; Tup 227: Vac 84, Keep 227, UnUsed
0.
        Total CPU 0.22s/0.03u sec elapsed 0.24 sec.
VACUUM

Why aren't there any unused tuples? Why is the
pg_largeobject_loid_pn_index table so big (2818 pages)?

Database info after second set of adds and deletes:
aesop=# \lo_list
  Large objects
 ID | Description
----+-------------
(0 rows)

aesop=# vacuum verbose pg_largeobject;
NOTICE:  --Relation pg_largeobject--
NOTICE:  Index pg_largeobject_loid_pn_index: Pages 2819; Tuples 460:
Deleted 84.

        CPU 0.21s/0.02u sec elapsed 0.23 sec.
NOTICE:  Removed 84 tuples in 19 pages.
        CPU 0.01s/0.01u sec elapsed 0.01 sec.
NOTICE:  Pages 19: Changed 0, Empty 0; Tup 460: Vac 84, Keep 460, UnUsed
2.
        Total CPU 0.22s/0.03u sec elapsed 0.25 sec.
VACUUM

Why has table grown by 4 pages.

Database info after third set of adds and deletes:
aesop=# \lo_list
  Large objects
 ID | Description
----+-------------
(0 rows)

aesop=# vacuum verbose pg_largeobject;
NOTICE:  --Relation pg_largeobject--
NOTICE:  Index pg_largeobject_loid_pn_index: Pages 2820; Tuples 690:
Deleted 84.

        CPU 0.18s/0.04u sec elapsed 0.24 sec.
NOTICE:  Removed 84 tuples in 22 pages.
        CPU 0.00s/0.01u sec elapsed 0.00 sec.
NOTICE:  Pages 22: Changed 0, Empty 0; Tup 690: Vac 84, Keep 690, UnUsed
8.
        Total CPU 0.18s/0.05u sec elapsed 0.24 sec.
VACUUM

Again table has grown by 3 pages.

Chris

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Chris White
(cjwhite)
Sent: Thursday, October 02, 2003 4:40 PM
To: 'Tom Lane'
Cc: 'Robert Treat'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question about DB VACUUM


Tom,

You were right, 1 or 2 of the large objects weren't actually getting
removed from the table, each cycle. I wasn't running on a completely
empty database when I first started, so I didn't notice the undeleted
objects.

The program which interfaces to the database uses the JDBC method
LargeObject.delete() to delete the large object associated to the entry
in the users data table. Plus every night I have a program which goes
through the database and checks for and removes large objects which are
no longer being referenced by any table.

I am now trying to find out why the occasion entry doesn't get deleted.

Chris

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, October 02, 2003 3:46 PM
To: cjwhite@cisco.com
Cc: 'Robert Treat'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question about DB VACUUM


"Chris White (cjwhite)" <cjwhite@cisco.com> writes:
> The index has grown by 4 pages and the table has grown by 10 pages.
> BTW, what is a page size? Why is this happening as this is the table
> that I am theoretically keeping the same size by adding/deleting the
> same objects from.

Kinda looks like you aren't doing anything to remove
no-longer-referenced large objects.  An LO is not a BLOB; it has an
independent existence.  You may care to look into contrib/lo and/or
contrib/vacuumlo.  Or possibly you should be using wide text or bytea
fields, not large objects at all...

            regards, tom lane


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