Re: Linux kernel impact on PostgreSQL performance

Hi,

I'm the chair for Linux Storage, Filesystem and Memory Management Summit 2014
(LSF/MM). A CFP was sent out last month (https://lwn.net/Articles/575681/)
that you may have seen already.

In recent years we have had at least one topic that was shared between
all three tracks that was lead by a person outside of the usual kernel
development community. I am checking if the PostgreSQL community
would be willing to volunteer someone to lead a topic discussing
PostgreSQL performance with recent kernels or to highlight regressions
or future developments you feel are potentially a problem. With luck
someone suitable is already travelling to the collaboration summit
(http://events.linuxfoundation.org/events/collaboration-summit) and it
would not be too inconvenient to drop in for LSF/MM as well.

There are two reasons why I'm suggesting this. First, PostgreSQL was the
basis of a test used to highlight a scheduler problem around kernel 3.6
but otherwise in my experience it is rare that PostgreSQL is part of a
bug report. I am skeptical this particular bug report was a typical use
case for PostgreSQL (pgbench, read-only, many threads, very small in-memory
database). I wonder why reports related to PostgreSQL are not more common.
One assumption would be that PostgreSQL is perfectly happy with the current
kernel behaviour in which case our discussion here is done.

This brings me to the second reason -- there is evidence
that the PostgreSQL community is not happy with the current
direction of kernel development. The most obvious example is this thread
http://postgresql.1045698.n5.nabble.com/Why-we-are-going-to-have-to-go-DirectIO-td5781471.html
but I suspect there are others. The thread alleges that the kernel community
are in the business of pushing hackish changes into the IO stack without
much thought or testing although the linked article describes a VM and not
a storage problem. I'm not here to debate the kernels regression testing
or development methodology but LSF/MM is one place where a large number
of people involved with the IO layers will be attending. If you have a
concrete complaint then here is a soap box.

Does the PostgreSQL community have a problem with recent kernels,
particularly with respect to the storage, filesystem or memory management
layers? If yes, do you have some data that can highlight this and can you
volunteer someone to represent your interests to the kernel community? Are
current developments in the IO layer counter to the PostgreSQL requirements?
If so, what developments, why are they a problem, do you have a suggested
alternative or some idea of what we should watch out for? The track topic
would be up to you but just as a hint, we'd need something a lot more
concrete than "you should test more".

--
Mel Gorman
SUSE Labs

Mel,

> I'm the chair for Linux Storage, Filesystem and Memory Management Summit 2014
> (LSF/MM). A CFP was sent out last month (https://lwn.net/Articles/575681/)
> that you may have seen already.
>
> In recent years we have had at least one topic that was shared between
> all three tracks that was lead by a person outside of the usual kernel
> development community. I am checking if the PostgreSQL community
> would be willing to volunteer someone to lead a topic discussing
> PostgreSQL performance with recent kernels or to highlight regressions
> or future developments you feel are potentially a problem. With luck
> someone suitable is already travelling to the collaboration summit
> (http://events.linuxfoundation.org/events/collaboration-summit) and it
> would not be too inconvenient to drop in for LSF/MM as well.

We can definitely get someone there. I'll certainly be there; I'm
hoping to get someone who has closer involvement with our kernel
interaction as well.

> There are two reasons why I'm suggesting this. First, PostgreSQL was the
> basis of a test used to highlight a scheduler problem around kernel 3.6
> but otherwise in my experience it is rare that PostgreSQL is part of a
> bug report. I am skeptical this particular bug report was a typical use
> case for PostgreSQL (pgbench, read-only, many threads, very small in-memory
> database). I wonder why reports related to PostgreSQL are not more common.
> One assumption would be that PostgreSQL is perfectly happy with the current
> kernel behaviour in which case our discussion here is done.

To be frank, it's because most people are still running on 2.6.19, and
as a result are completely unaware of recent developments. Second,
because there's no obvious place to complain to ... lkml doesn't welcome
bug reports, and where else do you go?

> Does the PostgreSQL community have a problem with recent kernels,
> particularly with respect to the storage, filesystem or memory management
> layers? If yes, do you have some data that can highlight this and can you
> volunteer someone to represent your interests to the kernel community?

Yes, and yes.

> Are
> current developments in the IO layer counter to the PostgreSQL requirements?
> If so, what developments, why are they a problem, do you have a suggested
> alternative or some idea of what we should watch out for?

Mostly the issue is changes to the IO scheduler which improve one use
case at the expense of others, or set defaults which emphasize desktop
hardware over server hardware.

What also came up with the recent change to LRU is that the Postgres
community apparently has more experience than the Linux community with
buffer-clearing algorithms, and we ought to share that.

> The track topic
> would be up to you but just as a hint, we'd need something a lot more
> concrete than "you should test more".

How about "don't add major IO behavior changes with no
backwards-compatibility switches"? ;-)

Seriously, one thing I'd like to get out of Collab would be a reasonable
regimen for testing database performance on Linux kernels.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus <josh(at)agliodbs(dot)com> wrote:

>> Does the PostgreSQL community have a problem with recent
>> kernels, particularly with respect to the storage, filesystem or
>> memory management layers?

> How about "don't add major IO behavior changes with no
> backwards-compatibility switches"?  ;-)

I notice, Josh, that you didn't mention the problems many people
have run into with Transparent Huge Page defrag and with NUMA
access.  Is that because there *are* configuration options that
allow people to get decent performance once the issue is diagnosed?
It seems like maybe there could be a better way to give a heads-up
on hazards in a new kernel to the database world, but I don't know
quite what that would be.  For all I know, it is already available
if you know where to look.

> Seriously, one thing I'd like to get out of Collab would be a
> reasonable regimen for testing database performance on Linux
> kernels.

... or perhaps you figure this is what would bring such issues to
the community's attention before people are bitten in production
environments?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 01/13/2014 10:51 AM, Kevin Grittner wrote:
>> How about "don't add major IO behavior changes with no
>> backwards-compatibility switches"? ;-)
>
> I notice, Josh, that you didn't mention the problems many people
> have run into with Transparent Huge Page defrag and with NUMA
> access. Is that because there *are* configuration options that
> allow people to get decent performance once the issue is diagnosed?
> It seems like maybe there could be a better way to give a heads-up
> on hazards in a new kernel to the database world, but I don't know
> quite what that would be. For all I know, it is already available
> if you know where to look.

Well, it was the lack of sysctl options which takes the 2Q change from
"annoyance" to "potential disaster". We can't ever get away from the
possibility that the Postgres use-case might be the minority use-case,
and we might have to use non-default options. It's when those options
aren't present *at all* that we're stuck.

However, I agree that a worthwhile thing to talk about is having some
better channel to notify the Postgres (and other DB) communities about
major changes to IO and Memory management.

Wanna go to Collab?

>> Seriously, one thing I'd like to get out of Collab would be a
>> reasonable regimen for testing database performance on Linux
>> kernels.
>
> ... or perhaps you figure this is what would bring such issues to
> the community's attention before people are bitten in production
> environments?

That, too.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> I notice, Josh, that you didn't mention the problems many people
> have run into with Transparent Huge Page defrag and with NUMA
> access.

Amen to that. Actually, I think NUMA can be (mostly?) fixed by
setting zone_reclaim_mode; is there some other problem besides that?

The other thing that comes to mind is the kernel's caching behavior.
We've talked a lot over the years about the difficulties of getting
the kernel to write data out when we want it to and to not write data
out when we don't want it to. When it writes data back to disk too
aggressively, we get lousy throughput because the same page can get
written more than once when caching it for longer would have allowed
write-combining. When it doesn't write data to disk aggressively
enough, we get huge latency spikes at checkpoint time when we call
fsync() and the kernel says "uh, what? you wanted that data *on the
disk*? sorry boss!" and then proceeds to destroy the world by starving
the rest of the system for I/O for many seconds or minutes at a time.
We've made some desultory attempts to use sync_file_range() to improve
things here, but I'm not sure that's really the right tool, and if it
is we don't know how to use it well enough to obtain consistent
positive results.

On a related note, there's also the problem of double-buffering. When
we read a page into shared_buffers, we leave a copy behind in the OS
buffers, and similarly on write-out. It's very unclear what to do
about this, since the kernel and PostgreSQL don't have intimate
knowledge of what each other are doing, but it would be nice to solve
somehow.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On a related note, there's also the problem of double-buffering. When
> we read a page into shared_buffers, we leave a copy behind in the OS
> buffers, and similarly on write-out. It's very unclear what to do
> about this, since the kernel and PostgreSQL don't have intimate
> knowledge of what each other are doing, but it would be nice to solve
> somehow.

There you have a much harder algorithmic problem.

You can basically control duplication with fadvise and WONTNEED. The
problem here is not the kernel and whether or not it allows postgres
to be smart about it. The problem is... what kind of smarts
(algorithm) to use.

On 1/13/14, 2:19 PM, Claudio Freire wrote:
> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On a related note, there's also the problem of double-buffering. When
>> we read a page into shared_buffers, we leave a copy behind in the OS
>> buffers, and similarly on write-out. It's very unclear what to do
>> about this, since the kernel and PostgreSQL don't have intimate
>> knowledge of what each other are doing, but it would be nice to solve
>> somehow.
>
>
> There you have a much harder algorithmic problem.
>
> You can basically control duplication with fadvise and WONTNEED. The
> problem here is not the kernel and whether or not it allows postgres
> to be smart about it. The problem is... what kind of smarts
> (algorithm) to use.

Isn't this a fairly simple matter of when we read a page into shared buffers tell the kernel do forget that page? And a corollary to that for when we dump a page out of shared_buffers (here kernel, please put this back into your cache).
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On 1/13/14, 2:19 PM, Claudio Freire wrote:
>>
>> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
>> wrote:
>>>
>>> On a related note, there's also the problem of double-buffering. When
>>> we read a page into shared_buffers, we leave a copy behind in the OS
>>> buffers, and similarly on write-out. It's very unclear what to do
>>> about this, since the kernel and PostgreSQL don't have intimate
>>> knowledge of what each other are doing, but it would be nice to solve
>>> somehow.
>>
>>
>>
>> There you have a much harder algorithmic problem.
>>
>> You can basically control duplication with fadvise and WONTNEED. The
>> problem here is not the kernel and whether or not it allows postgres
>> to be smart about it. The problem is... what kind of smarts
>> (algorithm) to use.
>
>
> Isn't this a fairly simple matter of when we read a page into shared buffers
> tell the kernel do forget that page? And a corollary to that for when we
> dump a page out of shared_buffers (here kernel, please put this back into
> your cache).

That's my point. In terms of kernel-postgres interaction, it's fairly simple.

What's not so simple, is figuring out what policy to use. Remember,
you cannot tell the kernel to put some page in its page cache without
reading it or writing it. So, once you make the kernel forget a page,
evicting it from shared buffers becomes quite expensive.

On 1/13/14, 2:27 PM, Claudio Freire wrote:
> On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>> On 1/13/14, 2:19 PM, Claudio Freire wrote:
>>>
>>> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
>>> wrote:
>>>>
>>>> On a related note, there's also the problem of double-buffering. When
>>>> we read a page into shared_buffers, we leave a copy behind in the OS
>>>> buffers, and similarly on write-out. It's very unclear what to do
>>>> about this, since the kernel and PostgreSQL don't have intimate
>>>> knowledge of what each other are doing, but it would be nice to solve
>>>> somehow.
>>>
>>>
>>>
>>> There you have a much harder algorithmic problem.
>>>
>>> You can basically control duplication with fadvise and WONTNEED. The
>>> problem here is not the kernel and whether or not it allows postgres
>>> to be smart about it. The problem is... what kind of smarts
>>> (algorithm) to use.
>>
>>
>> Isn't this a fairly simple matter of when we read a page into shared buffers
>> tell the kernel do forget that page? And a corollary to that for when we
>> dump a page out of shared_buffers (here kernel, please put this back into
>> your cache).
>
>
> That's my point. In terms of kernel-postgres interaction, it's fairly simple.
>
> What's not so simple, is figuring out what policy to use. Remember,
> you cannot tell the kernel to put some page in its page cache without
> reading it or writing it. So, once you make the kernel forget a page,
> evicting it from shared buffers becomes quite expensive.

Well, if we were to collaborate with the kernel community on this then presumably we can do better than that for eviction... even to the extent of "here's some data from this range in this file. It's (clean|dirty). Put it in your cache. Just trust me on this."
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Mon, 2014-01-13 at 14:32 -0600, Jim Nasby wrote:
> On 1/13/14, 2:27 PM, Claudio Freire wrote:
> > On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> >> On 1/13/14, 2:19 PM, Claudio Freire wrote:
> >>>
> >>> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> >>> wrote:
> >>>>
> >>>> On a related note, there's also the problem of double-buffering. When
> >>>> we read a page into shared_buffers, we leave a copy behind in the OS
> >>>> buffers, and similarly on write-out. It's very unclear what to do
> >>>> about this, since the kernel and PostgreSQL don't have intimate
> >>>> knowledge of what each other are doing, but it would be nice to solve
> >>>> somehow.
> >>>
> >>>
> >>>
> >>> There you have a much harder algorithmic problem.
> >>>
> >>> You can basically control duplication with fadvise and WONTNEED. The
> >>> problem here is not the kernel and whether or not it allows postgres
> >>> to be smart about it. The problem is... what kind of smarts
> >>> (algorithm) to use.
> >>
> >>
> >> Isn't this a fairly simple matter of when we read a page into shared buffers
> >> tell the kernel do forget that page? And a corollary to that for when we
> >> dump a page out of shared_buffers (here kernel, please put this back into
> >> your cache).
> >
> >
> > That's my point. In terms of kernel-postgres interaction, it's fairly simple.
> >
> > What's not so simple, is figuring out what policy to use. Remember,
> > you cannot tell the kernel to put some page in its page cache without
> > reading it or writing it. So, once you make the kernel forget a page,
> > evicting it from shared buffers becomes quite expensive.
>
> Well, if we were to collaborate with the kernel community on this then
> presumably we can do better than that for eviction... even to the
> extent of "here's some data from this range in this file. It's (clean|
> dirty). Put it in your cache. Just trust me on this."

This should be the madvise() interface (with MADV_WILLNEED and
MADV_DONTNEED) is there something in that interface that is
insufficient?

James

On Mon, Jan 13, 2014 at 5:32 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>>
>> That's my point. In terms of kernel-postgres interaction, it's fairly
>> simple.
>>
>> What's not so simple, is figuring out what policy to use. Remember,
>> you cannot tell the kernel to put some page in its page cache without
>> reading it or writing it. So, once you make the kernel forget a page,
>> evicting it from shared buffers becomes quite expensive.
>
>
> Well, if we were to collaborate with the kernel community on this then
> presumably we can do better than that for eviction... even to the extent of
> "here's some data from this range in this file. It's (clean|dirty). Put it
> in your cache. Just trust me on this."

If I had a kernel developer hat, I'd put it on to say: I don't think
allowing that last bit is wise for a kernel.

It would violate oh-so-many separation rules and open an oh-so-big can-o-worms.

On 2014-01-13 15:15:16 -0500, Robert Haas wrote:
> On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> > I notice, Josh, that you didn't mention the problems many people
> > have run into with Transparent Huge Page defrag and with NUMA
> > access.
>
> Amen to that. Actually, I think NUMA can be (mostly?) fixed by
> setting zone_reclaim_mode; is there some other problem besides that?

I think that fixes some of the worst instances, but I've seen machines
spending horrible amounts of CPU (& BUS) time in page reclaim
nonetheless. If I analyzed it correctly it's in RAM << working set
workloads where RAM is pretty large and most of it is used as page
cache. The kernel ends up spending a huge percentage of time finding and
potentially defragmenting pages when looking for victim buffers.

> On a related note, there's also the problem of double-buffering. When
> we read a page into shared_buffers, we leave a copy behind in the OS
> buffers, and similarly on write-out. It's very unclear what to do
> about this, since the kernel and PostgreSQL don't have intimate
> knowledge of what each other are doing, but it would be nice to solve
> somehow.

I've wondered before if there wouldn't be a chance for postgres to say
"my dear OS, that the file range 0-8192 of file x contains y, no need to
reread" and do that when we evict a page from s_b but I never dared to
actually propose that to kernel people...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Jan 13, 2014, at 15:40, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

> On 2014-01-13 15:15:16 -0500, Robert Haas wrote:
>> On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>>> I notice, Josh, that you didn't mention the problems many people
>>> have run into with Transparent Huge Page defrag and with NUMA
>>> access.
>>
>> Amen to that. Actually, I think NUMA can be (mostly?) fixed by
>> setting zone_reclaim_mode; is there some other problem besides that?
>
> I think that fixes some of the worst instances, but I've seen machines
> spending horrible amounts of CPU (& BUS) time in page reclaim
> nonetheless. If I analyzed it correctly it's in RAM << working set
> workloads where RAM is pretty large and most of it is used as page
> cache. The kernel ends up spending a huge percentage of time finding and
> potentially defragmenting pages when looking for victim buffers.
>
>> On a related note, there's also the problem of double-buffering. When
>> we read a page into shared_buffers, we leave a copy behind in the OS
>> buffers, and similarly on write-out. It's very unclear what to do
>> about this, since the kernel and PostgreSQL don't have intimate
>> knowledge of what each other are doing, but it would be nice to solve
>> somehow.
>
> I've wondered before if there wouldn't be a chance for postgres to say
> "my dear OS, that the file range 0-8192 of file x contains y, no need to
> reread" and do that when we evict a page from s_b but I never dared to
> actually propose that to kernel people...

O_DIRECT was specifically designed to solve the problem of double buffering between applications and the kernel. Why are you not able to use that in these situations?

Cheers,
Trond

On 1/13/14, 2:37 PM, Claudio Freire wrote:
> On Mon, Jan 13, 2014 at 5:32 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>>>
>>> That's my point. In terms of kernel-postgres interaction, it's fairly
>>> simple.
>>>
>>> What's not so simple, is figuring out what policy to use. Remember,
>>> you cannot tell the kernel to put some page in its page cache without
>>> reading it or writing it. So, once you make the kernel forget a page,
>>> evicting it from shared buffers becomes quite expensive.
>>
>>
>> Well, if we were to collaborate with the kernel community on this then
>> presumably we can do better than that for eviction... even to the extent of
>> "here's some data from this range in this file. It's (clean|dirty). Put it
>> in your cache. Just trust me on this."
>
>
> If I had a kernel developer hat, I'd put it on to say: I don't think
> allowing that last bit is wise for a kernel.
>
> It would violate oh-so-many separation rules and open an oh-so-big can-o-worms.

Yeah, if it were me I'd probably want to keep a hash of the page and it's address and only accept putting a page back into the kernel if it matched my hash. Otherwise you'd just have to treat it as a write.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On 2014-01-13 15:53:36 -0500, Trond Myklebust wrote:
> > I've wondered before if there wouldn't be a chance for postgres to say
> > "my dear OS, that the file range 0-8192 of file x contains y, no need to
> > reread" and do that when we evict a page from s_b but I never dared to
> > actually propose that to kernel people...
>
> O_DIRECT was specifically designed to solve the problem of double buffering between applications and the kernel. Why are you not able to use that in these situations?

Because we like to handle the OS handle part of postgres' caching. For
one, it makes servers with several applications/databases much more
realistic without seriously overallocating memory, for another it's a
huge chunk of platform dependent code to get good performance
everywhere.
The above was explicitly not to avoid double buffering but to move a
buffer away from postgres' own buffers to the kernel's buffers once it's
not 100% clear we need it in buffers anymore.

Part of the reason this is being discussed is because previously people
suggested going the direct IO route and some people (most prominently
J. Corbet in http://archives.postgresql.org/message-id/20131204083345.31c60dd1%40lwn.net
) and others disagreed because that goes the route of reinventing
storage layers everywhere without improving the common codepaths.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Jan 13, 2014 at 3:53 PM, Trond Myklebust <trondmy(at)gmail(dot)com> wrote:
> O_DIRECT was specifically designed to solve the problem of double buffering between applications and the kernel. Why are you not able to use that in these situations?

O_DIRECT was apparently designed by a deranged monkey on some serious
mind-controlling substances. But don't take it from me, I have it on
good authority:

http://yarchive.net/comp/linux/o_direct.html

One might even say the best authority.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jan 13, 2014 at 12:32 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:

> On 1/13/14, 2:27 PM, Claudio Freire wrote:
>
>> On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>>
>>> On 1/13/14, 2:19 PM, Claudio Freire wrote:
>>>
>>>>
>>>> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>>
>>>>> On a related note, there's also the problem of double-buffering. When
>>>>> we read a page into shared_buffers, we leave a copy behind in the OS
>>>>> buffers, and similarly on write-out. It's very unclear what to do
>>>>> about this, since the kernel and PostgreSQL don't have intimate
>>>>> knowledge of what each other are doing, but it would be nice to solve
>>>>> somehow.
>>>>>
>>>>
>>>>
>>>>
>>>> There you have a much harder algorithmic problem.
>>>>
>>>> You can basically control duplication with fadvise and WONTNEED. The
>>>> problem here is not the kernel and whether or not it allows postgres
>>>> to be smart about it. The problem is... what kind of smarts
>>>> (algorithm) to use.
>>>>
>>>
>>>
>>> Isn't this a fairly simple matter of when we read a page into shared
>>> buffers
>>> tell the kernel do forget that page? And a corollary to that for when we
>>> dump a page out of shared_buffers (here kernel, please put this back into
>>> your cache).
>>>
>>
>>
>> That's my point. In terms of kernel-postgres interaction, it's fairly
>> simple.
>>
>> What's not so simple, is figuring out what policy to use.
>
>
I think the above is pretty simple for both interaction (allow us to inject
a clean page into the file page cache) and policy (forget it after you hand
it to us, then remember it again when we hand it back to you clean). And I
think it would pretty likely be an improvement over what we currently do.
But I think it is probably the wrong way to get the improvement. I think
the real problem is that we don't trust ourselves to manage more of the
memory ourselves.

As far as I know, we still don't have a publicly disclosable and readily
reproducible test case for the reports of performance degradation when we
have more than 8GB in shared_buffers. If we had one of those, we could
likely reduce the double buffering problem by fixing our own scalability
issues and therefore taking responsibility for more of the data ourselves.

Remember,
>> you cannot tell the kernel to put some page in its page cache without
>> reading it or writing it. So, once you make the kernel forget a page,
>> evicting it from shared buffers becomes quite expensive.
>>
>
> Well, if we were to collaborate with the kernel community on this then
> presumably we can do better than that for eviction... even to the extent of
> "here's some data from this range in this file. It's (clean|dirty). Put it
> in your cache. Just trust me on this."

Which, in the case of it being clean, amounts to "Here is data we don't
want in memory any more because we think it is cold. But we don't trust
ourselves, so please hold on to it anyway." That might be a tough sell to
the kernel people.

Cheers,

Jeff

On Jan 13, 2014, at 16:03, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Mon, Jan 13, 2014 at 3:53 PM, Trond Myklebust <trondmy(at)gmail(dot)com> wrote:
>> O_DIRECT was specifically designed to solve the problem of double buffering between applications and the kernel. Why are you not able to use that in these situations?
>
> O_DIRECT was apparently designed by a deranged monkey on some serious
> mind-controlling substances. But don't take it from me, I have it on
> good authority:
>
> http://yarchive.net/comp/linux/o_direct.html
>
> One might even say the best authority.

You do realise that is 12 year old information, right? …and yes, we have added both aio and vectored operations to O_DIRECT in the meantime.

Meanwhile, no progress has been made on the “non-deranged” interface that authority was advocating.

Cheers,
Trond

Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> Wanna go to Collab?

I don't think that works out for me, but thanks for suggesting it.

I'd be happy to brainstorm with anyone who does go about issues to
discuss; although the ones I keep running into have already been
mentioned.

Regarding the problems others have mentioned, there are a few
features that might be a very big plus for us.  Additional ways of
hinting pages might be very useful.  If we had a way to specify how
many dirty pages were cached in PostgreSQL, the OS would count
those for calculations for writing dirty pages, and we could avoid
the "write avalanche" which is currently so tricky to avoid without
causing repeated writes to the same page.  Or perhaps instead a way
to hint a page as dirty so that the OS could not only count those,
but discard the obsolete data from its cache if it is not already
dirty at the OS level, and lower the write priority if it is dirty
(to improve the odds of collapsing multiple writes).  If there was
a way to use DONTNEED or something similar with the ability to
rescind it if the page was still happened to be in the OS cache,
that might help for when we discard a still-clean page from our
buffers.  And I seem to have a vague memory of there being cases
where the OS is first reading pages when we ask to write them,
which seems like avoidable I/O.  (I'm not sure about that one,
though.)

Also, something like THP support should really have sysctl support
rather than requiring people to put echo commands into scripts and
tie those into runlevel changes.  That's pretty ugly for something
which has turned out to be necessary so often.

I don't get too excited about changes to the default schedulers --
it's been pretty widely known for a long time that DEADLINE or NOOP
perform better than any alternatives for most database loads.
Anyone with a job setting up Linux machines to be used for database
servers should know to cover that.  As long as those two don't get
broken, I'm good.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2014-01-13 12:34:35 -0800, James Bottomley wrote:
> On Mon, 2014-01-13 at 14:32 -0600, Jim Nasby wrote:
> > Well, if we were to collaborate with the kernel community on this then
> > presumably we can do better than that for eviction... even to the
> > extent of "here's some data from this range in this file. It's (clean|
> > dirty). Put it in your cache. Just trust me on this."
>
> This should be the madvise() interface (with MADV_WILLNEED and
> MADV_DONTNEED) is there something in that interface that is
> insufficient?

For one, postgres doesn't use mmap for files (and can't without major
new interfaces). Frequently mmap()/madvise()/munmap()ing 8kb chunks has
horrible consequences for performance/scalability - very quickly you
contend on locks in the kernel.
Also, that will mark that page dirty, which isn't what we want in this
case. One major usecase is transplanting a page comming from postgres'
buffers into the kernel's buffercache because the latter has a much
better chance of properly allocating system resources across independent
applications running.

Oh, and the kernel's page-cache management while far from perfect,
actually scales much better than postgres'.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Jan 13, 2014 at 9:12 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> For one, postgres doesn't use mmap for files (and can't without major
> new interfaces). Frequently mmap()/madvise()/munmap()ing 8kb chunks has
> horrible consequences for performance/scalability - very quickly you
> contend on locks in the kernel.

I may as well dump this in this thread. We've discussed this in person
a few times, including at least once with Ted T'so when he visited
Dublin last year.

The fundamental conflict is that the kernel understands better the
hardware and other software using the same resources, Postgres
understands better its own access patterns. We need to either add
interfaces so Postgres can teach the kernel what it needs about its
access patterns or add interfaces so Postgres can find out what it
needs to know about the hardware context.

The more ambitious and interesting direction is to let Postgres tell
the kernel what it needs to know to manage everything. To do that we
would need the ability to control when pages are flushed out. This is
absolutely necessary to maintain consistency. Postgres would need to
be able to mark pages as unflushable until some point in time in the
future when the journal is flushed. We discussed various ways that
interface could work but it would be tricky to keep it low enough
overhead to be workable.

The less exciting, more conservative option would be to add kernel
interfaces to teach Postgres about things like raid geometries. Then
Postgres could use directio and decide to do prefetching based on the
raid geometry, how much available i/o bandwidth and iops is available,
etc.

Reimplementing i/o schedulers and all the rest of the work that the
kernel provides inside Postgres just seems like something outside our
competency and that none of us is really excited about doing.

--
greg

Everyone,

I am looking for one or more hackers to go to Collab with me to discuss
this. If you think that might be you, please let me know and I'll look
for funding for your travel.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Mon, 2014-01-13 at 22:12 +0100, Andres Freund wrote:
> On 2014-01-13 12:34:35 -0800, James Bottomley wrote:
> > On Mon, 2014-01-13 at 14:32 -0600, Jim Nasby wrote:
> > > Well, if we were to collaborate with the kernel community on this then
> > > presumably we can do better than that for eviction... even to the
> > > extent of "here's some data from this range in this file. It's (clean|
> > > dirty). Put it in your cache. Just trust me on this."
> >
> > This should be the madvise() interface (with MADV_WILLNEED and
> > MADV_DONTNEED) is there something in that interface that is
> > insufficient?
>
> For one, postgres doesn't use mmap for files (and can't without major
> new interfaces).

