Thread: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

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)

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

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

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
tablesare 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

* 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

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

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

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
entiretable 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

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

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

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

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

Hi Wales

2012/2/27 Wales Wang <wormwang@yahoo.com> wrote:
> There are many approach for PostgreSQL in-memory.
> The quick and easy way is making slave pgsql run on persistent RAM
> filesystem, the slave is part of master/slave replication cluster.
>
> The fstab and script make RAM file system persistent is below:
> Setup:
> First, create a mountpoint for the disk :
> mkdir /mnt/ramdisk
> Secondly, add this line to /etc/fstab in to mount the drive at boot-time.
> tmpfs           /mnt/ramdisk tmpfs      defaults,size=65536M 0 0
> #! /bin/sh
> # /etc/init.d/ramdisk.sh
> #
>
> case "$1" in
>   start)
>     echo "Copying files to ramdisk"
>     rsync -av /data/ramdisk-backup/ /mnt/ramdisk/
>     echo [`date +"%Y-%m-%d %H:%M"`] Ramdisk Synched from HD >>
> /var/log/ramdisk_sync.log
>     ;;
>   sync)
>     echo "Synching files from ramdisk to Harddisk"
>     echo [`date +"%Y-%m-%d %H:%M"`] Ramdisk Synched to HD >>
> /var/log/ramdisk_sync.log
>     rsync -av --delete --recursive --force /mnt/ramdisk/
> /data/ramdisk-backup/
>     ;;
>   stop)
>     echo "Synching logfiles from ramdisk to Harddisk"
>     echo [`date +"%Y-%m-%d %H:%M"`] Ramdisk Synched to HD >>
> /var/log/ramdisk_sync.log
>     rsync -av --delete --recursive --force /mnt/ramdisk/
> /data/ramdisk-backup/
>     ;;
>   *)
>     echo "Usage: /etc/init.d/ramdisk {start|stop|sync}"
>     exit 1
>     ;;
> esac
> exit 0
>
> you can run it when startup and shutdown and crontabe hoursly.
>
> Wales Wang

Thank you for the tipp.
Making slave pgsql run on persistent RAM filesystem is surely at least
a possibility which I'll try out.

But what I'm finally after is a solution, where records don't get
pushed back to disk a.s.a.p. but rather got hold in memory as long as
possible assuming that there is enough memory.
I suspect that currently there is quite some overhead because of that
(besides disk-oriented structures).

-Stefan

On Tue, Feb 28, 2012 at 5:30 AM, Stefan Keller <sfkeller@gmail.com> wrote:
>
> But what I'm finally after is a solution, where records don't get
> pushed back to disk a.s.a.p. but rather got hold in memory as long as
> possible assuming that there is enough memory.

fsync = off ?

On 28 Únor 2012, 14:08, Claudio Freire wrote:
> On Tue, Feb 28, 2012 at 5:30 AM, Stefan Keller <sfkeller@gmail.com> wrote:
>>
>> But what I'm finally after is a solution, where records don't get
>> pushed back to disk a.s.a.p. but rather got hold in memory as long as
>> possible assuming that there is enough memory.
>
> fsync = off ?

I don't think this is a viable idea, unless you don't care about the data.

Moreover, "fsyn=off" does not mean "not writing" and writing does not mean
"removing from shared buffers". A page written/fsynced during a checkpoint
may stay in shared buffers.

