Re: Sorting writes during checkpoint

[Greg Smith <gsmith(at)gregsmith(dot)com>]

On Wed, 16 Apr 2008, ITAGAKI Takahiro wrote:

> Dirty region of database was probably larger than disk controller's cache.

Might be worthwhile to run with log_checkpoints on and collect some 
statistics there next time you're running these tests.  It's a good habit 
to get other testers into regardless; it's nice to be able to say 
something like "during the 15 checkpoints encountered during this test, 
the largest dirty area was 516MB while the median was 175MB".

> Hmm, but I think we need to copy buffer tags into bgwriter's local memory
> in order to avoid locking taga many times in the sorting. Is it better to
> allocate sorting buffers at the first time and keep and reuse it from then on?

That what I was thinking:  allocate the memory when the background writer 
starts and just always have it there, the allocation you're doing is 
always the same size.  If it's in use 50% of the time anyway (which it is 
if you have checkpoint_completion_target at its default), why introduce 
the risk that an allocation will fail at checkpoint time?  Just allocate 
it once and keep it around.

> It is 0.25% of shared buffers; when shared_buffers is set to 10GB,
> it takes 25MB of process local memory.

Your numbers are probably closer to correct.  I was being pessimistic 
about the size of all the integers just to demonstrate that it's not 
really a significant amount of memory even if they're large.

> If we want to consume less memory for it, RelFileNode in BufferTag could 
> be hashed and packed into an integer

I personally don't feel it's worth making the code any more complicated 
than it needs to be just to save a fraction of a percent of the total 
memory used by the buffer pool.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD