Re: swapcache-style cache?

On 02/27/2012 03:24 PM, Jan Lentfer wrote:
> And, yes.. it does effect pgsql performance on read loads seriously.
>
> See BSD Mag 5/2011
> http://bsdmag.org/magazine/1691-embedded-bsd-freebsd-alix
>
> and
> http://www.shiningsilence.com/dbsdlog/2011/04/12/7586.html

Caching on the read-only pgbench is a well defined workload at this
point. If your database fits in RAM, once it's all in there additional
caching doesn't help. If the database is much larger than the cache,
the cache barely helps there too; you'll still be facing mostly cache
misses. The case in the middle is the one where an additional layer of
cache really helps. Read-heavy systems where the working set of the
database is larger than RAM, but not significantly larger than the extra
cache, will benefit the most here.

Your test results are in that zone, with 2GB RAM < 5.6GB database < 16GB
cache. Having a database slightly larger than physical RAM is where the
big win with SSD normally shows up at. Moving the whole database from a
regular drive to SSD might get as much as a 5X speedup, you're seeing a
3X to 4X one with the swap cache in the middle.

Having the OS manage all that, to keep the most relevant data on the
SSD, is a cool feature. Some systems won't benefit at all though, and
your test is showing near the best case possible for this feature. As
you should, of course.

Anyway, the question upthread was whether the database should manage
something like this on its own. I suggested it could be done perfectly
fine by the OS, without any database knowledge of what is going on.
Your results seem to validate that idea.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com