AFAIK the pages are not removed from shared buffers without a reason. So a
dirty buffer is written to a disk (because it needs to, to keep ACID) but
stays in shared buffers as "clean" (unless it was written by a backend,
which means there's not enough memory).

Tomas

On Tue, Feb 28, 2012 at 10:38 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 28 Únor 2012, 14:08, Claudio Freire wrote:
>> On Tue, Feb 28, 2012 at 5:30 AM, Stefan Keller <sfkeller@gmail.com> wrote:
>>>
>>> But what I'm finally after is a solution, where records don't get
>>> pushed back to disk a.s.a.p. but rather got hold in memory as long as
>>> possible assuming that there is enough memory.
>>
>> fsync = off ?
>
> I don't think this is a viable idea, unless you don't care about the data.

Well, if you "keep things in memory as long as possible" (as per the
quoted message), then you don't care about memory. There's no way
memory-only DBs can provide ACID guarantees.

synchronous_commit=off goes half way there without sacrificing crash
recovery, which is another option.

> Moreover, "fsyn=off" does not mean "not writing" and writing does not mean
> "removing from shared buffers". A page written/fsynced during a checkpoint
> may stay in shared buffers.

The OS will write in the background (provided there's enough memory,
which was an assumption on the quoted message). It will not interfere
with other operations, so, in any case, writing or not, you get what
you want.

> AFAIK the pages are not removed from shared buffers without a reason. So a
> dirty buffer is written to a disk (because it needs to, to keep ACID) but
> stays in shared buffers as "clean" (unless it was written by a backend,
> which means there's not enough memory).

Just writing is not enough. ACID requires fsync. If you don't fsync
(be it with synchronous_commit=off or fsync=off), then it's not full
ACID already.
Because a crash at a bad moment can always make your data nonpersistent.

That's an unavoidable result of keeping things in memory.

On 28 Únor 2012, 14:52, Claudio Freire wrote:
> On Tue, Feb 28, 2012 at 10:38 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
>> On 28 Únor 2012, 14:08, Claudio Freire wrote:
>>> On Tue, Feb 28, 2012 at 5:30 AM, Stefan Keller <sfkeller@gmail.com>
>>> wrote:
>>>>
>>>> But what I'm finally after is a solution, where records don't get
>>>> pushed back to disk a.s.a.p. but rather got hold in memory as long as
>>>> possible assuming that there is enough memory.
>>>
>>> fsync = off ?
>>
>> I don't think this is a viable idea, unless you don't care about the
>> data.
>
> Well, if you "keep things in memory as long as possible" (as per the
> quoted message), then you don't care about memory. There's no way
> memory-only DBs can provide ACID guarantees.
>
> synchronous_commit=off goes half way there without sacrificing crash
> recovery, which is another option.
>
>> Moreover, "fsyn=off" does not mean "not writing" and writing does not
>> mean
>> "removing from shared buffers". A page written/fsynced during a
>> checkpoint
>> may stay in shared buffers.
>
> The OS will write in the background (provided there's enough memory,
> which was an assumption on the quoted message). It will not interfere
> with other operations, so, in any case, writing or not, you get what
> you want.
>
>> AFAIK the pages are not removed from shared buffers without a reason. So
>> a
>> dirty buffer is written to a disk (because it needs to, to keep ACID)
>> but
>> stays in shared buffers as "clean" (unless it was written by a backend,
>> which means there's not enough memory).
>
> Just writing is not enough. ACID requires fsync. If you don't fsync
> (be it with synchronous_commit=off or fsync=off), then it's not full
> ACID already.
> Because a crash at a bad moment can always make your data nonpersistent.

I haven't said writing is sufficient for ACID, I said it's required. Which
is kind of obvious because of the "durability" part.

> That's an unavoidable result of keeping things in memory.

Why? IIRC the OP was interested in keeping the data in memory for querying
and that the database is read-only after it's populated with data (once a
day). How does writing the transactional logs / data files properly
interfere with that?

I haven't investigated why exactly the data are not cached initially, but
none of the options that I can think of could be "fixed" by setting
"fsync=off". That's something that influences writes (not read-only
database) and I don't think it influences how buffers are evicted from
shared buffers / page cache.

It might speed up the initial load of data, but that's not what the OP was
asking.

kind regards
Tomas

On Tue, Feb 28, 2012 at 11:15 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
> I haven't investigated why exactly the data are not cached initially, but
> none of the options that I can think of could be "fixed" by setting
> "fsync=off". That's something that influences writes (not read-only
> database) and I don't think it influences how buffers are evicted from
> shared buffers / page cache.
>
> It might speed up the initial load of data, but that's not what the OP was
> asking.

It speeds a lot more than the initial load of data.

Assuming the database is read-only, but not the filesystem (ie: it's
not a slave, in which case all this is moot, as you said, there are no
writes on a slave). That is, assuming this is a read-only master, then
read-only queries don't mean read-only filesystem. Bookkeeping tasks
like updating catalog dbs, statistics tables, page cleanup, stuff like
that can actually result in writes.

Writes that go through the WAL and then the filesystem.

