pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
32+ messages / 12 participants
[nested] [flat]
* PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 00:16 Stefan Keller <sfkeller@gmail.com>
0 siblings, 4 replies; 32+ messages in thread
From: Stefan Keller @ 2012-02-26 00:16 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, Stephen Frost <sfrost@snowman.net>
Hi,
2011/10/24 Stephen Frost <sfrost@snowman.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?
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)
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 02:13 Jeff Janes <jeff.janes@gmail.com>
parent: Stefan Keller <sfkeller@gmail.com>
3 siblings, 1 reply; 32+ messages in thread
From: Jeff Janes @ 2012-02-26 02:13 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: pgsql-performance@postgresql.org, Stephen Frost <sfrost@snowman.net>
On Sat, Feb 25, 2012 at 4:16 PM, Stefan Keller <sfkeller@gmail.com> wrote:
>
> 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.
Just that table, or the entire database?
>
> 1. How can I warm up or re-populate shared buffers of Postgres?
Instead, warm the OS cache. Then data will get transferred into the
postgres shared_buffers pool from the OS cache very quickly.
tar -c $PGDATA/base/ |wc -c
If you need to warm just one table, because the entire base directory
won't fit in OS cache, then you need to do a bit more work to find out
which files to use.
You might feel clever and try this instead:
tar -c /dev/null $PGDATA/base/ > /dev/null
But my tar program is too clever by half. It detects that it is
writing to /dev/null, and just does not actually read the data.
> 2. Are there any hints on how to tell Postgres to read in all table
> contents into memory?
I don't think so, at least not in core. I've wondered if it would
make sense to suppress ring-buffer strategy when there are buffers on
the free-list. That way a sequential scan would populate
shared_buffers after a restart. But it wouldn't help you get the
indexes into cache.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 10:56 Stefan Keller <sfkeller@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 32+ messages in thread
From: Stefan Keller @ 2012-02-26 10:56 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; Wales Wang <wormwang@yahoo.com>; +Cc: pgsql-performance@postgresql.org, Stephen Frost <sfrost@snowman.net>
Hi Jeff and Wales,
2012/2/26 Jeff Janes <jeff.janes@gmail.com> wrote:
>> 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.
>
> Just that table, or the entire database?
The entire database consisting of only about 5 tables which are
similar but with different geometry types plus a relations table (as
OpenStreetMap calls it).
>> 1. How can I warm up or re-populate shared buffers of Postgres?
>
> Instead, warm the OS cache. Then data will get transferred into the
> postgres shared_buffers pool from the OS cache very quickly.
>
> tar -c $PGDATA/base/ |wc -c
Ok. So with "OS cache" you mean the files which to me are THE database itself?
A cache to me is a second storage with "controlled redudancy" because
of performance reasons.
>> 2. Are there any hints on how to tell Postgres to read in all table
>> contents into memory?
>
> I don't think so, at least not in core. I've wondered if it would
> make sense to suppress ring-buffer strategy when there are buffers on
> the free-list. That way a sequential scan would populate
> shared_buffers after a restart. But it wouldn't help you get the
> indexes into cache.
So, are there any developments going on with PostgreSQL as Stephen
suggested in the former thread?
2012/2/26 Wales Wang <wormwang@yahoo.com>:
> You can try PostgreSQL 9.x master/slave replication, then try run slave
> on persistent RAM Fileystem (tmpfs)
> So, access your all data from slave PostgreSQL that run on tmpfs..
Nice idea.
I do have a single upscaled server and up to now I hesitated to
allocate say 48 Gigabytes (out of 72) to such a RAM Fileystem (tmpfs).
Still, would'nt it be more flexible when I could dynamically instruct
PostgreSQL to behave like an in-memory database?
Yours, Stefan
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 11:46 Thomas Kellerer <spam_eater@gmx.net>
parent: Stefan Keller <sfkeller@gmail.com>
3 siblings, 0 replies; 32+ messages in thread
From: Thomas Kellerer @ 2012-02-26 11:46 UTC (permalink / raw)
To: pgsql-performance
Stefan Keller wrote on 26.02.2012 01:16:
> 2. Are there any hints on how to tell Postgres to read in all table
> contents into memory?
What about creating tablespace on a RAM Fileystem (tmpfs), then create a second schema in your database where all tables are located in the that "temp" tablespace.
Then upon startup (or using triggers) you can copy all data from the persistent tables to the memory tables.
It would probably make sense to change the value of random_page_cost for that tablespace to 1
I'm not sure though how PostgreSQL handles a system-restart with tables on a tablespace that might not be there.
Thomas
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 13:55 Stephen Frost <sfrost@snowman.net>
parent: Stefan Keller <sfkeller@gmail.com>
1 sibling, 0 replies; 32+ messages in thread
From: Stephen Frost @ 2012-02-26 13:55 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; Wales Wang <wormwang@yahoo.com>; pgsql-performance
* Stefan Keller (sfkeller@gmail.com) wrote:
> So, are there any developments going on with PostgreSQL as Stephen
> suggested in the former thread?
While the idea has been getting kicked around, I don't know of anyone
actively working on developing code to implement it.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 15:20 Andy Colson <andy@squeakycode.net>
parent: Stefan Keller <sfkeller@gmail.com>
3 siblings, 1 reply; 32+ messages in thread
From: Andy Colson @ 2012-02-26 15:20 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: pgsql-performance@postgresql.org, Stephen Frost <sfrost@snowman.net>
On 02/25/2012 06:16 PM, Stefan Keller wrote:
>
> 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
>
How about after you load the data, vacuum freeze it, then do something like:
SELECT count(*) FROM osm_point WHERE tags @> 'tourism=>junk'
-Andy
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 19:11 Stefan Keller <sfkeller@gmail.com>
parent: Andy Colson <andy@squeakycode.net>
0 siblings, 1 reply; 32+ messages in thread
From: Stefan Keller @ 2012-02-26 19:11 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-performance@postgresql.org, Stephen Frost <sfrost@snowman.net>
2012/2/26 Andy Colson <andy@squeakycode.net> wrote:
> On 02/25/2012 06:16 PM, Stefan Keller wrote:
>> 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
>
> How about after you load the data, vacuum freeze it, then do something like:
>
> SELECT count(*) FROM osm_point WHERE tags @> 'tourism=>junk'
>
> -Andy
That good idea is what I proposed elsewhere on one of the PG lists and
got told that this does'nt help.
I can accept this approach that users should'nt directly interfere
with the optimizer. But I think it's still worth to discuss a
configuration option (per table) or so which tells PG that this table
contents should fit into memory so that it tries to load a table into
memory and keeps it there. This option probably only makes sense in
combination with unlogged tables.
Yours, Stefan
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 19:20 Andy Colson <andy@squeakycode.net>
parent: Stefan Keller <sfkeller@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Andy Colson @ 2012-02-26 19:20 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: pgsql-performance@postgresql.org, Stephen Frost <sfrost@snowman.net>
On 02/26/2012 01:11 PM, Stefan Keller wrote:
> 2012/2/26 Andy Colson<andy@squeakycode.net> wrote:
>> On 02/25/2012 06:16 PM, Stefan Keller wrote:
>>> 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
>>
>> How about after you load the data, vacuum freeze it, then do something like:
>>
>> SELECT count(*) FROM osm_point WHERE tags @> 'tourism=>junk'
>>
>> -Andy
>
> That good idea is what I proposed elsewhere on one of the PG lists and
> got told that this does'nt help.
>
> I can accept this approach that users should'nt directly interfere
> with the optimizer. But I think it's still worth to discuss a
> configuration option (per table) or so which tells PG that this table
> contents should fit into memory so that it tries to load a table into
> memory and keeps it there. This option probably only makes sense in
> combination with unlogged tables.
>
> Yours, Stefan
>
I don't buy that. Did you test it? Who/where did you hear this? And... how long does it take after you replace the entire table until things are good and cached? One or two queries?
After a complete reload of the data, do you vacuum freeze it?
After a complete reload of the data, how long until its fast?
-Andy
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 19:35 Cédric Villemain <cedric@2ndquadrant.fr>
parent: Stefan Keller <sfkeller@gmail.com>
3 siblings, 1 reply; 32+ messages in thread
From: Cédric Villemain @ 2012-02-26 19:35 UTC (permalink / raw)
To: pgsql-performance; +Cc: Stefan Keller <sfkeller@gmail.com>; Stephen Frost <sfrost@snowman.net>
Le dimanche 26 février 2012 01:16:08, Stefan Keller a écrit :
> Hi,
>
> 2011/10/24 Stephen Frost <sfrost@snowman.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
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 19:36 Cédric Villemain <cedric@2ndquadrant.com>
0 siblings, 0 replies; 32+ messages in thread
From: Cédric Villemain @ 2012-02-26 19:36 UTC (permalink / raw)
To: pgsql-performance; +Cc: Stefan Keller <sfkeller@gmail.com>; Stephen Frost <sfrost@snowman.net>
Le dimanche 26 février 2012 01:16:08, Stefan Keller a écrit :
> Hi,
>
> 2011/10/24 Stephen Frost <sfrost@snowman.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
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 20:37 Stefan Keller <sfkeller@gmail.com>
parent: Andy Colson <andy@squeakycode.net>
0 siblings, 1 reply; 32+ messages in thread
From: Stefan Keller @ 2012-02-26 20:37 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-performance
2012/2/26 Andy Colson <andy@squeakycode.net> wrote:
>>> How about after you load the data, vacuum freeze it, then do something
>>> like:
>>>
>>> SELECT count(*) FROM osm_point WHERE tags @> 'tourism=>junk'
>>>
>>> -Andy
>>
>>
>> That good idea is what I proposed elsewhere on one of the PG lists and
>> got told that this does'nt help.
>>
...
> I don't buy that. Did you test it? Who/where did you hear this? And...
> how long does it take after you replace the entire table until things are
> good and cached? One or two queries?
>
> After a complete reload of the data, do you vacuum freeze it?
Yes.
> After a complete reload of the data, how long until its fast?
Just after the second query. You can try it yourself online here:
http://bit.ly/A8duyB
-Stefan
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 20:56 Stefan Keller <sfkeller@gmail.com>
parent: Cédric Villemain <cedric@2ndquadrant.fr>
0 siblings, 0 replies; 32+ messages in thread
From: Stefan Keller @ 2012-02-26 20:56 UTC (permalink / raw)
To: cedric@2ndquadrant.fr; +Cc: pgsql-performance
Hi,
2012/2/26 Cédric Villemain <cedric@2ndquadrant.fr> wrote:
>> 1. How can I warm up or re-populate shared buffers of Postgres?
>
> There was a patch proposed for postgresql which purpose was to
Which patch are you referring to?
> snapshot/Restore postgresql buffers, but it is still not sure how far that
> really help to have that part loaded.
What's not sure and why?
>> 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
Yes, now I remember. I have a look at that.
I'd still like to see something where PG really preloads tuples and
treats them "always in-memory" (given they fit into RAM).
Since I have a "read-only" database there's no WAL and locking needed.
But as soon as we allow writes I realize that the in-memory feature
needs to be coupled with other enhancements like replication (which
somehow would avoid WAL).
Yours, Stefan
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-26 22:34 Jeff Janes <jeff.janes@gmail.com>
parent: Stefan Keller <sfkeller@gmail.com>
1 sibling, 0 replies; 32+ messages in thread
From: Jeff Janes @ 2012-02-26 22:34 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: Wales Wang <wormwang@yahoo.com>; pgsql-performance@postgresql.org, Stephen Frost <sfrost@snowman.net>
On Sun, Feb 26, 2012 at 2:56 AM, Stefan Keller <sfkeller@gmail.com> wrote:
> Hi Jeff and Wales,
>
> 2012/2/26 Jeff Janes <jeff.janes@gmail.com> wrote:
>>> 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.
>>
>> Just that table, or the entire database?
>
> The entire database consisting of only about 5 tables which are
> similar but with different geometry types plus a relations table (as
> OpenStreetMap calls it).
And all of those combined fit in RAM? With how much to spare?
>
>>> 1. How can I warm up or re-populate shared buffers of Postgres?
>>
>> Instead, warm the OS cache. Then data will get transferred into the
>> postgres shared_buffers pool from the OS cache very quickly.
>>
>> tar -c $PGDATA/base/ |wc -c
>
> Ok. So with "OS cache" you mean the files which to me are THE database itself?
Most operating systems will use any otherwise unused RAM to cache
"recently" accessed file-system data. That is the OS cache. The
purpose of the tar is to populate the OS cache with the "database
itself". That way, when postgres wants something that isn't already
in shared_buffers, it doesn't require a disk read to get it, just a
request to the OS.
But this trick is most useful after the OS has been restarted so the
OS cache is empty. If the OS has been up for a long time, then why
isn't it already populated with the data you need? Maybe the data
doesn't fit, maybe some other process has trashed the cache (in which
case, why would it not continue to trash the cache on an ongoing
basis?)
Since you just recently created the tables and indexes, they must have
passed through the OS cache on the way to disk. So why aren't they
still there? Is shared_buffers so large that little RAM is left over
for the OS? Did you reboot the OS? Are there other processes running
that drive the database-specific files out of the OS cache?
> A cache to me is a second storage with "controlled redudancy" because
> of performance reasons.
Yeah. But there are multiple caches, with different parties in
control and different opinions of what is redundant.
>>> 2. Are there any hints on how to tell Postgres to read in all table
>>> contents into memory?
>>
>> I don't think so, at least not in core. I've wondered if it would
>> make sense to suppress ring-buffer strategy when there are buffers on
>> the free-list. That way a sequential scan would populate
>> shared_buffers after a restart. But it wouldn't help you get the
>> indexes into cache.
>
> So, are there any developments going on with PostgreSQL as Stephen
> suggested in the former thread?
I don't see any active development for the upcoming release, and most
of what has been suggested wouldn't help you because they are about
re-populating the cache with previously hot data, while you are
destroying your previously hot data and wanting to specify the
future-hot data.
By the way, your explain plan would be more useful if it included
buffers. "Explain (analyze, buffers) select..."
I don't know that it is ever better to run analyze without buffers,
other than for backwards compatibility. I'm trying to get in the
habit of just automatically doing it.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 15:22 Jeff Janes <jeff.janes@gmail.com>
parent: Stefan Keller <sfkeller@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Jeff Janes @ 2012-02-28 15:22 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: Andy Colson <andy@squeakycode.net>; pgsql-performance
On Sun, Feb 26, 2012 at 12:37 PM, Stefan Keller <sfkeller@gmail.com> wrote:
> 2012/2/26 Andy Colson <andy@squeakycode.net> wrote:
>>>> How about after you load the data, vacuum freeze it, then do something
>>>> like:
>>>>
>>>> SELECT count(*) FROM osm_point WHERE tags @> 'tourism=>junk'
>>>>
>>>> -Andy
>>>
>>>
>>> That good idea is what I proposed elsewhere on one of the PG lists and
>>> got told that this does'nt help.
>>>
> ...
>> I don't buy that. Did you test it? Who/where did you hear this? And...
>> how long does it take after you replace the entire table until things are
>> good and cached? One or two queries?
>>
>> After a complete reload of the data, do you vacuum freeze it?
>
> Yes.
>
>> After a complete reload of the data, how long until its fast?
>
> Just after the second query. You can try it yourself online here:
> http://bit.ly/A8duyB
The second instance of the exact same query is fast. How long until
all similar but not identical queries are fast?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 20:48 Stefan Keller <sfkeller@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 32+ messages in thread
From: Stefan Keller @ 2012-02-28 20:48 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Andy Colson <andy@squeakycode.net>; pgsql-performance
Hi
2012/2/28 Jeff Janes <jeff.janes@gmail.com> wrote:
> It is hard to figure out what problem you are facing. Is your data
> not getting loaded into cache, or is it not staying there?
One could say both:
I'd like to warm up the cache befor hand in order to speed up the
first query right away.
And it's not staying there because when there comes a second slightly
different query it's slow again and I would expect that the tuples of
that table stay.
>> Just after the second query. You can try it yourself online here:
>> http://bit.ly/A8duyB
I should have said after the first query.
> The second instance of the exact same query is fast.
Right.
> How long until all similar but not identical queries are fast?
Good question. Can't tell for sure because it not so easy to make it repeatable.
I tested the following:
SELECT count(*) FROM osm_point WHERE tags @> 'amenity=>restaurant'
SELECT count(*) FROM osm_point WHERE tags @> 'cuisine=>pizza'
SELECT count(*) FROM osm_point WHERE tags @> 'tourism=>hotel'
SELECT count(*) FROM osm_point WHERE tags @> 'historic=>castle'
SELECT count(*) FROM osm_point WHERE tags @> 'natural=>peak'
AND to_number(ele, '9999') >= 4000
I would say that after the 4th query it remains fast (meaning less
than a second).
-Stefan
P.S. And yes, the database is aka 'read-only' and truncated and
re-populated from scratch every night. fsync is off so I don't care
about ACID. After the indexes on name, hstore and geometry are
generated I do a VACUUM FULL FREEZE. The current installation is a
virtual machine with 4GB memory and the filesystem is "read/write".
The future machine will be a pizza box with 72GB memory.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 22:41 Claudio Freire <klaussfreire@gmail.com>
parent: Stefan Keller <sfkeller@gmail.com>
1 sibling, 2 replies; 32+ messages in thread
From: Claudio Freire @ 2012-02-28 22:41 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: pgsql-performance
On Tue, Feb 28, 2012 at 5:48 PM, Stefan Keller <sfkeller@gmail.com> wrote:
> P.S. And yes, the database is aka 'read-only' and truncated and
> re-populated from scratch every night. fsync is off so I don't care
> about ACID. After the indexes on name, hstore and geometry are
> generated I do a VACUUM FULL FREEZE. The current installation is a
> virtual machine with 4GB memory and the filesystem is "read/write".
> The future machine will be a pizza box with 72GB memory.
I don't get this. Something's wrong.
In the OP, you say "There is enough main memory to hold all table
contents.". I'm assuming, there you refer to your current system, with
4GB memory.
So your data is less than 4GB, but then you'll be throwing a 72GB
server? It's either tremendous overkill, or your data simply isn't
less than 4GB.
It's quite possible the vacuum full is thrashing your disk cache due
to maintainance_work_mem. You can overcome this issue with the tar
trick, which is more easily performed as:
tar cf /dev/null $PG_DATA/base
tar will read all the table's contents and populate the OS cache. From
there to shared_buffers it should be very very quick. If it is true
that your data fits in 4GB, then that should fix it all. Beware,
whatever you allocate to shared buffers will be redundantly loaded
into RAM, first in shared buffers, then in the OS cache. So your data
has to fit in 4GB - shared buffers.
I don't think query-based tricks will load everything into RAM,
because you will get sequential scans and not index scans - the
indices will remain uncached. If you forced an index scan, it would
have to read the whole index in random order (random I/O), and that
would be horribly slow. The best way is to tar the whole database into
/dev/null and be done with it.
Another option is to issue a simple vacuum after the vacuum full.
Simple vacuum will just scan the tables and indices, I'm hoping doing
nothing since the vacuum full will have cleaned everything already,
but loading everything both in the OS cache and into shared_buffers.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 23:46 Stefan Keller <sfkeller@gmail.com>
parent: Claudio Freire <klaussfreire@gmail.com>
1 sibling, 1 reply; 32+ messages in thread
From: Stefan Keller @ 2012-02-28 23:46 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: pgsql-performance
2012/2/28 Claudio Freire <klaussfreire@gmail.com>:
> On Tue, Feb 28, 2012 at 5:48 PM, Stefan Keller <sfkeller@gmail.com> wrote:
>> P.S. And yes, the database is aka 'read-only' and truncated and
>> re-populated from scratch every night. fsync is off so I don't care
>> about ACID. After the indexes on name, hstore and geometry are
>> generated I do a VACUUM FULL FREEZE. The current installation is a
>> virtual machine with 4GB memory and the filesystem is "read/write".
>> The future machine will be a pizza box with 72GB memory.
>
> I don't get this. Something's wrong.
>
> In the OP, you say "There is enough main memory to hold all table
> contents.". I'm assuming, there you refer to your current system, with
> 4GB memory.
Sorry for the confusion: I'm doing these tests on this machine with
one table (osm_point) and one country. This table has a size of 2.6GB
and 10 million tuples. The other machine has to deal with at least 5
tables in total and will be hold more than one country plus routing
etc..
-Stefan
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-29 15:16 Jeff Janes <jeff.janes@gmail.com>
parent: Claudio Freire <klaussfreire@gmail.com>
1 sibling, 2 replies; 32+ messages in thread
From: Jeff Janes @ 2012-02-29 15:16 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Stefan Keller <sfkeller@gmail.com>; pgsql-performance
On Tue, Feb 28, 2012 at 2:41 PM, Claudio Freire <klaussfreire@gmail.com> wrote:
> On Tue, Feb 28, 2012 at 5:48 PM, Stefan Keller <sfkeller@gmail.com> wrote:
>> P.S. And yes, the database is aka 'read-only' and truncated and
>> re-populated from scratch every night. fsync is off so I don't care
>> about ACID. After the indexes on name, hstore and geometry are
>> generated I do a VACUUM FULL FREEZE. The current installation is a
>> virtual machine with 4GB memory and the filesystem is "read/write".
>> The future machine will be a pizza box with 72GB memory.
>
> I don't get this. Something's wrong.
>
> In the OP, you say "There is enough main memory to hold all table
> contents.". I'm assuming, there you refer to your current system, with
> 4GB memory.
>
> So your data is less than 4GB, but then you'll be throwing a 72GB
> server? It's either tremendous overkill, or your data simply isn't
> less than 4GB.
>
> It's quite possible the vacuum full is thrashing your disk cache due
> to maintainance_work_mem. You can overcome this issue with the tar
> trick, which is more easily performed as:
>
> tar cf /dev/null $PG_DATA/base
But on many implementations, that will not work. tar detects the
output is going to the bit bucket, and so doesn't bother to actually
read the data.
...
>
> Another option is to issue a simple vacuum after the vacuum full.
> Simple vacuum will just scan the tables and indices, I'm hoping doing
> nothing since the vacuum full will have cleaned everything already,
> but loading everything both in the OS cache and into shared_buffers.
Doesn't it use a ring buffer strategy, so it would load to OS, but
probably not to shared_buffers?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-29 15:18 Claudio Freire <klaussfreire@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 32+ messages in thread
From: Claudio Freire @ 2012-02-29 15:18 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Stefan Keller <sfkeller@gmail.com>; pgsql-performance
On Wed, Feb 29, 2012 at 12:16 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> But on many implementations, that will not work. tar detects the
> output is going to the bit bucket, and so doesn't bother to actually
> read the data.
Really? Getting smart on us?
Shame on it. Who asked it to be smart?
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-29 15:24 Stefan Keller <sfkeller@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 32+ messages in thread
From: Stefan Keller @ 2012-02-29 15:24 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; pgsql-performance
2012/2/29 Jeff Janes <jeff.janes@gmail.com>:
>> It's quite possible the vacuum full is thrashing your disk cache due
>> to maintainance_work_mem. You can overcome this issue with the tar
>> trick, which is more easily performed as:
>>
>> tar cf /dev/null $PG_DATA/base
>
> But on many implementations, that will not work. tar detects the
> output is going to the bit bucket, and so doesn't bother to actually
> read the data.
Right.
But what about the commands cp $PG_DATA/base /dev/null or cat
$PG_DATA/base > /dev/null ?
They seem to do something.
-Stefan
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-29 15:28 Stefan Keller <sfkeller@gmail.com>
parent: Stefan Keller <sfkeller@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Stefan Keller @ 2012-02-29 15:28 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; pgsql-performance
2012/2/29 Stefan Keller <sfkeller@gmail.com>:
> 2012/2/29 Jeff Janes <jeff.janes@gmail.com>:
>>> It's quite possible the vacuum full is thrashing your disk cache due
>>> to maintainance_work_mem. You can overcome this issue with the tar
>>> trick, which is more easily performed as:
>>>
>>> tar cf /dev/null $PG_DATA/base
>>
>> But on many implementations, that will not work. tar detects the
>> output is going to the bit bucket, and so doesn't bother to actually
>> read the data.
>
> Right.
> But what about the commands cp $PG_DATA/base /dev/null or cat
> $PG_DATA/base > /dev/null ?
> They seem to do something.
...or let's try /dev/zero instead /dev/null:
tar cf /dev/zero $PG_DATA/base
-Stefan
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-01 16:34 Jeff Janes <jeff.janes@gmail.com>
parent: Stefan Keller <sfkeller@gmail.com>
1 sibling, 0 replies; 32+ messages in thread
From: Jeff Janes @ 2012-03-01 16:34 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: Andy Colson <andy@squeakycode.net>; pgsql-performance
On Tue, Feb 28, 2012 at 12:48 PM, Stefan Keller <sfkeller@gmail.com> wrote:
> Hi
>
> 2012/2/28 Jeff Janes <jeff.janes@gmail.com> wrote:
>> It is hard to figure out what problem you are facing. Is your data
>> not getting loaded into cache, or is it not staying there?
>
> One could say both:
> I'd like to warm up the cache befor hand in order to speed up the
> first query right away.
> And it's not staying there because when there comes a second slightly
> different query it's slow again and I would expect that the tuples of
> that table stay.
Only the pages needed for a given query are loaded in the first place.
So even if they do stay, a new query that needs different pages
(because it accesses a different part of the index, and of the table)
won't find them already loaded, except by accident.
>
>>> Just after the second query. You can try it yourself online here:
>>> http://bit.ly/A8duyB
>
> I should have said after the first query.
>
>> The second instance of the exact same query is fast.
>
> Right.
>
>> How long until all similar but not identical queries are fast?
>
> Good question. Can't tell for sure because it not so easy to make it repeatable.
> I tested the following:
>
> SELECT count(*) FROM osm_point WHERE tags @> 'amenity=>restaurant'
>
> SELECT count(*) FROM osm_point WHERE tags @> 'cuisine=>pizza'
>
> SELECT count(*) FROM osm_point WHERE tags @> 'tourism=>hotel'
>
> SELECT count(*) FROM osm_point WHERE tags @> 'historic=>castle'
>
> SELECT count(*) FROM osm_point WHERE tags @> 'natural=>peak'
> AND to_number(ele, '9999') >= 4000
>
> I would say that after the 4th query it remains fast (meaning less
> than a second).
Hmm. I ran out of example queries before they started being fast.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-01 16:57 Jeff Janes <jeff.janes@gmail.com>
parent: Stefan Keller <sfkeller@gmail.com>
0 siblings, 2 replies; 32+ messages in thread
From: Jeff Janes @ 2012-03-01 16:57 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; pgsql-performance
On Tue, Feb 28, 2012 at 3:46 PM, Stefan Keller <sfkeller@gmail.com> wrote:
> 2012/2/28 Claudio Freire <klaussfreire@gmail.com>:
>>
>> In the OP, you say "There is enough main memory to hold all table
>> contents.". I'm assuming, there you refer to your current system, with
>> 4GB memory.
>
> Sorry for the confusion: I'm doing these tests on this machine with
> one table (osm_point) and one country. This table has a size of 2.6GB
> and 10 million tuples. The other machine has to deal with at least 5
> tables in total and will be hold more than one country plus routing
> etc..
What is your shared_buffers set to? 2.6GB is uncomfortably close to
4GB, considering the computer has other things it needs to use memory
for as well.
A problem is that often the shared_buffers and the OS cache end up
being basically copies of one another, rather than complementing each
other. So on read-only applications, the actually useful size of the
total cache turns out to be max(shared_buffers, RAM - 2*shared_buffers
- unknown_overhead).
So one choice is setting shared_buffers low (<0.5GB) and let the OS
cache be your main cache. Advantages of this are that the OS cache
survives PG server restarts, gets populated even by sequential scans,
and can be pre-warmed by the tar trick. Disadvantages are that pages
can be driven out of the OS cache by non-PG related activity, which
can be hard to monitor and control. Also, there is some small cost to
constantly transferring data from OS cache to PG cache, but in your
case I htink that would be negligible.
The other choice is setting shared_buffers high (>3GB) and having it
be your main cache. The advantage is that non-PG activity generally
won't drive it out. The disadvantages are that it is hard to
pre-populate as the tar trick won't work, and neither will sequential
scans on tables due to the ring buffer.
Actually, the tar trick might work somewhat if applied either shortly
before or shortly after the database is started. If the database
starts out not using its full allotment of memory, the OS will use it
for cache, and you can pre-populate that cache. Then as the database
runs, the PG cache gets larger by copying needed data from the OS
cache into it. As the PG cache grows, pages need to get evicted from
OS cache to make room for it. Ideally, the pages evicted from the OS
cache would be the ones just copied into PG, but the kernel is not
aware of that. So the whole thing is rather sub-optimal.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-01 17:35 Scott Marlowe <scott.marlowe@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 32+ messages in thread
From: Scott Marlowe @ 2012-03-01 17:35 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Stefan Keller <sfkeller@gmail.com>; Claudio Freire <klaussfreire@gmail.com>; pgsql-performance
On Thu, Mar 1, 2012 at 9:57 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Tue, Feb 28, 2012 at 3:46 PM, Stefan Keller <sfkeller@gmail.com> wrote:
>> 2012/2/28 Claudio Freire <klaussfreire@gmail.com>:
>>>
>>> In the OP, you say "There is enough main memory to hold all table
>>> contents.". I'm assuming, there you refer to your current system, with
>>> 4GB memory.
>>
>> Sorry for the confusion: I'm doing these tests on this machine with
>> one table (osm_point) and one country. This table has a size of 2.6GB
>> and 10 million tuples. The other machine has to deal with at least 5
>> tables in total and will be hold more than one country plus routing
>> etc..
>
> What is your shared_buffers set to? 2.6GB is uncomfortably close to
> 4GB, considering the computer has other things it needs to use memory
> for as well.
The real danger here is that the kernel will happily swap ut
shared_buffers memory to make room to cache more from the hard disks,
especially if that shared_mem hasn't been touched in a while. On a
stock kernel with swappinness of 60 etc, it's quite likely the OP is
seeing the DB go to get data from shared_buffers, and the OS is
actually paging in for shared_buffers. At that point reading from
kernel cache is MUCH faster, and reading from the HDs is still
probably faster than swapping in shared_buffers.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-01 17:38 Dave Crooke <dcrooke@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 32+ messages in thread
From: Dave Crooke @ 2012-03-01 17:38 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; Stefan Keller <sfkeller@gmail.com>; Jeff Janes <jeff.janes@gmail.com>; pgsql-performance
Just curious ... has anyone tried using a ram disk as the PG primary and
DRBD as the means to make it persistent?
On Mar 1, 2012 11:35 AM, "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
> On Thu, Mar 1, 2012 at 9:57 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> > On Tue, Feb 28, 2012 at 3:46 PM, Stefan Keller <sfkeller@gmail.com>
> wrote:
> >> 2012/2/28 Claudio Freire <klaussfreire@gmail.com>:
> >>>
> >>> In the OP, you say "There is enough main memory to hold all table
> >>> contents.". I'm assuming, there you refer to your current system, with
> >>> 4GB memory.
> >>
> >> Sorry for the confusion: I'm doing these tests on this machine with
> >> one table (osm_point) and one country. This table has a size of 2.6GB
> >> and 10 million tuples. The other machine has to deal with at least 5
> >> tables in total and will be hold more than one country plus routing
> >> etc..
> >
> > What is your shared_buffers set to? 2.6GB is uncomfortably close to
> > 4GB, considering the computer has other things it needs to use memory
> > for as well.
>
> The real danger here is that the kernel will happily swap ut
> shared_buffers memory to make room to cache more from the hard disks,
> especially if that shared_mem hasn't been touched in a while. On a
> stock kernel with swappinness of 60 etc, it's quite likely the OP is
> seeing the DB go to get data from shared_buffers, and the OS is
> actually paging in for shared_buffers. At that point reading from
> kernel cache is MUCH faster, and reading from the HDs is still
> probably faster than swapping in shared_buffers.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-01 22:52 Stefan Keller <sfkeller@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 32+ messages in thread
From: Stefan Keller @ 2012-03-01 22:52 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; pgsql-performance
2012/3/1 Jeff Janes <jeff.janes@gmail.com>:
> On Tue, Feb 28, 2012 at 3:46 PM, Stefan Keller <sfkeller@gmail.com> wrote:
>> 2012/2/28 Claudio Freire <klaussfreire@gmail.com>:
>>>
>>> In the OP, you say "There is enough main memory to hold all table
>>> contents.". I'm assuming, there you refer to your current system, with
>>> 4GB memory.
>>
>> Sorry for the confusion: I'm doing these tests on this machine with
>> one table (osm_point) and one country. This table has a size of 2.6GB
>> and 10 million tuples. The other machine has to deal with at least 5
>> tables in total and will be hold more than one country plus routing
>> etc..
>
> What is your shared_buffers set to? 2.6GB is uncomfortably close to
> 4GB, considering the computer has other things it needs to use memory
> for as well.
These are the current modified settings in postgresql.conf:
shared_buffers = 128MB
work_mem = 3MB
maintenance_work_mem = 30MB
effective_cache_size = 352MB
wal_buffers = 8MB
default_statistics_target = 50
constraint_exclusion = on
checkpoint_completion_target = 0.9
checkpoint_segments = 16
max_connections = 80
-Stefan
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-01 23:08 Andrew Dunstan <andrew@dunslane.net>
parent: Stefan Keller <sfkeller@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Andrew Dunstan @ 2012-03-01 23:08 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; Claudio Freire <klaussfreire@gmail.com>; pgsql-performance
On 03/01/2012 05:52 PM, Stefan Keller wrote:
> These are the current modified settings in postgresql.conf:
> shared_buffers = 128MB
> work_mem = 3MB
These are extremely low settings on virtually any modern computer. I
usually look to set shared buffers in numbers of Gb and work_mem at
least in tens if not hundreds of Mb for any significantly sized database.
cheers
andrew
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-02 00:23 Claudio Freire <klaussfreire@gmail.com>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 32+ messages in thread
From: Claudio Freire @ 2012-03-02 00:23 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: pgsql-performance
On Thu, Mar 1, 2012 at 8:08 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> These are extremely low settings on virtually any modern computer. I usually
> look to set shared buffers in numbers of Gb and work_mem at least in tens if
> not hundreds of Mb for any significantly sized database.
For a read-only database, as was discussed, a lower shared_buffers
settings makes sense. And 128M is low enough, I'd guess.
Setting work_mem to hundreds of MB in a 4G system is suicide. Tens
even is dangerous.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-02 00:28 Peter van Hardenberg <pvh@pvh.ca>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Peter van Hardenberg @ 2012-03-02 00:28 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; pgsql-performance
On Thu, Mar 1, 2012 at 4:23 PM, Claudio Freire <klaussfreire@gmail.com> wrote:
> For a read-only database, as was discussed, a lower shared_buffers
> settings makes sense. And 128M is low enough, I'd guess.
>
> Setting work_mem to hundreds of MB in a 4G system is suicide. Tens
> even is dangerous.
>
Why do you say that? We've had work_mem happily at 100MB for years. Is
there a particular degenerate case you're concerned about?
-p
--
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt." -- Kurt Vonnegut
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-02 00:35 Jeff Janes <jeff.janes@gmail.com>
parent: Stefan Keller <sfkeller@gmail.com>
0 siblings, 0 replies; 32+ messages in thread
From: Jeff Janes @ 2012-03-02 00:35 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; pgsql-performance
On Wed, Feb 29, 2012 at 7:28 AM, Stefan Keller <sfkeller@gmail.com> wrote:
> 2012/2/29 Stefan Keller <sfkeller@gmail.com>:
>> 2012/2/29 Jeff Janes <jeff.janes@gmail.com>:
>>>> It's quite possible the vacuum full is thrashing your disk cache due
>>>> to maintainance_work_mem. You can overcome this issue with the tar
>>>> trick, which is more easily performed as:
>>>>
>>>> tar cf /dev/null $PG_DATA/base
>>>
>>> But on many implementations, that will not work. tar detects the
>>> output is going to the bit bucket, and so doesn't bother to actually
>>> read the data.
>>
>> Right.
>> But what about the commands cp $PG_DATA/base /dev/null or cat
>> $PG_DATA/base > /dev/null ?
>> They seem to do something.
For me they both give errors, because neither of them works on an
directory rather than ordinary files.
>
> ...or let's try /dev/zero instead /dev/null:
> tar cf /dev/zero $PG_DATA/base
That does seem to work.
So, does it solve your problem?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-02 00:58 Claudio Freire <klaussfreire@gmail.com>
parent: Peter van Hardenberg <pvh@pvh.ca>
0 siblings, 1 reply; 32+ messages in thread
From: Claudio Freire @ 2012-03-02 00:58 UTC (permalink / raw)
To: Peter van Hardenberg <pvh@pvh.ca>; +Cc: pgsql-performance
On Thu, Mar 1, 2012 at 9:28 PM, Peter van Hardenberg <pvh@pvh.ca> wrote:
>> Setting work_mem to hundreds of MB in a 4G system is suicide. Tens
>> even is dangerous.
>>
>
> Why do you say that? We've had work_mem happily at 100MB for years. Is
> there a particular degenerate case you're concerned about?
Me too.
But I've analyzed the queries I'll be sending to the database and I've
carefully bound the effective amount of memory used given the load
I'll be experiencing.
Saying that it should be set to 100M without consideration for those
matters is the suicide part. work_mem applies to each sort operation.
Suppose, just for the sake of argument, that each connection is
performing 5 such sorts (ie, 5 joins of big tables - not unthinkable),
then suppose you have your max_connections to the default of 100, then
the system could request as much as 50G of ram.
I set work_mem higher in my database system since I *know* most of the
connections will not perform any merge or hash joins, nor will they
sort the output, so they won't use work_mem even once. The ones that
will, I have limited on the application side to a handful, hence I
*know* that 50G theoretical maximum will not be reached.
Can the OP say that? I have no reason to think so. Hence I don't
suggest 100M is OK on a 4G system.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-02 01:17 Andrew Dunstan <andrew@dunslane.net>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 0 replies; 32+ messages in thread
From: Andrew Dunstan @ 2012-03-02 01:17 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Peter van Hardenberg <pvh@pvh.ca>; pgsql-performance
On 03/01/2012 07:58 PM, Claudio Freire wrote:
> On Thu, Mar 1, 2012 at 9:28 PM, Peter van Hardenberg<pvh@pvh.ca> wrote:
>>> Setting work_mem to hundreds of MB in a 4G system is suicide. Tens
>>> even is dangerous.
>>>
>> Why do you say that? We've had work_mem happily at 100MB for years. Is
>> there a particular degenerate case you're concerned about?
> Me too.
>
> But I've analyzed the queries I'll be sending to the database and I've
> carefully bound the effective amount of memory used given the load
> I'll be experiencing.
>
> Saying that it should be set to 100M without consideration for those
> matters is the suicide part. work_mem applies to each sort operation.
> Suppose, just for the sake of argument, that each connection is
> performing 5 such sorts (ie, 5 joins of big tables - not unthinkable),
> then suppose you have your max_connections to the default of 100, then
> the system could request as much as 50G of ram.
>
> I set work_mem higher in my database system since I *know* most of the
> connections will not perform any merge or hash joins, nor will they
> sort the output, so they won't use work_mem even once. The ones that
> will, I have limited on the application side to a handful, hence I
> *know* that 50G theoretical maximum will not be reached.
>
> Can the OP say that? I have no reason to think so. Hence I don't
> suggest 100M is OK on a 4G system.
Well, obviously you need to know your workload. Nobody said otherwise.
cheers
andrew
^ permalink raw reply [nested|flat] 32+ messages in thread
end of thread, other threads:[~2012-03-02 01:17 UTC | newest]
Thread overview: 32+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-26 00:16 PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory? Stefan Keller <sfkeller@gmail.com>
2012-02-26 02:13 ` Jeff Janes <jeff.janes@gmail.com>
2012-02-26 10:56 ` Stefan Keller <sfkeller@gmail.com>
2012-02-26 13:55 ` Stephen Frost <sfrost@snowman.net>
2012-02-26 22:34 ` Jeff Janes <jeff.janes@gmail.com>
2012-02-26 11:46 ` Thomas Kellerer <spam_eater@gmx.net>
2012-02-26 15:20 ` Andy Colson <andy@squeakycode.net>
2012-02-26 19:11 ` Stefan Keller <sfkeller@gmail.com>
2012-02-26 19:20 ` Andy Colson <andy@squeakycode.net>
2012-02-26 20:37 ` Stefan Keller <sfkeller@gmail.com>
2012-02-28 15:22 ` Jeff Janes <jeff.janes@gmail.com>
2012-02-28 20:48 ` Stefan Keller <sfkeller@gmail.com>
2012-02-28 22:41 ` Claudio Freire <klaussfreire@gmail.com>
2012-02-28 23:46 ` Stefan Keller <sfkeller@gmail.com>
2012-03-01 16:57 ` Jeff Janes <jeff.janes@gmail.com>
2012-03-01 17:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-03-01 17:38 ` Dave Crooke <dcrooke@gmail.com>
2012-03-01 22:52 ` Stefan Keller <sfkeller@gmail.com>
2012-03-01 23:08 ` Andrew Dunstan <andrew@dunslane.net>
2012-03-02 00:23 ` Claudio Freire <klaussfreire@gmail.com>
2012-03-02 00:28 ` Peter van Hardenberg <pvh@pvh.ca>
2012-03-02 00:58 ` Claudio Freire <klaussfreire@gmail.com>
2012-03-02 01:17 ` Andrew Dunstan <andrew@dunslane.net>
2012-02-29 15:16 ` Jeff Janes <jeff.janes@gmail.com>
2012-02-29 15:18 ` Claudio Freire <klaussfreire@gmail.com>
2012-02-29 15:24 ` Stefan Keller <sfkeller@gmail.com>
2012-02-29 15:28 ` Stefan Keller <sfkeller@gmail.com>
2012-03-02 00:35 ` Jeff Janes <jeff.janes@gmail.com>
2012-03-01 16:34 ` Jeff Janes <jeff.janes@gmail.com>
2012-02-26 19:35 ` Cédric Villemain <cedric@2ndquadrant.fr>
2012-02-26 20:56 ` Stefan Keller <sfkeller@gmail.com>
2012-02-26 19:36 Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory? Cédric Villemain <cedric@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox