Re: Question about DB VACUUM

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(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Robert Treat
Sent: Thursday, October 02, 2003 2:09 PM
To: cjwhite(at)cisco(dot)com
Cc: pgsql-admin(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly