Bacula

In response to "Frank Altpeter" <frank.altpeter@...>:
> 
> My current bacula system (FreeBSD, bacula-2.0.1, mysql-4.1) has
> currently some massive performance problems. One of the reasons i
> think is caused by the massive amount of old and obsolete records.
> For example, i had a client to backup once, which has dissappeared
> some time ago. When this machine has been removed, the client has been
> deleted from the bacula configuration. But this way the database
> records for these clients remain in the database, thus making the db
> records more and more unusable.
> So, i would like to prune such records to reduce unneeded data.
> 
> Is there any way on archiving this?

dbcheck should clean this stuff up.

> And, for the future, what's the best practice to avoid this?

Purge the volumes prior to removing the clients.

On the flip side, running dbcheck periodically is pretty much a requirement
for keeping Bacula's database reasonably sized.  I have it run once a month
in read-only mode via cron and email us the results.  When the extra stuff
gets significant, I use it to go in and clean up.

-- 
Bill Moran
Collaborative Fusion Inc.

On 2/19/07, Bill Moran <wmoran@...> wrote:
> In response to "Frank Altpeter" <frank.altpeter@...>:
> >
> > My current bacula system (FreeBSD, bacula-2.0.1, mysql-4.1) has
> > currently some massive performance problems. One of the reasons i
> > think is caused by the massive amount of old and obsolete records.
> > For example, i had a client to backup once, which has dissappeared
> > some time ago. When this machine has been removed, the client has been
> > deleted from the bacula configuration. But this way the database
> > records for these clients remain in the database, thus making the db
> > records more and more unusable.
> > So, i would like to prune such records to reduce unneeded data.
> >
> > Is there any way on archiving this?
>
> dbcheck should clean this stuff up.
>
> > And, for the future, what's the best practice to avoid this?
>
> Purge the volumes prior to removing the clients.
>
> On the flip side, running dbcheck periodically is pretty much a requireme=
nt
> for keeping Bacula's database reasonably sized.  I have it run once a mon=
th
> in read-only mode via cron and email us the results.  When the extra stuf=
f
> gets significant, I use it to go in and clean up.

dbcheck is being run on a weekly basis (every sunday before backup
starts) but didn't catch all the old entries from the database.
I just removed manually old entries from Job and File database
relating to jobs back in 2005 ... about 5 million file entries (from
about 25 currently)...
So, dbcheck doesn't seem to clean up that much :)


--=20
Le deagh dh=F9raghd,

        Frank Altpeter

Two of the most famous products of Berkeley are LSD and Unix.
I don't think that this is a coincidence.
        -- Anonymous

>>>>> On Mon, 19 Feb 2007 15:06:13 +0100, Frank Altpeter said:
> 
> On 2/19/07, Bill Moran <wmoran@...> wrote:
> > In response to "Frank Altpeter" <frank.altpeter@...>:
> > >
> > > My current bacula system (FreeBSD, bacula-2.0.1, mysql-4.1) has
> > > currently some massive performance problems. One of the reasons i
> > > think is caused by the massive amount of old and obsolete records.
> > > For example, i had a client to backup once, which has dissappeared
> > > some time ago. When this machine has been removed, the client has been
> > > deleted from the bacula configuration. But this way the database
> > > records for these clients remain in the database, thus making the db
> > > records more and more unusable.
> > > So, i would like to prune such records to reduce unneeded data.
> > >
> > > Is there any way on archiving this?
> >
> > dbcheck should clean this stuff up.
> >
> > > And, for the future, what's the best practice to avoid this?
> >
> > Purge the volumes prior to removing the clients.
> >
> > On the flip side, running dbcheck periodically is pretty much a requirement
> > for keeping Bacula's database reasonably sized.  I have it run once a month
> > in read-only mode via cron and email us the results.  When the extra stuff
> > gets significant, I use it to go in and clean up.
> 
> dbcheck is being run on a weekly basis (every sunday before backup
> starts) but didn't catch all the old entries from the database.
> I just removed manually old entries from Job and File database
> relating to jobs back in 2005 ... about 5 million file entries (from
> about 25 currently)...
> So, dbcheck doesn't seem to clean up that much :)

