--- 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
|