I understand, that's why you get double buffering: because we can't
replace a page in the range you give us on read/write. However, you
don't have to switch entirely to mmap: you can use mmap/madvise
exclusively for cache control and still use read/write (and still pay
the double buffer penalty, of course). It's only read/write with
directio that would cause problems here (unless you're planning to
switch to DIO?).

> Frequently mmap()/madvise()/munmap()ing 8kb chunks has
> horrible consequences for performance/scalability - very quickly you
> contend on locks in the kernel.

Is this because of problems in the mmap_sem?

> Also, that will mark that page dirty, which isn't what we want in this
> case.

You mean madvise (page_addr)? It shouldn't ... the state of the dirty
bit should only be updated by actual writes. Which MADV_ primitive is
causing the dirty marking, because we might be able to fix it (unless
there's some weird corner case I don't know about).

> One major usecase is transplanting a page comming from postgres'
> buffers into the kernel's buffercache because the latter has a much
> better chance of properly allocating system resources across independent
> applications running.

If you want to share pages between the application and the page cache,
the only known interface is mmap ... perhaps we can discuss how better
to improve mmap for you?

We also do have a way of transplanting pages: it's called splice. How
do the semantics of splice differ from what you need?

> Oh, and the kernel's page-cache management while far from perfect,
> actually scales much better than postgres'.

Well, then, it sounds like the best way forward would be to get
postgress to use the kernel page cache more efficiently.

James

On Mon, 2014-01-13 at 21:29 +0000, Greg Stark wrote:
> On Mon, Jan 13, 2014 at 9:12 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > For one, postgres doesn't use mmap for files (and can't without major
> > new interfaces). Frequently mmap()/madvise()/munmap()ing 8kb chunks has
> > horrible consequences for performance/scalability - very quickly you
> > contend on locks in the kernel.
>
>
> I may as well dump this in this thread. We've discussed this in person
> a few times, including at least once with Ted T'so when he visited
> Dublin last year.
>
> The fundamental conflict is that the kernel understands better the
> hardware and other software using the same resources, Postgres
> understands better its own access patterns. We need to either add
> interfaces so Postgres can teach the kernel what it needs about its
> access patterns or add interfaces so Postgres can find out what it
> needs to know about the hardware context.
>
> The more ambitious and interesting direction is to let Postgres tell
> the kernel what it needs to know to manage everything. To do that we
> would need the ability to control when pages are flushed out. This is
> absolutely necessary to maintain consistency. Postgres would need to
> be able to mark pages as unflushable until some point in time in the
> future when the journal is flushed. We discussed various ways that
> interface could work but it would be tricky to keep it low enough
> overhead to be workable.

So in this case, the question would be what additional information do we
need to exchange that's not covered by the existing interfaces. Between
madvise and splice, we seem to have most of what you want; what's
missing?

> The less exciting, more conservative option would be to add kernel
> interfaces to teach Postgres about things like raid geometries. Then
> Postgres could use directio and decide to do prefetching based on the
> raid geometry, how much available i/o bandwidth and iops is available,
> etc.
>
> Reimplementing i/o schedulers and all the rest of the work that the
> kernel provides inside Postgres just seems like something outside our
> competency and that none of us is really excited about doing.

This would also be a well trodden path ... I believe that some large
database company introduced Direct IO for roughly this purpose.

James

On Mon, Jan 13, 2014 at 03:15:16PM -0500, Robert Haas wrote:
> On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> > I notice, Josh, that you didn't mention the problems many people
> > have run into with Transparent Huge Page defrag and with NUMA
> > access.
>

Ok, there are at least three potential problems there that you may or
may not have run into.

First, THP when it was first introduced was a bit of a disaster. In 3.0,
it was *very* heavy handed and would trash the system reclaiming memory
to satisfy an allocation. When it did this, it would also writeback a
bunch of data and block on it to boot. It was not the smartest move of
all time but was improved over time and in some cases the patches were
also backported by 3.0.101. This is a problem that should have
alleviated over time.

The general symptoms of the problem would be massive stalls and
monitoring the /proc/PID/stack of interesting processes would show it to
be somewhere in do_huge_pmd_anonymous_page -> alloc_page_nodemask ->
try_to_free_pages -> migrate_pages or something similar. You may have
worked around it by disabling THP with a command line switch or
/sys/kernel/mm/transparent_hugepage/enabled in the past.

This is "not meant to happen" any more or at least it has been a while
since a bug was filed against me in this area. There are corner cases
though. If the underlying filesystem is NFS, the problem might still be
experienced.

That is the simple case.

You might have also hit the case where THPages filled with zeros did not
use the zero page. That would have looked like a larger footprint than
anticipated and lead to another range of problems. This is also addressed
since but maybe not recently enough. It's less likely this is your problem
though as I expect you actually use your buffers, not leave them filled
with zeros.

You mention NUMA but that's trickier to figure out that problem without more
context. THP can cause unexpected interleaving between NUMA nodes. Memory
that would have been local on a 4K page boundary becomes remote accesses
when THP is enabled and performance would be hit (maybe 3-5% depending on
the machine). It's not the only possibility though. If memory was being
used sparsely and THP was in use then the overall memory footprint may be
higher than it should be. This potentially would cause allocations to spill
over to remote nodes while kswapd wakes up to reclaim local memory. That
would lead to weird buffer aging inversion problems. This is a hell of a
lot of guessing though and we'd need a better handle on the reproduction
case to pin it down.

> Amen to that. Actually, I think NUMA can be (mostly?) fixed by
> setting zone_reclaim_mode; is there some other problem besides that?
>

Really?

zone_reclaim_mode is often a complete disaster unless the workload is
partitioned to fit within NUMA nodes. On older kernels enabling it would
sometimes cause massive stalls. I'm actually very surprised to hear it
fixes anything and would be interested in hearing more about what sort
of circumstnaces would convince you to enable that thing.

> The other thing that comes to mind is the kernel's caching behavior.
> We've talked a lot over the years about the difficulties of getting
> the kernel to write data out when we want it to and to not write data
> out when we don't want it to.

Is sync_file_range() broke?

> When it writes data back to disk too
> aggressively, we get lousy throughput because the same page can get
> written more than once when caching it for longer would have allowed
> write-combining.

Do you think that is related to dirty_ratio or dirty_writeback_centisecs?
If it's dirty_writeback_centisecs then that would be particularly tricky
because poor interactions there would come down to luck basically.

> When it doesn't write data to disk aggressively
> enough, we get huge latency spikes at checkpoint time when we call
> fsync() and the kernel says "uh, what? you wanted that data *on the
> disk*? sorry boss!" and then proceeds to destroy the world by starving
> the rest of the system for I/O for many seconds or minutes at a time.

Ok, parts of that are somewhat expected. It *may* depend on the
underlying filesystem. Some of them handle fsync better than others. If
you are syncing the whole file though when you call fsync then you are
potentially burned by having to writeback dirty_ratio amounts of memory
which could take a substantial amount of time.

> We've made some desultory attempts to use sync_file_range() to improve
> things here, but I'm not sure that's really the right tool, and if it
> is we don't know how to use it well enough to obtain consistent
> positive results.
>

That implies that either sync_file_range() is broken in some fashion we
(or at least I) are not aware of and that needs kicking.

> On a related note, there's also the problem of double-buffering. When
> we read a page into shared_buffers, we leave a copy behind in the OS
> buffers, and similarly on write-out. It's very unclear what to do
> about this, since the kernel and PostgreSQL don't have intimate
> knowledge of what each other are doing, but it would be nice to solve
> somehow.
>

If it's mapped, clean and you do not need any more than
madvise(MADV_DONTNEED). If you are accessing teh data via a file handle,
then I would expect posix_fadvise(POSIX_FADV_DONTNEED). Offhand, I do
not know how it behaved historically but right now it will usually sync
the data and then discard the pages. I say usually because it will not
necessarily sync if the storage is congested and there is no guarantee it
will be discarded. In older kernels, there was a bug where small calls to
posix_fadvise() would not work at all. This was fixed in 3.9.

The flipside is also meant to hold true. If you know data will be needed
in the near future then posix_fadvise(POSIX_FADV_WILLNEED). Glancing at
the implementation it does a forced read-ahead on the range of pages of
interest. It doesn't look like it would block.

The completely different approach for double buffering is direct IO but
there may be reasons why you are avoiding that and are unhappy with the
interfaces that are meant to work.

Just from the start, it looks like there are a number of problem areas.
Some may be fixed -- in which case we should identify what fixed it, what
kernel version and see can it be verified with a test case or did we
manage to break something else in the process. Other bugs may still
exist because we believe some interface works how users want when it is
in fact unfit for purpose for some reason.

--
Mel Gorman
SUSE Labs

The issue with O_DIRECT is actually a much more general issue ---
namely, database programmers that for various reasons decide they
don't want to go down the O_DIRECT route, but then care about
performance. PostgreSQL is not the only database which is had this
issue.

There are two papers at this year's FAST conference about the "Journal
of Journal" (JoJ) problem, which has been triggered by the use of SQLite on
android handsets, and its write patterns, some of which some folks
(including myself) have characterized as "abusive". (As in, when the
database developer says to the kernel developer, "Doctor, doctor, it
hurts when I do that...")

The program statement for JoJ was introduced in last year's Usenix ATC
conference, I/O Stack Optimizations for Smartphones[1]

[1] https://www.usenix.org/conference/atc13/technical-sessions/presentation/jeong

The high order bit is what's the right thing to do when database
progammers come to kernel engineers saying, we want to do <FOO> and
the performance sucks. Do we say, "Use O_DIRECT, dummy", not
withstanding Linus's past comments on the issue? Or do we have some
general design principles that we tell database engineers that they
should do for better performance, and then all developers for all of
the file systems can then try to optimize for a set of new API's, or
recommended ways of using the existing API's?

Surely the wrong answer is that we do things which encourage people to
create entire new specialized file systems for different databases.
The f2fs file system was essentially created because someone thought
it was easier to create a new file system from sratch instad of trying
to change how SQLite or some other existing file system works.
Hopefully we won't have companies using MySQL and PostgreSQL deciding
they need their own mysqlfs and postgresqlfs! :-)

Cheers,

- Ted

On Mon, Jan 13, 2014 at 06:27:03PM -0200, Claudio Freire wrote:
> On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> > On 1/13/14, 2:19 PM, Claudio Freire wrote:
> >>
> >> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> >> wrote:
> >>>
> >>> On a related note, there's also the problem of double-buffering. When
> >>> we read a page into shared_buffers, we leave a copy behind in the OS
> >>> buffers, and similarly on write-out. It's very unclear what to do
> >>> about this, since the kernel and PostgreSQL don't have intimate
> >>> knowledge of what each other are doing, but it would be nice to solve
> >>> somehow.
> >>
> >>
> >>
> >> There you have a much harder algorithmic problem.
> >>
> >> You can basically control duplication with fadvise and WONTNEED. The
> >> problem here is not the kernel and whether or not it allows postgres
> >> to be smart about it. The problem is... what kind of smarts
> >> (algorithm) to use.
> >
> >
> > Isn't this a fairly simple matter of when we read a page into shared buffers
> > tell the kernel do forget that page? And a corollary to that for when we
> > dump a page out of shared_buffers (here kernel, please put this back into
> > your cache).
>
>
> That's my point. In terms of kernel-postgres interaction, it's fairly simple.
>
> What's not so simple, is figuring out what policy to use. Remember,
> you cannot tell the kernel to put some page in its page cache without
> reading it or writing it. So, once you make the kernel forget a page,
> evicting it from shared buffers becomes quite expensive.

posix_fadvise(POSIX_FADV_WILLNEED) is meant to cover this case by
forcing readahead. If you evict it prematurely then you do get kinda
screwed because you pay the IO cost to read it back in again even if you
had enough memory to cache it. Maybe this is the type of kernel-postgres
interaction that is annoying you.

If you don't evict, the kernel eventually steps in and evicts the wrong
thing. If you do evict and it was unnecessarily you pay an IO cost.

That could be something we look at. There are cases buried deep in the
VM where pages get shuffled to the end of the LRU and get tagged for
reclaim as soon as possible. Maybe you need access to something like
that via posix_fadvise to say "reclaim this page if you need memory but
leave it resident if there is no memory pressure" or something similar.
Not exactly sure what that interface would look like or offhand how it
could be reliably implemented.

--
Mel Gorman
SUSE Labs

On Mon 13-01-14 22:26:45, Mel Gorman wrote:
> The flipside is also meant to hold true. If you know data will be needed
> in the near future then posix_fadvise(POSIX_FADV_WILLNEED). Glancing at
> the implementation it does a forced read-ahead on the range of pages of
> interest. It doesn't look like it would block.
That's not quite true. POSIX_FADV_WILLNEED still needs to map logical
file offsets to physical disk blocks and create IO requests. This happens
synchronously. So if your disk is congested and relevant metadata is out of
cache, or we simply run out of free IO requests, POSIX_FADV_WILLNEED can
block for a significant amount of time.

Honza
--
Jan Kara <jack(at)suse(dot)cz>
SUSE Labs, CR

On 2014-01-13 14:19:56 -0800, James Bottomley wrote:
> > Frequently mmap()/madvise()/munmap()ing 8kb chunks has
> > horrible consequences for performance/scalability - very quickly you
> > contend on locks in the kernel.
>
> Is this because of problems in the mmap_sem?

It's been a while since I looked at it, but yes, mmap_sem was part of
it. I also seem to recall the amount of IPIs increasing far too much for
it to be practical, but I am not sure anymore.

> > Also, that will mark that page dirty, which isn't what we want in this
> > case.
>
> You mean madvise (page_addr)? It shouldn't ... the state of the dirty
> bit should only be updated by actual writes. Which MADV_ primitive is
> causing the dirty marking, because we might be able to fix it (unless
> there's some weird corner case I don't know about).

Not the madvise() itself, but transplanting the buffer from postgres'
buffers to the mmap() area of the underlying file would, right?

> We also do have a way of transplanting pages: it's called splice. How
> do the semantics of splice differ from what you need?

Hm. I don't really see how splice would allow us to seed the kernel's
pagecache with content *without* marking the page as dirty in the
kernel.
We don't need zero-copy IO here, the important thing is just to fill the
pagecache with content without a) rereading the page from disk b)
marking the page as dirty.

> > One major usecase is transplanting a page comming from postgres'
> > buffers into the kernel's buffercache because the latter has a much
> > better chance of properly allocating system resources across independent
> > applications running.
>
> If you want to share pages between the application and the page cache,
> the only known interface is mmap ... perhaps we can discuss how better
> to improve mmap for you?

I think purely using mmap() is pretty unlikely to work out - there's
just too many constraints about when a page is allowed to be written out
(e.g. it's interlocked with postgres' write ahead log). I also think
that for many practical purposes using mmap() would result in an absurd
number of mappings or mapping way too huge areas; e.g. large btree
indexes are usually accessed in a quite fragmented manner.

> > Oh, and the kernel's page-cache management while far from perfect,
> > actually scales much better than postgres'.
>
> Well, then, it sounds like the best way forward would be to get
> postgress to use the kernel page cache more efficiently.

No arguments there, although working on postgres scalability is a good
idea as well ;)

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon 13-01-14 22:36:06, Mel Gorman wrote:
> On Mon, Jan 13, 2014 at 06:27:03PM -0200, Claudio Freire wrote:
> > On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> > > On 1/13/14, 2:19 PM, Claudio Freire wrote:
> > >>
> > >> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> > >> wrote:
> > >>>
> > >>> On a related note, there's also the problem of double-buffering. When
> > >>> we read a page into shared_buffers, we leave a copy behind in the OS
> > >>> buffers, and similarly on write-out. It's very unclear what to do
> > >>> about this, since the kernel and PostgreSQL don't have intimate
> > >>> knowledge of what each other are doing, but it would be nice to solve
> > >>> somehow.
> > >>
> > >>
> > >>
> > >> There you have a much harder algorithmic problem.
> > >>
> > >> You can basically control duplication with fadvise and WONTNEED. The
> > >> problem here is not the kernel and whether or not it allows postgres
> > >> to be smart about it. The problem is... what kind of smarts
> > >> (algorithm) to use.
> > >
> > >
> > > Isn't this a fairly simple matter of when we read a page into shared buffers
> > > tell the kernel do forget that page? And a corollary to that for when we
> > > dump a page out of shared_buffers (here kernel, please put this back into
> > > your cache).
> >
> >
> > That's my point. In terms of kernel-postgres interaction, it's fairly simple.
> >
> > What's not so simple, is figuring out what policy to use. Remember,
> > you cannot tell the kernel to put some page in its page cache without
> > reading it or writing it. So, once you make the kernel forget a page,
> > evicting it from shared buffers becomes quite expensive.
>
> posix_fadvise(POSIX_FADV_WILLNEED) is meant to cover this case by
> forcing readahead. If you evict it prematurely then you do get kinda
> screwed because you pay the IO cost to read it back in again even if you
> had enough memory to cache it. Maybe this is the type of kernel-postgres
> interaction that is annoying you.
>
> If you don't evict, the kernel eventually steps in and evicts the wrong
> thing. If you do evict and it was unnecessarily you pay an IO cost.
>
> That could be something we look at. There are cases buried deep in the
> VM where pages get shuffled to the end of the LRU and get tagged for
> reclaim as soon as possible. Maybe you need access to something like
> that via posix_fadvise to say "reclaim this page if you need memory but
> leave it resident if there is no memory pressure" or something similar.
> Not exactly sure what that interface would look like or offhand how it
> could be reliably implemented.
Well, kernel managing user space cache postgres guys talk about looks
pretty much like what "volatile range" patches are trying to achieve.

