Stephen Thompson wrote:

Hello everyone,

We recently attempted a mysql to postgresql migration for our bacula 5.0.2 server. The data migration itself was successful, however we are disappointly either getting the same or significantly worse performance out of the postgres db.

I was hoping that someone might have some insight into this.

Here is some background:

software:
   centos 5.5 (64bit)
   bacula 5.0.2 (64bit)
   postgresql 8.1.21 (64bit)

Why 8.1 ..? 8.1 is more than 5 years old ...

   (previously... mysql-5.0.77 (64bit) MyISAM)

database:
   select count(*) from File --> 1,439,626,558
   du -sk /var/lib/pgsql/data --> 346,236,136 /var/lib/pgsql/data

hardware:
   1Tb EXT3 external fibre-RAID storage
   8Gb RAM
   2Gb SWAP
   2 dual-core [AMD Opteron(tm) Processor 2220] CPUs

Some of the postgres tuning that I've attempted thus far (comments are either default or alternatively settings I've tried without effect):

#shared_buffers = 1000    # min 16 or max_connections*2, 8KB each
shared_buffers = 262144                 # 2Gb

This is too large, set shared_buffers to something like 256-512 MB

#work_mem = 1024                        # min 64, size in KB
work_mem = 524288                       # 512Mb

#maintenance_work_mem = 16384           # min 1024, size in KB
maintenance_work_mem = 2097152          # 2Gb
#checkpoint_segments = 3  # in logfile segments, min 1, 16MB each
checkpoint_segments = 16
#checkpoint_warning = 30                # in seconds, 0 is off
checkpoint_warning = 16
#effective_cache_size = 1000            # typically 8KB each
#effective_cache_size = 262144          # 256Mb
effective_cache_size = 6291456          # 6Gb

6GB seems OK to me

#random_page_cost = 4     # units are one sequential page fetch cost
random_page_cost = 2

only reduce random_page_cost if you have fast disks (SAS, ...)

Now, as to what I'm 'seeing'. Building restore trees are on par with my previous mysql db, but what I'm seeing as significantly worse are:

                                                mysql   postgresql
Within Bat:
1) Version Browser (large sample job)            3min     9min          
2) Restore Tree (average sample job)            40sec    25sec
3) Restore Tree (large sample job)              10min   8.5min
2) Jobs Run (1000 Records)                      10sec     2min

Within psql/mysql:
1) select count(*) from File;                    1sec    30min

Catalog dump:
1) mysqldump/pgdump                              2hrs     3hrs

I get a win on building Restore trees, but everywhere else, it's painfully slow. It makes the bat utility virtually unusable as an interface. Why the win (albeit moderate) in some cases but terrible responses in others?

I admit that I am not familiar with postgres at all, but I tried to walk through some of the postgres tuning documents, including the notes in the bacula manual to arrive at the above settings. Also note that I've tried several variants on the configuration above (including the postgres defaults), don't have a detailed play by play of the results, but the time results above seemed typical regardless of what settings I tweaked.

Any help would be greatly appreciated!
Stephen

--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

jcigar.vcf
Description: Vcard

------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo

_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users