Re: Need some help in psql Configuration

On Dec 31, 2007 11:21 AM, Suresh Gupta VG <suresh(dot)g(at)zensar(dot)com> wrote:

>
>
> Hi Team,
>
>
>
> I am using Postgresql 7.4 version. We are willing to update the version to
> the latest. Can you pls give some guidelines and provide the link to the
> software to download. Is it free downloadable or commercial, pls advice us.
>

You didn't specify , which OS are you using, anyway you can find
downloadable stuff here http://www.postgresql.org/ftp/, or from your OS's
package manage if you are using Linux. There are many commercial sources as
well, and you find info on them on the download page
http://www.postgresql.org/download/. Latest stable version is 8.2.5

>
> Secondly, we found there is very less performance with the current
> version. So, we started to do "VACUUM VERBOSE ANALYZE" daily with the help
> of Cron jobs scheduler. We don't find any effective performance increase in
> the Database. So, we concentrated on configuration file. In our
> configuration file "postgresql.conf" where found the following entries
> commented. And "*max_connections = 100*", we found that at one particular
> peak period/timings of the day, the transactions are failing due to database
> updation. Can you pls suggest us to get rid of this problem.
>

Transactions are failing due to database updation, i didn't quite get that,
can you elaborate a bit more? In your version if you tables are heavily
occasional reindex might also be needed in addition to vacuum.

> Our ideas on this :-
>
> 1) Upgrade to latest version 8.0
>
> 2) Increase the "Max_connections" value to 200.
>
> 3) Change the configuration file by un-commenting the following
> parameters.
>

Why do you need to increase max_connections? is the default value of 100 not
sufficient for you? increasing max_connections will increase your memory
requirements for postgres, so keep to as less as you can per your
requirements of course

>
>
> Please advice on this and correct me if I am wrong in the above approach.
>
>
>
> # QUERY TUNING
>
> #---------------------------------------------------------------------------
>
> # - Planner Method Enabling -
> #enable_hashagg = true
> #enable_hashjoin = true
> #enable_indexscan = true
> #enable_mergejoin = true
> #enable_nestloop = true
> #enable_seqscan = true
> #enable_sort = true
> #enable_tidscan = true
>
> # - Planner Cost Constants -
> #effective_cache_size = 1000 # typically 8KB each
> #random_page_cost = 4 # units are one sequential page fetch cost
> #cpu_tuple_cost = 0.01 # (same)
> #cpu_index_tuple_cost = 0.001 # (same)
> #cpu_operator_cost = 0.0025 # (same)
>
> # - Genetic Query Optimizer -
> #geqo = true
> #geqo_threshold = 11
> #geqo_effort = 1
> #geqo_generations = 0
> #geqo_pool_size = 0 # default based on tables in statement,
> # range 128-1024
> #geqo_selection_bias = 2.0 # range 1.5-2.0
>
> # - Other Planner Options -
> #default_statistics_target = 10 # range 1-1000
> #from_collapse_limit = 8
> #join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
>

Looks like you are trying to shoot in the dark here, what type problem are
you trying to solve? each of there parameters are used to address particular
query tunning situations, you can't benefit from blindly enabling /disabling
them all.

--
Usama Munir Dar http://www.linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar