>>>>> On Tue, 3 Aug 2010 13:17:25 +0100, Rory Campbell-Lange said:
>
> Thanks very much for your response, Martin.
>
> On 03/08/10, Martin Simmons (martin AT lispworks DOT com) wrote:
> > >>>>> On Tue, 3 Aug 2010 10:15:18 +0100, Rory Campbell-Lange said:
>
> > > I have 3.4GB free in /var where Postgresql is located. At the end of a
> > > large backup job (7,643,966 files taking up 7.265TB of space) Postgres
> > > bails out copying a batch file into the File table due to a mysterious
> > > "no space left on device" error.
> > >
> > > Questions:
> > > 1. How should I size my postgresql partition?
> >
> > I expect 7.6 million records to need at least 800MB when inserted and the
> > batch tables will need a similar amount during the backup. It is difficult
> > to
> > predict what the hash-join temporary file will need because it depends on
> > the
> > internals of PostgreSQL.
> >
> > Firstly though I suggest running df frequently during the backup to verify
> > that the problem really is /var filling up.
>
> My server logs over the backup period still show over 2GB free in /var
> (where postgresql is held) and 8GB in /tmp. Thanks however for the rule
> of thumb sizes for the records.
It isn't clear to me if your logs cover the minutes and seconds up to the
point of failure. After the failure, the temporary table/file will have been
deleted so the free space will appear to be fine again.
>
> > > 2. Can I stop this needless after-backup insertion? I tried setting
> > > Spool Attributes to NO but it did not work
> >
> > You need to rebuild Bacula with the --disable-batch-insert option, but it
> > might run quite slowly. Setting synchronous_commit = off in postgresql.conf
> > might help to make it faster.
>
> Thanks about the note about the --disable-batch-insert compile time
> option. Changing the synchronous_commit flag to off will speed up
> inserts into the database which will is great, but it won't affect the
> size of the batch file. Please clarify why you are suggesting this.
Sorry, I wasn't clear. I was only suggesting that you change
synchronous_commit if you use --disable-batch-insert.
> > > 3. Why is Bacula using a batch file at all? Why not simply do a straight
> > > insert?
> >
> > Because 7,643,966 inserts would be much slower.
>
> Really? I've logged Bacula's performance on the server and the inserts
> run at around 0.35 ms and updates at around 0.5 ms.
> 8 million inserts at 0.35ms will take about 46 minutes.
The batch insert code made a noticable difference for me. It takes 2 to 3
minutes to process 950,000 file records, but I don't have the figures for the
non-batch insert now. "Process" here is the whole operation, not just
inserting into the File table (it has to query and update Filename and Path
too).
> But it would be
> quite possible for Bacula to do this asynchronously while it does the
> job of writing data from disk to tape, which in this case takes several
> days.
That's true. Moreover, your average file size is quite large, so the per file
time to insert the records may not be so important anyway.
> Perhaps this is something the developers could consider?
You can do that already by compiling with --disable-batch-insert and setting
Spool Attributes to NO.
__Martin
------------------------------------------------------------------------------
The Palm PDK Hot Apps Program offers developers who use the
Plug-In Development Kit to bring their C/C++ apps to Palm for a share
of $1 Million in cash or HP Products. Visit us here for more details:
http://p.sf.net/sfu/dev2dev-palm
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
