Please Help: PostgreSQL performance Optimization

От: Jamal Ghaffour
Тема: Please Help: PostgreSQL performance Optimization
Дата: ,
Msg-id: 43C5A38A.8090208@elios-informatique.fr
(см: обсуждение, исходный текст)
Ответы: Re: Please Help: PostgreSQL performance Optimization  (Frank Wiles)
Список: pgsql-performance

Скрыть дерево обсуждения

Please Help: PostgreSQL performance Optimization  (Jamal Ghaffour, )
 Re: Please Help: PostgreSQL performance Optimization  (Jamal Ghaffour, )
  Re: Please Help: PostgreSQL performance Optimization  (Andrew Lazarus, )
   Re: Please Help: PostgreSQL performance Optimization  (Jamal Ghaffour, )
  Re: Please Help: PostgreSQL performance Optimization  (Jaime Casanova, )
 Re: Please Help: PostgreSQL performance Optimization  (Frank Wiles, )

<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,
principalidvarchar(50) NOT NULL,   host text NOT NULL,   value text NOT NULL,   secure bool NOT NULL,   timestamp
timestampwith 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>

В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: indexes on primary and foreign keys
От: Simon Riggs
Дата:
Сообщение: Re: Extremely irregular query performance