I think that is correct -- dbcheck just removes unreferenced entries, so if
the job record is still there then it won't do anything to the file records.

__Martin

>>>>> On Mon, 19 Feb 2007 08:55:07 -0500, Bill Moran said:
> 
> On the flip side, running dbcheck periodically is pretty much a requirement
> for keeping Bacula's database reasonably sized.  I have it run once a month
> in read-only mode via cron and email us the results.  When the extra stuff
> gets significant, I use it to go in and clean up.

I think that is overly pessimistic -- unless you are backing up lots of data
where the file names or directory names keep changing in the Filename and Path
tables.

I find that those tables are dwarfed by the File table, but that is cleaned by
Bacula's own pruning to shouldn't be a problem (though there was a bug in this
in some obsolete version).

__Martin

In response to Martin Simmons <martin@...>:

> >>>>> On Mon, 19 Feb 2007 08:55:07 -0500, Bill Moran said:
> > 
> > On the flip side, running dbcheck periodically is pretty much a requirement
> > for keeping Bacula's database reasonably sized.  I have it run once a month
> > in read-only mode via cron and email us the results.  When the extra stuff
> > gets significant, I use it to go in and clean up.
> 
> I think that is overly pessimistic -- unless you are backing up lots of data
> where the file names or directory names keep changing in the Filename and Path
> tables.

Backup up a server with a bunch of Maildirs, and you'll see this fast enough.

Even without that, with 6 servers and 20 workstations being backed up, lots of
files get created, renamed, deleted, on a constant basis.  Every few months,
the amount of unreferenced stuff is big enough to warrant cleanup.

Personally, I don't think a monthly review of the state of the backup system
is overly pessimistic in any way :)

bacula=> select count(*) from file;
  count   
----------
 19667790
(1 row)

bacula=> select count(*) from filename;
 count  
--------
 927786
(1 row)

bacula=> select count(*) from path;
 count  
--------
 655714
(1 row)


> I find that those tables are dwarfed by the File table, but that is cleaned by
> Bacula's own pruning to shouldn't be a problem (though there was a bug in this
> in some obsolete version).

I agree that the file table gets dwarfs the other two, but it doesn't change
the fact that our filename and path tables get full of unreferenced stuff over
time.  Cleaning it out occasionally keeps database size down.

-- 
Bill Moran
Collaborative Fusion Inc.

Bill Moran wrote:
> In response to Martin Simmons <martin@...>:
> 
>>>>>>> On Mon, 19 Feb 2007 08:55:07 -0500, Bill Moran said:
>>> On the flip side, running dbcheck periodically is pretty much a requirement
>>> for keeping Bacula's database reasonably sized.  I have it run once a month
>>> in read-only mode via cron and email us the results.  When the extra stuff
>>> gets significant, I use it to go in and clean up.
>> I think that is overly pessimistic -- unless you are backing up lots of data
>> where the file names or directory names keep changing in the Filename and Path
>> tables.
> 
> Backup up a server with a bunch of Maildirs, and you'll see this fast enough.
> 
> Even without that, with 6 servers and 20 workstations being backed up, lots of
> files get created, renamed, deleted, on a constant basis.  Every few months,
> the amount of unreferenced stuff is big enough to warrant cleanup.
> 
> Personally, I don't think a monthly review of the state of the backup system
> is overly pessimistic in any way :)
> 
> bacula=> select count(*) from file;
>   count   
> ----------
>  19667790
> (1 row)
> 
> bacula=> select count(*) from filename;
>  count  
> --------
>  927786
> (1 row)
> 
> bacula=> select count(*) from path;
>  count  
> --------
>  655714
> (1 row)
> 
> 
>> I find that those tables are dwarfed by the File table, but that is cleaned by
>> Bacula's own pruning to shouldn't be a problem (though there was a bug in this
>> in some obsolete version).
> 
> I agree that the file table gets dwarfs the other two, but it doesn't change
> the fact that our filename and path tables get full of unreferenced stuff over
> time.  Cleaning it out occasionally keeps database size down.
> 

