[CLUE-Tech] Postgres tuning]

[Lynn Danielson]

Scott Marlowe wrote:
> 
> Here's my changes:
> 
> fsync = false
> 
> enable_seqscan = true
> enable_indexscan = true
> enable_tidscan = true
> # CPU sorts are 99.9% of the time slower than an index scan
> enable_sort = false
> enable_nestloop = true
> # These are slow as molasses for me, so I turned them off.)
> enable_mergejoin = false
> enable_hashjoin = true
> 
> # And these entries just ensure we consider the CPU to be VERY expensive and
> # indexes cheap.
> 
> cpu_tuple_cost = 0.001
> cpu_index_tuple_cost = 0.0000001
> cpu_operator_cost = 0.5
> 
> # Having two wal files seems to help a bit, as does
> # increasing the buffers.  The open_sync method seems fastest,
> # but only by a bit over the others, say 5-10% or so.
> 
> wal_buffers = 8
> wal_files = 2
> wal_sync_method = open_sync
> 
> # Increasing the commit delay and the number of siblings commited at
> # once helps speed things up too, but keep in mind, a lot of performance
> # tuning can lead to data corruption if the server crashes.  Mine
> # never does, but hey, who knows what could happen.
> 
> commit_delay = 5000 # range 0-100000
> commit_siblings = 500 # range 1-1000
> 
> Hope that helps
> 
> > Grant Johnson wrote:
> > >
> > > Hey, all, especially Scott, I did some tweaks.  With little load,
> > > queries are slightly slower (less than hals a second different) but I am
> > > hoping for better performance with many users.  Tell me what you think
> > > of the tweaks....
> > >
> > > #
> > > #
> > > Optimizer Parameters
> > > #
> > > #enable_seqscan = true
> > > #enable_indexscan = true
> > > #enable_tidscan = true
> > > #enable_sort = true
> > > #enable_nestloop = true
> > > #enable_mergejoin = true
> > > #enable_hashjoin = true
> > >
> > > #ksqo = false
> > > #geqo = true
> > >
> > > #effective_cache_size = 1000  # default in 8k pages
> > > #random_page_cost = 4
> > > #cpu_tuple_cost = 0.01
> > > #cpu_index_tuple_cost = 0.001
> > > #cpu_operator_cost = 0.0025
> > > cpu_tuple_cost = 0.1                  #tweaked
> > > cpu_index_tuple_cost = 0.00001        #tweaked
> > > cpu_operator_cost = 0.025             #tweaked
> > > #geqo_selection_bias = 2.0 # range 1.5-2.0
> > >
> > > #
> > > #
> > > Write-ahead log (WAL)
> > > #
> > > #wal_buffers = 8 # min 4
> > > wal_files = 32 # range 0-64                  #tweaked
> > > commit_delay = 500 # range 0-100000          #tweaked