Re: [Bacula-users] Catalog too big / not pruning?
On Thu, 6 Aug 2009, Jeremy Koppel wrote: 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 1000 orphaned File records. Deleting 1000 orphaned File records. How long did it take to remove these entries? AB -- 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
Re: [Bacula-users] Catalog too big / not pruning?
Wasn't sitting here the whole time, but it was 2-3 hours each run. --Jeremy -Original Message- From: Alan Brown [mailto:a...@mssl.ucl.ac.uk] Sent: Tuesday, August 11, 2009 12:13 To: Jeremy Koppel Cc: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] Catalog too big / not pruning? On Thu, 6 Aug 2009, Jeremy Koppel wrote: 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 1000 orphaned File records. Deleting 1000 orphaned File records. How long did it take to remove these entries? AB -- 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
Re: [Bacula-users] Catalog too big / not pruning?
: ) Thanks for the assist. Could be a version problem. This is on one of our older servers, and it's running Bacula version 1.36.3, which for years was the only version available on Gentoo. (looks like current version in Portage is 2.4.1). When a new server is available, I'll look into migrating it. --Jeremy -Original Message- From: Martin Simmons [mailto:mar...@lispworks.com] Sent: Friday, August 07, 2009 14:30 To: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] Catalog too big / not pruning? On Fri, 7 Aug 2009 07:55:08 -0700, Jeremy Koppel said: I ended up running dbcheck 3 more times. The first time got another 10,000,000, the second another 8,000,000+, and the 3rd was trivial. Running it a fourth time came up all 0s. Running another full vacuum got the DB size down to 597MB, which sounds right. Nice space saving! In theory Bacula will remove stale file records, but there was a bug in some version that could leave them. So, there has been a job in the crontab that runs the standard vacuumdb (not full), but, it does this while Bacula is running. For the past few days, while running the full vacuum, I shut it off as a precaution. Perhaps I should modify the script to shut down Bacula during the standard vacuum? Is this needed? It is probably better to avoid vacuuming while Bacula is updating the database, but there is no need to shut it down. You could run the vacuum from a Bacula admin job rather than cron if you want to control it better. I run it after the backups, so pruning has had a chance to remove records. __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
Re: [Bacula-users] Catalog too big / not pruning?
You're right; I was looking at something else entirely - I was comparing file_fp_idx and file_jobid_idx with a 3rd set of numbers I forgot to post - the numbers after running the vacuumdb, and then subsequently running the REINDEX command; they were identical. So, I guess it's no longer true that vacuumdb increases the indexes; it must reindex automatically now. I ended up running dbcheck 3 more times. The first time got another 10,000,000, the second another 8,000,000+, and the 3rd was trivial. Running it a fourth time came up all 0s. Running another full vacuum got the DB size down to 597MB, which sounds right. So, there has been a job in the crontab that runs the standard vacuumdb (not full), but, it does this while Bacula is running. For the past few days, while running the full vacuum, I shut it off as a precaution. Perhaps I should modify the script to shut down Bacula during the standard vacuum? Is this needed? --Jeremy -Original Message- From: Martin Simmons [mailto:mar...@lispworks.com] Sent: Thursday, August 06, 2009 13:11 To: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] Catalog too big / not pruning? On Thu, 6 Aug 2009 05:59:24 -0700, Jeremy Koppel said: 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 OK, that is 147 bytes per row, which is about what you would expect. 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 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. Look again at the sizes -- they actually got 5x smaller! Initially they were very bloated compared to the table size. 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 1000 File records, Deleting 1000 orphaned File records. Yes, I think there is a limit on the number of file records it will delete each time. __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
Re: [Bacula-users] Catalog too big / not pruning?
On Fri, 7 Aug 2009 07:55:08 -0700, Jeremy Koppel said: I ended up running dbcheck 3 more times. The first time got another 10,000,000, the second another 8,000,000+, and the 3rd was trivial. Running it a fourth time came up all 0s. Running another full vacuum got the DB size down to 597MB, which sounds right. Nice space saving! In theory Bacula will remove stale file records, but there was a bug in some version that could leave them. So, there has been a job in the crontab that runs the standard vacuumdb (not full), but, it does this while Bacula is running. For the past few days, while running the full vacuum, I shut it off as a precaution. Perhaps I should modify the script to shut down Bacula during the standard vacuum? Is this needed? It is probably better to avoid vacuuming while Bacula is updating the database, but there is no need to shut it down. You could run the vacuum from a Bacula admin job rather than cron if you want to control it better. I run it after the backups, so pruning has had a chance to remove records. __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
Re: [Bacula-users] Catalog too big / not pruning?
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 1000 orphaned File records. Deleting 1000 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 1000 File records, Deleting 1000 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
Re: [Bacula-users] Catalog too big / not pruning?
On Thu, 6 Aug 2009 05:59:24 -0700, Jeremy Koppel said: 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 OK, that is 147 bytes per row, which is about what you would expect. 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 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. Look again at the sizes -- they actually got 5x smaller! Initially they were very bloated compared to the table size. 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 1000 File records, Deleting 1000 orphaned File records. Yes, I think there is a limit on the number of file records it will delete each time. __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
Re: [Bacula-users] Catalog too big / not pruning?
2009/8/4 Jeremy Koppel jkop...@bluecanopy.com: Lately, I’ve been going though our file server looking for disk space to reclaim, and I’ve come across 14GB worth of data in the Postgres DB, used only by Bacula. Reading through the Bacula manual, I see that each file record is supposed to take up 154 bytes in the DB, so I have gone through the logs to see how many records should be saved, keying on “FD Files Written”. Our rotation: Level=Full Pool=Weekly sun at 2:00 Level=Differential Pool=Daily FullPool=Weekly mon-fri at 2:00 File and Job Retention is set to 5 weeks in the Client directives, Volume Retention is set to 5 weeks in the Pool directives. AutoPrune is set to Yes in both places. The exception is the Monthly pool, used by 2 clients (small): Level=Full Pool=Monthly 1st sun at 1:00 Level=Full Pool=Weekly 2nd-4th sun at 1:00 Level=Differential Pool=Daily FullPool=Weekly mon-fri at 2:00 For the Monthly pool, File Retention is set to 90d, and Job Retention is set to 1y in the Client directives, and Volume Retention is set to 1y in the pool. AutoPrune is set to Yes in the Client Directives, but had been set to No in the Pool, which was a red flag. I changed it to Yes, and restarted Bacula. The numbers: In the last 5 weeks, the sum of the backed up files to the Weekly and Daily pools is 3,588,224, and of the other two, 1 client backs up 2 files to the Monthly pool a month, and the other, consistently just under 6,640 files. So, if my catalog should have a total of 3,667,928 files in it (3,588,224 + (2*12) + (6,640 * 12)) * 154 bytes per file, the DB should be 564,860,912 bytes or 538MB? Made me either wonder where my math went wrong, or why my DB is so big. So, after running a full vacuum on the Postgres DB and only reclaiming 1GB of disk space, I started poking around in the DB itself (just looking). The most interesting thing I have found so far is a table named ‘job’, which seems to hold all the job records. And they go back to 2006…. Example: bacula=# select * from job where starttime like '2006%'; jobid | job | name | type | level | clientid | jobstatus | schedtime | starttime | endtime | jobtdate | volsessionid | volsessiontime | jobfiles | jobbytes | joberrors | jobmissingfiles | poo lid | filesetid | purgedfiles | hasbase ---+-+-+--+---+--+ ---+-+-+-+ +--++--+---+---+-+ +---+-+- 272 | Mail_Data.2006-01-26_02.00.06 | Mail Data | B | D | | f | 2006-01-26 02:00:05 | 2006-01-26 02:08:49 | 2006-01-26 02:37:33 | 1138261053 | 0 | 0 | 0 | 0 | 0 | 0 | | | 0 | 0 I thought I would then be clever, and in Bacula use the prune command for the old MailData job, one of the ones set to 5 weeks, but it didn’t show up in the list because it was no longer a defined job resource; we don’t run that particular job anymore, and haven’t for months: thus there should be no jobs for ‘MailData’ in the catalog at all. Luckily, it was just commented out in bacula-dir.conf, so I uncommented it and restarted bacula. It then showed MailData, and said: “Pruned 15 Jobs for client MailData from catalog.”. Encouraged, I exited Bacula and checked the DB. They are all still there. Rinse and repeat. This time, Bacula says: “No Jobs found to prune.”, so maybe it did get rid of a few of them after all. But the rest are sitting there derelict, just hogging space. So, why isn’t my DB pruning itself? I am not sure of that answer but dbcheck can fix this. Make sure you use the -f option otherwise it only checks. John -- 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
