Re: Sorting writes during checkpoint

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