With fsync=off, those writes happen on the background, and are carried
out by the OS. Effectively releasing postgres from having to wait on
them, and, assuming there's enough RAM, merging repeated writes to the
same sectors in one operation in the end. For stats, bookkeeping, and
who knows what else, the merging would be quite effective. With enough
RAM to hold the entire DB, the merging would effectively keep
everything in RAM (in system buffers) until there's enough I/O
bandwidth to transparently push that to persistent storage.

In essence, what was required, to keep everything in RAM for as much
as possible.

It *does* in the same way affect buffer eviction - it makes eviction
*very* quick, and re-population equally as quick, if everything fits
into memory.

On Tue, Feb 28, 2012 at 12:30 AM, Stefan Keller <sfkeller@gmail.com> wrote:
>
> Thank you for the tipp.
> Making slave pgsql run on persistent RAM filesystem is surely at least
> a possibility which I'll try out.
>
> But what I'm finally after is a solution, where records don't get
> pushed back to disk a.s.a.p. but rather got hold in memory as long as
> possible assuming that there is enough memory.

That is already the case.  There are two separate issues, when dirty
data is written to disk, and when clean data is dropped from memory.
The only connection between them is that dirty data can't just be
dropped, it must be written first.  But have written it, there is no
reason to immediately drop it.  When a checkpoint cleans data from the
shard_buffers, that now-clean data remains in shared_buffers.  And at
the OS level, when an fsync forces dirty data out to disk, the
now-clean data generally remains in cache (although I've seen nfs
implementations where that was not the case).

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?

Cheers,

Jeff

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

On 28 Únor 2012, 15:24, Claudio Freire wrote:
> On Tue, Feb 28, 2012 at 11:15 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
>> I haven't investigated why exactly the data are not cached initially,
>> but
>> none of the options that I can think of could be "fixed" by setting
>> "fsync=off". That's something that influences writes (not read-only
>> database) and I don't think it influences how buffers are evicted from
>> shared buffers / page cache.
>>
>> It might speed up the initial load of data, but that's not what the OP
>> was
>> asking.
>
> It speeds a lot more than the initial load of data.
>
> Assuming the database is read-only, but not the filesystem (ie: it's
> not a slave, in which case all this is moot, as you said, there are no
> writes on a slave). That is, assuming this is a read-only master, then
> read-only queries don't mean read-only filesystem. Bookkeeping tasks
> like updating catalog dbs, statistics tables, page cleanup, stuff like
> that can actually result in writes.
>
> Writes that go through the WAL and then the filesystem.

I'm not sure what maintenance tasks you mean. Sure, there are tasks that
need to be performed after the load (stats, hint bits, updating system
catalogs etc.) but this may happen once right after the load and then
there's effectively zero write activity. Unless the database needs to
write temp files, but that contradicts the 'fits into RAM' assumption ...

> With fsync=off, those writes happen on the background, and are carried
> out by the OS. Effectively releasing postgres from having to wait on
> them, and, assuming there's enough RAM, merging repeated writes to the
> same sectors in one operation in the end. For stats, bookkeeping, and
> who knows what else, the merging would be quite effective. With enough
> RAM to hold the entire DB, the merging would effectively keep
> everything in RAM (in system buffers) until there's enough I/O
> bandwidth to transparently push that to persistent storage.

The writes are always carried out by the OS - except when dirty_ratio is
exceeded (but that's a different story) and WAL with direct I/O enabled.
The best way to allow merging the writes in shared buffers or page cache
is to set the checkpoint_segments / checkpoint_timeout high enough.

That way the transactions won't need to wait for writes to data files
(which is the part related to evictions of buffers from cache). And
read-only transactions won't need to wait at all because they don't need
to wait for fsync on WAL.

> In essence, what was required, to keep everything in RAM for as much
> as possible.
>
> It *does* in the same way affect buffer eviction - it makes eviction
> *very* quick, and re-population equally as quick, if everything fits
> into memory.

No it doesn't. Only a write caused by a background process (due to full
shared buffers) means immediate eviction. A simple write (caused by a
checkpoint) does not evict the page from shared buffers. Not even a
background writer evicts a page from shared buffers, it merely marks them
as 'clean' and leaves them there. And all those writes happen on the
background, so the clients don't need to wait for them to complete (except
for xlog checkpoints).

kind regards
Tomas

On Tue, Feb 28, 2012 at 1:05 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 28 Únor 2012, 15:24, Claudio Freire wrote:
>> It speeds a lot more than the initial load of data.
>>
>> Assuming the database is read-only, but not the filesystem (ie: it's
>> not a slave, in which case all this is moot, as you said, there are no
>> writes on a slave). That is, assuming this is a read-only master, then
>> read-only queries don't mean read-only filesystem. Bookkeeping tasks
>> like updating catalog dbs, statistics tables, page cleanup, stuff like
>> that can actually result in writes.
>>
>> Writes that go through the WAL and then the filesystem.
>
> I'm not sure what maintenance tasks you mean. Sure, there are tasks that
> need to be performed after the load (stats, hint bits, updating system
> catalogs etc.) but this may happen once right after the load and then
> there's effectively zero write activity. Unless the database needs to
> write temp files, but that contradicts the 'fits into RAM' assumption ...

AFAIK, stats need to be constantly updated.
Not sure about the rest.

And yes, it's quite possible to require temp files without a database
that doesn't fit in memory, only big OLAP-style queries and small
enough work_mem.

> The writes are always carried out by the OS - except when dirty_ratio is
> exceeded (but that's a different story) and WAL with direct I/O enabled.
> The best way to allow merging the writes in shared buffers or page cache
> is to set the checkpoint_segments / checkpoint_timeout high enough.
> That way the transactions won't need to wait for writes to data files
> (which is the part related to evictions of buffers from cache). And
> read-only transactions won't need to wait at all because they don't need
> to wait for fsync on WAL.

Exactly

>> In essence, what was required, to keep everything in RAM for as much
>> as possible.
>>
>> It *does* in the same way affect buffer eviction - it makes eviction
>> *very* quick, and re-population equally as quick, if everything fits
>> into memory.
>
> No it doesn't. Only a write caused by a background process (due to full
> shared buffers) means immediate eviction. A simple write (caused by a
> checkpoint) does not evict the page from shared buffers. Not even a
> background writer evicts a page from shared buffers, it merely marks them
> as 'clean' and leaves them there. And all those writes happen on the
> background, so the clients don't need to wait for them to complete (except
> for xlog checkpoints).

So, we're saying the same.

With all that, and enough RAM, it already does what was requested.

Maybe it would help to tune shared_buffers-to-os-cache ratio, and
dirty_ratio to allow a big portion of RAM used for write caching (if
there were enough writes which I doubt), but, in essence, un
unmodified postgres installation with enough RAM to hold the whole DB
+ shared buffers in RAM should perform quite optimally.

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.

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.

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

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

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?

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

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

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

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

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.

Just curious ... has anyone tried using a ram disk as the PG primary and DRBD as the means to make it persistent?

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

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

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.

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

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

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.

On 28.2.2012 17:42, Claudio Freire wrote:
> On Tue, Feb 28, 2012 at 1:05 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>> On 28 Únor 2012, 15:24, Claudio Freire wrote:
>>> It speeds a lot more than the initial load of data.
>>>
>>> Assuming the database is read-only, but not the filesystem (ie: it's
>>> not a slave, in which case all this is moot, as you said, there are no
>>> writes on a slave). That is, assuming this is a read-only master, then
>>> read-only queries don't mean read-only filesystem. Bookkeeping tasks
>>> like updating catalog dbs, statistics tables, page cleanup, stuff like
>>> that can actually result in writes.
>>>
>>> Writes that go through the WAL and then the filesystem.
>>
>> I'm not sure what maintenance tasks you mean. Sure, there are tasks that
>> need to be performed after the load (stats, hint bits, updating system
>> catalogs etc.) but this may happen once right after the load and then
>> there's effectively zero write activity. Unless the database needs to
>> write temp files, but that contradicts the 'fits into RAM' assumption ...
>
> AFAIK, stats need to be constantly updated.

Err, what kind of stats are we talking about? Statistics capturing
characteristics of the data or runtime stats? There's no point in
updating data stats (histograms, MCV, ...) for read-only data and
PostgreSQL doesn't do that.

Runtime stats OTOH are collected and written continuously, that's true.
But in most cases this is not a write-heavy task, and if it is then it's
recommended to place the pg_stat_tmp on ramdrive (it's usually just few
MBs, written repeatedly).

> Not sure about the rest.

