Skip site navigation (1) Skip section navigation (2)

Re: Need some help in psql Configuration

From: Julio Leyva <jcleyva(at)hotmail(dot)com>
To: Suresh Gupta VG <suresh(dot)g(at)zensar(dot)com>, Peter Koczan <pjkoczan(at)gmail(dot)com>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: Need some help in psql Configuration
Date: 2008-01-01 17:58:25
Message-ID: BLU102-W14E1AA295AC42BE6BD3FA7A1510@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-admin
Subject: [ADMIN] Need some help in psql ConfigurationDate: Mon, 31 Dec 2007 11:51:38 +0530From: suresh(dot)g(at)zensar(dot)comTo: pjkoczan(at)gmail(dot)com; pgsql-admin(at)postgresql(dot)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(dot)g(at)zensar(dot)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. 


In response to

pgsql-admin by date

Next:From: ivan.houDate: 2008-01-02 04:52:02
Subject: Re: What's the XID?
Previous:From: Usama DarDate: 2008-01-01 17:44:25
Subject: Re: Need some help in psql Configuration

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group