From: =?utf-8?q?C=C3=A9dric_Villemain?= <cedric@2ndquadrant.fr>
Reply-To: cedric@2ndquadrant.fr
Organization: 2ndQuadrant
To: pgsql-performance@postgresql.org
Subject: Re: PG as in-memory db? How to warm up and re-populate buffers? How
	to read in all tuples into memory?
Date: Sun, 26 Feb 2012 20:35:44 +0100
User-Agent: KMail/1.13.7 (Linux/3.1.0-1-amd64; KDE/4.6.5; x86_64; ; )
Cc: Stefan Keller <sfkeller@gmail.com>,
 Stephen Frost <sfrost@snowman.net>
References: 
 <CAFcOn28A3dkmCEGsu-7Chi0gH1qAPYcfbSp8op9gswFawS6ECA@mail.gmail.com>
In-Reply-To: 
 <CAFcOn28A3dkmCEGsu-7Chi0gH1qAPYcfbSp8op9gswFawS6ECA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201202262035.44687.cedric@2ndquadrant.fr>

Le dimanche 26 f=C3=A9vrier 2012 01:16:08, Stefan Keller a =C3=A9crit :
> Hi,
>=20
> 2011/10/24 Stephen Frost <sfrost@snowman.net> wrote
>=20
> > 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..
>=20
> I'd like to come back on the issue of aka of in-memory key-value database.
>=20
> 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].
>=20
> 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.
>=20
> 1. How can I warm up or re-populate shared buffers of Postgres?

There was a patch proposed for postgresql which purpose was to=20
snapshot/Restore postgresql buffers, but it is still not sure how far that=
=20
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=20
cache, and do snapshot/restore if you want fine grain control of what part =
of=20
the object you want to warm.
https://github.com/klando/pgfincore


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

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