Which filesystem are you on too? I've found that ext3 is significantly faster than ext4 and xfs.
On 06/04/2010 04:01 PM, Stephen Thompson wrote: > > Thanks, yes it is Linux. I will look at those limits settings. > And yes, I've built indexes and analyze (nothing to vacuum yet since > it's a fresh import). > > Stephen > > On 06/04/2010 12:16 PM, Alan Brown wrote: > >> On Fri, 4 Jun 2010, Stephen Thompson wrote: >> >> >>> >>> Correction: >>> I didn't notice the 8k per unit settings at first with postgres 8.1. >>> Should read: >>> effective_cache_size = 786432 # 6Gb >>> >> Assuming this is linux, you need to tweak /etc/sysctl/limits.conf a >> little: >> >> postgres soft memlock unlimited >> postgres hard memlock unlimited >> @postgres hard memlock unlimited >> @postgres soft memlock unlimited >> bacula soft memlock unlimited >> bacula hard memlock unlimited >> @bacula soft memlock unlimited >> @bacula hard memlock unlimited >> >> postgres soft rss unlimited >> postgres hard rss unlimited >> >> >> Don't forget to build the indexes and run analyse/vacuum commands. >> >> So far I'm finding Postgres is far more forgiving than MySQL and has far >> fewer parts to tune... >> >> >> >> >>> >>> On 06/04/2010 10:58 AM, 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) >>>> (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 >>>> >>> >>> >>> >> > > ------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
