Le dimanche 26 février 2012 01:16:08, Stefan Keller a écrit : > Hi, > > 2011/10/24 Stephen Frost <sfrost@xxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
