On 31/10/13 06:46, Jeff Janes wrote:
On Wed, Oct 30, 2013 at 9:54 AM, Leonardo Francalanci
<m_li...@yahoo.it <mailto:m_li...@yahoo.it>> wrote:
Jeff Janes wrote
> The index insertions should be fast until the size of the active
part of
> the indexes being inserted into exceeds shared_buffers by some
amount
> (what
> that amount is would depend on how much dirty data the kernel is
willing
> to
> allow in the page cache before it starts suffering anxiety about
it). If
> you have enough shared_buffers to make that last for 15 minutes,
then you
> shouldn't have a problem inserting with live indexes.
Sooner or later you'll have to checkpoint those shared_buffers...
True, but that is also true of indexes created in bulk. It all has to
reach disk eventually--either the checkpointer writes it out and
fsyncs it, or the background writer or user backends writes it out and
the checkpoint fsyncs it. If bulk creation uses a ring buffer
strategy (I don't know if it does), then it might kick the buffers to
kernel in more or less physical order, which would help the kernel get
them to disk in long sequential writes. Or not. I think that this is
where sorted checkpoint could really help.
> and we are
> talking about GB of data (my understanding is that we change
basically every
> btree page, resulting in re-writing of the whole index).
If the checkpoint interval is as long as the partitioning period, then
hopefully the active index buffers get re-dirtied while protected in
shared_buffers, and only get written to disk once. If the buffers get
read, dirtied, and evicted from a small shared_buffers over and over
again then you are almost guaranteed that will get written to disk
multiple times while they are still hot, unless your kernel is very
aggressive about caching dirty data (which will cause other problems).
Cheers,
Jeff
How about being able to mark indexes:
'MEMORY ONLY' to make them not go to disk
and
'PERSISTENT | TRANSIENT' to mark if they should be recreated on
machine bootup?
or something similar
Cheers,
Gavin
