Please Help: PostgreSQL performance Optimization

From: Jamal Ghaffour <Jamal(dot)Ghaffour(at)elios-informatique(dot)fr>
To: pgsql-performance(at)postgresql(dot)org
Subject: Please Help: PostgreSQL performance Optimization
Date: 2006-01-12 00:32:10
Message-ID: 43C5A38A.8090208@elios-informatique.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<pre style="margin: 0em;">Hi,
<font face="Arial, Helvetica, sans-serif">
I'm working on a project, whose implementation deals with PostgreSQL. A brief description of our application is given below.</font>

I'm running version 8.0 on a dedicated server 1Gb of RAM.
my database isn't complex, it contains just 2 simple tables.
<tt>
CREATE TABLE cookies (
domain varchar(50) NOT NULL,
path varchar(50) NOT NULL,
name varchar(50) NOT NULL,
principalid varchar(50) NOT NULL,
host text NOT NULL,
value text NOT NULL,
secure bool NOT NULL,
timestamp timestamp with time zone NOT NULL DEFAULT
CURRENT_TIMESTAMP+TIME '04:00:00',
PRIMARY KEY (domain,path,name,principalid)
)

CREATE TABLE liberty (
principalid varchar(50) NOT NULL,
requestid varchar(50) NOT NULL,
spassertionurl text NOT NULL,
libertyversion varchar(50) NOT NULL,
relaystate varchar(50) NOT NULL,
PRIMARY KEY (principalid)
)

</tt>I'm developping an application that uses the libpqxx to execute
psql queries on the database and have to execute 500 requests at the same time.

<tt>
UPDATE cookies SET host='ping.icap-elios.com', value= '54E5B5491F27C0177083795F2E09162D', secure=FALSE,
timestamp=CURRENT_TIMESTAMP+INTERVAL '14400 SECOND' WHERE
domain='ping.icap-elios.com' AND path='/tfs' AND principalid='192.168.8.219' AND
name='jsessionid'</tt>

<tt>SELECT path, upper(name) AS name, value FROM cookies WHERE timestamp&lt;CURRENT_TIMESTAMP AND principalid='192.168.8.219' AND
secure=FALSE AND (domain='ping.icap-elios.com' OR domain='.icap-elios.com')</tt>

I have to notify that the performance of is extremely variable and irregular.
I can also see that the server process uses almost 100% of
a CPU.

I'm using the default configuration file, and i m asking if i have to change some paramters to have a good performance.

Any help would be greatly appreciated.

Thanks,
</pre>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 2.1 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message K C Lau 2006-01-12 00:36:19 Re: indexes on primary and foreign keys
Previous Message Burak Seydioglu 2006-01-11 23:52:40 Re: indexes on primary and foreign keys