Note to postgres guys: I think you should have a look at the proposed
'vrange' system call. The latest posting is here:
http://www.spinics.net/lists/linux-mm/msg67328.html. It contains a rather
detailed description of the feature. And if the feature looks good to you,
you can add your 'me to' plus if anyone would be willing to try that out
with postgress that would be most welcome (although I understand you might
not want to burn your time on experimental kernel feature).

Honza
--
Jan Kara <jack(at)suse(dot)cz>
SUSE Labs, CR

On Mon, Jan 13, 2014 at 11:38:44PM +0100, Jan Kara wrote:
> On Mon 13-01-14 22:26:45, Mel Gorman wrote:
> > The flipside is also meant to hold true. If you know data will be needed
> > in the near future then posix_fadvise(POSIX_FADV_WILLNEED). Glancing at
> > the implementation it does a forced read-ahead on the range of pages of
> > interest. It doesn't look like it would block.
> That's not quite true. POSIX_FADV_WILLNEED still needs to map logical
> file offsets to physical disk blocks and create IO requests. This happens
> synchronously. So if your disk is congested and relevant metadata is out of
> cache, or we simply run out of free IO requests, POSIX_FADV_WILLNEED can
> block for a significant amount of time.
>

Umm, yes, you're right. It also potentially stalls allocating the pages
up front even though it will only try and direct reclaim pages once.
That can stall in some circumstances, particularly if there are a number
of processes trying to reclaim memory.

That kinda sucks though. One point of discussion would be to check if
this is an interface that can be used and if so, is it required to never
block and if so is there something we can do about it -- queue the IO
asynchronously if you can but if the kernel would block then do not bother.
That does mean that fadvise is not guaranteeing that the pages will be
resident in the future but it was not the intent of the interface
anyway.

--
Mel Gorman
SUSE Labs

On 01/13/2014 02:26 PM, Mel Gorman wrote:
> Really?
>
> zone_reclaim_mode is often a complete disaster unless the workload is
> partitioned to fit within NUMA nodes. On older kernels enabling it would
> sometimes cause massive stalls. I'm actually very surprised to hear it
> fixes anything and would be interested in hearing more about what sort
> of circumstnaces would convince you to enable that thing.

So the problem with the default setting is that it pretty much isolates
all FS cache for PostgreSQL to whichever socket the postmaster is
running on, and makes the other FS cache unavailable. This means that,
for example, if you have two memory banks, then only one of them is
available for PostgreSQL filesystem caching ... essentially cutting your
available cache in half.

And however slow moving cached pages between memory banks is, it's an
order of magnitude faster than moving them from disk. But this isn't
how the NUMA stuff is configured; it seems to assume that it's less
expensive to get pages from disk than to move them between banks, so
whatever you've got cached on the other bank, it flushes it to disk as
fast as possible. I understand the goal was to make memory usage local
to the processors stuff was running on, but that includes an implicit
assumption that no individual process will ever want more than one
memory bank worth of cache.

So disabling all of the NUMA optimizations is the way to go for any
workload I personally deal with.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 01/13/2014 09:53 PM, Trond Myklebust wrote:
> On Jan 13, 2014, at 15:40, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>
>> On 2014-01-13 15:15:16 -0500, Robert Haas wrote:
>>> On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>>>> I notice, Josh, that you didn't mention the problems many people
>>>> have run into with Transparent Huge Page defrag and with NUMA
>>>> access.
>>> Amen to that. Actually, I think NUMA can be (mostly?) fixed by
>>> setting zone_reclaim_mode; is there some other problem besides that?
>> I think that fixes some of the worst instances, but I've seen machines
>> spending horrible amounts of CPU (& BUS) time in page reclaim
>> nonetheless. If I analyzed it correctly it's in RAM << working set
>> workloads where RAM is pretty large and most of it is used as page
>> cache. The kernel ends up spending a huge percentage of time finding and
>> potentially defragmenting pages when looking for victim buffers.
>>
>>> On a related note, there's also the problem of double-buffering. When
>>> we read a page into shared_buffers, we leave a copy behind in the OS
>>> buffers, and similarly on write-out. It's very unclear what to do
>>> about this, since the kernel and PostgreSQL don't have intimate
>>> knowledge of what each other are doing, but it would be nice to solve
>>> somehow.
>> I've wondered before if there wouldn't be a chance for postgres to say
>> "my dear OS, that the file range 0-8192 of file x contains y, no need to
>> reread" and do that when we evict a page from s_b but I never dared to
>> actually propose that to kernel people...
> O_DIRECT was specifically designed to solve the problem of double buffering
> between applications and the kernel. Why are you not able to use that in these situations?
What is asked is the opposite of O_DIRECT - the write from a buffer inside
postgresql to linux *buffercache* and telling linux that it is the same
as what
is currently on disk, so don't bother to write it back ever.

This would avoid current double-buffering between postgresql and linux
buffer caches while still making use of linux cache when possible.

The use case is pages that postgresql has moved into its buffer cache
but which it has not modified. They will at some point be evicted from the
postgresql cache, but it is likely that they will still be needed
sometime soon,
so what is required is "writing them back" to the original file, only
they should
not really be written - or marked dirty to be written later - more
levels than
just to the linux cache, as they *already* are on the disk.

It is probably ok to put them in the LRU position as they are "written"
out from postgresql, though it may be better if we get some more control
over
where in the LRU order they would be placed. It may make sense to put them
there based on when they were last read while residing inside postgresql
cache

Cheers

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

On Mon, Jan 13, 2014 at 7:36 PM, Mel Gorman <mgorman(at)suse(dot)de> wrote:
> That could be something we look at. There are cases buried deep in the
> VM where pages get shuffled to the end of the LRU and get tagged for
> reclaim as soon as possible. Maybe you need access to something like
> that via posix_fadvise to say "reclaim this page if you need memory but
> leave it resident if there is no memory pressure" or something similar.
> Not exactly sure what that interface would look like or offhand how it
> could be reliably implemented.

I don't see a reason not to make this behavior the default for WONTNEED.

On 1/13/14, 4:44 PM, Andres Freund wrote:
>>> > > One major usecase is transplanting a page comming from postgres'
>>> > >buffers into the kernel's buffercache because the latter has a much
>>> > >better chance of properly allocating system resources across independent
>>> > >applications running.
>> >
>> >If you want to share pages between the application and the page cache,
>> >the only known interface is mmap ... perhaps we can discuss how better
>> >to improve mmap for you?
> I think purely using mmap() is pretty unlikely to work out - there's
> just too many constraints about when a page is allowed to be written out
> (e.g. it's interlocked with postgres' write ahead log). I also think
> that for many practical purposes using mmap() would result in an absurd
> number of mappings or mapping way too huge areas; e.g. large btree
> indexes are usually accessed in a quite fragmented manner.

Which brings up another interesting area^Wcan-of-worms: the database is implementing journaling on top of a filesystem that's probably also journaling. And it's going to get worse: a Segate researcher presented at RICon East last year that the next generation (or maybe the one after that) of spinning rust will use "shingling", which means that the drive can't write randomly. So now the drive will ALSO have to journal. And of course SSDs already do this.

So now there's *three* pieces of software all doing the exact same thing, none of which are able to coordinate with each other.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On 1/13/14, 3:04 PM, Jeff Janes wrote:
>
> I think the above is pretty simple for both interaction (allow us to inject a clean page into the file page cache) and policy (forget it after you hand it to us, then remember it again when we hand it back to you clean). And I think it would pretty likely be an improvement over what we currently do. But I think it is probably the wrong way to get the improvement. I think the real problem is that we don't trust ourselves to manage more of the memory ourselves.
>
> As far as I know, we still don't have a publicly disclosable and readily reproducible test case for the reports of performance degradation when we have more than 8GB in shared_buffers. If we had one of those, we could likely reduce the double buffering problem by fixing our own scalability issues and therefore taking responsibility for more of the data ourselves.

While I agree we need to fix the 8GB limit, we're always going to have a problem here unless we put A LOT of new abilities into our memory capabilities. Like, for example, stealing memory from shared buffers to support a sort. Or implementing a system-wide limit on WORK_MEM. Or both.

I would much rather teach the OS and Postgres to work together on memory management than for us to try and re-implement everything the OS has already done for us.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Jan 13, 2014, at 19:03, Hannu Krosing <hannu(at)2ndQuadrant(dot)com> wrote:

> On 01/13/2014 09:53 PM, Trond Myklebust wrote:
>> On Jan 13, 2014, at 15:40, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>>
>>> On 2014-01-13 15:15:16 -0500, Robert Haas wrote:
>>>> On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>>>>> I notice, Josh, that you didn't mention the problems many people
>>>>> have run into with Transparent Huge Page defrag and with NUMA
>>>>> access.
>>>> Amen to that. Actually, I think NUMA can be (mostly?) fixed by
>>>> setting zone_reclaim_mode; is there some other problem besides that?
>>> I think that fixes some of the worst instances, but I've seen machines
>>> spending horrible amounts of CPU (& BUS) time in page reclaim
>>> nonetheless. If I analyzed it correctly it's in RAM << working set
>>> workloads where RAM is pretty large and most of it is used as page
>>> cache. The kernel ends up spending a huge percentage of time finding and
>>> potentially defragmenting pages when looking for victim buffers.
>>>
>>>> On a related note, there's also the problem of double-buffering. When
>>>> we read a page into shared_buffers, we leave a copy behind in the OS
>>>> buffers, and similarly on write-out. It's very unclear what to do
>>>> about this, since the kernel and PostgreSQL don't have intimate
>>>> knowledge of what each other are doing, but it would be nice to solve
>>>> somehow.
>>> I've wondered before if there wouldn't be a chance for postgres to say
>>> "my dear OS, that the file range 0-8192 of file x contains y, no need to
>>> reread" and do that when we evict a page from s_b but I never dared to
>>> actually propose that to kernel people...
>> O_DIRECT was specifically designed to solve the problem of double buffering
>> between applications and the kernel. Why are you not able to use that in these situations?
> What is asked is the opposite of O_DIRECT - the write from a buffer inside
> postgresql to linux *buffercache* and telling linux that it is the same
> as what
> is currently on disk, so don't bother to write it back ever.

I don’t understand. Are we talking about mmap()ed files here? Why would the kernel be trying to write back pages that aren’t dirty?

> This would avoid current double-buffering between postgresql and linux
> buffer caches while still making use of linux cache when possible.
>
> The use case is pages that postgresql has moved into its buffer cache
> but which it has not modified. They will at some point be evicted from the
> postgresql cache, but it is likely that they will still be needed
> sometime soon,
> so what is required is "writing them back" to the original file, only
> they should
> not really be written - or marked dirty to be written later - more
> levels than
> just to the linux cache, as they *already* are on the disk.
>
> It is probably ok to put them in the LRU position as they are "written"
> out from postgresql, though it may be better if we get some more control
> over
> where in the LRU order they would be placed. It may make sense to put them
> there based on when they were last read while residing inside postgresql
> cache
>
> Cheers
>
>
> --
> Hannu Krosing
> PostgreSQL Consultant
> Performance, Scalability and High Availability
> 2ndQuadrant Nordic OÜ

On 1/13/14, 4:47 PM, Jan Kara wrote:
> Note to postgres guys: I think you should have a look at the proposed
> 'vrange' system call. The latest posting is here:
> http://www.spinics.net/lists/linux-mm/msg67328.html. It contains a rather
> detailed description of the feature. And if the feature looks good to you,
> you can add your 'me to' plus if anyone would be willing to try that out
> with postgress that would be most welcome (although I understand you might
> not want to burn your time on experimental kernel feature).

I don't think that would help us with buffers unless we switched to MMAP (which is a huge change), but this part is interesting:

"* Opportunistic freeing of memory that may be quickly reused. Minchan
has done a malloc implementation where free() marks the pages as
volatile, allowing the kernel to reclaim under pressure. This avoids the
unmapping and remapping of anonymous pages on free/malloc."

Postgres has it's own memory management on top of malloc that gives us memory contexts; some of those contexts get destroyed frequently. Allowing the kernel to reclaim that free'd memory in the background might be a performance win for us.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Mon, Jan 13, 2014 at 09:29:02PM +0000, Greg Stark wrote:
> On Mon, Jan 13, 2014 at 9:12 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > For one, postgres doesn't use mmap for files (and can't without major
> > new interfaces). Frequently mmap()/madvise()/munmap()ing 8kb chunks has
> > horrible consequences for performance/scalability - very quickly you
> > contend on locks in the kernel.
>
> I may as well dump this in this thread. We've discussed this in person
> a few times, including at least once with Ted T'so when he visited
> Dublin last year.
>
> The fundamental conflict is that the kernel understands better the
> hardware and other software using the same resources, Postgres
> understands better its own access patterns. We need to either add
> interfaces so Postgres can teach the kernel what it needs about its
> access patterns or add interfaces so Postgres can find out what it
> needs to know about the hardware context.

In my experience applications don't need to know anything about the
underlying storage hardware - all they need is for someone to
tell them the optimal IO size and alignment to use.

> The more ambitious and interesting direction is to let Postgres tell
> the kernel what it needs to know to manage everything. To do that we
> would need the ability to control when pages are flushed out. This is
> absolutely necessary to maintain consistency. Postgres would need to
> be able to mark pages as unflushable until some point in time in the
> future when the journal is flushed. We discussed various ways that
> interface could work but it would be tricky to keep it low enough
> overhead to be workable.

IMO, the concept of allowing userspace to pin dirty page cache
pages in memory is just asking for trouble. Apart from the obvious
memory reclaim and OOM issues, some filesystems won't be able to
move their journals forward until the data is flushed. i.e. ordered
mode data writeback on ext3 will have all sorts of deadlock issues
that result from pinning pages and then issuing fsync() on another
file which will block waiting for the pinned pages to be flushed.

Indeed, what happens if you do pin_dirty_pages(fd); .... fsync(fd);?
If fsync() blocks because there are pinned pages, and there's no
other thread to unpin them, then that code just deadlocked. If
fsync() doesn't block and skips the pinned pages, then we haven't
done an fsync() at all, and so violated the expectation that users
have that after fsync() returns their data is safe on disk. And if
we return an error to fsync(), then what the hell does the user do
if it is some other application we don't know about that has pinned
the pages? And if the kernel unpins them after some time, then we
just violated the application's consistency guarantees....

Hmmmm. What happens if the process crashes after pinning the dirty
pages? How do we even know what process pinned the dirty pages so
we can clean up after it? What happens if the same page is pinned by
multiple processes? What happens on truncate/hole punch if the
partial pages in the range that need to be zeroed and written are
pinned? What happens if we do direct IO to a range with pinned,
unflushable pages in the page cache?

These are all complex corner cases that are introduced by allowing
applications to pin dirty pages in memory. I've only spent a few
minutes coming up with these, and I'm sure there's more of them.
As such, I just don't see that allowing userspace to pin dirty
page cache pages in memory being a workable solution.

> The less exciting, more conservative option would be to add kernel
> interfaces to teach Postgres about things like raid geometries. Then

/sys/block/<dev>/queue/* contains all the information that is
exposed to filesystems to optimise layout for storage geometry.
Some filesystems can already expose the relevant parts of this
information to userspace, others don't.

What I think we really need to provide is a generic interface
similar to the old XFS_IOC_DIOINFO ioctl that can be used to
expose IO characteristics to applications in a simple, easy to
gather manner. Something like:

struct io_info {
u64 minimum_io_size; /* sector size */
u64 maximum_io_size; /* currently 2GB */
u64 optimal_io_size; /* stripe unit/width */
u64 optimal_io_alignment; /* stripe unit/width */
u64 mem_alignment; /* PAGE_SIZE */
u32 queue_depth; /* max IO concurrency */
};

> Postgres could use directio and decide to do prefetching based on the
> raid geometry,

Underlying storage array raid geometry and optimal IO sizes for the
filesystem may be different. Hence you want what the filesystem
considers optimal, not what the underlying storage is configured
with. Indeed, a filesystem might be able to supply per-file IO
characteristics depending on where it is located in the filesystem
(think tiered storage)....

> how much available i/o bandwidth and iops is available,
> etc.

The kernel doesn't really know what a device is capable of - it can
only measure what the current IO workload is achieving - and it can
change based on the IO workload characteristics. Hence applications
can track this as well as the kernel does if they need this
information for any reason.

> Reimplementing i/o schedulers and all the rest of the work that the

Nobody needs to reimplement IO schedulers in userspace. Direct IO
still goes through the block layers where all that merging and
IO scheduling occurs.

> kernel provides inside Postgres just seems like something outside our
> competency and that none of us is really excited about doing.

That argument goes both ways - providing fine-grained control over
the page cache contents to userspace doesn't get me excited, either.
In fact, it scares the living daylights out of me. It's complex,
it's fragile and it introduces constraints into everything we do in
the kernel. Any one of those reasons is grounds for saying no to a
proposal, but this idea hits the trifecta....

I'm not saying that O_DIRECT is easy or perfect, but it seems to me
to be a more robust, secure, maintainable and simpler solution than
trying to give applications direct control over complex internal
kernel structures and algorithms.

Cheers,

Dave.
--
Dave Chinner
david(at)fromorbit(dot)com

On Mon, 2014-01-13 at 19:48 -0500, Trond Myklebust wrote:
> On Jan 13, 2014, at 19:03, Hannu Krosing <hannu(at)2ndQuadrant(dot)com> wrote:
>
> > On 01/13/2014 09:53 PM, Trond Myklebust wrote:
> >> On Jan 13, 2014, at 15:40, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> >>
> >>> On 2014-01-13 15:15:16 -0500, Robert Haas wrote:
> >>>> On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> >>>>> I notice, Josh, that you didn't mention the problems many people
> >>>>> have run into with Transparent Huge Page defrag and with NUMA
> >>>>> access.
> >>>> Amen to that. Actually, I think NUMA can be (mostly?) fixed by
> >>>> setting zone_reclaim_mode; is there some other problem besides that?
> >>> I think that fixes some of the worst instances, but I've seen machines
> >>> spending horrible amounts of CPU (& BUS) time in page reclaim
> >>> nonetheless. If I analyzed it correctly it's in RAM << working set
> >>> workloads where RAM is pretty large and most of it is used as page
> >>> cache. The kernel ends up spending a huge percentage of time finding and
> >>> potentially defragmenting pages when looking for victim buffers.
> >>>
> >>>> On a related note, there's also the problem of double-buffering. When
> >>>> we read a page into shared_buffers, we leave a copy behind in the OS
> >>>> buffers, and similarly on write-out. It's very unclear what to do
> >>>> about this, since the kernel and PostgreSQL don't have intimate
> >>>> knowledge of what each other are doing, but it would be nice to solve
> >>>> somehow.
> >>> I've wondered before if there wouldn't be a chance for postgres to say
> >>> "my dear OS, that the file range 0-8192 of file x contains y, no need to
> >>> reread" and do that when we evict a page from s_b but I never dared to
> >>> actually propose that to kernel people...
> >> O_DIRECT was specifically designed to solve the problem of double buffering
> >> between applications and the kernel. Why are you not able to use that in these situations?
> > What is asked is the opposite of O_DIRECT - the write from a buffer inside
> > postgresql to linux *buffercache* and telling linux that it is the same
> > as what
> > is currently on disk, so don't bother to write it back ever.
>
> I don’t understand. Are we talking about mmap()ed files here? Why
> would the kernel be trying to write back pages that aren’t dirty?

No ... if I have it right, it's pretty awful: they want to do a read of
a file into a user provided buffer, thus obtaining a page cache entry
and a copy in their userspace buffer, then insert the page of the user
buffer back into the page cache as the page cache page ... that's right,
isn't it postgress people?

Effectively you end up with buffered read/write that's also mapped into
the page cache. It's a pretty awful way to hack around mmap.

James

On 2014-01-13 17:13:51 -0800, James Bottomley wrote:
> a file into a user provided buffer, thus obtaining a page cache entry
> and a copy in their userspace buffer, then insert the page of the user
> buffer back into the page cache as the page cache page ... that's right,
> isn't it postgress people?

Pretty much, yes. We'd probably hint (*advise(DONTNEED)) that the page
isn't needed anymore when reading. And we'd normally write if the page
is dirty.

> Effectively you end up with buffered read/write that's also mapped into
> the page cache. It's a pretty awful way to hack around mmap.

Well, the problem is that you can't really use mmap() for the things we
do. Postgres' durability works by guaranteeing that our journal entries
(called WAL := Write Ahead Log) are written & synced to disk before the
corresponding entries of tables and indexes reach the disk. That also
allows to group together many random-writes into a few contiguous writes
fdatasync()ed at once. Only during a checkpointing phase the big bulk of
the data is then (slowly, in the background) synced to disk.

I don't see how that's doable with holding all pages in mmap()ed
buffers.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Jan 13, 2014 at 03:24:38PM -0800, Josh Berkus wrote:
> On 01/13/2014 02:26 PM, Mel Gorman wrote:
> > Really?
> >
> > zone_reclaim_mode is often a complete disaster unless the workload is
> > partitioned to fit within NUMA nodes. On older kernels enabling it would
> > sometimes cause massive stalls. I'm actually very surprised to hear it
> > fixes anything and would be interested in hearing more about what sort
> > of circumstnaces would convince you to enable that thing.
>
> So the problem with the default setting is that it pretty much isolates
> all FS cache for PostgreSQL to whichever socket the postmaster is
> running on, and makes the other FS cache unavailable. This means that,
> for example, if you have two memory banks, then only one of them is
> available for PostgreSQL filesystem caching ... essentially cutting your
> available cache in half.