I wasn't aware that it was important to run dbcheck at certain intervals. So I 
sat it up about 8 hours ago. It showed 4919334 orphaned file records and it is 
now deleting them. How long time is it supposed to take? It has now been 
deleting for almost 8 hours and mysqld takes about 85% of the cpu. Is it looping 
or is it just what I should expect?

-- 
Erik P. Olsen, Civilingeniør, MSc
Solsortvej 30, DK-2000 Frederiksberg, Denmark
Phone: +45 38346480, Fax: +45 32106480, Mobil: +45 40765300

On Tue, Feb 20, 2007 at 01:04:44AM +0100, Erik P. Olsen wrote:
> I wasn't aware that it was important to run dbcheck at certain intervals. So
> I sat it up about 8 hours ago. It showed 4919334 orphaned file records and it
> is now deleting them. How long time is it supposed to take? It has now been
> deleting for almost 8 hours and mysqld takes about 85% of the cpu. Is it
> looping or is it just what I should expect?

It can easily take that long, especially if you're missing some table indexes.

http://paramount.ind.wpi.edu/wiki/doku.php?id=faq#why_does_dbcheck_take_forever_to_run

-- 
Frank Sweetser fs at wpi.edu  |  For every problem, there is a solution that
WPI Network Engineer          |  is simple, elegant, and wrong. - HL Mencken
    GPG fingerprint = 6174 1257 129E 0D21 D8D4  E8A3 8E39 29E3 E2E8 8CEC

Frank Sweetser wrote:
> On Tue, Feb 20, 2007 at 01:04:44AM +0100, Erik P. Olsen wrote:
>> I wasn't aware that it was important to run dbcheck at certain intervals. So
>> I sat it up about 8 hours ago. It showed 4919334 orphaned file records and it
>> is now deleting them. How long time is it supposed to take? It has now been
>> deleting for almost 8 hours and mysqld takes about 85% of the cpu. Is it
>> looping or is it just what I should expect?
> 
> It can easily take that long, especially if you're missing some table indexes.
> 
> http://paramount.ind.wpi.edu/wiki/doku.php?id=faq#why_does_dbcheck_take_forever_to_run
> 
Well, it is still running now 16 hours after launch. In that case I think 
dbcheck ought to have a sort of progress indication, a counter perhaps.

-- 
Erik P. Olsen, Civilingeniør, MSc
Solsortvej 30, DK-2000 Frederiksberg, Denmark
Phone: +45 38346480, Fax: +45 32106480, Mobil: +45 40765300

On Monday 19 February 2007 15:06, Frank Altpeter wrote:
> On 2/19/07, Bill Moran <wmoran@...> wrote:
> > In response to "Frank Altpeter" <frank.altpeter@...>:
> > > My current bacula system (FreeBSD, bacula-2.0.1, mysql-4.1) has
> > > currently some massive performance problems. One of the reasons i
> > > think is caused by the massive amount of old and obsolete records.
> > > For example, i had a client to backup once, which has dissappeared
> > > some time ago. When this machine has been removed, the client has been
> > > deleted from the bacula configuration. But this way the database
> > > records for these clients remain in the database, thus making the db
> > > records more and more unusable.
> > > So, i would like to prune such records to reduce unneeded data.
> > >
> > > Is there any way on archiving this?
> >
> > dbcheck should clean this stuff up.
> >
> > > And, for the future, what's the best practice to avoid this?
> >
> > Purge the volumes prior to removing the clients.
> >
> > On the flip side, running dbcheck periodically is pretty much a
> > requirement for keeping Bacula's database reasonably sized.  I have it
> > run once a month in read-only mode via cron and email us the results. 
> > When the extra stuff gets significant, I use it to go in and clean up.
>
> dbcheck is being run on a weekly basis (every sunday before backup
> starts) but didn't catch all the old entries from the database.
> I just removed manually old entries from Job and File database
> relating to jobs back in 2005 ... about 5 million file entries (from
> about 25 currently)...
> So, dbcheck doesn't seem to clean up that much :)

