pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jaime Casanova <systemguards@gmail.com>
To: Jamal Ghaffour <Jamal.Ghaffour@elios-informatique.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Please Help: PostgreSQL performance Optimization
Date: Thu, 12 Jan 2006 15:16:00 -0500
Message-ID: <c2d9e70e0601121216j54e3988g8b58a0ab4b06a526@mail.gmail.com> (raw)
In-Reply-To: <43C66D67.1000502@elios-informatique.fr>
References: <43C66742.9060104@elios-informatique.fr>
	<43C66D67.1000502@elios-informatique.fr>

On 1/12/06, Jamal Ghaffour <Jamal.Ghaffour@elios-informatique.fr> wrote:
> Jamal Ghaffour a écrit :
> 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,
> Hi,
>
> There are some results that can give you concrete  idea about my problem:
> when i 'm launching my test that executes in loop manner the  SELECT and
> UPDATE queries described above, i'm obtaining this results:
>
> UPDATE Time execution :0s: 5225 us
> SELECT Time execution  :0s: 6908 us
>
> 5 minutes Later:
>
> UPDATE Time execution :0s: 6125 us
> SELECT Time execution  :0s: 10928 us
>
> 5 minutes Later:
>
> UPDATE Time execution :0s: 5825 us
> SELECT Time execution  :0s: 14978 us
>
> As you can see , the time execution of the SELECT request is growing
> relatively to time and not the UPDATE time execution.
>  I note that to stop the explosion of the Select time execution, i m using
> frequently the vaccum query on the cookies table.
> Set  the  autovacuum parmaeter in the configuation file to on wasn't able to
> remplace the use of the vaccum command, and i don't know if this behaivour
> is normal?
>
> Thanks,
> Jamal
>
>

please execute

EXPLAIN ANALYZE <your query>
and show the results, is the only way to know what's happening

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)



view thread (6+ messages)  latest in thread

Message-ID: <c2d9e70e0601121216j54e3988g8b58a0ab4b06a526@mail.gmail.com>
Permalink:  ../c2d9e70e0601121216j54e3988g8b58a0ab4b06a526@mail.gmail.com/
Also on:    postgresql.org/message-id/c2d9e70e0601121216j54e3988g8b58a0ab4b06a526@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: systemguards@gmail.com, Jamal.Ghaffour@elios-informatique.fr
  Subject: Re: Please Help: PostgreSQL performance Optimization
  In-Reply-To: <c2d9e70e0601121216j54e3988g8b58a0ab4b06a526@mail.gmail.com>
  References: <43C66742.9060104@elios-informatique.fr> <43C66D67.1000502@elios-informatique.fr> <c2d9e70e0601121216j54e3988g8b58a0ab4b06a526@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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