No matter what default NUMA allocation policy we set, there will be
an application for which that behaviour is wrong. As such, we've had
tools for setting application specific NUMA policies for quite a few
years now. e.g:

$ man 8 numactl
....
--interleave=nodes, -i nodes
Set a memory interleave policy. Memory will be
allocated using round robin on nodes. When memory
cannot be allocated on the current interleave target
fall back to other nodes. Multiple nodes may be
specified on --interleave, --membind and
--cpunodebind.

Cheers,

Dave.
--
Dave Chinner
david(at)fromorbit(dot)com

On 01/13/2014 05:30 PM, Dave Chinner wrote:
> On Mon, Jan 13, 2014 at 03:24:38PM -0800, Josh Berkus wrote:
> No matter what default NUMA allocation policy we set, there will be
> an application for which that behaviour is wrong. As such, we've had
> tools for setting application specific NUMA policies for quite a few
> years now. e.g:

Yeah, that's why I personally regard the NUMA stuff as just an
information problem; there's an easy configuration variable, and you
can't please everyone (and our project would hardly be one to point
fingers about sub-optimal default configurations). I was responding to
a question of "what's wrong with the default setting?"

Personally, I have my doubts that the NUMA memory isolation, as
currently implemented, accomplishes what it wants to do. But that's a
completely different discussion.

The real issue there was that our users had never heard of this change
until suddenly half their RAM became unavailable. So the solution is
for our project to somehow have these kinds of changes flagged for our
attention so that we can update our docs. The kernel change list is
quite volumnious, and it's very easy to miss changes of significance in
it. The easiest way to do this is going to be getting involved in
kernel-database performance testing.

Of course, we are annoyed that we finally removed the main reason to
modify sysctl.conf (SHMMAX), and here we are needing to advise users
about sysctl again. :-(

I'm much more bothered by the introduction of 2Q logic, since that comes
without a configuration variable to modify its behavior.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 2014-01-13 10:56:00 -0800, Josh Berkus wrote:
> Well, it was the lack of sysctl options which takes the 2Q change from
> "annoyance" to "potential disaster". We can't ever get away from the
> possibility that the Postgres use-case might be the minority use-case,
> and we might have to use non-default options. It's when those options
> aren't present *at all* that we're stuck.

Unless I am missing something the kernel's going further *away* from a
simple 2q system, not the contrary.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 01/13/2014 05:48 PM, Andres Freund wrote:
> On 2014-01-13 10:56:00 -0800, Josh Berkus wrote:
>> Well, it was the lack of sysctl options which takes the 2Q change from
>> "annoyance" to "potential disaster". We can't ever get away from the
>> possibility that the Postgres use-case might be the minority use-case,
>> and we might have to use non-default options. It's when those options
>> aren't present *at all* that we're stuck.
>
> Unless I am missing something the kernel's going further *away* from a
> simple 2q system, not the contrary.

Well, they implemented a 2Q system and deliberately offered no sysctl
variables to modify its behavior. Now they're talking about
implementing an ARC system -- which we know the perils of -- again,
without any configuration variables in case the default behavior doesn't
work for everyone. And it's highly unlikely that an ARC which is
designed for desktop and/or file server users -- let alone mobile users
-- is going to be optimal for PostgreSQL out of the box.

In fact, I'd assert that it's flat-out impossible to engineer an ARC
which will work for multiple different use cases without user-level
configuration.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Tue, Jan 14, 2014 at 02:26:25AM +0100, Andres Freund wrote:
> On 2014-01-13 17:13:51 -0800, James Bottomley wrote:
> > a file into a user provided buffer, thus obtaining a page cache entry
> > and a copy in their userspace buffer, then insert the page of the user
> > buffer back into the page cache as the page cache page ... that's right,
> > isn't it postgress people?
>
> Pretty much, yes. We'd probably hint (*advise(DONTNEED)) that the page
> isn't needed anymore when reading. And we'd normally write if the page
> is dirty.

So why, exactly, do you even need the kernel page cache here? You've
got direct access to the copy of data read into userspace, and you
want direct control of when and how the data in that buffer is
written and reclaimed. Why push that data buffer back into the
kernel and then have to add all sorts of kernel interfaces to
control the page you already have control of?

> > Effectively you end up with buffered read/write that's also mapped into
> > the page cache. It's a pretty awful way to hack around mmap.
>
> Well, the problem is that you can't really use mmap() for the things we
> do. Postgres' durability works by guaranteeing that our journal entries
> (called WAL := Write Ahead Log) are written & synced to disk before the
> corresponding entries of tables and indexes reach the disk. That also
> allows to group together many random-writes into a few contiguous writes
> fdatasync()ed at once. Only during a checkpointing phase the big bulk of
> the data is then (slowly, in the background) synced to disk.

Which is the exact algorithm most journalling filesystems use for
ensuring durability of their metadata updates. Indeed, here's an
interesting piece of architecture that you might like to consider:

* Neither XFS and BTRFS use the kernel page cache to back their
metadata transaction engines.

Why not? Because the page cache is too simplistic to adequately
represent the complex object heirarchies that the filesystems have
and so it's flat LRU reclaim algorithms and writeback control
mechanisms are a terrible fit and cause lots of performance issues
under memory pressure.

IOWs, the two most complex high performance transaction engines in
the Linux kernel have moved to fully customised cache and (direct)
IO implementations because the requirements for scalability and
performance are far more complex than the kernel page cache
infrastructure can provide.

Just food for thought....

Cheers,

Dave.
--
Dave Chinner
david(at)fromorbit(dot)com

On 01/14/2014 03:44 AM, Dave Chinner wrote:
> On Tue, Jan 14, 2014 at 02:26:25AM +0100, Andres Freund wrote:
>> On 2014-01-13 17:13:51 -0800, James Bottomley wrote:
>>> a file into a user provided buffer, thus obtaining a page cache entry
>>> and a copy in their userspace buffer, then insert the page of the user
>>> buffer back into the page cache as the page cache page ... that's right,
>>> isn't it postgress people?
>> Pretty much, yes. We'd probably hint (*advise(DONTNEED)) that the page
>> isn't needed anymore when reading. And we'd normally write if the page
>> is dirty.
> So why, exactly, do you even need the kernel page cache here? You've
> got direct access to the copy of data read into userspace, and you
> want direct control of when and how the data in that buffer is
> written and reclaimed. Why push that data buffer back into the
> kernel and then have to add all sorts of kernel interfaces to
> control the page you already have control of?
To let kernel do the job that it is good at, namely managing the write-back
of dirty buffers to disk and to manage (possible) read-ahead pages.

While we do have control of "the page", we do not (and really don't want to)
have control of the complex and varied side of efficiently reading and
writing
to various file-systems with possibly very different disk configurations.

We quite prefer kernel to take care of it and generally like how kernel
manages it.

We have a few suggestions about giving the kernel extra info about the
applications usage patterns of the data.
>
>>> Effectively you end up with buffered read/write that's also mapped into
>>> the page cache. It's a pretty awful way to hack around mmap.
>> Well, the problem is that you can't really use mmap() for the things we
>> do. Postgres' durability works by guaranteeing that our journal entries
>> (called WAL := Write Ahead Log) are written & synced to disk before the
>> corresponding entries of tables and indexes reach the disk. That also
>> allows to group together many random-writes into a few contiguous writes
>> fdatasync()ed at once. Only during a checkpointing phase the big bulk of
>> the data is then (slowly, in the background) synced to disk.
> Which is the exact algorithm most journalling filesystems use for
> ensuring durability of their metadata updates. Indeed, here's an
> interesting piece of architecture that you might like to consider:
>
> * Neither XFS and BTRFS use the kernel page cache to back their
> metadata transaction engines.
But file system code is supposed to know much more about the
underlying disk than a mere application program like postgresql.

We do not want to start duplicating OS if we can avoid it.

What we would like is to have a way to tell the kernel

1) "here is the modified copy of file page, it is now safe to write
it back" - the current 'lazy' write

2) "here is the page, write it back now, before returning success
to me" - unbuffered write or write + sync

but we also would like to have

3) "here is the page as it is currently on disk, I may need it soon,
so keep it together with your other clean pages accessed at time X"
- this is the non-dirtying write discussed

the page may be in buffer cache, in which case just update its LRU
position (to either current time or time provided by postgresql), or
it may not be there, in which case put it there if reasonable by it's
LRU position.

And we would like all this to work together with other current linux
kernel goodness of managing the whole disk-side interaction of
efficient reading and writing and managing the buffers :)
> Why not? Because the page cache is too simplistic to adequately
> represent the complex object heirarchies that the filesystems have
> and so it's flat LRU reclaim algorithms and writeback control
> mechanisms are a terrible fit and cause lots of performance issues
> under memory pressure.
Same is true for postgresql - if we would just use direct writes
and reads from disk then the performance would be terrible.

We would need to duplicate all the complicated algorithms in file
system do for good performance if we were to start implementing
that part of the file system ourselves.

> IOWs, the two most complex high performance transaction engines in
> the Linux kernel have moved to fully customised cache and (direct)
> IO implementations because the requirements for scalability and
> performance are far more complex than the kernel page cache
> infrastructure can provide.
And we would like to avoid implementing this again this by delegating
this part of work to said complex high performance transaction
engines in the Linux kernel.

We do not want to abandon all work for postgresql business code
and go into file system development mode for next few years.

Again, as said above the linux file system is doing fine. What we
want is a few ways to interact with it to let it do even better when
working with postgresql by telling it some stuff it otherwise would
have to second guess and by sometimes giving it back some cache
pages which were copied away for potential modifying but ended
up clean in the end.

And let the linux kernel decide if and how long to keep these pages
in its cache using its superior knowledge of disk subsystem and
about what else is going on in the system in general.

Just food for thought....

We want to have all the performance and complexity provided
by linux, and we would like it to work even better with postgresql by
having a bit more information for its decisions.

We just don't want to re-implement it ;)

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

On 01/13/2014 11:22 PM, James Bottomley wrote:
>
>> The less exciting, more conservative option would be to add kernel
>> interfaces to teach Postgres about things like raid geometries. Then
>> Postgres could use directio and decide to do prefetching based on the
>> raid geometry, how much available i/o bandwidth and iops is available,
>> etc.
>>
>> Reimplementing i/o schedulers and all the rest of the work that the
>> kernel provides inside Postgres just seems like something outside our
>> competency and that none of us is really excited about doing.
> This would also be a well trodden path ... I believe that some large
> database company introduced Direct IO for roughly this purpose.
>
The file system at that time were much worse than they are now,
so said large companies had no choice but to write their own.

As linux file handling has been much better for most of active
development of postgresql we have been able to avoid
it and still have reasonable performance.

What was been pointed out above are some (allegedly
desktop/mobile influenced) decisions which broke good
performance.

Cheers

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

On Tue, Jan 14, 2014 at 5:08 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
> Again, as said above the linux file system is doing fine. What we
> want is a few ways to interact with it to let it do even better when
> working with postgresql by telling it some stuff it otherwise would
> have to second guess and by sometimes giving it back some cache
> pages which were copied away for potential modifying but ended
> up clean in the end.

You don't need new interfaces. Only a slight modification of what
fadvise DONTNEED does.

This insistence in injecting pages from postgres to kernel is just a
bad idea. At the very least, it still needs postgres to know too much
of the filesystem (block layout) to properly work. Ie: pg must be
required to put entire filesystem-level blocks into the page cache,
since that's how the page cache works. At the very worst, it may
introduce serious security and reliability implications, when
applications can destroy the consistency of the page cache (even if
full access rights are checked, there's still the possibility this
inconsistency might be exploitable).

Simply making fadvise DONTNEED move pages to the head of the LRU (ie:
discard next if you need) should work as expected without all the
complication of the above proposal.

On 01/14/2014 12:26 AM, Mel Gorman wrote:
> On Mon, Jan 13, 2014 at 03:15:16PM -0500, Robert Haas wrote:
>> The other thing that comes to mind is the kernel's caching behavior.
>> We've talked a lot over the years about the difficulties of getting
>> the kernel to write data out when we want it to and to not write data
>> out when we don't want it to.
>
> Is sync_file_range() broke?
>
>> When it writes data back to disk too
>> aggressively, we get lousy throughput because the same page can get
>> written more than once when caching it for longer would have allowed
>> write-combining.
>
> Do you think that is related to dirty_ratio or dirty_writeback_centisecs?
> If it's dirty_writeback_centisecs then that would be particularly tricky
> because poor interactions there would come down to luck basically.

>> When it doesn't write data to disk aggressively
>> enough, we get huge latency spikes at checkpoint time when we call
>> fsync() and the kernel says "uh, what? you wanted that data *on the
>> disk*? sorry boss!" and then proceeds to destroy the world by starving
>> the rest of the system for I/O for many seconds or minutes at a time.
>
> Ok, parts of that are somewhat expected. It *may* depend on the
> underlying filesystem. Some of them handle fsync better than others. If
> you are syncing the whole file though when you call fsync then you are
> potentially burned by having to writeback dirty_ratio amounts of memory
> which could take a substantial amount of time.
>
>> We've made some desultory attempts to use sync_file_range() to improve
>> things here, but I'm not sure that's really the right tool, and if it
>> is we don't know how to use it well enough to obtain consistent
>> positive results.
>
> That implies that either sync_file_range() is broken in some fashion we
> (or at least I) are not aware of and that needs kicking.

Let me try to explain the problem: Checkpoints can cause an I/O spike,
which slows down other processes.

When it's time to perform a checkpoint, PostgreSQL will write() all
dirty buffers from the PostgreSQL buffer cache, and finally perform an
fsync() to flush the writes to disk. After that, we know the data is
safely on disk.

In older PostgreSQL versions, the write() calls would cause an I/O storm
as the OS cache quickly fills up with dirty pages, up to dirty_ratio,
and after that all subsequent write()s block. That's OK as far as the
checkpoint is concerned, but it significantly slows down queries running
at the same time. Even a read-only query often needs to write(), to
evict a dirty page from the buffer cache to make room for a different
page. We made that less painful by adding sleeps between the write()
calls, so that they are trickled over a long period of time and
hopefully stay below dirty_ratio at all times. However, we still have to
perform the fsync()s after the writes(), and sometimes that still causes
a similar I/O storm.

The checkpointer is not in a hurry. A checkpoint typically has 10-30
minutes to finish, before it's time to start the next checkpoint, and
even if it misses that deadline that's not too serious either. But the
OS doesn't know that, and we have no way of telling it.

As a quick fix, some sort of a lazy fsync() call would be nice. It would
behave just like fsync() but it would not change the I/O scheduling at
all. Instead, it would sleep until all the pages have been flushed to
disk, at the speed they would've been without the fsync() call.

Another approach would be to give the I/O that the checkpointer process
initiates a lower priority. This would be slightly preferable, because
PostgreSQL could then issue the writes() as fast as it can, and have the
checkpoint finish earlier when there's not much other load. Last I
looked into this (which was a long time ago), there was no suitable
priority system for writes, only reads.

- Heikki

On Tue 14-01-14 09:08:40, Hannu Krosing wrote:
> >>> Effectively you end up with buffered read/write that's also mapped into
> >>> the page cache. It's a pretty awful way to hack around mmap.
> >> Well, the problem is that you can't really use mmap() for the things we
> >> do. Postgres' durability works by guaranteeing that our journal entries
> >> (called WAL := Write Ahead Log) are written & synced to disk before the
> >> corresponding entries of tables and indexes reach the disk. That also
> >> allows to group together many random-writes into a few contiguous writes
> >> fdatasync()ed at once. Only during a checkpointing phase the big bulk of
> >> the data is then (slowly, in the background) synced to disk.
> > Which is the exact algorithm most journalling filesystems use for
> > ensuring durability of their metadata updates. Indeed, here's an
> > interesting piece of architecture that you might like to consider:
> >
> > * Neither XFS and BTRFS use the kernel page cache to back their
> > metadata transaction engines.
> But file system code is supposed to know much more about the
> underlying disk than a mere application program like postgresql.
>
> We do not want to start duplicating OS if we can avoid it.
>
> What we would like is to have a way to tell the kernel
>
> 1) "here is the modified copy of file page, it is now safe to write
> it back" - the current 'lazy' write
>
> 2) "here is the page, write it back now, before returning success
> to me" - unbuffered write or write + sync
>
> but we also would like to have
>
> 3) "here is the page as it is currently on disk, I may need it soon,
> so keep it together with your other clean pages accessed at time X"
> - this is the non-dirtying write discussed
>
> the page may be in buffer cache, in which case just update its LRU
> position (to either current time or time provided by postgresql), or
> it may not be there, in which case put it there if reasonable by it's
> LRU position.
>
> And we would like all this to work together with other current linux
> kernel goodness of managing the whole disk-side interaction of
> efficient reading and writing and managing the buffers :)
So when I was speaking about the proposed vrange() syscall in this thread,
I thought that instead of injecting pages into pagecache for aging as you
describe in 3), you would mark pages as volatile (i.e. for reclaim by
kernel) through vrange() syscall. Next time you need the page, you check
whether the kernel reclaimed the page or not. If yes, you reload it from
disk, if not, you unmark it and use it.

Now the aging of pages marked as volatile as it is currently implemented
needn't be perfect for your needs but you still have time to influence what
gets implemented... Actually developers of the vrange() syscall were
specifically looking for some ideas what to base aging on. Currently I
think it is first marked - first evicted.

Honza
--
Jan Kara <jack(at)suse(dot)cz>
SUSE Labs, CR

On Mon, Jan 13, 2014 at 03:24:38PM -0800, Josh Berkus wrote:
> On 01/13/2014 02:26 PM, Mel Gorman wrote:
> > Really?
> >
> > zone_reclaim_mode is often a complete disaster unless the workload is
> > partitioned to fit within NUMA nodes. On older kernels enabling it would
> > sometimes cause massive stalls. I'm actually very surprised to hear it
> > fixes anything and would be interested in hearing more about what sort
> > of circumstnaces would convince you to enable that thing.
>
> So the problem with the default setting is that it pretty much isolates
> all FS cache for PostgreSQL to whichever socket the postmaster is
> running on, and makes the other FS cache unavailable.

I'm not being pedantic but the default depends on the NUMA characteristics of
the machine so I need to know if it was enabled or disabled. Some machines
will default zone_reclaim_mode to 0 and others will default it to 1. In my
experience the majority of bugs that involved zone_reclaim_mode were due
to zone_reclaim_mode enabled by default. If I see a bug that involves
a file-based workload on a NUMA machine with stalls and/or excessive IO
when there is plenty of memory free then zone_reclaim_mode is the first
thing I check.

I'm guessing from context that in your experience it gets enabled by default
on the machines you care about. This would indeed limit FS cache usage to
the node where the process is initiating IO (postmaster I guess).

> This means that,
> for example, if you have two memory banks, then only one of them is
> available for PostgreSQL filesystem caching ... essentially cutting your
> available cache in half.
>
> And however slow moving cached pages between memory banks is, it's an
> order of magnitude faster than moving them from disk. But this isn't
> how the NUMA stuff is configured; it seems to assume that it's less
> expensive to get pages from disk than to move them between banks, so

Yes, this is right. The history behind this "logic" is that it was assumed
NUMA machines would only ever be used for HPC and that the workloads would
always be partitioned to run within NUMA nodes. This has not been the case
for a long time and I would argue that we should leave that thing disabled
by default in all cases. Last time I tried it was met with resistance but
maybe it's time to try again.

> whatever you've got cached on the other bank, it flushes it to disk as
> fast as possible. I understand the goal was to make memory usage local
> to the processors stuff was running on, but that includes an implicit
> assumption that no individual process will ever want more than one
> memory bank worth of cache.
>
> So disabling all of the NUMA optimizations is the way to go for any
> workload I personally deal with.
>

I would hesitate to recommend "all" on the grounds that zone_reclaim_mode
is brain damage and I'd hate to lump all tuning parameters into the same box.

There is an interesting side-line here. If all IO is initiated by one
process in postgres then the memory locality will be sub-optimal.
The consumer of the data may or may not be running on the same
node as the process that read the data from disk. It is possible to
migrate this from user space but the interface is clumsy and assumes the
data is mapped.

Automatic NUMA balancing does not help you here because that thing also
depends on the data being mapped. It does nothing for data accessed via
read/write. There is nothing fundamental that prevents this, it was not
implemented because it was not deemed to be important enough. The amount
of effort spent on addressing this would depend on how important NUMA
locality is for postgres performance.

--
Mel Gorman
SUSE Labs

On Tue 14-01-14 11:11:28, Heikki Linnakangas wrote:
> On 01/14/2014 12:26 AM, Mel Gorman wrote:
> >On Mon, Jan 13, 2014 at 03:15:16PM -0500, Robert Haas wrote:
> >>The other thing that comes to mind is the kernel's caching behavior.
> >>We've talked a lot over the years about the difficulties of getting
> >>the kernel to write data out when we want it to and to not write data
> >>out when we don't want it to.
> >
> >Is sync_file_range() broke?
> >
> >>When it writes data back to disk too
> >>aggressively, we get lousy throughput because the same page can get
> >>written more than once when caching it for longer would have allowed
> >>write-combining.
> >
> >Do you think that is related to dirty_ratio or dirty_writeback_centisecs?
> >If it's dirty_writeback_centisecs then that would be particularly tricky
> >because poor interactions there would come down to luck basically.
>
> >>When it doesn't write data to disk aggressively
> >>enough, we get huge latency spikes at checkpoint time when we call
> >>fsync() and the kernel says "uh, what? you wanted that data *on the
> >>disk*? sorry boss!" and then proceeds to destroy the world by starving
> >>the rest of the system for I/O for many seconds or minutes at a time.
> >
> >Ok, parts of that are somewhat expected. It *may* depend on the
> >underlying filesystem. Some of them handle fsync better than others. If
> >you are syncing the whole file though when you call fsync then you are
> >potentially burned by having to writeback dirty_ratio amounts of memory
> >which could take a substantial amount of time.
> >
> >>We've made some desultory attempts to use sync_file_range() to improve
> >>things here, but I'm not sure that's really the right tool, and if it
> >>is we don't know how to use it well enough to obtain consistent
> >>positive results.
> >
> >That implies that either sync_file_range() is broken in some fashion we
> >(or at least I) are not aware of and that needs kicking.
>
> Let me try to explain the problem: Checkpoints can cause an I/O
> spike, which slows down other processes.
>
> When it's time to perform a checkpoint, PostgreSQL will write() all
> dirty buffers from the PostgreSQL buffer cache, and finally perform
> an fsync() to flush the writes to disk. After that, we know the data
> is safely on disk.
>
> In older PostgreSQL versions, the write() calls would cause an I/O
> storm as the OS cache quickly fills up with dirty pages, up to
> dirty_ratio, and after that all subsequent write()s block. That's OK
> as far as the checkpoint is concerned, but it significantly slows
> down queries running at the same time. Even a read-only query often
> needs to write(), to evict a dirty page from the buffer cache to
> make room for a different page. We made that less painful by adding
> sleeps between the write() calls, so that they are trickled over a
> long period of time and hopefully stay below dirty_ratio at all
> times.
Hum, I wonder whether you see any difference with reasonably recent
kernels (say newer than 3.2). Because those have IO-less dirty throttling.
That means that:
a) checkpointing thread (or other threads blocked due to dirty limit)
won't issue IO on their own but rather wait for flusher thread to do the
work.
b) there should be more noticeable difference between the delay imposed
on heavily dirtying thread (i.e. the checkpointing thread) and the delay
imposed on lightly dirtying thread (that's what I would expect from those
threads having to do occasional page eviction to make room for other page).

