Usama Dar | 1 Jan 18:44 2008
Picon

Re: Need some help in psql Configuration



On Dec 31, 2007 11:21 AM, Suresh Gupta VG <suresh.g <at> 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
Julio Leyva | 1 Jan 18:58 2008
Picon

Re: Need some help in psql Configuration





Subject: [ADMIN] Need some help in psql Configuration
Date: Mon, 31 Dec 2007 11:51:38 +0530
From: suresh.g <at> zensar.com
To: pjkoczan <at> gmail.com; pgsql-admin <at> postgresql.org

.ExternalClass .EC_shape {;} .ExternalClass EC_p.MsoNormal, .ExternalClass EC_li.MsoNormal, .ExternalClass EC_div.MsoNormal {margin-bottom:.0001pt;font-size:12.0pt;font-family:'Times New Roman';} .ExternalClass a:link, .ExternalClass EC_span.MsoHyperlink {color:blue;text-decoration:underline;} .ExternalClass a:visited, .ExternalClass EC_span.MsoHyperlinkFollowed {color:purple;text-decoration:underline;} .ExternalClass p {margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:'Times New Roman';} .ExternalClass EC_span.EmailStyle18 {font-family:Arial;color:navy;} <at> page Section1 {size:8.5in 11.0in;} .ExternalClass EC_div.Section1 {page:Section1;} .ExternalClass ol {margin-bottom:0in;} .ExternalClass ul {margin-bottom:0in;}

 

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 page

http://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 sites

http://edoceo.com/liber/db-postgresql-performance
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.argudo.org/postgresql/soft-tuning.php
http://www.linuxjournal.com/article/4791


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 <at> 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.
ivan.hou | 2 Jan 05:52 2008
Picon

Re: What's the XID?


how can i prove that the xid had been increased by 1?

i do a test,

step 1. SELECT datname, age(datfrozenxid) FROM pg_database;
--> mydb age:33183

step 2. SELECT datname, age(datfrozenxid) FROM pg_database;
--> mydb age:33188

step 3. SELECT datname, age(datfrozenxid) FROM pg_database;
--> mydb age:33193

why always increased by 5?

Postgresql Ver:8.1.3
using pgAdminIII Query window

Andrew Sullivan wrote:
> 
> On Wed, Dec 26, 2007 at 05:48:14PM -0800, ivan.hou wrote:
>> 
>> XID is transcation ID?
> 
> Yes.
> 
>> so, "select * from mydb" is a transcation?
> 
> Yes.
> 
>> if i executed "select * from mydb" twice, the XID wil be increased by 2
> 
> Yep.  Whereas if you did
> 
> 	BEGIN;
> 	SELECT 1;
> 	SELECT 1;
> 	COMMIT;
> 	
> the xid would be increased by 1.
> 
> A
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                 http://www.postgresql.org/about/donate
> 
> 

--

-- 
View this message in context: http://www.nabble.com/What%27s-the-XID--tp14508639p14573789.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane | 2 Jan 06:32 2008
Picon

Re: What's the XID?

"ivan.hou" <ivan.hou <at> msa.hinet.net> writes:
> step 1. SELECT datname, age(datfrozenxid) FROM pg_database;
> --> mydb age:33183
> step 2. SELECT datname, age(datfrozenxid) FROM pg_database;
> --> mydb age:33188

> why always increased by 5?

If you do it directly in psql, it increases by 1.

> Postgresql Ver:8.1.3
> using pgAdminIII Query window

Perhaps you should ask the pgAdmin people what they're doing behind your
back ...

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Guillaume Lelarge | 2 Jan 09:37 2008

Re: What's the XID?

Tom Lane wrote:
> "ivan.hou" <ivan.hou <at> msa.hinet.net> writes:
>> step 1. SELECT datname, age(datfrozenxid) FROM pg_database;
>> --> mydb age:33183
>> step 2. SELECT datname, age(datfrozenxid) FROM pg_database;
>> --> mydb age:33188
> 
>> why always increased by 5?
> 
> If you do it directly in psql, it increases by 1.
> 
>> Postgresql Ver:8.1.3
>> using pgAdminIII Query window
> 
> Perhaps you should ask the pgAdmin people what they're doing behind your
> back ...

We first send the query to the database. For each returned column, we
send two queries. The first one gives us the type's name of the column
and the second one gives us the OID of the base type. So, each query
executed with pgAdmin's query tool should increase age(datfrozenxid) by :
  1 + (2 * number of columns in the SELECT)

Regards.

--

-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Bernhard D Rohrer | 2 Jan 10:46 2008
Picon

Re: pg recovery

Tom Lane wrote:
> Bernhard D Rohrer <graylion <at> sm-wg.net> writes:
>> I have a broken database and not much clue about postgres, sorry for the 
>> n00b questions :(
> 
> I think you've got a cross-version problem, as in the database is really
> PG 8.0 or earlier but you're trying to run 8.1 against it.  What is in
> the PG_VERSION file?  Have you done "pg_resetxlog -f", and if so do you
> have the original pg_control file to put back?
> 
> 			regards, tom lane

Hi Tom

I have not run "pg_resetxlog -f" on the pg_control file for which I 
posted the output of pg_resetxlog -n

as for the versions see for yourself:

root <at> collab:/home/adminlion# cat /var/lib/postgresql/8.1/main/PG_VERSION
8.1
root <at> collab:/home/adminlion# cat 
/olddrive/var/lib/postgresql/8.1/main/PG_VERSION
8.1

/olddrive is the former raid drive that holds the database I am trying 
to restore.

thanks :)

Bernhard

--

-- 
Graylion's Fetish & Fashion Store
Goth and Kinky Boots, Clothing and Jewellery
http://www.graylion.net

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

