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?
2. Are there any hints on how to tell Postgres to read in all table
contents into memory?
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)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