> However, we still have to perform the fsync()s after the
> writes(), and sometimes that still causes a similar I/O storm.
Because there is still quite some dirty data in the page cache or because
e.g. ext3 has to flush a lot of unrelated dirty data?

> The checkpointer is not in a hurry. A checkpoint typically has 10-30
> minutes to finish, before it's time to start the next checkpoint,
> and even if it misses that deadline that's not too serious either.
> But the OS doesn't know that, and we have no way of telling it.
>
> As a quick fix, some sort of a lazy fsync() call would be nice. It
> would behave just like fsync() but it would not change the I/O
> scheduling at all. Instead, it would sleep until all the pages have
> been flushed to disk, at the speed they would've been without the
> fsync() call.
>
> Another approach would be to give the I/O that the checkpointer
> process initiates a lower priority. This would be slightly
> preferable, because PostgreSQL could then issue the writes() as fast
> as it can, and have the checkpoint finish earlier when there's not
> much other load. Last I looked into this (which was a long time
> ago), there was no suitable priority system for writes, only reads.
Well, IO priority works for writes in principle, the trouble is it
doesn't work for writes which end up just in the page cache. Then writeback
of page cache is usually done by flusher thread so that's completely
disconnected from whoever created the dirty data (now I know this is dumb
and long term we want to do something about it so that IO cgroups work
reasonably reliably but it is a tough problem, lots of complexity for not so
great gain...).

However, if you really issue the IO from the thread with low priority, it
will have low priority. So specifically if you call fsync() from a thread
with low IO priority, the flushing done by fsync() will have this low
IO priority.

Similarly if you called sync_file_range() once in a while from a thread
with low IO priority, the flushing IO will have low IO priority. But I
would be really careful about the periodic sync_file_range() calls - it has
a potential of mixing with writeback from flusher thread and mixing these
two on different parts of a file can lead to bad IO patterns...

Honza
--
Jan Kara <jack(at)suse(dot)cz>
SUSE Labs, CR

On 01/14/2014 09:39 AM, Claudio Freire wrote:
> On Tue, Jan 14, 2014 at 5:08 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
>> Again, as said above the linux file system is doing fine. What we
>> want is a few ways to interact with it to let it do even better when
>> working with postgresql by telling it some stuff it otherwise would
>> have to second guess and by sometimes giving it back some cache
>> pages which were copied away for potential modifying but ended
>> up clean in the end.
> You don't need new interfaces. Only a slight modification of what
> fadvise DONTNEED does.
>
> This insistence in injecting pages from postgres to kernel is just a
> bad idea.
Do you think it would be possible to map copy-on-write pages
from linux cache to postgresql cache ?

this would be a step in direction of solving the double-ram-usage
of pages which have not been read from syscache to postgresql
cache without sacrificing linux read-ahead (which I assume does
not happen when reads bypass system cache).

and we can write back the copy at the point when it is safe (from
postgresql perspective) to let the system write them back ?

Do you think it is possible to make it work with good performance
for a few million 8kb pages ?

> At the very least, it still needs postgres to know too much
> of the filesystem (block layout) to properly work. Ie: pg must be
> required to put entire filesystem-level blocks into the page cache,
> since that's how the page cache works.
I was more thinking of an simple write() interface with extra
flags/sysctls to tell kernel that "we already have this on disk"
> At the very worst, it may
> introduce serious security and reliability implications, when
> applications can destroy the consistency of the page cache (even if
> full access rights are checked, there's still the possibility this
> inconsistency might be exploitable).
If you allow write() which just writes clean pages, I can not see
where the extra security concerns are beyond what normal
write can do.

Cheers

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

On Mon, Jan 13, 2014 at 5:26 PM, Mel Gorman <mgorman(at)suse(dot)de> wrote:
>> Amen to that. Actually, I think NUMA can be (mostly?) fixed by
>> setting zone_reclaim_mode; is there some other problem besides that?
>
> Really?
>
> zone_reclaim_mode is often a complete disaster unless the workload is
> partitioned to fit within NUMA nodes. On older kernels enabling it would
> sometimes cause massive stalls. I'm actually very surprised to hear it
> fixes anything and would be interested in hearing more about what sort
> of circumstnaces would convince you to enable that thing.

By "set" I mean "set to zero". We've seen multiple of instances of
people complaining about large amounts of system memory going unused
because this setting defaulted to 1.

>> The other thing that comes to mind is the kernel's caching behavior.
>> We've talked a lot over the years about the difficulties of getting
>> the kernel to write data out when we want it to and to not write data
>> out when we don't want it to.
>
> Is sync_file_range() broke?

I don't know. I think a few of us have played with it and not been
able to achieve a clear win. Whether the problem is with the system
call or the programmer is harder to determine. I think the problem is
in part that it's not exactly clear when we should call it. So
suppose we want to do a checkpoint. What we used to do a long time
ago is write everything, and then fsync it all, and then call it good.
But that produced horrible I/O storms. So what we do now is do the
writes over a period of time, with sleeps in between, and then fsync
it all at the end, hoping that the kernel will write some of it before
the fsyncs arrive so that we don't get a huge I/O spike.

And that sorta works, and it's definitely better than doing it all at
full speed, but it's pretty imprecise. If the kernel doesn't write
enough of the data out in advance, then there's still a huge I/O storm
when we do the fsyncs and everything grinds to a halt. If it writes
out more data than needed in advance, it increases the total number of
physical writes because we get less write-combining, and that hurts
performance, too. I basically feel like the I/O scheduler sucks,
though whether it sucks because it's not theoretically possible to do
any better or whether it sucks because of some more tractable reason
is not clear to me. In an ideal world, when I call fsync() a bunch of
times from one process, other processes on the same machine should
begin to observe 30+-second (or sometimes 300+-second) times for read
or write of an 8kB block. Imagine a hypothetical UNIX-like system
where when one process starts running at 100% CPU, every other process
on the machine gets timesliced in only once per minute. That's
obviously ridiculous, and yet it's pretty much exactly what happens
with I/O.

>> When it writes data back to disk too
>> aggressively, we get lousy throughput because the same page can get
>> written more than once when caching it for longer would have allowed
>> write-combining.
>
> Do you think that is related to dirty_ratio or dirty_writeback_centisecs?
> If it's dirty_writeback_centisecs then that would be particularly tricky
> because poor interactions there would come down to luck basically.

See above; I think it's related to fsync.

>> When it doesn't write data to disk aggressively
>> enough, we get huge latency spikes at checkpoint time when we call
>> fsync() and the kernel says "uh, what? you wanted that data *on the
>> disk*? sorry boss!" and then proceeds to destroy the world by starving
>> the rest of the system for I/O for many seconds or minutes at a time.
>
> Ok, parts of that are somewhat expected. It *may* depend on the
> underlying filesystem. Some of them handle fsync better than others. If
> you are syncing the whole file though when you call fsync then you are
> potentially burned by having to writeback dirty_ratio amounts of memory
> which could take a substantial amount of time.

Yeah. ext3 apparently fsyncs the whole filesystem, which is terrible
for throughput, but if you happen to have xlog (which is flushed
regularly) on the same filesystem as the data files (which are flushed
only periodically) then at least you don't have the problem of the
write queue getting too large. But I think most of our users are on
ext4 at this point, probably some xfs and other things.

We track the number of un-fsync'd blocks we've written to each file,
and have gotten desperate enough to think of approaches like - ok,
well if the total number of un-fsync'd blocks in the system exceeds
some threshold, then fsync the file with the most such blocks, not
because we really need the data on disk just yet but so that the write
queue won't get too large for the kernel to deal with. And I think
there may even be some test results from such crocks showing some
benefit. But really, I don't understand why we have to baby the
kernel like this. Ensuring scheduling fairness is a basic job of the
kernel; if we wanted to have to control caching behavior manually, we
could use direct I/O. Having accepted the double buffering that comes
with NOT using direct I/O, ideally we could let the kernel handle
scheduling and call it good.

>> We've made some desultory attempts to use sync_file_range() to improve
>> things here, but I'm not sure that's really the right tool, and if it
>> is we don't know how to use it well enough to obtain consistent
>> positive results.
>
> That implies that either sync_file_range() is broken in some fashion we
> (or at least I) are not aware of and that needs kicking.

So the problem is - when do you call it? What happens is: before a
checkpoint, we may have already written some blocks to a file. During
the checkpoint, we're going to write some more. At the end of the
checkpoint, we'll need all blocks written before and during the
checkpoint to be on disk. If we call sync_file_range() at the
beginning of the checkpoint, then in theory that should get the ball
rolling, but we may be about to rewrite some of those blocks, or at
least throw some more on the pile. If we call sync_file_range() near
the end of the checkpoint, just before calling fsync, there's not
enough time for the kernel to reorder I/O to a sufficient degree to do
any good. What we want, sorta, is to have the kernel start writing it
out just at the right time to get it on disk by the time we're aiming
to complete the checkpoint, but it's not clear exactly how to do that.
We can't just write all the blocks, sync_file_range(), wait, and then
fsync() because the "write all the blocks" step can trigger an I/O
storm if the kernel decides there's too much dirty data.

I suppose what we really want to do during a checkpoint is write data
into the O/S cache at a rate that matches what the kernel can
physically get down to the disk, and have the kernel schedule those
writes in as timely a fashion as it can without disrupting overall
system throughput too much. But the feedback mechanisms that exist
today are just too crude for that. You can easily write() to the
point where the whole system freezes up, or equally wait between
write()s when the system could easily have handled more right away.
And it's very hard to tell how much you can fsync() at once before
performance falls off a cliff. A certain number of writes get
absorbed by various layers of caching between us and the physical
hardware - and then at some point, they're all full, and further
writes lead to disaster. But I don't know of any way to assess how
close we are to that point at any give time except to cross it, and at
that point, it's too late.

>> On a related note, there's also the problem of double-buffering. When
>> we read a page into shared_buffers, we leave a copy behind in the OS
>> buffers, and similarly on write-out. It's very unclear what to do
>> about this, since the kernel and PostgreSQL don't have intimate
>> knowledge of what each other are doing, but it would be nice to solve
>> somehow.
>
> If it's mapped, clean and you do not need any more than
> madvise(MADV_DONTNEED). If you are accessing teh data via a file handle,
> then I would expect posix_fadvise(POSIX_FADV_DONTNEED). Offhand, I do
> not know how it behaved historically but right now it will usually sync
> the data and then discard the pages. I say usually because it will not
> necessarily sync if the storage is congested and there is no guarantee it
> will be discarded. In older kernels, there was a bug where small calls to
> posix_fadvise() would not work at all. This was fixed in 3.9.
>
> The flipside is also meant to hold true. If you know data will be needed
> in the near future then posix_fadvise(POSIX_FADV_WILLNEED). Glancing at
> the implementation it does a forced read-ahead on the range of pages of
> interest. It doesn't look like it would block.
>
> The completely different approach for double buffering is direct IO but
> there may be reasons why you are avoiding that and are unhappy with the
> interfaces that are meant to work.
>
> Just from the start, it looks like there are a number of problem areas.
> Some may be fixed -- in which case we should identify what fixed it, what
> kernel version and see can it be verified with a test case or did we
> manage to break something else in the process. Other bugs may still
> exist because we believe some interface works how users want when it is
> in fact unfit for purpose for some reason.

It's all read, not mapped, because we have a need to prevent pages
from being written back to their backing files until WAL is fsync'd,
and there's no way to map a file and modify the page but not let it be
written back to disk until some other event happens. We've
experimented with don't-need but it's tricky.

Here's an example. Our write-ahead log files (WAL) are all 16MB;
eventually, when they're no longer needed for any purpose, older files
cease to be needed, but there's a continued demand for new files
driven by database modifications. Experimentation some years ago
revealed that it's faster to rename and overwrite the old files than
to remove them and create new ones, so that's what we do. Ideally
this means that at steady state we're just recycling the files over
and over and never creating or destroying any, though I'm not sure
whether we ever actually achieve that ideal. However, benchmarking
has showed that making the wrong decision about whether to don't-need
those files has a significant effect on performance. If there's
enough cache around to keep all the files in memory, then we don't
want to don't-need them because then access will be slow when the old
files are recycled. If however there is cache pressure then we want
to don't-need them as quickly as possible to make room for other,
higher priority data.

Now that may not really be the kernel's fault; it's a general property
of ring buffers that you want to an LRU policy if they fit in cache
and immediate eviction of everything but the active page if they
don't. But I think it demonstrates the general difficulty of using
posix_fadvise. Similar cases arise for prefetching: gee, we'd like to
prefetch this data because we're going to use it soon, but if the
system is under enough pressure, the data may get evicted again before
"soon" actually arrives.

Thanks for taking the time to write all of these comments, and listen
to our concerns. I really appreciate it, whether anything tangible
comes of it or not.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

First off, I want to give a +1 on everything in the recent posts
from Heikki and Hannu.

Jan Kara <jack(at)suse(dot)cz> wrote:

> Now the aging of pages marked as volatile as it is currently
> implemented needn't be perfect for your needs but you still have
> time to influence what gets implemented... Actually developers of
> the vrange() syscall were specifically looking for some ideas
> what to base aging on. Currently I think it is first marked -
> first evicted.

The "first marked - first evicted" seems like what we would want.
The ability to "unmark" and have the page no longer be considered
preferred for eviction would be very nice.  That seems to me like
it would cover the multiple layers of buffering *clean* pages very
nicely (although I know nothing more about vrange() than what has
been said on this thread, so I could be missing something).

The other side of that is related avoiding multiple writes of the
same page as much as possible, while avoid write gluts.  The issue
here is that PostgreSQL tries to hang on to dirty pages for as long
as possible before "writing" them to the OS cache, while the OS
tries to avoid writing them to storage for as long as possible
until they reach a (configurable) threshold or are fsync'd.  The
problem is that a under various conditions PostgreSQL may need to
write and fsync a lot of dirty pages it has accumulated in a short
time.  That has an "avalanche" effect, creating a "write glut"
which can stall all I/O for a period of many seconds up to a few
minutes.  If the OS was aware of the dirty pages pending write in
the application, and counted those for purposes of calculating when
and how much to write, the glut could be avoided.  Currently,
people configure the PostgreSQL background writer to be very
aggressive, configure a small PostgreSQL shared_buffers setting,
and/or set the OS thresholds low enough to minimize the problem;
but all of these mitigation strategies have their own costs.

A new hint that the application has dirtied a page could be used by
the OS to improve things this way:  When the OS is notified that a
page is dirty, it takes action depending on whether the page is
considered dirty by the OS.  If it is not dirty, the page is
immediately discarded from the OS cache.  It is known that the
application has a modified version of the page that it intends to
write, so the version in the OS cache has no value.  We don't want
this page forcing eviction of vrange()-flagged pages.  If it is
dirty, any write ordering to storage by the OS based on when the
page was written to the OS would be pushed back as far as possible
without crossing any write barriers, in hopes that the writes could
be combined.  Either way, this page is counted toward dirty pages
for purposes of calculating how much to write from the OS to
storage, and the later write of the page doesn't redundantly add to
this number.

The combination of these two changes could boost PostgreSQL
performance quite a bit, at least for some common workloads.

The MMAP approach always seems tempting on first blush, but the
need to "pin" pages and the need to assure that dirty pages are not
written ahead of the WAL-logging of those pages makes it hard to
see how we can use it.  The "pin" means that we need to ensure that
a particular 8KB page remains available for direct reference by all
PostgreSQL processes until it is "unpinned".  The other thing we
would need is the ability to modify a page with a solid assurance
that the modified page would *not* be written to disk until we
authorize it.  The page would remain pinned until we do authorize
write, at which point the changes are available to be written, but
can wait for an fsync or accumulations of sufficient dirty pages to
cross the write threshold.  Next comes the hard part.  The page may
or may not be unpinned after that, and if it remains pinned or is
pinned again, there may be further changes to the page.  While the
prior changes can be written (and *must* be written for an fsync),
these new changes must *not* be until we authorize it.  If MMAP can
be made to handle that, we could probably use it (and some of the
previously-discussed techniques might not be needed), but my
understanding is that there is currently no way to do so.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 14, 2014 at 11:39 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
> On 01/14/2014 09:39 AM, Claudio Freire wrote:
>> On Tue, Jan 14, 2014 at 5:08 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
>>> Again, as said above the linux file system is doing fine. What we
>>> want is a few ways to interact with it to let it do even better when
>>> working with postgresql by telling it some stuff it otherwise would
>>> have to second guess and by sometimes giving it back some cache
>>> pages which were copied away for potential modifying but ended
>>> up clean in the end.
>> You don't need new interfaces. Only a slight modification of what
>> fadvise DONTNEED does.
>>
>> This insistence in injecting pages from postgres to kernel is just a
>> bad idea.
> Do you think it would be possible to map copy-on-write pages
> from linux cache to postgresql cache ?
>
> this would be a step in direction of solving the double-ram-usage
> of pages which have not been read from syscache to postgresql
> cache without sacrificing linux read-ahead (which I assume does
> not happen when reads bypass system cache).
>
> and we can write back the copy at the point when it is safe (from
> postgresql perspective) to let the system write them back ?
>
> Do you think it is possible to make it work with good performance
> for a few million 8kb pages ?

I don't think so. The kernel would need to walk the page mapping on
each page fault, which would incurr the cost of a read cache hit on
each page fault.

A cache hit is still orders of magnitude slower than a regular page
fault, because the process page map is compact and efficient. But if
you bloat it, or if you make the kernel go read the buffer cache, it
would mean bad performance for RAM access, which I'd venture isn't
really a net gain.

That's probably the reason there is no zero-copy read mechanism.
Because you always have to copy from/to the buffer cache anyway.

Of course, this is just OTOMH. Without actually benchmarking, this is
all blabber.

>> At the very worst, it may
>> introduce serious security and reliability implications, when
>> applications can destroy the consistency of the page cache (even if
>> full access rights are checked, there's still the possibility this
>> inconsistency might be exploitable).
> If you allow write() which just writes clean pages, I can not see
> where the extra security concerns are beyond what normal
> write can do.

I've been working on security enough to never dismiss any kind of
system-level inconsistency.

The fact that you can make user-land applications see different data
than kernel-land code has over-reaching consequences that are hard to
ponder.

On Tue, Jan 14, 2014 at 3:39 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Tue, Jan 14, 2014 at 5:08 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
>> Again, as said above the linux file system is doing fine. What we
>> want is a few ways to interact with it to let it do even better when
>> working with postgresql by telling it some stuff it otherwise would
>> have to second guess and by sometimes giving it back some cache
>> pages which were copied away for potential modifying but ended
>> up clean in the end.
>
> You don't need new interfaces. Only a slight modification of what
> fadvise DONTNEED does.

Yeah. DONTREALLYNEEDALLTHATTERRIBLYMUCH.

> This insistence in injecting pages from postgres to kernel is just a
> bad idea. At the very least, it still needs postgres to know too much
> of the filesystem (block layout) to properly work. Ie: pg must be
> required to put entire filesystem-level blocks into the page cache,
> since that's how the page cache works. At the very worst, it may
> introduce serious security and reliability implications, when
> applications can destroy the consistency of the page cache (even if
> full access rights are checked, there's still the possibility this
> inconsistency might be exploitable).

I agree with all that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 14, 2014 at 5:00 AM, Jan Kara <jack(at)suse(dot)cz> wrote:
> I thought that instead of injecting pages into pagecache for aging as you
> describe in 3), you would mark pages as volatile (i.e. for reclaim by
> kernel) through vrange() syscall. Next time you need the page, you check
> whether the kernel reclaimed the page or not. If yes, you reload it from
> disk, if not, you unmark it and use it.
>
> Now the aging of pages marked as volatile as it is currently implemented
> needn't be perfect for your needs but you still have time to influence what
> gets implemented... Actually developers of the vrange() syscall were
> specifically looking for some ideas what to base aging on. Currently I
> think it is first marked - first evicted.

This is an interesting idea but it stinks of impracticality.
Essentially when the last buffer pin on a page is dropped we'd have to
mark it as discardable, and then the next person wanting to pin it
would have to check whether it's still there. But the system call
overhead of calling vrange() every time the last pin on a page was
dropped would probably hose us.

*thinks*

Well, I guess it could be done lazily: make periodic sweeps through
shared_buffers, looking for pages that haven't been touched in a
while, and vrange() them. That's quite a bit of new mechanism, but in
theory it could work out to a win. vrange() would have to scale well
to millions of separate ranges, though. Will it? And a lot depends
on whether the kernel makes the right decision about whether to chunk
data from our vrange() vs. any other page it could have reclaimed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, 2014-01-14 at 15:39 +0100, Hannu Krosing wrote:
> On 01/14/2014 09:39 AM, Claudio Freire wrote:
> > On Tue, Jan 14, 2014 at 5:08 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
> >> Again, as said above the linux file system is doing fine. What we
> >> want is a few ways to interact with it to let it do even better when
> >> working with postgresql by telling it some stuff it otherwise would
> >> have to second guess and by sometimes giving it back some cache
> >> pages which were copied away for potential modifying but ended
> >> up clean in the end.
> > You don't need new interfaces. Only a slight modification of what
> > fadvise DONTNEED does.
> >
> > This insistence in injecting pages from postgres to kernel is just a
> > bad idea.
> Do you think it would be possible to map copy-on-write pages
> from linux cache to postgresql cache ?
>
> this would be a step in direction of solving the double-ram-usage
> of pages which have not been read from syscache to postgresql
> cache without sacrificing linux read-ahead (which I assume does
> not happen when reads bypass system cache).

