Re: Linux kernel impact on PostgreSQL performance - Mailing list pgsql-hackers

From Claudio Freire
Subject Re: Linux kernel impact on PostgreSQL performance
Date
Msg-id CAGTBQpZwtN=7UgQcTZ-87-36oFTjKaWYHis2M4fLqzhPKkEPYw@mail.gmail.com
Whole thread Raw
In response to Re: Linux kernel impact on PostgreSQL performance  (Jim Nasby <jim@nasby.net>)
Responses Re: Linux kernel impact on PostgreSQL performance
List pgsql-hackers
On Wed, Jan 15, 2014 at 1:07 AM, Jim Nasby <jim@nasby.net> wrote:
>>>
>>> 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.
>
>
> Well, to be honest, so do we. Practically zero in fact...

I've used mmap'd files for years, they're great for sharing mutable
memory across unrelated (as in out-of-heirarchy) processes.

And my experience is, that when swapping to-from disk is expectably a
significant percentage of the workload, explicit I/O of even the
dumbest kind far outperforms swap-based I/O.

I've read the kernel code and I'm not 100% sure of why is that, but I
have a suspect.

My completely unproven theory is that swapping is overwhelmed by
near-misses. Ie: a process touches a page, and before it's actually
swapped in, another process touches it too, blocking on the other
process' read. But the second process doesn't account for that page
when evaluating predictive models (ie: read-ahead), so the next I/O by
process 2 is unexpected to the kernel. Then the same with 1. Etc... In
essence, swap, by a fluke of its implementation, fails utterly to
predict the I/O pattern, and results in far sub-optimal reads.

Explicit I/O is free from that effect, all read calls are accountable,
and that makes a difference.

Maybe, if the kernel could be fixed in that respect, you could
consider mmap'd files as a suitable form of temporary storage. But
that would depend on the success and availability of such a fix/patch.



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Create function prototype as part of PG_FUNCTION_INFO_V1
Next
From: Florian Pflug
Date:
Subject: Re: plpgsql.warn_shadow