Extremely irregular query performance

От: Jean-Philippe Côté
Тема: Extremely irregular query performance
Дата: ,
Msg-id: 20060111222900.EA357384020@crt0.crt.umontreal.ca
(см: обсуждение, исходный текст)
Ответы: Re: Extremely irregular query performance  (Tom Lane)
Re: Extremely irregular query performance  (Scott Marlowe)
Список: pgsql-performance

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

Extremely irregular query performance  (Jean-Philippe Côté<>, )
 Re: Extremely irregular query performance  (Tom Lane, )
  Re: Extremely irregular query performance  (Jean-Philippe Côté<>, )
   Re: Extremely irregular query performance  (Mark Lewis, )
   Re: Extremely irregular query performance  (Tom Lane, )
    Re: Extremely irregular query performance  (Simon Riggs, )
     Re: Extremely irregular query performance  (Kenneth Marshall, )
     Re: Extremely irregular query performance  (Bruce Momjian, )
      Re: Extremely irregular query performance  (Tom Lane, )
       Re: Extremely irregular query performance  (Bruce Momjian, )
 Re: Extremely irregular query performance  (Scott Marlowe, )
 Re: Extremely irregular query performance  (Jean-Philippe Cote, )
  Re: Extremely irregular query performance  (Bruce Momjian, )
  Re: Extremely irregular query performance  (Kenneth Marshall, )

Hi,

I'm running version 8.1 on a dedicated Sun v20 server (2 AMD x64's)
with 4Gb of RAM. I have recently noticed that the performance of
some more complex queries is extremely variable and irregular.
For example, I currently have a query that returns a small number
of rows (5) by joining a dozen of tables. Below are the running times
obtained by repeatedly lauching this query in psql (nothing else
was running on the server at that time):

Time: 424.848 ms
Time: 1615.143 ms
Time: 15036.475 ms
Time: 83471.683 ms
Time: 163.224 ms
Time: 2454.939 ms
Time: 188.093 ms
Time: 158.071 ms
Time: 192.431 ms
Time: 195.076 ms
Time: 635.739 ms
Time: 164549.902 ms

As you can see, the performance is most of the time pretty good (less
than 1 second), but every fourth of fifth time I launch the query
the server seems to go into orbit. For the longer running times,
I can see from 'top' that the server process uses almost 100% of
a CPU.

This is rather worrisome, as I cannot be confident of the overall performance
of my application with so much variance in query response times.

I suspect a configuration problem related to the cache mechanism
(shared_buffers? effective_cache_size?), but to be honest I do not know
where to start to diagnose it. I also noticed that the query plan
can vary when the same query is launched two times in a row (with
no other changes to the DB in between). Is there a random aspect to
the query optimizer that could explain some of the observed variance
in performance ?

Any help would be greatly appreciated.

Thanks in advance,

J-P





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

От: Robert Creager
Дата:
Сообщение: Re: Index isn't used during a join.
От: Tom Lane
Дата:
Сообщение: Re: Stable function being evaluated more than once in a single query