Re: Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 9:22 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On 1/14/14, 11:30 AM, Jeff Janes wrote:
>>
>> I think the "reclaim this page if you need memory but leave it resident if
>> there is no memory pressure" hint would be more useful for temporary working
>> files than for what was being discussed above (shared buffers). When I do
>> work that needs large temporary files, I often see physical write IO spike
>> but physical read IO does not. I interpret that to mean that the temporary
>> data is being written to disk to satisfy either dirty_expire_centisecs or
>> dirty_*bytes, but the data remains in the FS cache and so disk reads are not
>> needed to satisfy it. So a hint that says "this file will never be fsynced
>> so please ignore dirty_*bytes and dirty_expire_centisecs. I will need it
>> again relatively soon (but not after a reboot), but will do so mostly
>> sequentially, so please don't evict this without need, but if you do need to
>> then it is a good candidate" would be good.
>
>
> I also frequently see this, and it has an even larger impact if pgsql_tmp is
> on the same filesystem as WAL. Which *theoretically* shouldn't matter with a
> BBU controller, except that when the kernel suddenly decides your
> *temporary* data needs to hit the media you're screwed.
>
> Though, it also occurs to me... perhaps it would be better for us to simply
> map temp objects to memory and let the kernel swap them out if needed...

Oum... bad idea.

Swap logic has very poor taste for I/O patterns.