That is exactly correct. dbcheck is designed to remove orphaned records.  That 
is records that are not consistant with the state of the database -- i.e. a 
File record where there is no corresponding Job that contains them or Path or 
Filename records where there is no File record that points to them (deleted 
files ...).

dbcheck does not do pruning -- that is something that is done within Bacula 
providing you run jobs.  If you have a job that you never run, those job 
records will never be prunned.  As a consequence, if you have old jobs that 
you never run any more, you must manually prune or delete them.  The same 
goes for client records.  If you no longer use a client, you need to manually 
clean up the database.

On Tuesday 20 February 2007 09:05, Erik P. Olsen wrote:
> Frank Sweetser wrote:
> > On Tue, Feb 20, 2007 at 01:04:44AM +0100, Erik P. Olsen wrote:
> >> I wasn't aware that it was important to run dbcheck at certain
> >> intervals. So I sat it up about 8 hours ago. It showed 4919334 orphaned
> >> file records and it is now deleting them. How long time is it supposed
> >> to take? It has now been deleting for almost 8 hours and mysqld takes
> >> about 85% of the cpu. Is it looping or is it just what I should expect?
> >
> > It can easily take that long, especially if you're missing some table
> > indexes.
> >
> > http://paramount.ind.wpi.edu/wiki/doku.php?id=faq#why_does_dbcheck_take_f
> >orever_to_run
>
> Well, it is still running now 16 hours after launch. In that case I think
> dbcheck ought to have a sort of progress indication, a counter perhaps.

That would be a nice patch for someone to submit.  The current development 
version probably eliminates the need for a counter because it breaks the work 
up into more reasonable size chunks, so it *should* run much faster.

---------- Forwarded message ----------
From: Frank Altpeter <frank.altpeter@...>
Date: Feb 20, 2007 7:00 PM
Subject: Re: [Bacula-users] Cleaning up database records
To: Kern Sibbald <kern@...>


I'm running dbcheck periodically every sunday with the following script:

---------------------------------------------------------------------------=
---------------------
#!/bin/sh

PATH=3D"/bin:/usr/bin:/usr/local/bin:/sbin:/usr/sbin:/usr/local/sbin"

echo "$(date) Creating temp indices for bacula database..."

mysql -ubacula <<EOFA
use bacula
CREATE INDEX file_tmp_filenameid_idx ON File (FilenameId);
CREATE INDEX file_tmp_pathid_idx ON File (PathId);
EOFA

echo "$(date) Running dbcheck..."
dbcheck -c /usr/local/etc/bacula-dir.conf -f -b -v

echo "$(date) Removing indices and optimizing bacula database..."

mysql -ubacula <<EOFB
use bacula
DROP INDEX file_tmp_filenameid_idx ON File;
DROP INDEX file_tmp_pathid_idx ON File;
OPTIMIZE TABLE UnsavedFiles, Counters, CDImages, BaseFiles, Device,
Version, Status, MediaType, Storage, FileSet, Client, Pool, Media,
Job, JobMedia, File, Path, Filename;
EOFB

echo "$(date) Done..."
---------------------------------------------------------------------------=
---------------------

Due to the additional index entries, the script runs for about 90
minutes (eternally without them) with a database of 24 million file
entries from 88 clients, mostly application servers.

It helps a lot, but still it doesn't help against orphaned entries
from previously removed clients.



--
Le deagh dh=F9raghd,

        Frank Altpeter

Two of the most famous products of Berkeley are LSD and Unix.
I don't think that this is a coincidence.
        -- Anonymous


