X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id BCEC29DCA2E for ; Wed, 11 Jan 2006 20:32:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27709-06 for ; Wed, 11 Jan 2006 20:32:33 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 9F2E49DCA26 for ; Wed, 11 Jan 2006 20:32:28 -0400 (AST) Received: from smtp6.clb.oleane.net (smtp6.clb.oleane.net [213.56.31.26]) by svr4.postgresql.org (Postfix) with ESMTP id 62E635AF09D for ; Thu, 12 Jan 2006 00:32:31 +0000 (GMT) Received: from smtp6.clb.oleane.net (localhost [127.0.0.1]) by smtp6.clb.oleane.net (antivirus) with ESMTP id k0C0WSTY010743; Thu, 12 Jan 2006 01:32:28 +0100 Received: from [127.0.0.1] (pat35-1-82-231-148-216.fbx.proxad.net [82.231.148.216]) (authenticated) by smtp6.clb.oleane.net with ESMTP id k0C0WB90010453; Thu, 12 Jan 2006 01:32:28 +0100 Message-ID: <43C5A38A.8090208@elios-informatique.fr> Date: Thu, 12 Jan 2006 01:32:10 +0100 From: Jamal Ghaffour User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: fr, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Please Help: PostgreSQL performance Optimization Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.947 required=5 tests=[HTML_10_20=0.945, HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.001] X-Spam-Score: 0.947 X-Spam-Level: X-Archive-Number: 200601/139 X-Sequence-Number: 16617

Hi,

I'm working on a project, whose implementation deals with PostgreSQL. A brief description of our application is given  below.

I'm running version 8.0 on a dedicated  server 1Gb of RAM. 
my database isn't complex, it contains just 2 simple tables.

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)
)

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.


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'

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

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,