On Thu, 10 Mar 2005 09:58:02 +0000, vinita bansal <sagivini@xxxxxxxxxxx> wrote: > Hi, > > I have a 64 bit Opteron m/c with 32GB RAM and ~500GB HardDrive. The database > size is ~45GB. > I've got a similar box, but with only 16G RAM. What is the storage subsystem, fibre channel or SCSI? Also, what OS? > I am using the following values in postgresql.conf: > > shared_buffers = 100000 Seems high. I did some testing with my real data and found that anything of 15000 wasn't really gaining my anything. This is an 8.x config file (maintenance_work_mem vs. sort_mem), and pg 8+ can actually make do with smaller shared_buffers because of the ARC (soon to be 2Q) buffer management algorithm. Unless your working set PER QUERY is enormous I would suggest lowering this. > work_mem = 128000 That's fine, but you may need to bump it up if (as above) you have individual queries that sort/group huge rowsets. > maintenance_work_mem = 100000 Remember to pump this way up when building very large indexes. > max_fsm_pages = 200000 Should probably be bigger. Mine is 2000000 (2 million). > bgwriter_percent = 0 > bgwriter_maxpages = 0 > fsync = false *KLAXON SOUNDS* Unless you dislike having your data around after power/hardware anomalies you'd better turn that on! > wal_buffers = 1000 > checkpoint_segments = 2048 This will require 16 * ((2 * 1000) + 1) MB of drive space... 100 should be fine, really. (next setting related) > checkpoint_timeout = 3600 Conventional wisdom is not to set this higher than 1800. It also means that you can cut your checkpoint_segments in half (more or less). > effective_cache_size = 1840000 > random_page_cost = 2 This might be low, but it depends on your storage subsystem. Is it fibre channel? > geqo_threshold = 25 Wide queries, eh? > geqo_effort = 1 > stats_start_collector = false > stats_command_string = false > If you want to use pg_autovacuum then you will need to turn the stats stuff back on, including row statistics. Plus it's a big help in debugging. > Do these settings seem fine or I am making some mistake. These settings when > used with Postgres 7.4 gave me good results but they don't seem to work with > Postgres 8.0. Am I missing out on something?? > > Regards, > Vinita Bansal > > _________________________________________________________________ > Click, Upload, Print. http://www.kodakexpress.co.in?soe=4956 Deliver in > India. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Mike Rylander mrylander@xxxxxxxxx GPLS -- PINES Development Database Developer http://open-ils.org ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
