From: Jamal Ghaffour <Jamal.Ghaffour@elios-informatique.fr>
To: Jamal Ghaffour <Jamal.Ghaffour@elios-informatique.fr>
To: pgsql-performance@postgresql.org
Subject: Re: Please Help: PostgreSQL performance Optimization
Date: Thu, 12 Jan 2006 15:53:27 +0100
Message-ID: <43C66D67.1000502@elios-informatique.fr> (raw)
In-Reply-To: <43C66742.9060104@elios-informatique.fr>
References: <43C66742.9060104@elios-informatique.fr>
This is a multi-part message in MIME format.
--------------060805020108060506070404
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!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">
Jamal Ghaffour a écrit :
<blockquote cite="mid43C66742.9060104@elios-informatique.fr" type="cite">
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
<font><font color="#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<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>
</font></font>
</blockquote>
Hi,<br>
<br>
There are some results that can give you <span class="def-example">concrete </span>
idea about my problem: <br>
when i 'm launching my test that executes in loop manner the SELECT
and UPDATE queries described above, i'm obtaining this results:<br>
<br>
UPDATE Time execution :0s: 5225 us<br>
SELECT Time execution :0s: 6908 us<br>
<br>
5 minutes Later: <br>
<br>
UPDATE Time execution :0s: 6125 us<br>
SELECT Time execution :0s: 10928 us<br>
<br>
5 minutes Later: <br>
<br>
UPDATE Time execution :0s: 5825 us<br>
SELECT Time execution :0s: 14978 us<br>
<br>
As you can see , the time execution of the SELECT request is growing
relatively to time and not the UPDATE time execution. <br>
I note that to stop the explosion of the Select time execution, i m
using frequently the vaccum query on the cookies table.<br>
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?<br>
<br>
Thanks,<br>
Jamal<br>
</body>
</html>
--------------060805020108060506070404
Content-Type: text/x-vcard; charset=utf-8;
name="Jamal.Ghaffour.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="Jamal.Ghaffour.vcf"
begin:vcard
fn:Jamal Ghaffour
n:Ghaffour;Jamal
org:ELIOS Informatique
adr;quoted-printable:;;1, sq de ch=C3=AAne Germain,;CESSON SEVIGNE;;35510;FRANCE
email;internet:Jamal.Ghaffour@elios-informatique.fr
tel;work:(+33) 2.99.63.85.30
tel;fax:(+33) 2.99.63.85.93
tel;home:(+33) 2 99 36 73 96
tel;cell:(+33) 6.21.85.15.91
url:http://www.elios-informatique.fr
version:2.1
end:vcard
--------------060805020108060506070404--
Attachments:Jamal.Ghaffour.vcf (text/x-vcard, 376B) download
begin:vcard
fn:Jamal Ghaffour
n:Ghaffour;Jamal
org:ELIOS Informatique
adr;quoted-printable:;;1, sq de ch=C3=AAne Germain,;CESSON SEVIGNE;;35510;FRANCE
email;internet:Jamal.Ghaffour@elios-informatique.fr
tel;work:(+33) 2.99.63.85.30
tel;fax:(+33) 2.99.63.85.93
tel;home:(+33) 2 99 36 73 96
tel;cell:(+33) 6.21.85.15.91
url:http://www.elios-informatique.fr
version:2.1
end:vcard
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Jamal.Ghaffour@elios-informatique.fr
Subject: Re: Please Help: PostgreSQL performance Optimization
In-Reply-To: <43C66D67.1000502@elios-informatique.fr>
* 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