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 > > > -- The real metric of thumb drives: "Will it Survive a Tumble in the Wash?". ------------------------------------------------------------------------------ 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
