--- On Tue, 7/1/08, Hemant Shah <hjrrs AT yahoo DOT com> wrote:

> From: Hemant Shah <hjrrs AT yahoo DOT com>
> Subject: Re: [Bacula-users] Restore very slow, looks like database problem.
> To: "baculausers" <bacula-users AT lists.sourceforge DOT net>
> Date: Tuesday, July 1, 2008, 2:07 PM
> --- On Tue, 7/1/08, John Drescher
> <drescherjm AT gmail DOT com> wrote:
> 
> > From: John Drescher <drescherjm AT gmail DOT com>
> > Subject: Re: [Bacula-users] Restore very slow, looks
> like database problem.
> > To: hjrrs AT yahoo DOT com
> > Cc: "baculausers"
> <bacula-users AT lists.sourceforge DOT net>
> > Date: Tuesday, July 1, 2008, 1:35 PM
> > On Tue, Jul 1, 2008 at 2:28 PM, Hemant Shah
> > <hjrrs AT yahoo DOT com> wrote:
> > > Folks,
> > >
> > >  I do a full backup on 1st friday of the month
> and
> > incremental on other days. Everything is backed up to
> disk.
> > >
> > >  I tried to run restore from bat. After selecting
> the
> > directories to restore (all the jobs from the last
> full
> > backup to the last incremental backup were selected),
> when
> > I clicked on "Restore" button, it spent over
> 2
> > hours processing selected directories and filling
> database
> > tables. Once it was done doing that, I selected
> alternate
> > directory for restoring the files, the files were
> restored
> > in less than 5 minutes.
> > >
> > >  During this time postmaster (postgresql) process
> was
> > using up all CPU time and there was lots of disk I/O.
> > >
> > >  Why is it taking so long to process the list? I
> am
> > using postgresql. I ran vacuum full and analyze
> commands
> > and ran restore again, but still no difference.
> > >
> > 
> > Do you have your database properly indexed?
> > 
> >
> http://www.bacula.org/en/dev-manual/Catalog_Maintenance.html#SECTION002480000000000000000
> > 
> > John
> 
> Yes. I checked, all three indexes (file_pkey,
> file_jobid_idx, and file_fp_idx) are there.
> 
> I restarted postgresql with log_statement_stats = on. In
> the log file
> I can see that it calls a select statement for each file
> selected and each select statement takes more than 4 secs.
> 
> Here is a sample of the entry in the log file:
> 
> STATEMENT:  SELECT Filename.Name AS Filename, t1.JobId AS
> JobId, File.FileIndex AS FileIndex FROM ( SELECT
> File.FilenameId AS FilenameId, MAX(Job.JobId) AS JobId FROM
> File INNER JOIN Job ON (Job.JobId=File.JobId) WHERE
> File.PathId=106399 AND Job.Jobid IN
> (400,374,346,319,294,267,243,195,172,149,124,101,78,55,32)
> AND File.FilenameId!=1 GROUP BY File.FilenameId) t1, File 
> INNER JOIN Filename on
> (Filename.FilenameId=File.FilenameId) INNER JOIN Job ON
> (Job.JobId=File.JobId) WHERE File.PathId=106399 AND
> File.FilenameId=t1.FilenameId AND Job.Jobid=t1.JobId ORDER
> BY Filename
> LOG:  QUERY STATISTICS
> DETAIL:  ! system usage stats:
>        !       4.788294 elapsed 3.868412 user 0.911861
> system sec
>        !       [426.016235 user 101.814521 sys total]
>        !       32/0 [201272/90000] filesystem blocks in/out
>        !       0/0 [0/10818] page faults/reclaims, 0 [0]
> swaps
>        !       0 [0] signals rcvd, 0/0 [0/0] messages
> rcvd/sent
>        !       2/61 [6286/10403] voluntary/involuntary
> context switches
>        ! buffer usage stats:
>        !       Shared blocks:     287756 read,          0
> written, buffer hit rate = 1.05%
>        !       Local  blocks:          0 read,          0
> written, buffer hit rate = 0.00%
>        !       Direct blocks:          0 read,          0
> written
> 
> 
> 
> 
> Hemant Shah
> E-mail: hjrrs AT yahoo DOT com
> 
> 
> 
>       
> 
> -------------------------------------------------------------------------
> Sponsored by: SourceForge.net Community Choice Awards: VOTE
> NOW!
> Studies have shown that voting for your favorite open
> source project,
> along with a healthy diet, reduces your potential for
> chronic lameness
> and boredom. Vote Now at
> http://www.sourceforge.net/community/cca08
> _______________________________________________
> Bacula-users mailing list
> Bacula-users AT lists.sourceforge DOT net
> https://lists.sourceforge.net/lists/listinfo/bacula-users

I ran REINDEX command and reduced the time for each query to 1s, still
it takes 45 minutes to process selection.

I tried to restore same files using bconsole restore command but that did not 
work. I will start new discussion about that.


Hemant Shah
E-mail: hjrrs AT yahoo DOT com




      

-------------------------------------------------------------------------
Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
Studies have shown that voting for your favorite open source project,
along with a healthy diet, reduces your potential for chronic lameness
and boredom. Vote Now at http://www.sourceforge.net/community/cca08
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users