Re: postgres 8 settings

On Thu, 10 Mar 2005 09:58:02 +0000, vinita bansal <sagivini(at)hotmail(dot)com> 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(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org