AFAIK it's like this:

  updating catalog tables - no updates on read-only data

  updating statistics - data stats: no, runtime stats: yes

  page cleanup - no (just once after the load)

> And yes, it's quite possible to require temp files without a database
> that doesn't fit in memory, only big OLAP-style queries and small
> enough work_mem.

Right. I'm not exactly sure how I arrived to the crazy conclusion that
writing temp files somehow contradicts the 'fits into RAM' assumption.
That's clearly nonsense ...

>
>> The writes are always carried out by the OS - except when dirty_ratio is
>> exceeded (but that's a different story) and WAL with direct I/O enabled.
>> The best way to allow merging the writes in shared buffers or page cache
>> is to set the checkpoint_segments / checkpoint_timeout high enough.
>> That way the transactions won't need to wait for writes to data files
>> (which is the part related to evictions of buffers from cache). And
>> read-only transactions won't need to wait at all because they don't need
>> to wait for fsync on WAL.
>
> Exactly
>
>>> In essence, what was required, to keep everything in RAM for as much
>>> as possible.
>>>
>>> It *does* in the same way affect buffer eviction - it makes eviction
>>> *very* quick, and re-population equally as quick, if everything fits
>>> into memory.
>>
>> No it doesn't. Only a write caused by a background process (due to full
>> shared buffers) means immediate eviction. A simple write (caused by a
>> checkpoint) does not evict the page from shared buffers. Not even a
>> background writer evicts a page from shared buffers, it merely marks them
>> as 'clean' and leaves them there. And all those writes happen on the
>> background, so the clients don't need to wait for them to complete (except
>> for xlog checkpoints).
>
> So, we're saying the same.

Maybe. I still am not sure how fsync=off affects the eviction in your
opinion. I think it does not (or just very remotely) and you were saying
the opposite. IMHO the eviction of (dirty) buffers is either very fast
or slow, no matter what the fsync setting is.

> With all that, and enough RAM, it already does what was requested.
>
> Maybe it would help to tune shared_buffers-to-os-cache ratio, and
> dirty_ratio to allow a big portion of RAM used for write caching (if
> there were enough writes which I doubt), but, in essence, un
> unmodified postgres installation with enough RAM to hold the whole DB
> + shared buffers in RAM should perform quite optimally.

Probably, for a read-write database that fits into memory. In case of a
read-only database I don't think this really matters because the main
issue there are temp files and if you can stuff them into page cache
then you can just increase the work_mem instead and you're golden.

Tomas

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

On Thu, Mar 1, 2012 at 10:13 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>
> Maybe. I still am not sure how fsync=off affects the eviction in your
> opinion. I think it does not (or just very remotely) and you were saying
> the opposite. IMHO the eviction of (dirty) buffers is either very fast
> or slow, no matter what the fsync setting is.

I was thinking page cleanup, but if you're confident it doesn't happen
on a read-only database, I'd have to agree on all your other points.

I have seen a small amount of writes on a read-only devel DB I work
with, though. Usually in the order of 100kb/s writes per 10mb/s reads
- I attributed that to page cleanup. In that case, it can add some
wait time to fsync, even though it's really a slow volume of writes.
If you're right, I'm thinking, it may be some other thing... atime
updates maybe, I'd have to check the filesystem configuration I guess.

On 2.3.2012 03:05, Claudio Freire wrote:
> On Thu, Mar 1, 2012 at 10:13 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>>
>> Maybe. I still am not sure how fsync=off affects the eviction in your
>> opinion. I think it does not (or just very remotely) and you were saying
>> the opposite. IMHO the eviction of (dirty) buffers is either very fast
>> or slow, no matter what the fsync setting is.
>
> I was thinking page cleanup, but if you're confident it doesn't happen
> on a read-only database, I'd have to agree on all your other points.
>
> I have seen a small amount of writes on a read-only devel DB I work
> with, though. Usually in the order of 100kb/s writes per 10mb/s reads
> - I attributed that to page cleanup. In that case, it can add some
> wait time to fsync, even though it's really a slow volume of writes.
> If you're right, I'm thinking, it may be some other thing... atime
> updates maybe, I'd have to check the filesystem configuration I guess.

I'd guess those writes were caused by hint bits (~ page cleanup, but
that's a one-time thing and should be fixed by VACUUM FREEZE right after
the load). Or maybe it was related to runtime stats (i.e. pgstat).

T.