On Fri, 29 Oct 2010 10:21:14 -0400 Vick Khera <vivek@xxxxxxxxx> wrote: > On Thu, Oct 28, 2010 at 1:06 PM, Ivan Sergio Borgonovo > <mail@xxxxxxxxxxxxxxx> wrote: > > What I'm planning to do is: > > max_connections = 5 > > shared_buffers = 240M > > work_mem = 90MB > > maintenance_work_mem = 1GB > > max_fsm_pages = 437616 > > max_fsm_relations = 1200 > > checkpoint_segments = 70 > > default_statistics_target = 30 > > #log_min_duration_statement = 1000 > default_statistics_target = 100 is the new "default" for newer > postgres, and with good reason... try that. > > if you boost your checkpoint_segments, also twiddle the > checkpoint_timeout (increase it) and checkpoint_completion_target > (something like 0.8 would be good, depending on how fast your disks > are) values to try to smooth out your I/O (ie, keep it from > bursting at checkpoint timeout). Is 5 connections really enough > for you? No. 5 is too few. OK... this is what I end up with: max_connections = 100 shared_buffers = 240M work_mem = 90MB maintenance_work_mem = 1GB max_fsm_pages = 437616 max_fsm_relations = 1200 default_statistics_target = 100 checkpoint_segments = 70 checkpoint_timeout = 10min checkpoint_completion_target = 0.6 #(not very fast drives in raid5) #log_min_duration_statement = 1000 random_page_cost = 3.0 I tested this on a RAID10 SATA, 8Gb RAM and 2x4cores Xeons - updating 227985 records over roughly 1.4M took 197744.374 ms - recreating the gin index took 313962.162 ms - commit took 7699.595 ms - vacuum analyse 188261.481 ms The total update took around 13min. I've just heard that a similar update on a slower box (RAID1 SAS, 4Gb, 2x2Cores Xeon) running MS SQL took over 30min. Considering MUCH less pk/fk, constraint and actions where defined on the MS SQL DB, things now look much better for postgres. Furthermore postgresql full text search kicks ass to the MS SQL box even on the slowest box of all (RAID5 SATA, 4Gb, 2x1core HT Xeon, over 6 years old). I'll take note of performance even on the slower box as soon as I'll have large updates, still I'm looking how to make it faster. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
