Re: [Bacula-users] Restore very slow, looks like database problem.


--- 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, 4:57 PM
> --- 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

I figured out I should mark instead of markdir.

I restored same files using bacula restore command and it took less than 
fifteen minutes to go through file selection and restoring the files.

Why is bat so inefficient?


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