[Bacula-users] tuning for bacula restore, database postgresql

Hi,

I tried to tune bacula restore as described below.
I use now postgresql that is faster than mysql.
With these optimisations, it's better, duration divided by 2,  but do 
you still know how to make shorter the duration of restore ?

Our Configuration :
  Host: x86_64-linux-gnu -- debian 6.0.4 - virtual machine LXC
  Bacula version: Bacula 5.2.6 (21 February 2012)
  Database : PostgreSQL 9.1
  Bat : bat 5.2.6 (21 February 2012)

#cat /proc/sys/kernel/shmmax
12884901888
#cat /proc/sys/kernel/shmall
4194304

Without optimisation in postgresql, Building directory tree for JobId(s) 
... +++++++++++++++++++++++++++++++++++++++++++ takes about 9 minutes.

We changed values in /etc/postgresql/9.1/main/postgresql.conf :

shared_buffers = 10GB
temp_buffers = 12GB
work_mem = 12GB
maintenance_work_mem = 512MB
synchronous_commit = off                # synchronization level; on, 
off, or local
checkpoint_segments = 1024              # in logfile segments, min 1, 
16MB each
checkpoint_completion_target = 0.9      # checkpoint target duration, 
0.0 - 1.0
effective_cache_size = 20GB
track_counts = on

log_destination = 'syslog'

# For Postgresql logs :
silent_mode = on                        #Run server silently.
log_min_duration_statement = 100 # -1 is disabled, 0 logs all statements
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
track_counts = on
lc_messages = 'en_US.UTF-8'

And we chose these mounting options in fstab :
/dev/mapper/monbazillac--R2P0L249-bacula--psql--1               
/var/lib/lxc/10-bacula-64/rootfs/var/lib/postgresql     xfs 
noatime,inode64,nobarrier 0 0

With these optimisations, Building directory tree for JobId(s) ... 
+++++++++++++++++++++++++++++++++++++++++++ takes about 7 minutes.

We saw in postgresql logs that a FETCH command took a long time :
bacula LOG:  duration: 276322.521 ms  statement: FETCH 100 FROM _bac_cursor

So I re-compiled bacula with different values for FETCH instead of FETCH 
100.
Now here the durations with these values :

5 000        --> 5 min 18 s
25 000      --> 5 min 06 s
100 000    --> 4 min 53 s
500 000    --> 4 min 43 s
1 000 000 --> 4 min 25 s
1 500 000 --> 5 min 35 s
to have : Building directory tree for JobId(s) ... 
+++++++++++++++++++++++++++++++++++++++++++

I choose the value : FETCH 1000000 FROM _bac_cursor

The restore jobs is this one (with console or bat we have nearly the 
same values) :

*restore

Automatically selected FileSet: Set_titeuf
+-------+-------+------------+-----------------+---------------------+-------------------------------------+
| jobid | level | jobfiles   | jobbytes        | starttime           | 
volumename                          |
+-------+-------+------------+-----------------+---------------------+-------------------------------------+
|   127 | F     | 12,822,260 | 810,390,999,893 | 2012-03-09 17:51:20 | 
Vol_Complete_0056 |
|   127 | F     | 12,822,260 | 810,390,999,893 | 2012-03-09 17:51:20 | 
Vol_Complete_0050 |
|   127 | F     | 12,822,260 | 810,390,999,893 | 2012-03-09 17:51:20 | 
Vol_Complete_0057 |
|   127 | F     | 12,822,260 | 810,390,999,893 | 2012-03-09 17:51:20 | 
Vol_Complete_0058 |
|   127 | F     | 12,822,260 | 810,390,999,893 | 2012-03-09 17:51:20 | 
Vol_Complete_0054 |
|   127 | F     | 12,822,260 | 810,390,999,893 | 2012-03-09 17:51:20 | 
Vol_Complete_0053 |
|   127 | F     | 12,822,260 | 810,390,999,893 | 2012-03-09 17:51:20 | 
Vol_Complete_0051 |
|   127 | F     | 12,822,260 | 810,390,999,893 | 2012-03-09 17:51:20 | 
Vol_Complete_0052 |
|   221 | D     |    546,578 |  72,324,132,233 | 2012-03-23 19:30:03 | 
Vol_Diff_0137     |
|   229 | I     |         47 |       9,706,799 | 2012-03-24 19:30:02 | 
Vol_I_0155        |
|   236 | I     |         45 |       9,782,553 | 2012-03-25 19:30:02 | 
Vol_I_0161        |
+-------+-------+------------+-----------------+---------------------+-------------------------------------+
You have selected the following JobIds: 127,221,229,236

Building directory tree for JobId(s) 127,221,229,236 ... 
+++++++++++++++++++++++++++++++++++++++++++

11,178,353 files inserted into the tree.

You are now entering file selection mode where you add (mark) and
remove (unmark) files to be restored. No files are initially added, unless
you used the "all" keyword on the command line.
Enter "done" to leave this mode.

cwd is: /
$

Thanks for your answers
Jacky


------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here 
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users