Re: [Lsf-pc] Linux kernel impact on PostgreSQL performance

On 01/15/2014 02:01 PM, Jan Kara wrote:
> On Wed 15-01-14 12:16:50, Hannu Krosing wrote:
>> On 01/14/2014 06:12 PM, Robert Haas wrote:
>>> This would be pretty similar to copy-on-write, except
>>> without the copying. It would just be
>>> forget-from-the-buffer-pool-on-write. 
>> +1
>>
>> A version of this could probably already be implement using MADV_DONTNEED
>> and MADV_WILLNEED
>>
>> Thet is, just after reading the page in, use MADV_DONTNEED on it. When
>> evicting
>> a clean page, check that it is still in cache and if it is, then
>> MADV_WILLNEED it.
>>
>> Another nice thing to do would be dynamically adjusting kernel
>> dirty_background_ratio
>> and other related knobs in real time based on how many buffers are dirty
>> inside postgresql.
>> Maybe in background writer.
>>
>> Question to LKM folks - will kernel react well to frequent changes to
>> /proc/sys/vm/dirty_*  ?
>> How frequent can they be (every few second? every second? 100Hz ?)
>   So the question is what do you mean by 'react'. We check whether we
> should start background writeback every dirty_writeback_centisecs (5s). We
> will also check whether we didn't exceed the background dirty limit (and
> wake writeback thread) when dirtying pages. However this check happens once
> per several dirtied MB (unless we are close to dirty_bytes).
>
> When writeback is running we check roughly once per second (the logic is
> more complex there but I don't think explaining details would be useful
> here) whether we are below dirty_background_bytes and stop writeback in
> that case.
>
> So changing dirty_background_bytes every few seconds should work
> reasonably, once a second is pushing it and 100 Hz - no way. But I'd also
> note that you have conflicting requirements on the kernel writeback. On one
> hand you want checkpoint data to steadily trickle to disk (well, trickle
> isn't exactly the proper word since if you need to checkpoing 16 GB every 5
> minutes than you need a steady throughput of ~50 MB/s just for
> checkpointing) so you want to set dirty_background_bytes low, on the other
> hand you don't want temporary files to get to disk so you want to set
> dirty_background_bytes high. 
Is it possible to have more fine-grained control over writeback, like
configuring dirty_background_bytes per file system / device (or even
a file or a group of files) ?

If not, then how hard would it be to provide this ?

This is a bit backwards from keeping-the-cache-clean perspective,
but would help a lot with hinting the writer that a big sync is coming.

> And also that changes of
> dirty_background_bytes probably will not take into account other events
> happening on the system (maybe a DB backup is running...). So I'm somewhat
> skeptical you will be able to tune dirty_background_bytes frequently in a
> useful way.
>


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