Re: Please Help: PostgreSQL performance Optimization

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