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 10000000 File records, Deleting > 10000000 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