Re: shared_buffers performance

"Gaetano Mendola" <mendola@[EMAIL PROTECTED]
> writes:

> The following graph re****ts the results:
>
> http://img84.imageshack.us/my.php?image=totalid7.png

That's a *fascinating* graph.

It seems there are basically three domains. 

The small domain where the database fits in shared buffers -- though
actually
this domain seems to hold until the accounts table is about 1G so maybe
it's
more that the *indexes* fit in memory. Here larger shared buffers do
clearly
win.

The transition domain where performance drops dramatically as the database
starts to not fit in shared buffers but does still fit in filesystem
cache.
Here every megabyte stolen from the filesystem cache makes a *huge*
difference. At a scale factor of 120 or so you're talking about a factor
of 4
between each of the shared buffer sizes.

The large domain where the database doesn't fit in filesystem cache. Here
it
doesn't make a large difference but the more buffers duplicated between
postgres and the filesystem cache the lower the overall cache
effectiveness.

If we used something like either mmap or directio to avoid the double
buffering we would be able to squeeze these into a single curve, as well
as
push the dropoff slightly to the right. In theory. 

In practice it would depend on the OS's ability to handle page faults
efficiently in the mmap case, and our ability to do read-ahead and cache
management in the directio case. And it would be a huge increase in
complexity
for Postgres and a push into a direction which isn't our "core
competency". We
might find that while in theory it should perform better our code just
can't
keep up with Linux's and it doesn't.

I'm curious about the total database size as a for each of the scaling
factors
as well as the total of the index sizes. And how much memory Linux says is
being used for filesystem buffers.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS sup****t!

-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance