We're running Postgresql 8.0.8; we can't currently update this machine (we'll
have to move Bacula to a newer box when we have one available). Ran that
query, and the top 4 do have very large numbers:
relname | reltuples | relpages
---------------------------------+-------------+----------
file | 3.28168e+07 | 592614
file_fp_idx | 3.28168e+07 | 378580
file_jobid_idx | 3.28168e+07 | 368832
file_pkey | 3.28168e+07 | 364870
And running vacuumdb with the --analyze flag says:
INFO: "file": found 0 removable, 32828342 nonremovable row versions in 592867
pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 113 to 154 bytes long.
I thought that meant it wasn't going to actually do anything, but it did reduce
the DB size to 6.5GB. I had actually stopped bacula before running it this
time, so perhaps that had an effect. After that, I went ahead and ran dbcheck
(thanks, John), and it found a bunch of stuff:
Found 10000000 orphaned File records.
Deleting 10000000 orphaned File records.
Checking for orphaned Path entries. This may take some time!
Found 17688 orphaned Path records.
Deleting 17688 orphaned Path records.
Checking for orphaned Filename entries. This may take some time!
Found 72448 orphaned Filename records.
Deleting 72448 orphaned Filename records.
Checking for orphaned FileSet entries. This takes some time!
Found 25 orphaned FileSet records.
Deleting 25 orphaned FileSet records.
Checking for orphaned Client entries.
Found 2 orphaned Client records.
Deleting 2 orphaned Client records.
Checking for orphaned Job entries.
Found 1284 orphaned Job records.
I ran the full vacuum after that, and now it is down to 5.9GB, so I guess all
those records really weren't taking up much space. Also, the indexes actually
got bigger:
relname | reltuples | relpages
-------------------------+-------------+----------
file | 3.28283e+07 | 592684
file_fp_idx | 3.28283e+07 | 90029
file_jobid_idx | 3.28283e+07 | 71896
file_pkey | 3.28283e+07 | 71895
I read up on it and saw that this was expected behavior, and that running a
reindex on the table should fix it. So I ran REINDEX TABLE file;, but that
didn't have any effect. I'll do some looking into that today.
Also, I found the output from dbcheck curious. Of all the orphaned records it
found, the file records were an even number: 10,000,000. It sort of seems
like maybe dbcheck can only clean 10,000,000 records at a time. : ) So, I
have just now started running it again, and so far it has found 0 bad Path
records, 0 bad Filename records, etc., all 0 this time, until it got to File
records, where it says again: Found 10000000 File records, Deleting 10000000
orphaned File records.
I'll report back when it is finished.
Thanks,
--Jeremy
-----Original Message-----
From: Martin Simmons [mailto:mar...@lispworks.com]
Sent: Wednesday, August 05, 2009 6:58
To: bacula-users@lists.sourceforge.net
Subject: Re: [Bacula-users] Catalog too big / not pruning?
The job table is probably not causing the bloat, unless you have millions of
rows. The space is usually consumed by the file table and its indexes.
Try running vacuumdb with the --analyze and --verbose options, which prints
info about the number of pages used by each table/indexes and also the number
of unused rows. You can also get info from the SQL query
SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC;
Which version of postgresql are you using? With postgresql 7, I found that
regularly doing reindex on the file table indexes was needed to prevent bloat.
__Martin
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