--=20
Le deagh dh=F9raghd,

        Frank Altpeter

Two of the most famous products of Berkeley are LSD and Unix.
I don't think that this is a coincidence.
        -- Anonymous

>>>>> On Mon, 19 Feb 2007 15:10:26 -0500, Bill Moran said:
> 
> In response to Martin Simmons <martin@...>:
> 
> > >>>>> On Mon, 19 Feb 2007 08:55:07 -0500, Bill Moran said:
> > > 
> > > On the flip side, running dbcheck periodically is pretty much a requirement
> > > for keeping Bacula's database reasonably sized.  I have it run once a month
> > > in read-only mode via cron and email us the results.  When the extra stuff
> > > gets significant, I use it to go in and clean up.
> > 
> > I think that is overly pessimistic -- unless you are backing up lots of data
> > where the file names or directory names keep changing in the Filename and Path
> > tables.
> 
> Backup up a server with a bunch of Maildirs, and you'll see this fast enough.

Ah, good point.


> Even without that, with 6 servers and 20 workstations being backed up, lots of
> files get created, renamed, deleted, on a constant basis.  Every few months,
> the amount of unreferenced stuff is big enough to warrant cleanup.
> 
> Personally, I don't think a monthly review of the state of the backup system
> is overly pessimistic in any way :)
> 
> bacula=> select count(*) from file;
>   count   
> ----------
>  19667790
> (1 row)
> 
> bacula=> select count(*) from filename;
>  count  
> --------
>  927786
> (1 row)
> 
> bacula=> select count(*) from path;
>  count  
> --------
>  655714
> (1 row)

That's an interestingly low ratio of filename/path :-)

__Martin

In response to Martin Simmons <martin@...>:

> >>>>> On Mon, 19 Feb 2007 15:10:26 -0500, Bill Moran said:
> > 
> > In response to Martin Simmons <martin@...>:
> > 
> > > >>>>> On Mon, 19 Feb 2007 08:55:07 -0500, Bill Moran said:
> > > > 
> > > > On the flip side, running dbcheck periodically is pretty much a requirement
> > > > for keeping Bacula's database reasonably sized.  I have it run once a month
> > > > in read-only mode via cron and email us the results.  When the extra stuff
> > > > gets significant, I use it to go in and clean up.
> > > 
> > > I think that is overly pessimistic -- unless you are backing up lots of data
> > > where the file names or directory names keep changing in the Filename and Path
> > > tables.
> > 
> > Backup up a server with a bunch of Maildirs, and you'll see this fast enough.
> 
> Ah, good point.
> 
> 
> > Even without that, with 6 servers and 20 workstations being backed up, lots of
> > files get created, renamed, deleted, on a constant basis.  Every few months,
> > the amount of unreferenced stuff is big enough to warrant cleanup.
> > 
> > Personally, I don't think a monthly review of the state of the backup system
> > is overly pessimistic in any way :)
> > 
> > bacula=> select count(*) from file;
> >   count   
> > ----------
> >  19667790
> > (1 row)
> > 
> > bacula=> select count(*) from filename;
> >  count  
> > --------
> >  927786
> > (1 row)
> > 
> > bacula=> select count(*) from path;
> >  count  
> > --------
> >  655714
> > (1 row)
> 
> That's an interestingly low ratio of filename/path :-)

Never really thought about it, but it does seem a little odd.

It's probably an indicator of the fact that we're a devel shop.  Most
of our users have copies of the software project in their home directory,
so lots of new paths, but copies of the same files (filenames)

-- 
Bill Moran
Collaborative Fusion Inc.

On Tue, February 20, 2007 10:00 am, Frank Altpeter wrote:

> I'm running dbcheck periodically every sunday with the following script:

SNIP

Thanks for sharing your script, Frank.  I modified it to work on our
servers, and on one of them that had a File table that was over 1.6G, it
is now down to a little over 800M.

Michael


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.