Thread: Question about DB VACUUM
Hi, I am using a Postgres 7.2.1 db to store binary data as large objects which users can add or delete as they need. I have found that without frequent vacuums of the database the disk space used by the database grows very quickly, as users on average add and delete binary objects of about 160K at a time. So I was trying to determine how often I should do a vacuum in order to keep the database from growing too quickly. So I ran a test where I did a full vacuum of the database to compact the tables, then I added and deleted 12 large objects of 80K each and then did a vacuum, not a full vacuum. I did this add/delete/vacuum process 4 times in a row. I thought that by adding and deleting the same objects and then vacuuming, the database shouldn't grow, as the new inserts would reuse the space taken up by the deleted objects after the vacuum. However, I was seeing the database grow each time. Here are the disk usage values after each step: After initial vacuum full: bash-2.05b# du -b -s /data/sql 56664064 /data/sql After first add/delete/vacuum: bash-2.05b# du -b -s /data/sql 56987648 /data/sql 323584 byte increase After second add/delete/vacuum: bash-2.05b# du -b -s /data/sql 57012224 /data/sql 24576 byte increase After third add/delete/vacuum: bash-2.05b# du -b -s /data/sql 57061376 /data/sql 49152 byte increase After fourth add/delete/vacuum: bash-2.05b# du -b -s /data/sql 57085952 /data/sql 24576 byte increase Is this expected behavior? As at some point in time, if I carry on repeating this test, I would have to do a vacuum full to retrieve disk space, even though the actual contents of the database has not increased from the initial starting point. Chris White
As a starting point, check your free space map settings in the
postgresql.conf. They are low by default in 7.2.x.
free_space_relations* can safely be bumped to 1000. free_space_pages*
should probably be bumped to something like 50000, though you might be
able to determine a better amount be seeing how many pages are used up
after each add/delete/vacuum cycle. One other thing to check on is if
you have an indexes involved in the process, you may get some index
growth issues that will require periodic indexing. HTH,
Robert Treat
* I'm pretty sure those aren't the exact names, but their similar so you
should be able to find them.
On Thu, 2003-10-02 at 14:39, Chris White (cjwhite) wrote:
> Hi,
>
> I am using a Postgres 7.2.1 db to store binary data as large objects
> which users can add or delete as they need. I have found that without
> frequent vacuums of the database the disk space used by the database
> grows very quickly, as users on average add and delete binary objects of
> about 160K at a time. So I was trying to determine how often I should do
> a vacuum in order to keep the database from growing too quickly. So I
> ran a test where I did a full vacuum of the database to compact the
> tables, then I added and deleted 12 large objects of 80K each and then
> did a vacuum, not a full vacuum. I did this add/delete/vacuum process 4
> times in a row. I thought that by adding and deleting the same objects
> and then vacuuming, the database shouldn't grow, as the new inserts
> would reuse the space taken up by the deleted objects after the vacuum.
> However, I was seeing the database grow each time. Here are the disk
> usage values after each step:
>
> After initial vacuum full:
> bash-2.05b# du -b -s /data/sql
> 56664064 /data/sql
>
> After first add/delete/vacuum:
> bash-2.05b# du -b -s /data/sql
> 56987648 /data/sql
>
> 323584 byte increase
>
> After second add/delete/vacuum:
> bash-2.05b# du -b -s /data/sql
> 57012224 /data/sql
>
> 24576 byte increase
>
> After third add/delete/vacuum:
> bash-2.05b# du -b -s /data/sql
> 57061376 /data/sql
>
> 49152 byte increase
>
> After fourth add/delete/vacuum:
> bash-2.05b# du -b -s /data/sql
> 57085952 /data/sql
>
> 24576 byte increase
>
> Is this expected behavior? As at some point in time, if I carry on
> repeating this test, I would have to do a vacuum full to retrieve disk
> space, even though the actual contents of the database has not increased
> from the initial starting point.
>
> Chris White
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
I made the changes you suggested and the usage was about the same
Initial after vacuum full
bash-2.05b# du -s -b /data/sql
57729024 /data/sql
After 1st iteration
bash-2.05b# du -s -b /data/sql
57978880 /data/sql
249856 byte increase
After 2nd iteration
bash-2.05b# du -s -b /data/sql
58052608 /data/sql
73728 byte increase
After 3rd iteration
bash-2.05b# du -s -b /data/sql
58101760 /data/sql
49152 byte increase
After 4th iteration
bash-2.05b# du -s -b /data/sql
58126336 /data/sql
24576 byte increase
However what I am seeing is the pg_largeobject table is growing.
Vacuum info after 1st iteration
NOTICE: --Relation pg_largeobject--
NOTICE: Index pg_largeobject_loid_pn_index: Pages 157; Tuples 2564:
Deleted 168. CPU 0.02s/0.01u sec elapsed 0.04 sec.
NOTICE: Removed 168 tuples in 28 pages.
CPU 0.01s/0.00u sec elapsed 0.01 sec.
NOTICE: Pages 61: Changed 61, Empty 0; Tup 2564: Vac 168, Keep 458,
UnUsed 360.
Total CPU 0.03s/0.02u sec elapsed 0.06 sec.
Vacuum info after 4th iteration
NOTICE: --Relation pg_largeobject--
NOTICE: Index pg_largeobject_loid_pn_index: Pages 161; Tuples 2576:
Deleted 629. CPU 0.00s/0.03u sec elapsed 0.05 sec.
NOTICE: Removed 629 tuples in 68 pages.
CPU 0.02s/0.04u sec elapsed 0.06 sec.
NOTICE: Pages 71: Changed 55, Empty 0; Tup 2576: Vac 629, Keep 464,
UnUsed 635.
Total CPU 0.03s/0.07u sec elapsed 0.13 sec.
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.
Chris
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Robert Treat
Sent: Thursday, October 02, 2003 2:09 PM
To: cjwhite@cisco.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question about DB VACUUM
As a starting point, check your free space map settings in the
postgresql.conf. They are low by default in 7.2.x.
free_space_relations* can safely be bumped to 1000. free_space_pages*
should probably be bumped to something like 50000, though you might be
able to determine a better amount be seeing how many pages are used up
after each add/delete/vacuum cycle. One other thing to check on is if
you have an indexes involved in the process, you may get some index
growth issues that will require periodic indexing. HTH,
Robert Treat
* I'm pretty sure those aren't the exact names, but their similar so you
should be able to find them.
On Thu, 2003-10-02 at 14:39, Chris White (cjwhite) wrote:
> Hi,
>
> I am using a Postgres 7.2.1 db to store binary data as large objects
> which users can add or delete as they need. I have found that without
> frequent vacuums of the database the disk space used by the database
> grows very quickly, as users on average add and delete binary objects
> of about 160K at a time. So I was trying to determine how often I
> should do a vacuum in order to keep the database from growing too
> quickly. So I ran a test where I did a full vacuum of the database to
> compact the tables, then I added and deleted 12 large objects of 80K
> each and then did a vacuum, not a full vacuum. I did this
> add/delete/vacuum process 4 times in a row. I thought that by adding
> and deleting the same objects and then vacuuming, the database
> shouldn't grow, as the new inserts would reuse the space taken up by
> the deleted objects after the vacuum. However, I was seeing the
> database grow each time. Here are the disk usage values after each
> step:
>
> After initial vacuum full:
> bash-2.05b# du -b -s /data/sql
> 56664064 /data/sql
>
> After first add/delete/vacuum:
> bash-2.05b# du -b -s /data/sql
> 56987648 /data/sql
>
> 323584 byte increase
>
> After second add/delete/vacuum:
> bash-2.05b# du -b -s /data/sql
> 57012224 /data/sql
>
> 24576 byte increase
>
> After third add/delete/vacuum:
> bash-2.05b# du -b -s /data/sql
> 57061376 /data/sql
>
> 49152 byte increase
>
> After fourth add/delete/vacuum:
> bash-2.05b# du -b -s /data/sql
> 57085952 /data/sql
>
> 24576 byte increase
>
> Is this expected behavior? As at some point in time, if I carry on
> repeating this test, I would have to do a vacuum full to retrieve disk
> space, even though the actual contents of the database has not
> increased from the initial starting point.
>
> Chris White
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
"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
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
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
"Chris White \(cjwhite\)" <cjwhite@cisco.com> writes:
> Why aren't there any unused tuples?
The "unused" number isn't especially interesting, it's just the number
of line pointer slots that were once used and aren't at the moment.
At 4 bytes apiece, they aren't costing you anything worth noticing.
> Why is the pg_largeobject_loid_pn_index table so big (2818 pages)?
This looks like a standard "index bloat" problem (see the archives
for details). "REINDEX pg_largeobject" would make the bloat go away
for awhile. 7.4 should largely solve this problem, but in earlier
releases you need to figure on periodic reindexing.
> Why has table grown by 4 pages.
Probably because there are now 460 live tuples instead of 227.
I don't think you've entirely fixed your problem of not removing
all unused large objects...
regards, tom lane
But as you could see from the prior query \lo_list showed no large
objects, this was done just prior to the vacuum.
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
I am using the JDBC LargeObject.delete() method to remove large objects
from the pg_largeobject table. Could you suggest a better mechanism to
use from java?
Chris
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, October 06, 2003 8:53 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:
> Why aren't there any unused tuples?
The "unused" number isn't especially interesting, it's just the number
of line pointer slots that were once used and aren't at the moment. At 4
bytes apiece, they aren't costing you anything worth noticing.
> Why is the pg_largeobject_loid_pn_index table so big (2818 pages)?
This looks like a standard "index bloat" problem (see the archives for
details). "REINDEX pg_largeobject" would make the bloat go away for
awhile. 7.4 should largely solve this problem, but in earlier releases
you need to figure on periodic reindexing.
> Why has table grown by 4 pages.
Probably because there are now 460 live tuples instead of 227. I don't
think you've entirely fixed your problem of not removing all unused
large objects...
regards, tom lane
"Chris White \(cjwhite\)" <cjwhite@cisco.com> writes:
> But as you could see from the prior query \lo_list showed no large
> objects, this was done just prior to the vacuum.
> 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.
This would seem to indicate that you have open transactions hanging
around somewhere in the background. VACUUM can't delete tuples that
might still be visible under MVCC rules to some open transaction.
regards, tom lane
Okay now I understand what is going on. I have a second thread which is
being used to read these objects out of the database to present to the
user, and because large objects can only be accessed in a transaction
mode I have not closed the transaction on this thread. Should I do a
commit or rollback to terminate the transaction, once I have closed the
large object, even though I have not done any modifications to the large
objects?
Chris
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, October 06, 2003 9:08 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:
> But as you could see from the prior query \lo_list showed no large
> objects, this was done just prior to the vacuum.
> 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.
This would seem to indicate that you have open transactions hanging
around somewhere in the background. VACUUM can't delete tuples that
might still be visible under MVCC rules to some open transaction.
regards, tom lane
"Chris White \(cjwhite\)" <cjwhite@cisco.com> writes:
> Okay now I understand what is going on. I have a second thread which is
> being used to read these objects out of the database to present to the
> user, and because large objects can only be accessed in a transaction
> mode I have not closed the transaction on this thread. Should I do a
> commit or rollback to terminate the transaction, once I have closed the
> large object, even though I have not done any modifications to the large
> objects?
Yes, if you need to reclaim the space from recently-deleted large
objects. Under ordinary scenarios I'd not think that you have to be
real tense about this. However, if your reader thread was in the habit
of holding open the same transaction for hours or days, that would be
a Bad Thing ...
regards, tom lane
Tom,
I have found that vacuum only truly gets back all the tuples when there
are no other connections to the database. I found I had a connection to
the database which was doing nothing, only when I dropped this
connection did the vacuum give back all the tuples and reduce the pages
to zero, until then pages grew:
Vacuum with another connection to db:
aesop=# vacuum verbose pg_largeobject;
NOTICE: --Relation pg_largeobject--
NOTICE: Index pg_largeobject_loid_pn_index: Pages 2824; Tuples 231:
Deleted 670.
CPU 0.23s/0.02u sec elapsed 0.25 sec.
NOTICE: Removed 670 tuples in 40 pages.
CPU 0.02s/0.01u sec elapsed 0.02 sec.
NOTICE: Pages 40: Changed 40, Empty 0; Tup 231: Vac 670, Keep 0, UnUsed
0.
Total CPU 0.25s/0.03u sec elapsed 0.30 sec.
VACUUM
Vacuum with no ther connection to db:
aesop=# vacuum verbose pg_largeobject;
NOTICE: --Relation pg_largeobject--
NOTICE: Index pg_largeobject_loid_pn_index: Pages 2824; Tuples 0:
Deleted 231.
CPU 0.21s/0.02u sec elapsed 0.23 sec.
NOTICE: Removed 231 tuples in 16 pages.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Pages 40: Changed 16, Empty 0; Tup 0: Vac 231, Keep 0, UnUsed
670.
Total CPU 0.21s/0.03u sec elapsed 0.24 sec.
NOTICE: Truncated 40 --> 0 pages.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
VACUUM
Chris
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, October 06, 2003 9:24 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:
> Okay now I understand what is going on. I have a second thread which
> is being used to read these objects out of the database to present to
> the user, and because large objects can only be accessed in a
> transaction mode I have not closed the transaction on this thread.
> Should I do a commit or rollback to terminate the transaction, once I
> have closed the large object, even though I have not done any
> modifications to the large objects?
Yes, if you need to reclaim the space from recently-deleted large
objects. Under ordinary scenarios I'd not think that you have to be
real tense about this. However, if your reader thread was in the habit
of holding open the same transaction for hours or days, that would be a
Bad Thing ...
regards, tom lane
BTW, the connection I shutdown, had not read, written or deleted any
large objects. It had read and written to other tables. This is causing
me concern as I am using a thread pool to provide access to the data in
the large object table, and this seems to imply I have to close each
connection after reading/writing or deleting a large object in order for
me to truly reclaim unused space when I issue my periodic vacuum
command.
Chris
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Chris White
(cjwhite)
Sent: Tuesday, October 07, 2003 11:09 PM
To: 'Tom Lane'
Cc: 'Robert Treat'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question about DB VACUUM
Tom,
I have found that vacuum only truly gets back all the tuples when there
are no other connections to the database. I found I had a connection to
the database which was doing nothing, only when I dropped this
connection did the vacuum give back all the tuples and reduce the pages
to zero, until then pages grew:
Vacuum with another connection to db:
aesop=# vacuum verbose pg_largeobject;
NOTICE: --Relation pg_largeobject--
NOTICE: Index pg_largeobject_loid_pn_index: Pages 2824; Tuples 231:
Deleted 670.
CPU 0.23s/0.02u sec elapsed 0.25 sec.
NOTICE: Removed 670 tuples in 40 pages.
CPU 0.02s/0.01u sec elapsed 0.02 sec.
NOTICE: Pages 40: Changed 40, Empty 0; Tup 231: Vac 670, Keep 0, UnUsed
0.
Total CPU 0.25s/0.03u sec elapsed 0.30 sec.
VACUUM
Vacuum with no ther connection to db:
aesop=# vacuum verbose pg_largeobject;
NOTICE: --Relation pg_largeobject--
NOTICE: Index pg_largeobject_loid_pn_index: Pages 2824; Tuples 0:
Deleted 231.
CPU 0.21s/0.02u sec elapsed 0.23 sec.
NOTICE: Removed 231 tuples in 16 pages.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Pages 40: Changed 16, Empty 0; Tup 0: Vac 231, Keep 0, UnUsed
670.
Total CPU 0.21s/0.03u sec elapsed 0.24 sec.
NOTICE: Truncated 40 --> 0 pages.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
VACUUM
Chris
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, October 06, 2003 9:24 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:
> Okay now I understand what is going on. I have a second thread which
> is being used to read these objects out of the database to present to
> the user, and because large objects can only be accessed in a
> transaction mode I have not closed the transaction on this thread.
> Should I do a commit or rollback to terminate the transaction, once I
> have closed the large object, even though I have not done any
> modifications to the large objects?
Yes, if you need to reclaim the space from recently-deleted large
objects. Under ordinary scenarios I'd not think that you have to be
real tense about this. However, if your reader thread was in the habit
of holding open the same transaction for hours or days, that would be a
Bad Thing ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
In the last exciting episode, cjwhite@cisco.com ("Chris White (cjwhite)") wrote:
> BTW, the connection I shutdown, had not read, written or deleted any
> large objects. It had read and written to other tables. This is causing
> me concern as I am using a thread pool to provide access to the data in
> the large object table, and this seems to imply I have to close each
> connection after reading/writing or deleting a large object in order for
> me to truly reclaim unused space when I issue my periodic vacuum
> command.
Yup, that sounds like a more-than-vaguely familiar story...
The implication may not be _precisely_ correct, but the difference
between what you're expecting and reality seems to be difficult to get
at.
I would expect that if you fired a (perhaps trivial) transaction
through each of the connections once in a while, that would "clear
things up" too. How to accomplish that may be the challenge...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://www.ntlug.org/~cbbrowne/postgresql.html
"With sufficient thrust, pigs fly just fine. However, this is not
necessarily a good idea. It is hard to be sure where they are going to
land, and it could be dangerous sitting under them as they fly
overhead." -- RFC 1925
Sorry to be such a pain on this, but we are running postgres on a system
where we are using a 512MB compact flash as our physical disk media, so
disk space usage is of utmost importance.
Chris
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Christopher
Browne
Sent: Wednesday, October 08, 2003 6:22 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question about DB VACUUM
In the last exciting episode, cjwhite@cisco.com ("Chris White
(cjwhite)") wrote:
> BTW, the connection I shutdown, had not read, written or deleted any
> large objects. It had read and written to other tables. This is
> causing me concern as I am using a thread pool to provide access to
> the data in the large object table, and this seems to imply I have to
> close each connection after reading/writing or deleting a large object
> in order for me to truly reclaim unused space when I issue my periodic
> vacuum command.
Yup, that sounds like a more-than-vaguely familiar story...
The implication may not be _precisely_ correct, but the difference
between what you're expecting and reality seems to be difficult to get
at.
I would expect that if you fired a (perhaps trivial) transaction through
each of the connections once in a while, that would "clear things up"
too. How to accomplish that may be the challenge...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://www.ntlug.org/~cbbrowne/postgresql.html
"With sufficient thrust, pigs fly just fine. However, this is not
necessarily a good idea. It is hard to be sure where they are going to
land, and it could be dangerous sitting under them as they fly
overhead." -- RFC 1925
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html