Glyn Astill | 2 Jan 12:33 2008
Picon

Shutting down warm standby server? "

Hi people,

I've setup a warm standby reading WAL files, however when I try to
shut it down I get the message "server does not shut down".

Can someone help please, I presume it has something to do with the
server being busy waiting for the next WAL file? What is the correct
way to shut down a server reading WALs?

The command line output is below:

postgres <at> way5c:/mnt/archive$ /usr/local/pgsql/bin/pg_ctl stop -D
/data/postgres/ -m smart
waiting for server to shut
down...............................................................
failed
pg_ctl: server does not shut down

If I do a ps -ax:

 7956 pts/0    S      0:00 /usr/local/pgsql/bin/postgres -D
/data/postgres/
 7957 ?        Ss     0:10 postgres: startup process
 7371 ?        S      0:00 /bin/bash /data/postgres/restore.sh
/mnt/archive/000000010000001A000000A8 pg_xlog/RECOVERYXLOG
 1160 ?        S      0:00 sleep 1
 1161 pts/0    R+     0:00 ps -ax

it looks like it's waikting for the next WAL file.

      __________________________________________________________
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Alvaro Herrera | 2 Jan 12:54 2008

Re: What's the XID?

Guillaume Lelarge wrote:

> We first send the query to the database. For each returned column, we
> send two queries. The first one gives us the type's name of the column
> and the second one gives us the OID of the base type. So, each query
> executed with pgAdmin's query tool should increase age(datfrozenxid) by :
>   1 + (2 * number of columns in the SELECT)

Ever thought of doing it all in a single transaction?

--

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Guillaume Lelarge | 2 Jan 13:57 2008

Re: What's the XID?

Alvaro Herrera wrote:
> Guillaume Lelarge wrote:
> 
>> We first send the query to the database. For each returned column, we
>> send two queries. The first one gives us the type's name of the column
>> and the second one gives us the OID of the base type. So, each query
>> executed with pgAdmin's query tool should increase age(datfrozenxid) by :
>>   1 + (2 * number of columns in the SELECT)
> 
> Ever thought of doing it all in a single transaction?
> 

Well, I'm not sure of the consequences of this. I'll get a look at this.
 What I first thought on my previous mail was to keep in memory a list
of all types available, but this was stupid : it needs refresh when
another user adds a new type and I'm sure there are others corner cases.
I much prefer your idea, thanks.

--

-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo <at> postgresql.org so that your
       message can get through to the mailing list cleanly

Mark Steben | 2 Jan 16:04 2008

Re: reconfiguring diskspace while upgrading to 8.2.5

Postgres admin team,

 

I posed this question last week.  Probably a bad week to ask questions as many people off

For the holidays so response was light.  Thanks to Pete Eisentraut for his feedback.

 

I’d like to pose the question again.  Our upgrade time-table is getting close.  Any and

All feedback would be appreciated.

 

Thank you,

Mark Steben

 

From: Mark Steben [mailto:msteben <at> autorevenue.com]
Sent: Friday, December 28, 2007 10:09 AM
To: 'pgsql-admin <at> postgresql.org'
Cc: 'rich <at> autorevenue.com'; 'David Parks'; 'Craig Brothers'
Subject: reconfiguring diskspace while upgrading to 8.2.5

 

Admin team,

 

We are upgrading from Postgres 7.4.5 to 8.2.5 in mid January.  We are also adding new disks

As we go.  I am investigating using the new tablespace facility to move our biggest and most accessed

Table to the new disk.  Here are the statistics.  We are running RAID 10

  Current database size -------------   63 GIG

  Heavy accessed table to move:

           Table -----------------------   7.4 GIG

           2 indexes -------------------   3 GIG apiece

           2 other indexes -------------   2.5 GIG apiece

 

Current database disk configuration

          TOTAL space -------------- 404 GIG

           TOTAL spindles -----------      3

           TOTAL mirrors ------------      3

 New additional disk configuration

           TOTAL  space ------------   290 GIG

           TOTAL spindles -----------       2

           TOTAL mirrors   -----------      2

 

The choices we see are:

    1. Adding the two new spindles to the other three making one huge logical partition

        And all 350+ tables and 400+ indexes continue to reside there

    2. Keeping the two new spindles separate and dedicating the heavy access table

         And its 4 indexes to it.

    3. Keeping the two new spindles separate and dedicating the heavy access table

          To it, but keeping the 4 indexes on the old partition.

 

I know that maintaining almost 700 GIG of total disk space being utilized by a 63 GIG

  Database looks like disk-space overkill but we do expect massive growth over the

  Next 2 – 3 years.

 

Any thoughts / comments would be appreciated.

 

Also are there tools out there that monitor disk I/O and disk speed?

 

Thanks for your time,      

 

Mark Steben

Senior Database Administrator
<at> utoRevenue™
A Dominion Enterprises Company
480 Pleasant Street
Suite B200
Lee, MA01238
413-243-4800 Home Office 
413-243-4809 Corporate Fax

msteben <at> autorevenue.com

Visit our new website at
www.autorevenue.com

 

IMPORTANT: The information contained in this e-mail message is confidential and is intended only for the named addressee(s). If the reader of this e-mail message is not the intended recipient (or the individual responsible for the delivery of this e-mail message to the intended recipient), please be advised that any re-use, dissemination, distribution or copying of this e-mail message is prohibited.  If you have received this e-mail message in error, please reply to the sender that you have received this e-mail message in error and then delete it.  Thank you.

Come Visit Us at NADA!  Booth #5735N
February 9th - February 12th
Moscone Center in San Francisco, CA

 


Gmane