pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
13+ messages / 5 participants
[nested] [flat]
* 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-27 12:46 Wales Wang <wormwang@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Wales Wang @ 2012-02-27 12:46 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; Stefan Keller <sfkeller@gmail.com>; +Cc: pgsql-performance; Stephen Frost <sfrost@snowman.net>
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
________________________________
发件人： Jeff Janes <jeff.janes@gmail.com>
收件人： Stefan Keller <sfkeller@gmail.com>
抄送： Wales Wang <wormwang@yahoo.com>; pgsql-performance@postgresql.org; Stephen Frost <sfrost@snowman.net>
发送日期： 2012年2月27日, 星期一, 上午 6:34
主题: Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
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. 燭hen 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. 營've wondered if it would
>> make sense to suppress ring-buffer strategy when there are buffers on
>> the free-list. 燭hat way a sequential scan would populate
>> shared_buffers after a restart. 燘ut 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
--
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] 13+ messages in thread
* Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 08:30 Stefan Keller <sfkeller@gmail.com>
parent: Wales Wang <wormwang@yahoo.com>
0 siblings, 2 replies; 13+ messages in thread
From: Stefan Keller @ 2012-02-28 08:30 UTC (permalink / raw)
To: Wales Wang <wormwang@yahoo.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-performance; Stephen Frost <sfrost@snowman.net>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 13:08 Claudio Freire <klaussfreire@gmail.com>
parent: Stefan Keller <sfkeller@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Claudio Freire @ 2012-02-28 13:08 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: Wales Wang <wormwang@yahoo.com>; Jeff Janes <jeff.janes@gmail.com>; pgsql-performance; Stephen Frost <sfrost@snowman.net>
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 ?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 13:38 Tomas Vondra <tv@fuzzy.cz>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tomas Vondra @ 2012-02-28 13:38 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Stefan Keller <sfkeller@gmail.com>; Wales Wang <wormwang@yahoo.com>; Jeff Janes <jeff.janes@gmail.com>; pgsql-performance; Stephen Frost <sfrost@snowman.net>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 13:52 Claudio Freire <klaussfreire@gmail.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 13+ messages in thread
From: Claudio Freire @ 2012-02-28 13:52 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: Stefan Keller <sfkeller@gmail.com>; Wales Wang <wormwang@yahoo.com>; Jeff Janes <jeff.janes@gmail.com>; pgsql-performance; Stephen Frost <sfrost@snowman.net>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 14:15 Tomas Vondra <tv@fuzzy.cz>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tomas Vondra @ 2012-02-28 14:15 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Tomas Vondra <tv@fuzzy.cz>; Stefan Keller <sfkeller@gmail.com>; Wales Wang <wormwang@yahoo.com>; Jeff Janes <jeff.janes@gmail.com>; pgsql-performance; Stephen Frost <sfrost@snowman.net>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 14:24 Claudio Freire <klaussfreire@gmail.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 13+ messages in thread
From: Claudio Freire @ 2012-02-28 14:24 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: 回复： [PERFORM] 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:14 Jeff Janes <jeff.janes@gmail.com>
parent: Stefan Keller <sfkeller@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Jeff Janes @ 2012-02-28 15:14 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: Wales Wang <wormwang@yahoo.com>; pgsql-performance; Stephen Frost <sfrost@snowman.net>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 16:05 Tomas Vondra <tv@fuzzy.cz>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tomas Vondra @ 2012-02-28 16:05 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Tomas Vondra <tv@fuzzy.cz>; pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-02-28 16:42 Claudio Freire <klaussfreire@gmail.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 13+ messages in thread
From: Claudio Freire @ 2012-02-28 16:42 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] 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:13 Tomas Vondra <tv@fuzzy.cz>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tomas Vondra @ 2012-03-02 01:13 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-02 02:05 Claudio Freire <klaussfreire@gmail.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 13+ messages in thread
From: Claudio Freire @ 2012-03-02 02:05 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
@ 2012-03-03 00:30 Tomas Vondra <tv@fuzzy.cz>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Tomas Vondra @ 2012-03-03 00:30 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2012-03-03 00:30 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-27 12:46 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory? Wales Wang <wormwang@yahoo.com>
2012-02-28 08:30 ` Stefan Keller <sfkeller@gmail.com>
2012-02-28 13:08 ` Claudio Freire <klaussfreire@gmail.com>
2012-02-28 13:38 ` Tomas Vondra <tv@fuzzy.cz>
2012-02-28 13:52 ` Claudio Freire <klaussfreire@gmail.com>
2012-02-28 14:15 ` Tomas Vondra <tv@fuzzy.cz>
2012-02-28 14:24 ` Claudio Freire <klaussfreire@gmail.com>
2012-02-28 16:05 ` Tomas Vondra <tv@fuzzy.cz>
2012-02-28 16:42 ` Claudio Freire <klaussfreire@gmail.com>
2012-03-02 01:13 ` Tomas Vondra <tv@fuzzy.cz>
2012-03-02 02:05 ` Claudio Freire <klaussfreire@gmail.com>
2012-03-03 00:30 ` Tomas Vondra <tv@fuzzy.cz>
2012-02-28 15:14 ` Jeff Janes <jeff.janes@gmail.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