Re: Postgres performance comments from a MySQL user

On Fri, 13 Jun 2003, Ernest E Vogelsinger wrote:

> At 22:10 13.06.2003, scott.marlowe said:
> --------------------[snip]--------------------
> >Good idea. Don't forget to make sure it sets effective_cache_size while
> >we're at it. Just add up the kernel cache size and the free memory on a
> >machine to get an approximation.
> --------------------[snip]--------------------
>
> Scott - while we're at it, a quick question.
>
> On that machine, cat /proc/meminfo reports "Cached: 1740844 kB" (which is
> approx. 1.6GB cache), the machine has 1GB memory installed. So I did what
> you suggested before:
> # 1.6GB ~= 200000 pages at 8k
> effective_cache_size = 200000

Are you sure? You shouldn't be able to cache more than how much memory
you have, so I'd guess you either have more than 1gig of ram or the cached
memory is less thatn 1.6 gig.

> I have a query that runs for 17secs for the first time. Now, when executed
> a second time, it consumes 270 msec. From the third execution it's running
> with 29 msec.
>
> However this only holds for the open connection. When connecting anew, it
> starts with 270 msec and drops to 29 msec again.
>
> Any clues about this interesting behaviour?

So, once the machine's been up a while, the 17 second behaviour goes away
completely, but the .27 second -> .03 second behaviour remains?

Hmmmm. Sounds like the very first time you run it the data set moves from
disk to kernel cache. Then on each new connect postgresql pulls it from
kernel cache to postgresql's buffer, and it's faster still.

Not sure what all you can do about it, since postgresql drops the data
from its buffer on disconnect.