The current mechanism for coherency between a userspace cache and the
in-kernel page cache is mmap ... that's the only way you get the same
page in both currently.

glibc used to have an implementation of read/write in terms of mmap, so
it should be possible to insert it into your current implementation
without a major rewrite. The problem I think this brings you is
uncontrolled writeback: you don't want dirty pages to go to disk until
you issue a write() I think we could fix this with another madvise():
something like MADV_WILLUPDATE telling the page cache we expect to alter
the pages again, so don't be aggressive about cleaning them. Plus all
the other issues with mmap() ... but if you can detail those, we might
be able to fix them.

> and we can write back the copy at the point when it is safe (from
> postgresql perspective) to let the system write them back ?

Using MADV_WILLUPDATE, possibly ... you're still not going to have
absolute control. The kernel will write back the pages if the dirty
limits are exceeded, for instance, but we could tune it to be useful.

> Do you think it is possible to make it work with good performance
> for a few million 8kb pages ?
>
> > At the very least, it still needs postgres to know too much
> > of the filesystem (block layout) to properly work. Ie: pg must be
> > required to put entire filesystem-level blocks into the page cache,
> > since that's how the page cache works.
> I was more thinking of an simple write() interface with extra
> flags/sysctls to tell kernel that "we already have this on disk"
> > At the very worst, it may
> > introduce serious security and reliability implications, when
> > applications can destroy the consistency of the page cache (even if
> > full access rights are checked, there's still the possibility this
> > inconsistency might be exploitable).
> If you allow write() which just writes clean pages, I can not see
> where the extra security concerns are beyond what normal
> write can do.

The problem is we can't give you absolute control of when pages are
written back because that interface can be used to DoS the system: once
we get too many dirty uncleanable pages, we'll thrash looking for memory
and the system will livelock.

James

James Bottomley <James(dot)Bottomley(at)HansenPartnership(dot)com> writes:
> The current mechanism for coherency between a userspace cache and the
> in-kernel page cache is mmap ... that's the only way you get the same
> page in both currently.

Right.

> glibc used to have an implementation of read/write in terms of mmap, so
> it should be possible to insert it into your current implementation
> without a major rewrite. The problem I think this brings you is
> uncontrolled writeback: you don't want dirty pages to go to disk until
> you issue a write()

Exactly.

> I think we could fix this with another madvise():
> something like MADV_WILLUPDATE telling the page cache we expect to alter
> the pages again, so don't be aggressive about cleaning them.

"Don't be aggressive" isn't good enough. The prohibition on early write
has to be absolute, because writing a dirty page before we've done
whatever else we need to do results in a corrupt database. It has to
be treated like a write barrier.

> The problem is we can't give you absolute control of when pages are
> written back because that interface can be used to DoS the system: once
> we get too many dirty uncleanable pages, we'll thrash looking for memory
> and the system will livelock.

Understood, but that makes this direction a dead end. We can't use
it if the kernel might decide to write anyway.

regards, tom lane

On Jan 14, 2014, at 10:39, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> James Bottomley <James(dot)Bottomley(at)HansenPartnership(dot)com> writes:
>> The current mechanism for coherency between a userspace cache and the
>> in-kernel page cache is mmap ... that's the only way you get the same
>> page in both currently.
>
> Right.
>
>> glibc used to have an implementation of read/write in terms of mmap, so
>> it should be possible to insert it into your current implementation
>> without a major rewrite. The problem I think this brings you is
>> uncontrolled writeback: you don't want dirty pages to go to disk until
>> you issue a write()
>
> Exactly.
>
>> I think we could fix this with another madvise():
>> something like MADV_WILLUPDATE telling the page cache we expect to alter
>> the pages again, so don't be aggressive about cleaning them.
>
> "Don't be aggressive" isn't good enough. The prohibition on early write
> has to be absolute, because writing a dirty page before we've done
> whatever else we need to do results in a corrupt database. It has to
> be treated like a write barrier.

Then why are you dirtying the page at all? It makes no sense to tell the kernel “we’re changing this page in the page cache, but we don’t want you to change it on disk”: that’s not consistent with the function of a page cache.

>> The problem is we can't give you absolute control of when pages are
>> written back because that interface can be used to DoS the system: once
>> we get too many dirty uncleanable pages, we'll thrash looking for memory
>> and the system will livelock.
>
> Understood, but that makes this direction a dead end. We can't use
> it if the kernel might decide to write anyway.
>
> regards, tom lane

On Tue, Jan 14, 2014 at 12:42 PM, Trond Myklebust <trondmy(at)gmail(dot)com> wrote:
>> James Bottomley <James(dot)Bottomley(at)HansenPartnership(dot)com> writes:
>>> The current mechanism for coherency between a userspace cache and the
>>> in-kernel page cache is mmap ... that's the only way you get the same
>>> page in both currently.
>>
>> Right.
>>
>>> glibc used to have an implementation of read/write in terms of mmap, so
>>> it should be possible to insert it into your current implementation
>>> without a major rewrite. The problem I think this brings you is
>>> uncontrolled writeback: you don't want dirty pages to go to disk until
>>> you issue a write()
>>
>> Exactly.
>>
>>> I think we could fix this with another madvise():
>>> something like MADV_WILLUPDATE telling the page cache we expect to alter
>>> the pages again, so don't be aggressive about cleaning them.
>>
>> "Don't be aggressive" isn't good enough. The prohibition on early write
>> has to be absolute, because writing a dirty page before we've done
>> whatever else we need to do results in a corrupt database. It has to
>> be treated like a write barrier.
>
> Then why are you dirtying the page at all? It makes no sense to tell the kernel “we’re changing this page in the page cache, but we don’t want you to change it on disk”: that’s not consistent with the function of a page cache.

PG doesn't currently.

All that dirtying happens in anonymous shared memory, in pg-specific buffers.

The proposal is to use mmap instead of anonymous shared memory as
pg-specific buffers to avoid the extra copy (mmap would share the page
with both kernel and user space). But that would dirty the page when
written to, because now the kernel has the correspondence between that
specific memory region and the file, and that's forbidden for PG's
usage.

I believe the only option here is for the kernel to implement
zero-copy reads. But that implementation is doomed for the performance
reasons I outlined on an eariler mail. So...

Trond Myklebust <trondmy(at)gmail(dot)com> writes:
> On Jan 14, 2014, at 10:39, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "Don't be aggressive" isn't good enough. The prohibition on early write
>> has to be absolute, because writing a dirty page before we've done
>> whatever else we need to do results in a corrupt database. It has to
>> be treated like a write barrier.

> Then why are you dirtying the page at all? It makes no sense to tell the kernel were changing this page in the page cache, but we dont want you to change it on disk: thats not consistent with the function of a page cache.

As things currently stand, we dirty the page in our internal buffers,
and we don't write it to the kernel until we've written and fsync'd the
WAL data that needs to get to disk first. The discussion here is about
whether we could somehow avoid double-buffering between our internal
buffers and the kernel page cache.

I personally think there is no chance of using mmap for that; the
semantics of mmap are pretty much dictated by POSIX and they don't work
for this. However, disregarding the fact that the two communities
speaking here don't control the POSIX spec, you could maybe imagine
making it work if *both* pending WAL file contents and data file
contents were mmap'd, and there were kernel APIs allowing us to say
"you can write this mmap'd page if you want, but not till you've written
that mmap'd data over there". That'd provide the necessary
write-barrier semantics, and avoid the cache coherency question because
all the data visible to the kernel could be thought of as the "current"
filesystem contents, it just might not all have reached disk yet; which
is the behavior of the kernel disk cache already.

I'm dubious that this sketch is implementable with adequate efficiency,
though, because in a live system the kernel would be forced to deal with
a whole lot of active barrier restrictions. Within Postgres we can
reduce write-ordering tests to a very simple comparison: don't write
this page until WAL is flushed to disk at least as far as WAL sequence
number XYZ. I think any kernel API would have to be a great deal more
general and thus harder to optimize.

Another difficulty with merging our internal buffers with the kernel
cache is that when we're in the process of applying a change to a page,
there are intermediate states of the page data that should under no
circumstances reach disk (eg, we might need to shuffle records around
within the page). We can deal with that fairly easily right now by not
issuing a write() while a page change is in progress. I don't see that
it's even theoretically possible in an mmap'd world; there are no atomic
updates to an mmap'd page that are larger than whatever is an atomic
update for the CPU.

regards, tom lane

On Tue, 2014-01-14 at 10:39 -0500, Tom Lane wrote:
> James Bottomley <James(dot)Bottomley(at)HansenPartnership(dot)com> writes:
> > The current mechanism for coherency between a userspace cache and the
> > in-kernel page cache is mmap ... that's the only way you get the same
> > page in both currently.
>
> Right.
>
> > glibc used to have an implementation of read/write in terms of mmap, so
> > it should be possible to insert it into your current implementation
> > without a major rewrite. The problem I think this brings you is
> > uncontrolled writeback: you don't want dirty pages to go to disk until
> > you issue a write()
>
> Exactly.
>
> > I think we could fix this with another madvise():
> > something like MADV_WILLUPDATE telling the page cache we expect to alter
> > the pages again, so don't be aggressive about cleaning them.
>
> "Don't be aggressive" isn't good enough. The prohibition on early write
> has to be absolute, because writing a dirty page before we've done
> whatever else we need to do results in a corrupt database. It has to
> be treated like a write barrier.
>
> > The problem is we can't give you absolute control of when pages are
> > written back because that interface can be used to DoS the system: once
> > we get too many dirty uncleanable pages, we'll thrash looking for memory
> > and the system will livelock.
>
> Understood, but that makes this direction a dead end. We can't use
> it if the kernel might decide to write anyway.

No, I'm sorry, that's never going to be possible. No user space
application has all the facts. If we give you an interface to force
unconditional holding of dirty pages in core you'll livelock the system
eventually because you made a wrong decision to hold too many dirty
pages. I don't understand why this has to be absolute: if you advise
us to hold the pages dirty and we do up until it becomes a choice to
hold on to the pages or to thrash the system into a livelock, why would
you ever choose the latter? And if, as I'm assuming, you never would,
why don't you want the kernel to make that choice for you?

James

On Tue, Jan 14, 2014 at 11:44 AM, James Bottomley
<James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
> No, I'm sorry, that's never going to be possible. No user space
> application has all the facts. If we give you an interface to force
> unconditional holding of dirty pages in core you'll livelock the system
> eventually because you made a wrong decision to hold too many dirty
> pages. I don't understand why this has to be absolute: if you advise
> us to hold the pages dirty and we do up until it becomes a choice to
> hold on to the pages or to thrash the system into a livelock, why would
> you ever choose the latter? And if, as I'm assuming, you never would,
> why don't you want the kernel to make that choice for you?

If you don't understand how write-ahead logging works, this
conversation is going nowhere. Suffice it to say that the word
"ahead" is not optional.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 14, 2014 at 1:48 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Jan 14, 2014 at 11:44 AM, James Bottomley
> <James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
>> No, I'm sorry, that's never going to be possible. No user space
>> application has all the facts. If we give you an interface to force
>> unconditional holding of dirty pages in core you'll livelock the system
>> eventually because you made a wrong decision to hold too many dirty
>> pages. I don't understand why this has to be absolute: if you advise
>> us to hold the pages dirty and we do up until it becomes a choice to
>> hold on to the pages or to thrash the system into a livelock, why would
>> you ever choose the latter? And if, as I'm assuming, you never would,
>> why don't you want the kernel to make that choice for you?
>
> If you don't understand how write-ahead logging works, this
> conversation is going nowhere. Suffice it to say that the word
> "ahead" is not optional.

In essence, if you do flush when you shouldn't, and there is a
hardware failure, or kernel panic, or anything that stops the rest of
the writes from succeeding, your database is kaputt, and you've got to
restore a backup.

Ie: very very bad.

On Tue, 2014-01-14 at 11:48 -0500, Robert Haas wrote:
> On Tue, Jan 14, 2014 at 11:44 AM, James Bottomley
> <James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
> > No, I'm sorry, that's never going to be possible. No user space
> > application has all the facts. If we give you an interface to force
> > unconditional holding of dirty pages in core you'll livelock the system
> > eventually because you made a wrong decision to hold too many dirty
> > pages. I don't understand why this has to be absolute: if you advise
> > us to hold the pages dirty and we do up until it becomes a choice to
> > hold on to the pages or to thrash the system into a livelock, why would
> > you ever choose the latter? And if, as I'm assuming, you never would,
> > why don't you want the kernel to make that choice for you?
>
> If you don't understand how write-ahead logging works, this
> conversation is going nowhere. Suffice it to say that the word
> "ahead" is not optional.

No, I do ... you mean the order of write out, if we have to do it, is
important. In the rest of the kernel, we do this with barriers which
causes ordered grouping of I/O chunks. If we could force a similar
ordering in the writeout code, is that enough?

James

On 01/14/2014 06:08 PM, Tom Lane wrote:
> Trond Myklebust <trondmy(at)gmail(dot)com> writes:
>> On Jan 14, 2014, at 10:39, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> "Don't be aggressive" isn't good enough. The prohibition on early write
>>> has to be absolute, because writing a dirty page before we've done
>>> whatever else we need to do results in a corrupt database. It has to
>>> be treated like a write barrier.
>
>> Then why are you dirtying the page at all? It makes no sense to tell the kernel “we’re changing this page in the page cache, but we don’t want you to change it on disk”: that’s not consistent with the function of a page cache.
>
> As things currently stand, we dirty the page in our internal buffers,
> and we don't write it to the kernel until we've written and fsync'd the
> WAL data that needs to get to disk first. The discussion here is about
> whether we could somehow avoid double-buffering between our internal
> buffers and the kernel page cache.

To be honest, I think the impact of double buffering in real-life
applications is greatly exaggerated. If you follow the usual guideline
and configure shared_buffers to 25% of available RAM, at worst you're
wasting 25% of RAM to double buffering. That's significant, but it's not
the end of the world, and it's a problem that can be compensated by
simply buying more RAM.

Of course, if someone can come up with an easy way to solve that, that'd
be great, but if it means giving up other advantages that we get from
relying on the OS page cache, then -1 from me. The usual response to the
"why don't you just use O_DIRECT?" is that it'd require reimplementing a
lot of I/O infrastructure, but misses an IMHO more important point: it
would require setting shared_buffers a lot higher to get the same level
of performance you get today. That has a number of problems:

1. It becomes a lot more important to tune shared_buffers correctly. Set
it too low, and you're not taking advantage of all the RAM available.
Set it too high, and you'll start swapping, totally killing performance.
I can already hear consultants rubbing their hands, waiting for the rush
of customers that will need expert help to determine the optimal
shared_buffers setting.

2. Memory spent on the buffer cache can't be used for other things. For
example, an index build can temporarily allocate several gigabytes of
memory; if that memory is allocated to the shared buffer cache, it can't
be used for that purpose. Yeah, we could change that, and allow
borrowing pages from the shared buffer cache for other purposes, but
that means more work and more code.

3. Memory used for the shared buffer cache can't be used by other
processes (without swapping). It becomes a lot harder to be a good
citizen on a system that's not entirely dedicated to PostgreSQL.

So not only would we need to re-implement I/O infrastructure, we'd also
need to make memory management a lot smarter and a lot more flexible.
We'd need a lot more information on what else is running on the system
and how badly they need memory.

> I personally think there is no chance of using mmap for that; the
> semantics of mmap are pretty much dictated by POSIX and they don't work
> for this.

Agreed. It would be possible to use mmap() for pages that are not
modified, though. When you're not modifying, you could mmap() the data
you need, and bypass the PostgreSQL buffer cache that way. The
interaction with the buffer cache becomes complicated, because you
couldn't use the buffer cache's locks etc., and some pages might have a
never version in the buffer cache than on-disk, but it might be doable.

- Heikki

Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> James Bottomley <James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:

>>> I don't understand why this has to be absolute: if you advise
>>> us to hold the pages dirty and we do up until it becomes a
>>> choice to hold on to the pages or to thrash the system into a
>>> livelock, why would you ever choose the latter?

Because the former creates database corruption and the latter does
not.

>>> And if, as I'm assuming, you never would,

That assumption is totally wrong.

>>> why don't you want the kernel to make that choice for you?
>>
>> If you don't understand how write-ahead logging works, this
>> conversation is going nowhere.  Suffice it to say that the word
>> "ahead" is not optional.
>
> In essence, if you do flush when you shouldn't, and there is a
> hardware failure, or kernel panic, or anything that stops the
> rest of the writes from succeeding, your database is kaputt, and
> you've got to restore a backup.
>
> Ie: very very bad.

Yup.  And when that's a few terrabytes, you will certainly find
yourself wishing that you had been able to do a recovery up to the
end of the last successfully committed transaction rather than a
restore from backup.

Now, as Tom said, if there was an API to create write boundaries
between particular dirty pages we could leave it to the OS.  Each
WAL record's write would be conditional on the previous one and
each data page write would be conditional on the WAL record for the
last update to the page.  But nobody seems to think that would
yield acceptable performance.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 14, 2014 at 11:57 AM, James Bottomley
<James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
> On Tue, 2014-01-14 at 11:48 -0500, Robert Haas wrote:
>> On Tue, Jan 14, 2014 at 11:44 AM, James Bottomley
>> <James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
>> > No, I'm sorry, that's never going to be possible. No user space
>> > application has all the facts. If we give you an interface to force
>> > unconditional holding of dirty pages in core you'll livelock the system
>> > eventually because you made a wrong decision to hold too many dirty
>> > pages. I don't understand why this has to be absolute: if you advise
>> > us to hold the pages dirty and we do up until it becomes a choice to
>> > hold on to the pages or to thrash the system into a livelock, why would
>> > you ever choose the latter? And if, as I'm assuming, you never would,
>> > why don't you want the kernel to make that choice for you?
>>
>> If you don't understand how write-ahead logging works, this
>> conversation is going nowhere. Suffice it to say that the word
>> "ahead" is not optional.
>
> No, I do ... you mean the order of write out, if we have to do it, is
> important. In the rest of the kernel, we do this with barriers which
> causes ordered grouping of I/O chunks. If we could force a similar
> ordering in the writeout code, is that enough?

Probably not. There are a whole raft of problems here. For that to
be any of any use, we'd have to move to mmap()ing each buffer instead
of read()ing them in, and apparently mmap() doesn't scale well to
millions of mappings. And even if it did, then we'd have a solution
that only works on Linux. Plus, as Tom pointed out, there are
critical sections where it's not just a question of ordering but in
fact you need to completely hold off writes.

In terms of avoiding double-buffering, here's my thought after reading
what's been written so far. Suppose we read a page into our buffer
pool. Until the page is clean, it would be ideal for the mapping to
be shared between the buffer cache and our pool, sort of like
copy-on-write. That way, if we decide to evict the page, it will
still be in the OS cache if we end up needing it again (remember, the
OS cache is typically much larger than our buffer pool). But if the
page is dirtied, then instead of copying it, just have the buffer pool
forget about it, because at that point we know we're going to write
the page back out anyway before evicting it.

This would be pretty similar to copy-on-write, except without the
copying. It would just be forget-from-the-buffer-pool-on-write.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 14, 2014 at 12:12 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> In terms of avoiding double-buffering, here's my thought after reading
> what's been written so far. Suppose we read a page into our buffer
> pool. Until the page is clean, it would be ideal for the mapping to

Correction: "For so long as the page is clean..."

> be shared between the buffer cache and our pool, sort of like
> copy-on-write. That way, if we decide to evict the page, it will
> still be in the OS cache if we end up needing it again (remember, the
> OS cache is typically much larger than our buffer pool). But if the
> page is dirtied, then instead of copying it, just have the buffer pool
> forget about it, because at that point we know we're going to write
> the page back out anyway before evicting it.
>
> This would be pretty similar to copy-on-write, except without the
> copying. It would just be forget-from-the-buffer-pool-on-write.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> In terms of avoiding double-buffering, here's my thought after reading
> what's been written so far. Suppose we read a page into our buffer
> pool. Until the page is clean, it would be ideal for the mapping to
> be shared between the buffer cache and our pool, sort of like
> copy-on-write. That way, if we decide to evict the page, it will
> still be in the OS cache if we end up needing it again (remember, the
> OS cache is typically much larger than our buffer pool). But if the
> page is dirtied, then instead of copying it, just have the buffer pool
> forget about it, because at that point we know we're going to write
> the page back out anyway before evicting it.
>
> This would be pretty similar to copy-on-write, except without the
> copying. It would just be forget-from-the-buffer-pool-on-write.

But... either copy-on-write or forget-on-write needs a page fault, and
thus a page mapping.

Is a page fault more expensive than copying 8k?

