pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Need some help in psql Configuration 3+ messages / 3 participants [nested] [flat]
* Need some help in psql Configuration @ 2007-12-31 06:21 Suresh Gupta VG <suresh.g@zensar.com> 0 siblings, 2 replies; 3+ messages in thread From: Suresh Gupta VG @ 2007-12-31 06:21 UTC (permalink / raw) To: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin 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. 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. 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. 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 Regards, G. V. Suresh Gupta ------------------------------------------------------------------------ --------------------------------------------------------- Innovative Technology Solutions(ITS), Zensar Technologies Zensar Knowledge Park, Plot#5, MIDC IT Tower, Kharadi, Off Nagar Road, Pune - 411014 Landline : +91-20-66453471 | +91-9890898688 Email : suresh.g@zensar.com | website: www.zensar.com <http://www.zensar.com/; DISCLAIMER: This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails. Attachments:^ permalink raw reply [nested|flat] 3+ messages in threadimage001.jpg (image/jpeg, 3.1K) download
view image
* Re: Need some help in psql Configuration @ 2008-01-01 17:44 Usama Dar <munir.usama@gmail.com> parent: Suresh Gupta VG <suresh.g@zensar.com> 1 sibling, 0 replies; 3+ messages in thread From: Usama Dar @ 2008-01-01 17:44 UTC (permalink / raw) To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin On Dec 31, 2007 11:21 AM, Suresh Gupta VG <suresh.g@zensar.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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need some help in psql Configuration @ 2008-01-01 17:58 Julio Leyva <jcleyva@hotmail.com> parent: Suresh Gupta VG <suresh.g@zensar.com> 1 sibling, 0 replies; 3+ messages in thread From: Julio Leyva @ 2008-01-01 17:58 UTC (permalink / raw) To: Suresh Gupta VG <suresh.g@zensar.com>; Peter Koczan <pjkoczan@gmail.com>; pgsql-admin Subject: [ADMIN] Need some help in psql ConfigurationDate: Mon, 31 Dec 2007 11:51:38 +0530From: suresh.g@zensar.comTo: pjkoczan@gmail.com; pgsql-admin@postgresql.org 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. Which OS are u using? 32 bits 64 bits? just in case you are using SUSE this is the pagehttp://ftp.suse.com/pub/projects/postgresql/Also to update 7.4 to 8.x , you need to do a pg_dump of your D.B using 7.4 , then restore it using 8.x.... 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.The commented entries mean the default values that postgresql uses, you need to uncommented then just in case you need to change the values. Commented values mean the default values that postgresql uses, you need to uncommented them just in case you need to change the values.Also si your D.B mostly for Insert Updates? or just Selects...check out these siteshttp://edoceo.com/liber/db-postgresql-performancehttp://www.varlena.com/varlena/GeneralBits/Tidbits/perf.htmlhttp://www.argudo.org/postgresql/soft-tuning.phphttp://www.linuxjournal.com/article/4791Our 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. 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 Regards, G. V. Suresh Gupta --------------------------------------------------------------------------------------------------------------------------------- Innovative Technology Solutions(ITS), Zensar Technologies Zensar Knowledge Park, Plot#5, MIDC IT Tower, Kharadi, Off Nagar Road, Pune – 411014 Landline : +91-20-66453471 | +91-9890898688 Email : suresh.g@zensar.com | website: www.zensar.com DISCLAIMER:This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails. = Attachments:^ permalink raw reply [nested|flat] 3+ messages in threadimage001.jpg (image/jpeg, 3.1K) download
view image
end of thread, other threads:[~2008-01-01 17:58 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-12-31 06:21 Need some help in psql Configuration Suresh Gupta VG <suresh.g@zensar.com> 2008-01-01 17:44 ` Usama Dar <munir.usama@gmail.com> 2008-01-01 17:58 ` Julio Leyva <jcleyva@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox