Обсуждение: Please Help: PostgreSQL performance Optimization

От:
Jamal Ghaffour
Дата:

<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>
От:
Jamal Ghaffour
Дата:

Jamal Ghaffour a écrit : <blockquote cite=""
type="cite"></blockquote><font><fontcolor="#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,
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> </font></font>  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
mannerthe  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
requestis growing relatively to time and not the UPDATE time execution. <br />  I note that to stop the explosion of
theSelect time execution, i m using frequently the vaccum query on the cookies table.<br /> Set  the  autovacuum
parmaeterin the configuation file to on wasn't able to remplace the use of the vaccum command, and i don't know if this
behaivouris normal?<br /><br /> Thanks,<br /> Jamal<br /> 
От:
Andrew Lazarus
Дата:

Jamal Ghaffour wrote:

>>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)
>>)
[snip]
>>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 think the problem here is that the column order in the index doesn't
match the columns used in the WHERE clause criteria. Try adding an index
on (domain,principalid) or (domain,principalid,timestamp). If these are
your only queries, you can get the same effect by re-ordering the
columns in the table so that this is the column order used by the
primary key and its implicit index.

You should check up on EXPLAIN and EXPLAIN ANALYZE to help you debug
slow queries.

От:
Jaime Casanova
Дата:

On 1/12/06, Jamal Ghaffour <> 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 ;)

От:
Jamal Ghaffour
Дата:

Andrew Lazarus a écrit :

> Jamal Ghaffour wrote:
>
>>> 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)
>>> )
>>
> [snip]
>
>>> 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 think the problem here is that the column order in the index doesn't
> match the columns used in the WHERE clause criteria. Try adding an
> index on (domain,principalid) or (domain,principalid,timestamp). If
> these are your only queries, you can get the same effect by
> re-ordering the columns in the table so that this is the column order
> used by the primary key and its implicit index.
>
> You should check up on EXPLAIN and EXPLAIN ANALYZE to help you debug
> slow queries.

Hi,
I created an index into the cookies table
CREATE INDEX index_cookies_select ON cookies (domain, principalid,
timestamp);
and execute my UPDATE and select queries:

1 - The first select quey give the following results:

icap=# EXPLAIN ANALYZE 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');

            
QUERY
PLAN
          


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on cookies  (cost=4.02..8.04 rows=1 width=268) (actual
time=0.107..0.108 rows=1 loops=1)
   Recheck Cond: (((("domain")::text = 'ping.icap-elios.com'::text) AND
((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now()))
OR ((("domain")::text = '.icap-elios.com'::text) AND
((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())))
   Filter: (("timestamp" > now()) AND (NOT secure))
   ->  BitmapOr  (cost=4.02..4.02 rows=1 width=0) (actual
time=0.091..0.091 rows=0 loops=1)
         ->  Bitmap Index Scan on index_cookies_select  (cost=0.00..2.01
rows=1 width=0) (actual time=0.077..0.077 rows=1 loops=1)
               Index Cond: ((("domain")::text =
'ping.icap-elios.com'::text) AND ((principalid)::text =
'192.168.8.219'::text) AND ("timestamp" > now()))
         ->  Bitmap Index Scan on index_cookies_select  (cost=0.00..2.01
rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1)
               Index Cond: ((("domain")::text = '.icap-elios.com'::text)
AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now()))
 Total runtime: 0.155 ms
(9 rows)

2- After that, i launch my test code  that execute continuely the UPDATE
and select queries (in loop manner), after 1 minute of continuous
execution, i obtain the following result:
icap=# EXPLAIN ANALYZE 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');

            
QUERY
PLAN
          


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on cookies  (cost=4.02..8.04 rows=1 width=268) (actual
time=39.545..39.549 rows=1 loops=1)
   Recheck Cond: (((("domain")::text = 'ping.icap-elios.com'::text) AND
((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now()))
OR ((("domain")::text = '.icap-elios.com'::text) AND
((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())))
   Filter: (("timestamp" > now()) AND (NOT secure))
   ->  BitmapOr  (cost=4.02..4.02 rows=1 width=0) (actual
time=39.512..39.512 rows=0 loops=1)
         ->  Bitmap Index Scan on index_cookies_select  (cost=0.00..2.01
rows=1 width=0) (actual time=39.471..39.471 rows=2 loops=1)
               Index Cond: ((("domain")::text =
'ping.icap-elios.com'::text) AND ((principalid)::text =
'192.168.8.219'::text) AND ("timestamp" > now()))
         ->  Bitmap Index Scan on index_cookies_select  (cost=0.00..2.01
rows=1 width=0) (actual time=0.036..0.036 rows=0 loops=1)
               Index Cond: ((("domain")::text = '.icap-elios.com'::text)
AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now()))
 Total runtime: 39.616 ms
(9 rows)

I notice that the time execution increases significantly.  and i need
the vacuum query to obtain normal time execution:


3- After vacuum execution:
icap=# vacuum cookies;
VACUUM
icap=# EXPLAIN ANALYZE 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');

            
QUERY
PLAN
          


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on cookies  (cost=4.02..8.04 rows=1 width=268) (actual
time=0.111..0.112 rows=1 loops=1)
   Recheck Cond: (((("domain")::text = 'ping.icap-elios.com'::text) AND
((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now()))
OR ((("domain")::text = '.icap-elios.com'::text) AND
((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())))
   Filter: (("timestamp" > now()) AND (NOT secure))
   ->  BitmapOr  (cost=4.02..4.02 rows=1 width=0) (actual
time=0.095..0.095 rows=0 loops=1)
         ->  Bitmap Index Scan on index_cookies_select  (cost=0.00..2.01
rows=1 width=0) (actual time=0.081..0.081 rows=1 loops=1)
               Index Cond: ((("domain")::text =
'ping.icap-elios.com'::text) AND ((principalid)::text =
'192.168.8.219'::text) AND ("timestamp" > now()))
         ->  Bitmap Index Scan on index_cookies_select  (cost=0.00..2.01
rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1)
               Index Cond: ((("domain")::text = '.icap-elios.com'::text)
AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now()))
 Total runtime: 0.159 ms
(9 rows)



Thanks,
Jamal


От:
Frank Wiles
Дата:

On Thu, 12 Jan 2006 01:32:10 +0100
Jamal Ghaffour <> wrote:

> I'm using the default configuration file, and i m asking if i have to
> change some paramters to have a good performance.

  In general the answer is yes.  The default is a pretty good best guess
  at what sorts of values work for your "typical system", but if you run
  into performance problems the config file is where you should look
  first, provided you've done the simple things like adding good
  indexes, vacumm analyze, etc.

  You'll want to consult the following various documentation out there
  to help your properly tune your configuration:

  http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
  http://www.powerpostgresql.com/Docs
  http://www.powerpostgresql.com/PerfList
  http://www.revsys.com/writings/postgresql-performance.html

  Hopefully these will help you understand how to set your configuration
  values.

 ---------------------------------
   Frank Wiles <>
   http://www.wiles.org
 ---------------------------------