(I really don't know).

On Tue, Jan 14, 2014 at 12:15 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> In terms of avoiding double-buffering, here's my thought after reading
>> what's been written so far. Suppose we read a page into our buffer
>> pool. Until the page is clean, it would be ideal for the mapping to
>> be shared between the buffer cache and our pool, sort of like
>> copy-on-write. That way, if we decide to evict the page, it will
>> still be in the OS cache if we end up needing it again (remember, the
>> OS cache is typically much larger than our buffer pool). But if the
>> page is dirtied, then instead of copying it, just have the buffer pool
>> forget about it, because at that point we know we're going to write
>> the page back out anyway before evicting it.
>>
>> This would be pretty similar to copy-on-write, except without the
>> copying. It would just be forget-from-the-buffer-pool-on-write.
>
> But... either copy-on-write or forget-on-write needs a page fault, and
> thus a page mapping.
>
> Is a page fault more expensive than copying 8k?

I don't know either. I wasn't thinking so much that it would save CPU
time as that it would save memory. Consider a system with 32GB of
RAM. If you set shared_buffers=8GB, then in the worst case you've got
25% of your RAM wasted storing pages that already exist, dirtied, in
shared_buffers. It's easy to imagine scenarios in which that results
in lots of extra I/O, so that the CPU required to do the accounting
comes to seem cheap by comparison.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 01/14/2014 05:44 PM, James Bottomley wrote:
> On Tue, 2014-01-14 at 10:39 -0500, Tom Lane wrote:
>> James Bottomley <James(dot)Bottomley(at)HansenPartnership(dot)com> writes:
>>> The current mechanism for coherency between a userspace cache and the
>>> in-kernel page cache is mmap ... that's the only way you get the same
>>> page in both currently.
>> Right.
>>
>>> glibc used to have an implementation of read/write in terms of mmap, so
>>> it should be possible to insert it into your current implementation
>>> without a major rewrite. The problem I think this brings you is
>>> uncontrolled writeback: you don't want dirty pages to go to disk until
>>> you issue a write()
>> Exactly.
>>
>>> I think we could fix this with another madvise():
>>> something like MADV_WILLUPDATE telling the page cache we expect to alter
>>> the pages again, so don't be aggressive about cleaning them.
>> "Don't be aggressive" isn't good enough. The prohibition on early write
>> has to be absolute, because writing a dirty page before we've done
>> whatever else we need to do results in a corrupt database. It has to
>> be treated like a write barrier.
>>
>>> The problem is we can't give you absolute control of when pages are
>>> written back because that interface can be used to DoS the system: once
>>> we get too many dirty uncleanable pages, we'll thrash looking for memory
>>> and the system will livelock.
>> Understood, but that makes this direction a dead end. We can't use
>> it if the kernel might decide to write anyway.
> No, I'm sorry, that's never going to be possible. No user space
> application has all the facts. If we give you an interface to force
> unconditional holding of dirty pages in core you'll livelock the system
> eventually because you made a wrong decision to hold too many dirty
> pages. I don't understand why this has to be absolute: if you advise
> us to hold the pages dirty and we do up until it becomes a choice to
> hold on to the pages or to thrash the system into a livelock, why would
> you ever choose the latter? And if, as I'm assuming, you never would,
> why don't you want the kernel to make that choice for you?
The short answer is "crash safety".

A database system worth its name must make sure that all data
reported as stored to clients is there even after crash.

Write ahead log is the means for that. And writing wal files and
data pages has to be in certain order to guarantee consistent
recovery after crash.

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

On Tue, 2014-01-14 at 15:15 -0200, Claudio Freire wrote:
> On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> >
> > In terms of avoiding double-buffering, here's my thought after reading
> > what's been written so far. Suppose we read a page into our buffer
> > pool. Until the page is clean, it would be ideal for the mapping to
> > be shared between the buffer cache and our pool, sort of like
> > copy-on-write. That way, if we decide to evict the page, it will
> > still be in the OS cache if we end up needing it again (remember, the
> > OS cache is typically much larger than our buffer pool). But if the
> > page is dirtied, then instead of copying it, just have the buffer pool
> > forget about it, because at that point we know we're going to write
> > the page back out anyway before evicting it.
> >
> > This would be pretty similar to copy-on-write, except without the
> > copying. It would just be forget-from-the-buffer-pool-on-write.
>
>
> But... either copy-on-write or forget-on-write needs a page fault, and
> thus a page mapping.
>
> Is a page fault more expensive than copying 8k?
>
> (I really don't know).

A page fault can be expensive, yes ... but perhaps you don't need one.

What you want is a range of memory that's read from a file but treated
as anonymous for writeout (i.e. written to swap if we need to reclaim
it). Then at some time later, you want to designate it as written back
to the file instead so you control the writeout order. I'm not sure we
can do this: the separation between file backed and anonymous pages is
pretty deeply ingrained into the OS, but if it were possible, is that
what you want?

James

James Bottomley <James(dot)Bottomley(at)HansenPartnership(dot)com> wrote:

> you mean the order of write out, if we have to do it, is
> important.  In the rest of the kernel, we do this with barriers
> which causes ordered grouping of I/O chunks.  If we could force a
> similar ordering in the writeout code, is that enough?

Unless it can be between particular pairs of pages, I don't think
performance could be at all acceptable.  Each data page has an
associated Log Sequence Number reflecting the last Write-Ahead Log
record which records a change to that page, and the referenced WAL
record must be safely persisted before the data page is allowed to
be written.  Currently, when we need to write a dirty page to the
OS, we must ensure that the WAL record is written and fsync'd
first.  We also write a WAL record for transaction command and
fsync it at each COMMIT, before telling the client that the COMMIT
request was successful.  (Well, at least by default; they can
choose to set synchronous_commit to off for some or all
transactions.)  If a write barrier to control this applied to
everything on the filesystem, performance would be horrible.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jan 13, 2014 at 2:36 PM, Mel Gorman <mgorman(at)suse(dot)de> wrote:

> On Mon, Jan 13, 2014 at 06:27:03PM -0200, Claudio Freire wrote:
> > On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> > > On 1/13/14, 2:19 PM, Claudio Freire wrote:
> > >>
> > >> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> > >> wrote:
> > >>>
> > >>> On a related note, there's also the problem of double-buffering.
> When
> > >>> we read a page into shared_buffers, we leave a copy behind in the OS
> > >>> buffers, and similarly on write-out. It's very unclear what to do
> > >>> about this, since the kernel and PostgreSQL don't have intimate
> > >>> knowledge of what each other are doing, but it would be nice to solve
> > >>> somehow.
> > >>
> > >>
> > >>
> > >> There you have a much harder algorithmic problem.
> > >>
> > >> You can basically control duplication with fadvise and WONTNEED. The
> > >> problem here is not the kernel and whether or not it allows postgres
> > >> to be smart about it. The problem is... what kind of smarts
> > >> (algorithm) to use.
> > >
> > >
> > > Isn't this a fairly simple matter of when we read a page into shared
> buffers
> > > tell the kernel do forget that page? And a corollary to that for when
> we
> > > dump a page out of shared_buffers (here kernel, please put this back
> into
> > > your cache).
> >
> >
> > That's my point. In terms of kernel-postgres interaction, it's fairly
> simple.
> >
> > What's not so simple, is figuring out what policy to use. Remember,
> > you cannot tell the kernel to put some page in its page cache without
> > reading it or writing it. So, once you make the kernel forget a page,
> > evicting it from shared buffers becomes quite expensive.
>
> posix_fadvise(POSIX_FADV_WILLNEED) is meant to cover this case by
> forcing readahead.

But telling the kernel to forget a page, then telling it to read it in
again from disk because it might be needed again in the near future is
itself very expensive. We would need to hand the page to the kernel so it
has it without needing to go to disk to get it.

> If you evict it prematurely then you do get kinda
> screwed because you pay the IO cost to read it back in again even if you
> had enough memory to cache it. Maybe this is the type of kernel-postgres
> interaction that is annoying you.
>
> If you don't evict, the kernel eventually steps in and evicts the wrong
> thing. If you do evict and it was unnecessarily you pay an IO cost.
>
> That could be something we look at. There are cases buried deep in the
> VM where pages get shuffled to the end of the LRU and get tagged for
> reclaim as soon as possible. Maybe you need access to something like
> that via posix_fadvise to say "reclaim this page if you need memory but
> leave it resident if there is no memory pressure" or something similar.
> Not exactly sure what that interface would look like or offhand how it
> could be reliably implemented.
>

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.

Cheers,

Jeff

On Tue, Jan 14, 2014 at 12:20 PM, James Bottomley
<James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
> On Tue, 2014-01-14 at 15:15 -0200, Claudio Freire wrote:
>> On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> > In terms of avoiding double-buffering, here's my thought after reading
>> > what's been written so far. Suppose we read a page into our buffer
>> > pool. Until the page is clean, it would be ideal for the mapping to
>> > be shared between the buffer cache and our pool, sort of like
>> > copy-on-write. That way, if we decide to evict the page, it will
>> > still be in the OS cache if we end up needing it again (remember, the
>> > OS cache is typically much larger than our buffer pool). But if the
>> > page is dirtied, then instead of copying it, just have the buffer pool
>> > forget about it, because at that point we know we're going to write
>> > the page back out anyway before evicting it.
>> >
>> > This would be pretty similar to copy-on-write, except without the
>> > copying. It would just be forget-from-the-buffer-pool-on-write.
>>
>> But... either copy-on-write or forget-on-write needs a page fault, and
>> thus a page mapping.
>>
>> Is a page fault more expensive than copying 8k?
>>
>> (I really don't know).
>
> A page fault can be expensive, yes ... but perhaps you don't need one.
>
> What you want is a range of memory that's read from a file but treated
> as anonymous for writeout (i.e. written to swap if we need to reclaim
> it). Then at some time later, you want to designate it as written back
> to the file instead so you control the writeout order. I'm not sure we
> can do this: the separation between file backed and anonymous pages is
> pretty deeply ingrained into the OS, but if it were possible, is that
> what you want?

Doesn't sound exactly like what I had in mind. What I was suggesting
is an analogue of read() that, if it reads full pages of data to a
page-aligned address, shares the data with the buffer cache until it's
first written instead of actually copying the data. The pages are
write-protected so that an attempt to write the address range causes a
page fault. In response to such a fault, the pages become anonymous
memory and the buffer cache no longer holds a reference to the page.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Jan 14, 2014 at 11:57 AM, James Bottomley
> <James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
>> No, I do ... you mean the order of write out, if we have to do it, is
>> important. In the rest of the kernel, we do this with barriers which
>> causes ordered grouping of I/O chunks. If we could force a similar
>> ordering in the writeout code, is that enough?

> Probably not. There are a whole raft of problems here. For that to
> be any of any use, we'd have to move to mmap()ing each buffer instead
> of read()ing them in, and apparently mmap() doesn't scale well to
> millions of mappings.

We would presumably mmap whole files, not individual pages (at least
on 64-bit machines; else address space size is going to be a problem).
However, without a fix for the critical-section/atomic-update problem,
the idea's still going nowhere.

> This would be pretty similar to copy-on-write, except without the
> copying. It would just be forget-from-the-buffer-pool-on-write.

That might possibly work.

regards, tom lane

On Mon, Jan 13, 2014 at 6:44 PM, Dave Chinner <david(at)fromorbit(dot)com> wrote:

> On Tue, Jan 14, 2014 at 02:26:25AM +0100, Andres Freund wrote:
> > On 2014-01-13 17:13:51 -0800, James Bottomley wrote:
> > > a file into a user provided buffer, thus obtaining a page cache entry
> > > and a copy in their userspace buffer, then insert the page of the user
> > > buffer back into the page cache as the page cache page ... that's
> right,
> > > isn't it postgress people?
> >
> > Pretty much, yes. We'd probably hint (*advise(DONTNEED)) that the page
> > isn't needed anymore when reading. And we'd normally write if the page
> > is dirty.
>
> So why, exactly, do you even need the kernel page cache here?

We don't need it, but it would be nice.

> You've
> got direct access to the copy of data read into userspace, and you
> want direct control of when and how the data in that buffer is
> written and reclaimed. Why push that data buffer back into the
> kernel and then have to add all sorts of kernel interfaces to
> control the page you already have control of?
>

Say 25% of the RAM is dedicated to the database's shared buffers, and 75%
is left to the kernel's judgement. It sure would be nice if the kernel had
the capability of using some of that 75% for database pages, if it thought
that that was the best use for it.

Which is what we do get now, at the expense of quite a lot of double
buffering (by which I mean, a lot of pages are both in the kernel cache and
the database cache--not just transiently during the copy process, but for
quite a while). If we had the ability to re-inject clean pages into the
kernel's cache, we would get that benefit without the double buffering.

Cheers,

Jeff

On Tue, Jan 14, 2014 at 2:17 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Jan 14, 2014 at 12:15 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
>> On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> In terms of avoiding double-buffering, here's my thought after reading
>>> what's been written so far. Suppose we read a page into our buffer
>>> pool. Until the page is clean, it would be ideal for the mapping to
>>> be shared between the buffer cache and our pool, sort of like
>>> copy-on-write. That way, if we decide to evict the page, it will
>>> still be in the OS cache if we end up needing it again (remember, the
>>> OS cache is typically much larger than our buffer pool). But if the
>>> page is dirtied, then instead of copying it, just have the buffer pool
>>> forget about it, because at that point we know we're going to write
>>> the page back out anyway before evicting it.
>>>
>>> This would be pretty similar to copy-on-write, except without the
>>> copying. It would just be forget-from-the-buffer-pool-on-write.
>>
>> But... either copy-on-write or forget-on-write needs a page fault, and
>> thus a page mapping.
>>
>> Is a page fault more expensive than copying 8k?
>
> I don't know either. I wasn't thinking so much that it would save CPU
> time as that it would save memory. Consider a system with 32GB of
> RAM. If you set shared_buffers=8GB, then in the worst case you've got
> 25% of your RAM wasted storing pages that already exist, dirtied, in
> shared_buffers. It's easy to imagine scenarios in which that results
> in lots of extra I/O, so that the CPU required to do the accounting
> comes to seem cheap by comparison.

Not necessarily, you pay the CPU cost on each page fault (ie: first
write to the buffer at least), each time the page checks into the
shared buffers level.

It's like a tiered cache.

When promoting is expensive, one must be careful. The traffic to/from
the L0 (shared buffers) and L1 (page cache) will be considerable, even
if everything fits in RAM.

I guess it's the constant battle between inclusive and exclusive caches.

On Tue 14-01-14 06:42:43, Kevin Grittner wrote:
> First off, I want to give a +1 on everything in the recent posts
> from Heikki and Hannu.
>
> Jan Kara <jack(at)suse(dot)cz> wrote:
>
> > Now the aging of pages marked as volatile as it is currently
> > implemented needn't be perfect for your needs but you still have
> > time to influence what gets implemented... Actually developers of
> > the vrange() syscall were specifically looking for some ideas
> > what to base aging on. Currently I think it is first marked -
> > first evicted.
>
> The "first marked - first evicted" seems like what we would want.
> The ability to "unmark" and have the page no longer be considered
> preferred for eviction would be very nice.  That seems to me like
> it would cover the multiple layers of buffering *clean* pages very
> nicely (although I know nothing more about vrange() than what has
> been said on this thread, so I could be missing something).
Here:
http://www.spinics.net/lists/linux-mm/msg67328.html
is an email which introduces the syscall. As you say, it might be a
reasonable fit for your problems with double caching of clean pages.

> The other side of that is related avoiding multiple writes of the
> same page as much as possible, while avoid write gluts.  The issue
> here is that PostgreSQL tries to hang on to dirty pages for as long
> as possible before "writing" them to the OS cache, while the OS
> tries to avoid writing them to storage for as long as possible
> until they reach a (configurable) threshold or are fsync'd.  The
> problem is that a under various conditions PostgreSQL may need to
> write and fsync a lot of dirty pages it has accumulated in a short
> time.  That has an "avalanche" effect, creating a "write glut"
> which can stall all I/O for a period of many seconds up to a few
> minutes.  If the OS was aware of the dirty pages pending write in
> the application, and counted those for purposes of calculating when
> and how much to write, the glut could be avoided.  Currently,
> people configure the PostgreSQL background writer to be very
> aggressive, configure a small PostgreSQL shared_buffers setting,
> and/or set the OS thresholds low enough to minimize the problem;
> but all of these mitigation strategies have their own costs.
>
> A new hint that the application has dirtied a page could be used by
> the OS to improve things this way:  When the OS is notified that a
> page is dirty, it takes action depending on whether the page is
> considered dirty by the OS.  If it is not dirty, the page is
> immediately discarded from the OS cache.  It is known that the
> application has a modified version of the page that it intends to
> write, so the version in the OS cache has no value.  We don't want
> this page forcing eviction of vrange()-flagged pages.  If it is
> dirty, any write ordering to storage by the OS based on when the
> page was written to the OS would be pushed back as far as possible
> without crossing any write barriers, in hopes that the writes could
> be combined.  Either way, this page is counted toward dirty pages
> for purposes of calculating how much to write from the OS to
> storage, and the later write of the page doesn't redundantly add to
> this number.
The evict if clean part is easy. That could be easily a new fadvise()
option - btw. note that POSIX_FADV_DONTNEED has quite close meaning. Only
that it also starts writeback on a dirty page if backing device isn't
congested. Which is somewhat contrary to what you want to achieve. But I'm
not sure the eviction would be a clear win since filesystem then has to
re-create the mapping from logical file block to disk block (it is cached
in the page) and that potentially needs to go to disk to fetch the mapping
data.

I have a hard time thinking how we would implement pushing back writeback
of a particular page (or better set of pages). When we need to write pages
because we are nearing dirty_bytes limit, we likely want to write these
marked pages anyway to make as many pages freeable as possible. So the only
thing we could do is to ignore these pages during periodic writeback and
I'm not sure that would make a big difference.

Just to get some idea about the sizes - how large are the checkpoints we
are talking about that cause IO stalls?

Honza

--
Jan Kara <jack(at)suse(dot)cz>
SUSE Labs, CR

On Tue, Jan 14, 2014 at 2:39 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Jan 14, 2014 at 12:20 PM, James Bottomley
> <James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
>> On Tue, 2014-01-14 at 15:15 -0200, Claudio Freire wrote:
>>> On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> > In terms of avoiding double-buffering, here's my thought after reading
>>> > what's been written so far. Suppose we read a page into our buffer
>>> > pool. Until the page is clean, it would be ideal for the mapping to
>>> > be shared between the buffer cache and our pool, sort of like
>>> > copy-on-write. That way, if we decide to evict the page, it will
>>> > still be in the OS cache if we end up needing it again (remember, the
>>> > OS cache is typically much larger than our buffer pool). But if the
>>> > page is dirtied, then instead of copying it, just have the buffer pool
>>> > forget about it, because at that point we know we're going to write
>>> > the page back out anyway before evicting it.
>>> >
>>> > This would be pretty similar to copy-on-write, except without the
>>> > copying. It would just be forget-from-the-buffer-pool-on-write.
>>>
>>> But... either copy-on-write or forget-on-write needs a page fault, and
>>> thus a page mapping.
>>>
>>> Is a page fault more expensive than copying 8k?
>>>
>>> (I really don't know).
>>
>> A page fault can be expensive, yes ... but perhaps you don't need one.
>>
>> What you want is a range of memory that's read from a file but treated
>> as anonymous for writeout (i.e. written to swap if we need to reclaim
>> it). Then at some time later, you want to designate it as written back
>> to the file instead so you control the writeout order. I'm not sure we
>> can do this: the separation between file backed and anonymous pages is
>> pretty deeply ingrained into the OS, but if it were possible, is that
>> what you want?
>
> Doesn't sound exactly like what I had in mind. What I was suggesting
> is an analogue of read() that, if it reads full pages of data to a
> page-aligned address, shares the data with the buffer cache until it's
> first written instead of actually copying the data. The pages are
> write-protected so that an attempt to write the address range causes a
> page fault. In response to such a fault, the pages become anonymous
> memory and the buffer cache no longer holds a reference to the page.

Yes, that's basically zero-copy reads.

It could be done. The kernel can remap the page to the physical page
holding the shared buffer and mark it read-only, then expire the
buffer and transfer ownership of the page if any page fault happens.

But that incurrs:
- Page faults, lots
- Hugely bloated mappings, unless KSM is somehow leveraged for this

And there's a nice bingo. Had forgotten about KSM. KSM could help lots.

I could try to see of madvising shared_buffers as mergeable helps. But
this should be an automatic case of KSM - ie, when reading into a
page-aligned address, the kernel should summarily apply KSM-style
sharing without hinting. The current madvise interface puts the burden
of figuring out what duplicates what on the kernel, but postgres
already knows.

* Claudio Freire (klaussfreire(at)gmail(dot)com) wrote:
> On Tue, Jan 14, 2014 at 2:17 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > I don't know either. I wasn't thinking so much that it would save CPU
> > time as that it would save memory. Consider a system with 32GB of
> > RAM. If you set shared_buffers=8GB, then in the worst case you've got
> > 25% of your RAM wasted storing pages that already exist, dirtied, in
> > shared_buffers. It's easy to imagine scenarios in which that results
> > in lots of extra I/O, so that the CPU required to do the accounting
> > comes to seem cheap by comparison.
>
> Not necessarily, you pay the CPU cost on each page fault (ie: first
> write to the buffer at least), each time the page checks into the
> shared buffers level.

I'm really not sure that this is a real issue for us, but if it is,
perhaps having this as an option for each read() call would work..?
That is to say, rather than have this be an open() flag or similar, it's
normal read() with a flags field where we could decide when we want
pages to be write-protected this way and when we don't (perhaps because
we know we're about to write to them).

I'm not 100% sure it'd be easy for us to manage that flag perfectly, but
it's our issue and it'd be on us to deal with as the kernel can't
possibly guess our intentions.

There were concerns brought up earlier that such a zero-copy-read option
wouldn't be performant though and I'm curious to hear more about those
and if we could avoid the performance issues by manging the
zero-copy-read case ourselves as Robert suggests.

Thanks,

Stephen

On Tue 14-01-14 10:04:16, Robert Haas wrote:
> On Tue, Jan 14, 2014 at 5:00 AM, Jan Kara <jack(at)suse(dot)cz> wrote:
> > I thought that instead of injecting pages into pagecache for aging as you
> > describe in 3), you would mark pages as volatile (i.e. for reclaim by
> > kernel) through vrange() syscall. Next time you need the page, you check
> > whether the kernel reclaimed the page or not. If yes, you reload it from
> > disk, if not, you unmark it and use it.
> >
> > Now the aging of pages marked as volatile as it is currently implemented
> > needn't be perfect for your needs but you still have time to influence what
> > gets implemented... Actually developers of the vrange() syscall were
> > specifically looking for some ideas what to base aging on. Currently I
> > think it is first marked - first evicted.
>
> This is an interesting idea but it stinks of impracticality.
> Essentially when the last buffer pin on a page is dropped we'd have to
> mark it as discardable, and then the next person wanting to pin it
> would have to check whether it's still there. But the system call
> overhead of calling vrange() every time the last pin on a page was
> dropped would probably hose us.
>
> *thinks*
>
> Well, I guess it could be done lazily: make periodic sweeps through
> shared_buffers, looking for pages that haven't been touched in a
> while, and vrange() them. That's quite a bit of new mechanism, but in
> theory it could work out to a win. vrange() would have to scale well
> to millions of separate ranges, though. Will it?
It is intented to be rather lightweight so I believe milions should be
OK. But I didn't try :).

> And a lot depends on whether the kernel makes the right decision about
> whether to chunk data from our vrange() vs. any other page it could have
> reclaimed.
I think the intent is to reclaim pages in the following order:
used once pages -> volatile pages -> active pages, swapping

Honza
--
Jan Kara <jack(at)suse(dot)cz>
SUSE Labs, CR

On 14/01/14 14:09, Dave Chinner wrote:
> On Mon, Jan 13, 2014 at 09:29:02PM +0000, Greg Stark wrote:
>> On Mon, Jan 13, 2014 at 9:12 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
[...]
>> The more ambitious and interesting direction is to let Postgres tell
>> the kernel what it needs to know to manage everything. To do that we
>> would need the ability to control when pages are flushed out. This is
>> absolutely necessary to maintain consistency. Postgres would need to
>> be able to mark pages as unflushable until some point in time in the
>> future when the journal is flushed. We discussed various ways that
>> interface could work but it would be tricky to keep it low enough
>> overhead to be workable.
> IMO, the concept of allowing userspace to pin dirty page cache
> pages in memory is just asking for trouble. Apart from the obvious
> memory reclaim and OOM issues, some filesystems won't be able to
> move their journals forward until the data is flushed. i.e. ordered
> mode data writeback on ext3 will have all sorts of deadlock issues
> that result from pinning pages and then issuing fsync() on another
> file which will block waiting for the pinned pages to be flushed.
>
> Indeed, what happens if you do pin_dirty_pages(fd); .... fsync(fd);?
> If fsync() blocks because there are pinned pages, and there's no
> other thread to unpin them, then that code just deadlocked. If
> fsync() doesn't block and skips the pinned pages, then we haven't
> done an fsync() at all, and so violated the expectation that users
> have that after fsync() returns their data is safe on disk. And if
> we return an error to fsync(), then what the hell does the user do
> if it is some other application we don't know about that has pinned
> the pages? And if the kernel unpins them after some time, then we
> just violated the application's consistency guarantees....
>
[...]

What if Postgres could tell the kernel how strongly that it wanted to
hold on to the pages?

Say a byte (this is arbitrary, it could be a single hint bit which meant
"please, Please, PLEASE don't flush, if that is okay with you Mr
Kernel..."), so strength would be S = (unsigned byte value)/256, so 0 <=
S < 1.

S = 0 flush now.
0 < S < 1 flush if the 'need' is greater than the S
S = 1 never flush (note a value of 1 cannot occur, as max S = 255/256)

Postgres could use low non-zero S values if it thinks that pages /might/
still be useful later, and very high values when it is /more certain/.
I am sure Postgres must sometimes know when some pages are more
important to held onto than others, hence my feeling that S should be
more than one bit.

The kernel might simply flush pages starting at ones with low values of
S working upwards until it has freed enough memory to resolve its memory
pressure. So an explicit numerical value of 'need' (as implied above)
is not required. Also any practical implementation would not use 'S' as
a float/double, but use integer values for 'S' & 'need' - assuming that
'need' did have to be an actual value, which I suspect would not be
reequired.

This way the kernel is free to flush all such pages, when sufficient
need arises - yet usually, when there is sufficient memory, the pages
will be held unflushed.

Cheers,
Gavin

On Tue, Jan 14, 2014 at 1:37 PM, Jan Kara <jack(at)suse(dot)cz> wrote:
> Just to get some idea about the sizes - how large are the checkpoints we
> are talking about that cause IO stalls?

Big. Potentially, we might have dirtied all of shared_buffers and
then started evicting pages from there to the OS buffer pool and
dirties as much memory as the OS will allow, and then the OS might
have started writeback and filled up all the downstream caches between
the OS and the disk. And then just then the checkpoint hits.

I dunno what a typical checkpoint size is but I don't think you'll be
exaggerating much if you imagine that everything that could possibly
be dirty is.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 14, 2014 at 2:03 PM, Gavin Flower
<GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
> Say a byte (this is arbitrary, it could be a single hint bit which meant
> "please, Please, PLEASE don't flush, if that is okay with you Mr
> Kernel..."), so strength would be S = (unsigned byte value)/256, so 0 <= S <
> 1.
>
> S = 0 flush now.
> 0 < S < 1 flush if the 'need' is greater than the S
> S = 1 never flush (note a value of 1 cannot occur, as max S = 255/256)
>
> Postgres could use low non-zero S values if it thinks that pages might still
> be useful later, and very high values when it is more certain. I am sure
> Postgres must sometimes know when some pages are more important to held onto
> than others, hence my feeling that S should be more than one bit.
>
> The kernel might simply flush pages starting at ones with low values of S
> working upwards until it has freed enough memory to resolve its memory
> pressure. So an explicit numerical value of 'need' (as implied above) is
> not required. Also any practical implementation would not use 'S' as a
> float/double, but use integer values for 'S' & 'need' - assuming that 'need'
> did have to be an actual value, which I suspect would not be reequired.
>
> This way the kernel is free to flush all such pages, when sufficient need
> arises - yet usually, when there is sufficient memory, the pages will be
> held unflushed.

Well, this just begs the question of what value PG ought to pass as
the parameter.

I think the alternate don't-need semantics (we don't think we need
this but please don't throw it away arbitrarily if there's no memory
pressure) would be a big win. I don't think we know enough in user
space to be more precise than that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> I dunno what a typical checkpoint size is but I don't think you'll be
> exaggerating much if you imagine that everything that could possibly
> be dirty is.

This is not uncommon for us, at least:

checkpoint complete: wrote 425844 buffers (20.3%); 0 transaction log
file(s) added, 0 removed, 249 recycled; write=175.535 s, sync=17.428 s,
total=196.357 s; sync files=1011, longest=2.675 s, average=0.017 s

That's a checkpoint writing out 20% of 16GB, or over 3GB, and that's
just from one of the four postmasters running- we get this kind of
checkpointing happening on all of them. All told, it's easy for us to
want to write over 12GB during a single checkpoint period on this box.
(checkpoint_timeout is 5m, checkpoint_target is 0.9).

Thankfully, the box has 256G of RAM and so the shared buffers only use
up 25% of the RAM in the box. :)

I'm sure others could post larger numbers.

Thanks,

Stephen

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Jan Kara <jack(at)suse(dot)cz> wrote:
>
>> Just to get some idea about the sizes - how large are the
>> checkpoints we are talking about that cause IO stalls?
>
> Big.

To quantify that, in a production setting we were seeing pauses of
up to two minutes with shared_buffers set to 8GB and default dirty
page settings for Linux, on a machine with 256GB RAM and 512MB
non-volatile cache on the RAID controller.  To eliminate stalls we
had to drop shared_buffers to 2GB (to limit how many dirty pages
could build up out-of-sight from the OS), spread checkpoints to 90%
of allowed time (almost no gap between finishing one checkpoint and
starting the next) and crank up the background writer so that no
dirty page sat unwritten in PostgreSQL shared_buffers for more than
4 seconds. Less aggressive pushing to the OS resulted in the
avalanche of writes I previously described, with the corresponding
I/O stalls.  We approached that incrementally, and that's the point
where stalls stopped occurring.  We did not adjust the OS
thresholds for writing dirty pages, although I know of others who
have had to do so.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, 2014-01-14 at 12:39 -0500, Robert Haas wrote:
> On Tue, Jan 14, 2014 at 12:20 PM, James Bottomley
> <James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
> > On Tue, 2014-01-14 at 15:15 -0200, Claudio Freire wrote:
> >> On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> >> > In terms of avoiding double-buffering, here's my thought after reading
> >> > what's been written so far. Suppose we read a page into our buffer
> >> > pool. Until the page is clean, it would be ideal for the mapping to
> >> > be shared between the buffer cache and our pool, sort of like
> >> > copy-on-write. That way, if we decide to evict the page, it will
> >> > still be in the OS cache if we end up needing it again (remember, the
> >> > OS cache is typically much larger than our buffer pool). But if the
> >> > page is dirtied, then instead of copying it, just have the buffer pool
> >> > forget about it, because at that point we know we're going to write
> >> > the page back out anyway before evicting it.
> >> >
> >> > This would be pretty similar to copy-on-write, except without the
> >> > copying. It would just be forget-from-the-buffer-pool-on-write.
> >>
> >> But... either copy-on-write or forget-on-write needs a page fault, and
> >> thus a page mapping.
> >>
> >> Is a page fault more expensive than copying 8k?
> >>
> >> (I really don't know).
> >
> > A page fault can be expensive, yes ... but perhaps you don't need one.
> >
> > What you want is a range of memory that's read from a file but treated
> > as anonymous for writeout (i.e. written to swap if we need to reclaim
> > it). Then at some time later, you want to designate it as written back
> > to the file instead so you control the writeout order. I'm not sure we
> > can do this: the separation between file backed and anonymous pages is
> > pretty deeply ingrained into the OS, but if it were possible, is that
> > what you want?
>
> Doesn't sound exactly like what I had in mind. What I was suggesting
> is an analogue of read() that, if it reads full pages of data to a
> page-aligned address, shares the data with the buffer cache until it's
> first written instead of actually copying the data.

The only way to make this happen is mmap the file to the buffer and use
MADV_WILLNEED.

> The pages are
> write-protected so that an attempt to write the address range causes a
> page fault. In response to such a fault, the pages become anonymous
> memory and the buffer cache no longer holds a reference to the page.

OK, so here I thought of another madvise() call to switch the region to
anonymous memory. A page fault works too, of course, it's just that one
per page in the mapping will be expensive.

Do you care about handling aliases ... what happens if someone else
reads from the file, or will that never occur? The reason for asking is
that it's much easier if someone else mmapping the file gets your
anonymous memory than we create an alias in the page cache.

James

On Tue, Jan 14, 2014 at 3:00 PM, James Bottomley
<James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
>> Doesn't sound exactly like what I had in mind. What I was suggesting
>> is an analogue of read() that, if it reads full pages of data to a
>> page-aligned address, shares the data with the buffer cache until it's
>> first written instead of actually copying the data.
>
> The only way to make this happen is mmap the file to the buffer and use
> MADV_WILLNEED.
>
>> The pages are
>> write-protected so that an attempt to write the address range causes a
>> page fault. In response to such a fault, the pages become anonymous
>> memory and the buffer cache no longer holds a reference to the page.
>
> OK, so here I thought of another madvise() call to switch the region to
> anonymous memory. A page fault works too, of course, it's just that one
> per page in the mapping will be expensive.

I don't think either of these ideas works for us. We start by
creating a chunk of shared memory that all processes (we do not use
threads) will have mapped at a common address, and we read() and
write() into that chunk.

> Do you care about handling aliases ... what happens if someone else
> reads from the file, or will that never occur? The reason for asking is
> that it's much easier if someone else mmapping the file gets your
> anonymous memory than we create an alias in the page cache.

All reads and writes go through the buffer pool stored in shared
memory, but any of the processes that have that shared memory region
mapped could be responsible for any individual I/O request.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, 2014-01-14 at 15:09 -0500, Robert Haas wrote:
> On Tue, Jan 14, 2014 at 3:00 PM, James Bottomley
> <James(dot)Bottomley(at)hansenpartnership(dot)com> wrote:
> >> Doesn't sound exactly like what I had in mind. What I was suggesting
> >> is an analogue of read() that, if it reads full pages of data to a
> >> page-aligned address, shares the data with the buffer cache until it's
> >> first written instead of actually copying the data.
> >
> > The only way to make this happen is mmap the file to the buffer and use
> > MADV_WILLNEED.
> >
> >> The pages are
> >> write-protected so that an attempt to write the address range causes a
> >> page fault. In response to such a fault, the pages become anonymous
> >> memory and the buffer cache no longer holds a reference to the page.
> >
> > OK, so here I thought of another madvise() call to switch the region to
> > anonymous memory. A page fault works too, of course, it's just that one
> > per page in the mapping will be expensive.
>
> I don't think either of these ideas works for us. We start by
> creating a chunk of shared memory that all processes (we do not use
> threads) will have mapped at a common address, and we read() and
> write() into that chunk.

Yes, that's what I was thinking: it's a cache. About how many files
comprise this cache? Are you thinking it's too difficult for every
process to map the files?

> > Do you care about handling aliases ... what happens if someone else
> > reads from the file, or will that never occur? The reason for asking is
> > that it's much easier if someone else mmapping the file gets your
> > anonymous memory than we create an alias in the page cache.
>
> All reads and writes go through the buffer pool stored in shared
> memory, but any of the processes that have that shared memory region
> mapped could be responsible for any individual I/O request.

That seems to be possible with the abstraction. The initial mapping
gets the file backed pages: you can do madvise to read them (using
readahead), flush them (using wontneed) and flip them to anonymous
(using something TBD). Since it's a shared mapping API based on the
file, any of the mapping processes can do any operation. Future mappers
of the file get the mix of real and anon memory, so it's truly shared.

Given that you want to use this as a shared cache, it seems that the API
to flip back from anon to file mapped is wontneed. That would also
trigger writeback of any dirty pages in the previously anon region ...
which you could force with msync. As far as I can see, this is
identical to read/write on a shared region with the exception that you
don't need to copy in and out of the page cache.

From our point of view, the implementation is nice because the pages
effectively never leave the page cache. We just use an extra per page
flag (which I'll get shot for suggesting) to alter the writeout path
(which is where the complexity which may kill the implementation is).

James

James Bottomley <James(dot)Bottomley(at)HansenPartnership(dot)com> wrote:

> About how many files comprise this cache?  Are you thinking it's
> too difficult for every process to map the files?

The shared_buffers area can be mapping anywhere from about 200
files to millions of files, representing a total space of about 6MB
on the low end to over 100TB on the high end.  For many workloads
performance falls off above a shared_buffers size of about 8GB,
although for data warehousing environments larger sizes sometimes
work out and to avoid write gluts it must often be limited to 1GB
to 1GB.

Data access is in fixed-sized pages, normally of 8KB each.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 14, 2014 at 09:40:48AM -0500, Robert Haas wrote:
> On Mon, Jan 13, 2014 at 5:26 PM, Mel Gorman <mgorman(at)suse(dot)de> wrote:
> >> Amen to that. Actually, I think NUMA can be (mostly?) fixed by
> >> setting zone_reclaim_mode; is there some other problem besides that?
> >
> > Really?
> >
> > zone_reclaim_mode is often a complete disaster unless the workload is
> > partitioned to fit within NUMA nodes. On older kernels enabling it would
> > sometimes cause massive stalls. I'm actually very surprised to hear it
> > fixes anything and would be interested in hearing more about what sort
> > of circumstnaces would convince you to enable that thing.
>
> By "set" I mean "set to zero". We've seen multiple of instances of
> people complaining about large amounts of system memory going unused
> because this setting defaulted to 1.
>
> >> The other thing that comes to mind is the kernel's caching behavior.
> >> We've talked a lot over the years about the difficulties of getting
> >> the kernel to write data out when we want it to and to not write data
> >> out when we don't want it to.
> >
> > Is sync_file_range() broke?
>
> I don't know. I think a few of us have played with it and not been
> able to achieve a clear win.

Before you go back down the sync_file_range path, keep in mind that
it is not a guaranteed data integrity operation: it does not force
device cache flushes like fsync/fdatasync(). Hence it does not
guarantee that the metadata that points at the data written nor the
volatile caches in the storage path has been flushed...

IOWs, using sync_file_range() does not avoid the need to fsync() a
file for data integrity purposes...

> Whether the problem is with the system
> call or the programmer is harder to determine. I think the problem is
> in part that it's not exactly clear when we should call it. So
> suppose we want to do a checkpoint. What we used to do a long time
> ago is write everything, and then fsync it all, and then call it good.
> But that produced horrible I/O storms. So what we do now is do the
> writes over a period of time, with sleeps in between, and then fsync
> it all at the end, hoping that the kernel will write some of it before
> the fsyncs arrive so that we don't get a huge I/O spike.
> And that sorta works, and it's definitely better than doing it all at
> full speed, but it's pretty imprecise. If the kernel doesn't write
> enough of the data out in advance, then there's still a huge I/O storm
> when we do the fsyncs and everything grinds to a halt. If it writes
> out more data than needed in advance, it increases the total number of
> physical writes because we get less write-combining, and that hurts
> performance, too.

Yup, the kernel defaults to maximising bulk write throughput, which
means it waits to the last possible moment to issue write IO. And
that's exactly to maximise write combining, optimise delayed
allocation, etc. There are many good reasons for doing this, and for
the majority of workloads it is the right behaviour to have.

It sounds to me like you want the kernel to start background
writeback earlier so that it doesn't build up as much dirty data
before you require a flush. There are several ways to do this by
tweaking writeback knobs. The simplest is probably just to set
/proc/sys/vm/dirty_background_bytes to an appropriate threshold (say
50MB) and dirty_expire_centiseconds to a few seconds so that
background writeback starts and walks all dirty inodes almost
immediately. This will keep a steady stream of low level background
IO going, and fsync should then not take very long.

Fundamentally, though, we need bug reports from people seeing these
problems when they see them so we can diagnose them on their
systems. Trying to discuss/diagnose these problems without knowing
anything about the storage, the kernel version, writeback
thresholds, etc really doesn't work because we can't easily
determine a root cause.

Cheers,

Dave.
--
Dave Chinner
david(at)fromorbit(dot)com

I wrote:

> to avoid write gluts it must often be limited to 1GB to 1GB.

That should have been "1GB to 2GB."

James Bottomley <James(dot)Bottomley(at)HansenPartnership(dot)com> wrote:

>> We start by creating a chunk of shared memory that all processes
>> (we do not use threads) will have mapped at a common address,
>> and we read() and write() into that chunk.
>
> Yes, that's what I was thinking: it's a cache.  About how many
> files comprise this cache?  Are you thinking it's too difficult
> for every process to map the files?

It occurred to me that I don't remember seeing any indication of
how many processes we're talking about.  There is once process per
database connection, plus some administrative processes, like the
checkpoint process and the background writer.  At the low end,
about 10 processes would be connected to the shared memory.  The
highest I've personally seen is about 3000; I don't know how far
above that people might try to push it.  I always recommend a
connection pool to limit the number of database connections to
something near ((2 * core count) + effective spindle count), since
that's where I typically see best performance; but people don't
always do that.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 14, 2014 at 11:40:38AM -0800, Kevin Grittner wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > Jan Kara <jack(at)suse(dot)cz> wrote:
> >
> >> Just to get some idea about the sizes - how large are the
> >> checkpoints we are talking about that cause IO stalls?
> >
> > Big.
>
> To quantify that, in a production setting we were seeing pauses of
> up to two minutes with shared_buffers set to 8GB and default dirty
^^^^^^^^^^^^^
> page settings for Linux, on a machine with 256GB RAM and 512MB
^^^^^^^^^^^^^
There's your problem.

By default, background writeback doesn't start until 10% of memory
is dirtied, and on your machine that's 25GB of RAM. That's way to
high for your workload.

It appears to me that we are seeing large memory machines much more
commonly in data centers - a couple of years ago 256GB RAM was only
seen in supercomputers. Hence machines of this size are moving from
"tweaking settings for supercomputers is OK" class to "tweaking
settings for enterprise servers is not OK"....

Perhaps what we need to do is deprecate dirty_ratio and
dirty_background_ratio as the default values as move to the byte
based values as the defaults and cap them appropriately. e.g.
10/20% of RAM for small machines down to a couple of GB for large
machines....

> non-volatile cache on the RAID controller.  To eliminate stalls we
> had to drop shared_buffers to 2GB (to limit how many dirty pages
> could build up out-of-sight from the OS), spread checkpoints to 90%
> of allowed time (almost no gap between finishing one checkpoint and
> starting the next) and crank up the background writer so that no
> dirty page sat unwritten in PostgreSQL shared_buffers for more than
> 4 seconds. Less aggressive pushing to the OS resulted in the
> avalanche of writes I previously described, with the corresponding
> I/O stalls.  We approached that incrementally, and that's the point
> where stalls stopped occurring.  We did not adjust the OS
> thresholds for writing dirty pages, although I know of others who
> have had to do so.

Essentially, changing dirty_background_bytes, dirty_bytes and
dirty_expire_centiseconds to be much smaller should make the kernel
start writeback much sooner and so you shouldn't have to limit the
amount of buffers the application has to prevent major fsync
triggered stalls...

Cheers,

Dave.
--
Dave Chinner
david(at)fromorbit(dot)com

On Wed, Jan 15, 2014 at 08:03:28AM +1300, Gavin Flower wrote:
> On 14/01/14 14:09, Dave Chinner wrote:
> >On Mon, Jan 13, 2014 at 09:29:02PM +0000, Greg Stark wrote:
> >>On Mon, Jan 13, 2014 at 9:12 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> [...]
> >>The more ambitious and interesting direction is to let Postgres tell
> >>the kernel what it needs to know to manage everything. To do that we
> >>would need the ability to control when pages are flushed out. This is
> >>absolutely necessary to maintain consistency. Postgres would need to
> >>be able to mark pages as unflushable until some point in time in the
> >>future when the journal is flushed. We discussed various ways that
> >>interface could work but it would be tricky to keep it low enough
> >>overhead to be workable.
> >IMO, the concept of allowing userspace to pin dirty page cache
> >pages in memory is just asking for trouble. Apart from the obvious
> >memory reclaim and OOM issues, some filesystems won't be able to
> >move their journals forward until the data is flushed. i.e. ordered
> >mode data writeback on ext3 will have all sorts of deadlock issues
> >that result from pinning pages and then issuing fsync() on another
> >file which will block waiting for the pinned pages to be flushed.
> >
> >Indeed, what happens if you do pin_dirty_pages(fd); .... fsync(fd);?
> >If fsync() blocks because there are pinned pages, and there's no
> >other thread to unpin them, then that code just deadlocked. If
> >fsync() doesn't block and skips the pinned pages, then we haven't
> >done an fsync() at all, and so violated the expectation that users
> >have that after fsync() returns their data is safe on disk. And if
> >we return an error to fsync(), then what the hell does the user do
> >if it is some other application we don't know about that has pinned
> >the pages? And if the kernel unpins them after some time, then we
> >just violated the application's consistency guarantees....
> >
> [...]
>
> What if Postgres could tell the kernel how strongly that it wanted
> to hold on to the pages?

That doesn't get rid of the problems, it just makes it harder to
diagnose them when they occur. :/

Cheers,

Dave.
--
Dave Chinner
david(at)fromorbit(dot)com

Dave Chinner <david(at)fromorbit(dot)com> write:

> Essentially, changing dirty_background_bytes, dirty_bytes and
> dirty_expire_centiseconds to be much smaller should make the
> kernel start writeback much sooner and so you shouldn't have to
> limit the amount of buffers the application has to prevent major
> fsync triggered stalls...

Is there any "rule of thumb" about where to start with these?  For
example, should a database server maybe have dirty_background_bytes
set to 75% of the non-volatile write cache present on the
controller, in an attempt to make sure that there is always some
"slack" space for writes?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company