Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

Le dimanche 26 février 2012 01:16:08, Stefan Keller a écrit :
> Hi,
>
> 2011/10/24 Stephen Frost <sfrost(at)snowman(dot)net> wrote
>
> > Now, we've also been discussing ways to have PG automatically
> > re-populate shared buffers and possibly OS cache based on what was in
> > memory at the time of the last shut-down, but I'm not sure that would
> > help your case either since you're rebuilding everything every night and
> > that's what's trashing your buffers (because everything ends up getting
> > moved around). You might actually want to consider if that's doing more
> > harm than good for you. If you weren't doing that, then the cache
> > wouldn't be getting destroyed every night..
>
> I'd like to come back on the issue of aka of in-memory key-value database.
>
> To remember, it contains table definition and queries as indicated in
> the appendix [0]. There exist 4 other tables of similar structure.
> There are indexes on each column. The tables contain around 10 million
> tuples. The database is "read-only"; it's completely updated every
> day. I don't expect more than 5 concurrent users at any time. A
> typical query looks like [1] and varies in an unforeseable way (that's
> why hstore is used). EXPLAIN tells me that the indexes are used [2].
>
> The problem is that the initial queries are too slow - and there is no
> second chance. I do have to trash the buffer every night. There is
> enough main memory to hold all table contents.
>
> 1. How can I warm up or re-populate shared buffers of Postgres?

There was a patch proposed for postgresql which purpose was to
snapshot/Restore postgresql buffers, but it is still not sure how far that
really help to have that part loaded.

> 2. Are there any hints on how to tell Postgres to read in all table
> contents into memory?

I wrote pgfincore for the OS part: you can use it to preload table/index in OS
cache, and do snapshot/restore if you want fine grain control of what part of
the object you want to warm.
https://github.com/klando/pgfincore

>
> Yours, Stefan
>
>
> APPENDIX
>
> [0]
> CREATE TABLE osm_point (
> osm_id integer,
> name text,
> tags hstore
> geom geometry(Point,4326)
> );
>
>
> [1]
> SELECT osm_id, name FROM osm_point
> WHERE tags @> 'tourism=>viewpoint'
> AND ST_Contains(
> GeomFromText('BOX(8.42 47.072, 9.088 47.431)'::box2d, 4326),
> geom)
>
> [2]
> EXPLAIN ANALYZE returns:
> Bitmap Heap Scan on osm_point (cost=402.15..40465.85 rows=430
> width=218) (actual time=121.888..137.
> Recheck Cond: (tags @> '"tourism"=>"viewpoint"'::hstore)
> Filter: (('01030...'::geometry && geom) AND
> _st_contains('01030'::geometry, geom))
> -> Bitmap Index Scan on osm_point_tags_idx (cost=0.00..402.04
> rows=11557 width=0) (actual time=1 6710 loops=1)
> Index Cond: (tags @> '"tourism"=>"viewpoint"'::hstore)
> Total runtime: 137.881 ms
> (6 rows)

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation