Re: [Bacula-users] Better way to garbage collect postgresql database

Hemant Shah wrote:
>
> --- On Thu, 3/19/09, Kevin Keane <subscription AT kkeane DOT com> wrote:
>
>   
>> From: Kevin Keane <subscription AT kkeane DOT com>
>> Subject: Re: [Bacula-users] Better way to garbage collect postgresql database
>> To: 
>> Cc: "baculausers" <bacula-users AT lists.sourceforge DOT net>
>> Date: Thursday, March 19, 2009, 8:30 PM
>> Hemant Shah wrote:
>>     
>>> Folks,
>>>
>>> This is a database question, but I figured some of the
>>>       
>> bacula users may have come across this problem so I am
>> posting it here.
>>     
>>> Every monday I run following commands to check and
>>>       
>> garbage collect bacula database:
>>     
>>> dbcheck command
>>> vacuumdb -q -d bacula -z -f
>>> reindexdb
>>>
>>> Usually I purge one or two backup volumes and the
>>>       
>> above commands run in less than 20 minutes. 
>>     
>>> Before my monthly Full backup I delete large amount of
>>>       
>> data from the database as I delete one month worth of Full
>> and Incremental backups. When I run the above commands after
>> the Full backup, the vacummdb command take 12 hours to run.
>> Is there a faster/better way of doing it?
>>     
>>>   
>>>       
>> It has been a long time since I administered a postgres DB,
>> but if 
>> memory serves me right you might be able to drop some
>> indexes, then do 
>> the vacuuming, and then recreate them. Also, I believe you
>> can vacuum 
>> individual tables rather than the database as a whole.
>>
>> The lion's share of the vacuuming would happen in the
>> files table, so 
>> that's probably the one you'd want to first look at
>> in terms of 
>> dropping/recreating indexes, and also in terms of vacuuming
>> separately.
>>
>> Also there are several levels of vacuuming. With this type
>> of table, you 
>> would probably not want to get too aggressive. What you
>> don't want to do 
>> is eliminate all the empty space in the database, only to
>> later need the 
>> same empty space again. You do want to vacuum simply to
>> consolidate 
>> empty space into larger chunks. Basically, the same idea as
>> disk 
>> defragmentation. If memory serves me right, this
>> "milder" vacuuming is 
>> the default.
>>
>>     
>
> Kevin,
>
>   This is exactly what I want to do. I come from DB2 world and we would reorg 
> the table. I run milder version of vacuum on other days, but I run extensive 
> vacuum after full backup. 
>   
I think that's excessive. I used to run a major production database, 
processing hundreds of online purchases per hour, in Postgres. And we 
only did the regular vacuum once a week, if memory serves me right. Full 
vacuums only in very rare circumstances - pretty much never.

Not saying that this is always true; our database at the time pretty 
much only ever grew, we almost never deleted any records from it. The 
bacula database obviously does a lot more deleting.

In fact, a full vacuum can be harmful to performance, because it 
eliminates the free space that Postgres can work with. In a scenario 
such as yours, it may well be better if the database stays at its 
biggest size at all times, and you let Postgres manage the free space.

-- 
Kevin Keane
Owner
The NetTech
Find the Uncommon: Expert Solutions for a Network You Never Have to Think About

Office: 866-642-7116
http://www.4nettech.com

This e-mail and attachments, if any, may contain confidential and/or 
proprietary information. Please be advised that the unauthorized use or 
disclosure of the information is strictly prohibited. The information herein is 
intended only for use by the intended recipient(s) named above. If you have 
received this transmission in error, please notify the sender immediately and 
permanently delete the e-mail and any copies, printouts or attachments thereof.


------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users