Re: Direct I/O

Hi,

On 2022-11-04 14:47:31 -0500, Jim Nasby wrote:
> On 11/1/22 2:36 AM, Thomas Munro wrote:
> > Here is a patch to allow PostgreSQL to use $SUBJECT. It is from the
>
> This is exciting to see! There's two other items to add to the TODO list
> before this would be ready for production:
>
> 1) work_mem. This is a significant impediment to scaling shared buffers the
> way you'd want to.

I don't really think that's closely enough related to tackle together. Yes,
it'd be easier to set a large s_b if we had better work_mem management, but
it's a completely distinct problem, and in a lot of cases you could use DIO
without tackling the work_mem issue.

> 2) Clock sweep. Specifically, currently the only thing that drives
> usage_count is individual backends running the clock hand. On large systems
> with 75% of memory going to shared_buffers, that becomes a very significant
> problem, especially when the backend running the clock sweep is doing so in
> order to perform an operation like a b-tree page split. I suspect it
> shouldn't be too hard to deal with this issue by just having bgwriter or
> another bgworker proactively ensuring some reasonable number of buffers with
> usage_count=0 exist.

I agree this isn't great, but I don't think the replacement efficiency is that
big a problem. Replacing the wrong buffers is a bigger issue.

I've run tests with s_b=768GB (IIRC) without it showing up as a major
issue. If you have an extreme replacement rate at such a large s_b you have a
lot of other problems.

I don't want to discourage anybody from tackling the clock replacement issues,
the contrary, but AIO+DIO can show significant wins without those
changes. It's already a humongous project...

> One other thing to be aware of: overflowing as SLRU becomes a massive
> problem if there isn't a filesystem backing the SLRU. Obviously only an
> issue if you try and apply DIO to SLRU files.

Which would be a very bad idea for now.... Thomas does have a patch for moving
them into the main buffer pool.

Greetings,

Andres Freund