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 . 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  and varies in an unforeseable way (that's
why hstore is used). EXPLAIN tells me that the indexes are used .
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?
2. Are there any hints on how to tell Postgres to read in all table
contents into memory?
CREATE TABLE osm_point (
SELECT osm_id, name FROM osm_point
WHERE tags @> 'tourism=>viewpoint'
GeomFromText('BOX(8.42 47.072, 9.088 47.431)'::box2d, 4326),
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
-> 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
pgsql-performance by date
|Next:||From: Jeff Janes||Date: 2012-02-26 02:13:49|
|Subject: Re: PG as in-memory db? How to warm up and re-populate
buffers? How to read in all tuples into memory?|
|Previous:||From: Jeff Janes||Date: 2012-02-25 19:17:57|
|Subject: Re: Very long deletion time on a 200 GB database|