Just as a point of interest
Postgresql 8.3.* allows you to set up a default temporary tablespace
for temporary files. I noticed that bacula creates a lot of
temporary tables during a restore in order to call up the filesystem
image.
I have 16GB of memory in my machine and my /tmp directory is on a
tmpfs file system (solaris default) so I placed my default temporary
tablespace in that directory (have to remember to create the
directory upon postgresql startup and turn off the cron job that
tries to wipe /tmp clean every once and a while).
The time to recreate an image of the filesystem for restores dropped
dramatically. It takes all of five minutes to recreate a filesystem
with 9 million files and directories. My machine has two Sparc IIIi
1.1Ghz CPUs so computational it's pretty slow. I still can't use the
restore command to restore the entire filesystem (2GB memory limit
for a process which is compiled as a 64bit binary and I haven't
figured this one out yet) but that's not a database issue.
If you place your bacula tablespaces and wal on a battery backed up,
large memory cache, raid array which guarantees (hopefully) all
writes get flushed to disk even if your server crashes you can
consider turning of the fsync parameter (with an emphasis on consider
and you have to have a consistent backup of your catalog so if things
go south you can at least go back to a known stable image).
Things really fly then.
Postgresql has turned into a really nice database management system.
John Kloss.
On Jul 1, 2008, at 6:33 PM, Hemant Shah wrote:
>
>
> --- 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
-------------------------------------------------------------------------
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
|
