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