On Thu, Oct 31, 2013 at 12:43 AM, Leonardo Francalanci <m_lists@yahoo.it> wrote:
Jeff Janes wrote
> True, but that is also true of indexes created in bulk. It all has to > reach disk eventually--
> [...]
> 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.
Honestly, I made a lot of tests in the past, and I don't remember if I tried 15-minute checkpoints + high shared_buffers. That might work. I'm going to try it and see what happens.
You might want to go even beyond 15 minutes.
Jeff Janes wrote
> 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
(as I understand, but I might be wrong): high shared_buffers don't help because in such a random index writing, lots and lots of pages get dirtied, even if the change in the page was minimal. So, in the "15-minute" period, you write the same pages over and over again.
But you write them only if you need to due to a checkpoint, needing new buffers to read in something else that is not already in shared_buffers, or because you are using a buffer-access-strategy that uses a ring. If you make checkpoints longs, it will cut down on the first. If shared_buffers is large enough to contain the active part of the indexes being updated, that should cut down on the second. I don't know if the third is a problem or not--I think copy might try to use a ring-buffer, but I don't if it does that for indexed table.
Even if you have high shared_buffers, the same page will get sync-ed to disk multiple times (at every checkpoint).
If the active part of the indexes is much larger than you can could possibly set shared_buffers to, then there is probably little point in increasing shared_buffers from, say, 1% of the active index size to 8% of it. It only makes sense to increase it if you can do so large enough to cover ~100% of the needed space.
The idea of those "other" indexes is to avoid the random writing, maximizing the writing in sequence, even if that means writing more bytes. In other words: writing a full 8KB is no different than write 20 bytes in a page, as we'll have to sync the whole page anyway...
True, but that is the idea here as well. If you can delay writing the page until 20 bytes of it have been dirtied on 400 different occasions...
I'm not saying we shouldn't think about some kind of insert buffer, but I really doubt that that is going to happen in 9.4 while increasing shared_buffers can be done today, if it works and if you can live with the consequences.
