GiST, caching, and consistency

От: Matthew Wakeling
Тема: GiST, caching, and consistency
Дата: ,
Msg-id: alpine.DEB.2.00.0908041659410.18938@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответы: Re: GiST, caching, and consistency  (Robert Haas)
Список: pgsql-performance

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

GiST, caching, and consistency  (Matthew Wakeling, )
 Re: GiST, caching, and consistency  (Robert Haas, )
  Re: GiST, caching, and consistency  (Greg Stark, )
   Re: GiST, caching, and consistency  (Matthew Wakeling, )
    Re: GiST, caching, and consistency  (Robert Haas, )
     Re: GiST, caching, and consistency  (Matthew Wakeling, )
    Re: GiST, caching, and consistency  (Tom Lane, )

I'm seeing an interesting phenomenon while I'm trying to
performance-optimise a GiST index. Basically, running a performance test
appears to be the same thing as running a random number generator. For
example, here I'm running the same statement eight times in quick
succession:

> modmine_overlap_test=# \timing
> Timing is on.
> modmine_overlap_test=# select count(*) from (select * FROM
> locatedsequencefeatureoverlappingfeatures limit 1000000) AS a;
>   count
> ---------
>  1000000
> (1 row)
>
> Time: 138583.140 ms
>
> Time: 153769.152 ms
>
> Time: 127518.574 ms
>
> Time: 49629.036 ms
>
> Time: 70926.034 ms
>
> Time: 7625.034 ms
>
> Time: 7382.609 ms
>
> Time: 7985.379 ms

"locatedsequencefeatureoverlappingfeatures" is a view, which performs a
join with a GiST index. The machine was otherwise idle, and has plenty of
RAM free.

Shouldn't the data be entirely in cache the second time I run the
statement? However, it's worse than that, because while the long-running
statements were running, I saw significant CPU usage in top - more than
eight seconds worth. Again, one one test there was no io-wait, but on a
subsequent test there was lots of io-wait.

How can this be so inconsistent?

Matthew

--
 "Interwoven alignment preambles are not allowed."
 If you have been so devious as to get this message, you will understand
 it, and you deserve no sympathy.  -- Knuth, in the TeXbook


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

От: Ibrahim Harrani
Дата:
Сообщение: postgresql and syslog
От: Robert Haas
Дата:
Сообщение: Re: GiST, caching, and consistency