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)
(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
#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
#random_page_cost = 4 # units are one sequential page fetch cost
random_page_cost = 2
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
--
Stephen Thompson Berkeley Seismological Laboratory
[email protected] 215 McCone Hall # 4760
404.538.7077 (phone) University of California, Berkeley
510.643.5811 (fax) Berkeley, CA 94720-4760
------------------------------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-users
