pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeda question about Direct I/O and double buffering
16+ messages / 6 participants
[nested] [flat]
* a question about Direct I/O and double buffering
@ 2007-04-05 17:09 Xiaoning Ding <dingxn@cse.ohio-state.edu>
0 siblings, 1 reply; 16+ messages in thread
From: Xiaoning Ding @ 2007-04-05 17:09 UTC (permalink / raw)
To: pgsql-performance
Hi,
A page may be double buffered in PG's buffer pool and in OS's buffer cache.
Other DBMS like DB2 and Oracle has provided Direct I/O option to eliminate
double buffering. I noticed there were discusses on the list. But
I can not find similar option in PG. Does PG support direct I/O now?
The tuning guide of PG usually recommends a small shared buffer pool
(compared
to the size of physical memory). I think it is to avoid swapping. If
there were
swapping, OS kernel may swap out some pages in PG's buffer pool even PG
want to keep them in memory. i.e. PG would loose full control over
buffer pool.
A large buffer pool is not good because it may
1. cause more pages double buffered, and thus decrease the efficiency of
buffer
cache and buffer pool.
2. may cause swapping.
Am I right?
If PG's buffer pool is small compared with physical memory, can I say
that the
hit ratio of PG's buffer pool is not so meaningful because most misses
can be
satisfied by OS Kernel's buffer cache?
Thanks!
Xiaoning
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 18:09 Erik Jones <erik@myemma.com>
parent: Xiaoning Ding <dingxn@cse.ohio-state.edu>
0 siblings, 2 replies; 16+ messages in thread
From: Erik Jones @ 2007-04-05 18:09 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>; +Cc: pgsql-performance
On Apr 5, 2007, at 12:09 PM, Xiaoning Ding wrote:
> Hi,
>
> A page may be double buffered in PG's buffer pool and in OS's
> buffer cache.
> Other DBMS like DB2 and Oracle has provided Direct I/O option to
> eliminate
> double buffering. I noticed there were discusses on the list. But
> I can not find similar option in PG. Does PG support direct I/O now?
>
> The tuning guide of PG usually recommends a small shared buffer pool
> (compared
> to the size of physical memory). I think it is to avoid swapping. If
> there were
> swapping, OS kernel may swap out some pages in PG's buffer pool
> even PG
> want to keep them in memory. i.e. PG would loose full control over
> buffer pool.
> A large buffer pool is not good because it may
> 1. cause more pages double buffered, and thus decrease the
> efficiency of
> buffer
> cache and buffer pool.
> 2. may cause swapping.
> Am I right?
>
> If PG's buffer pool is small compared with physical memory, can I say
> that the
> hit ratio of PG's buffer pool is not so meaningful because most misses
> can be
> satisfied by OS Kernel's buffer cache?
>
> Thanks!
To the best of my knowledge, Postgres itself does not have a direct
IO option (although it would be a good addition). So, in order to
use direct IO with postgres you'll need to consult your filesystem
docs for how to set the forcedirectio mount option. I believe it can
be set dynamically, but if you want it to be permanent you'll to add
it to your fstab/vfstab file.
erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 18:22 Xiaoning Ding <dingxn@cse.ohio-state.edu>
parent: Erik Jones <erik@myemma.com>
1 sibling, 2 replies; 16+ messages in thread
From: Xiaoning Ding @ 2007-04-05 18:22 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-performance
Erik Jones wrote:
> On Apr 5, 2007, at 12:09 PM, Xiaoning Ding wrote:
>
>> Hi,
>>
>> A page may be double buffered in PG's buffer pool and in OS's buffer
>> cache.
>> Other DBMS like DB2 and Oracle has provided Direct I/O option to eliminate
>> double buffering. I noticed there were discusses on the list. But
>> I can not find similar option in PG. Does PG support direct I/O now?
>>
>> The tuning guide of PG usually recommends a small shared buffer pool
>> (compared
>> to the size of physical memory). I think it is to avoid swapping. If
>> there were
>> swapping, OS kernel may swap out some pages in PG's buffer pool even PG
>> want to keep them in memory. i.e. PG would loose full control over
>> buffer pool.
>> A large buffer pool is not good because it may
>> 1. cause more pages double buffered, and thus decrease the efficiency of
>> buffer
>> cache and buffer pool.
>> 2. may cause swapping.
>> Am I right?
>>
>> If PG's buffer pool is small compared with physical memory, can I say
>> that the
>> hit ratio of PG's buffer pool is not so meaningful because most misses
>> can be
>> satisfied by OS Kernel's buffer cache?
>>
>> Thanks!
>
> To the best of my knowledge, Postgres itself does not have a direct IO
> option (although it would be a good addition). So, in order to use
> direct IO with postgres you'll need to consult your filesystem docs for
> how to set the forcedirectio mount option. I believe it can be set
> dynamically, but if you want it to be permanent you'll to add it to your
> fstab/vfstab file.
I use Linux. It supports direct I/O on a per-file basis only. To
bypass OS buffer cache,
files should be opened with O_DIRECT option. I afraid that I have to
modify PG.
Xiaoning
>
> erik jones <erik@myemma.com <mailto:erik@myemma.com>>
> software developer
> 615-296-0838
> emma(r)
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 18:27 Mark Lewis <mark.lewis@mir3.com>
parent: Erik Jones <erik@myemma.com>
1 sibling, 1 reply; 16+ messages in thread
From: Mark Lewis @ 2007-04-05 18:27 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Xiaoning Ding <dingxn@cse.ohio-state.edu>; pgsql-performance
Not to hijack this thread, but has anybody here tested the behavior of
PG on a file system with OS-level caching disabled via forcedirectio or
by using an inherently non-caching file system such as ocfs2?
I've been thinking about trying this setup to avoid double-caching now
that the 8.x series scales shared buffers better, but I figured I'd ask
first if anybody here had experience with similar configurations.
-- Mark
On Thu, 2007-04-05 at 13:09 -0500, Erik Jones wrote:
> On Apr 5, 2007, at 12:09 PM, Xiaoning Ding wrote:
>
> > Hi,
> >
> >
> > A page may be double buffered in PG's buffer pool and in OS's buffer
> > cache.
> > Other DBMS like DB2 and Oracle has provided Direct I/O option to
> > eliminate
> > double buffering. I noticed there were discusses on the list. But
> > I can not find similar option in PG. Does PG support direct I/O now?
> >
> >
> > The tuning guide of PG usually recommends a small shared buffer pool
> > (compared
> > to the size of physical memory). I think it is to avoid swapping.
> > If
> > there were
> > swapping, OS kernel may swap out some pages in PG's buffer pool even
> > PG
> > want to keep them in memory. i.e. PG would loose full control over
> > buffer pool.
> > A large buffer pool is not good because it may
> > 1. cause more pages double buffered, and thus decrease the
> > efficiency of
> > buffer
> > cache and buffer pool.
> > 2. may cause swapping.
> > Am I right?
> >
> >
> > If PG's buffer pool is small compared with physical memory, can I
> > say
> > that the
> > hit ratio of PG's buffer pool is not so meaningful because most
> > misses
> > can be
> > satisfied by OS Kernel's buffer cache?
> >
> >
> > Thanks!
>
>
> To the best of my knowledge, Postgres itself does not have a direct IO
> option (although it would be a good addition). So, in order to use
> direct IO with postgres you'll need to consult your filesystem docs
> for how to set the forcedirectio mount option. I believe it can be
> set dynamically, but if you want it to be permanent you'll to add it
> to your fstab/vfstab file.
>
>
> erik jones <erik@myemma.com>
> software developer
> 615-296-0838
> emma(r)
>
>
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 18:47 Erik Jones <erik@myemma.com>
parent: Xiaoning Ding <dingxn@cse.ohio-state.edu>
1 sibling, 1 reply; 16+ messages in thread
From: Erik Jones @ 2007-04-05 18:47 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>; +Cc: pgsql-performance
On Apr 5, 2007, at 1:22 PM, Xiaoning Ding wrote:
> Erik Jones wrote:
>> On Apr 5, 2007, at 12:09 PM, Xiaoning Ding wrote:
>>> Hi,
>>>
>>> A page may be double buffered in PG's buffer pool and in OS's
>>> buffer cache.
>>> Other DBMS like DB2 and Oracle has provided Direct I/O option to
>>> eliminate
>>> double buffering. I noticed there were discusses on the list. But
>>> I can not find similar option in PG. Does PG support direct I/O now?
>>>
>>> The tuning guide of PG usually recommends a small shared buffer pool
>>> (compared
>>> to the size of physical memory). I think it is to avoid
>>> swapping. If
>>> there were
>>> swapping, OS kernel may swap out some pages in PG's buffer pool
>>> even PG
>>> want to keep them in memory. i.e. PG would loose full control over
>>> buffer pool.
>>> A large buffer pool is not good because it may
>>> 1. cause more pages double buffered, and thus decrease the
>>> efficiency of
>>> buffer
>>> cache and buffer pool.
>>> 2. may cause swapping.
>>> Am I right?
>>>
>>> If PG's buffer pool is small compared with physical memory, can I
>>> say
>>> that the
>>> hit ratio of PG's buffer pool is not so meaningful because most
>>> misses
>>> can be
>>> satisfied by OS Kernel's buffer cache?
>>>
>>> Thanks!
>> To the best of my knowledge, Postgres itself does not have a
>> direct IO option (although it would be a good addition). So, in
>> order to use direct IO with postgres you'll need to consult your
>> filesystem docs for how to set the forcedirectio mount option. I
>> believe it can be set dynamically, but if you want it to be
>> permanent you'll to add it to your fstab/vfstab file.
>
> I use Linux. It supports direct I/O on a per-file basis only. To
> bypass OS buffer cache,
> files should be opened with O_DIRECT option. I afraid that I have
> to modify PG.
>
> Xiaoning
Looks like it. I just did a cursory search of the archives and it
seems that others have looked at this before so you'll probably want
to start there if your up to it.
erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 18:56 Alex Deucher <alexdeucher@gmail.com>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 16+ messages in thread
From: Alex Deucher @ 2007-04-05 18:56 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Xiaoning Ding <dingxn@cse.ohio-state.edu>; pgsql-performance
On 4/5/07, Erik Jones <erik@myemma.com> wrote:
>
> On Apr 5, 2007, at 1:22 PM, Xiaoning Ding wrote:
>
> Erik Jones wrote:
> On Apr 5, 2007, at 12:09 PM, Xiaoning Ding wrote:
> Hi,
>
> A page may be double buffered in PG's buffer pool and in OS's buffer cache.
> Other DBMS like DB2 and Oracle has provided Direct I/O option to eliminate
> double buffering. I noticed there were discusses on the list. But
> I can not find similar option in PG. Does PG support direct I/O now?
>
> The tuning guide of PG usually recommends a small shared buffer pool
> (compared
> to the size of physical memory). I think it is to avoid swapping. If
> there were
> swapping, OS kernel may swap out some pages in PG's buffer pool even PG
> want to keep them in memory. i.e. PG would loose full control over
> buffer pool.
> A large buffer pool is not good because it may
> 1. cause more pages double buffered, and thus decrease the efficiency of
> buffer
> cache and buffer pool.
> 2. may cause swapping.
> Am I right?
>
> If PG's buffer pool is small compared with physical memory, can I say
> that the
> hit ratio of PG's buffer pool is not so meaningful because most misses
> can be
> satisfied by OS Kernel's buffer cache?
>
> Thanks!
> To the best of my knowledge, Postgres itself does not have a direct IO
> option (although it would be a good addition). So, in order to use direct
> IO with postgres you'll need to consult your filesystem docs for how to set
> the forcedirectio mount option. I believe it can be set dynamically, but if
> you want it to be permanent you'll to add it to your fstab/vfstab file.
>
> I use Linux. It supports direct I/O on a per-file basis only. To bypass OS
> buffer cache,
> files should be opened with O_DIRECT option. I afraid that I have to modify
> PG.
>
> Xiaoning
> Looks like it. I just did a cursory search of the archives and it seems
> that others have looked at this before so you'll probably want to start
> there if your up to it.
>
Linux used to have (still does?) a RAW interface which might also be
useful. I think the original code was contributed by oracle so they
could support direct IO.
Alex
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 18:58 Erik Jones <erik@myemma.com>
parent: Mark Lewis <mark.lewis@mir3.com>
0 siblings, 1 reply; 16+ messages in thread
From: Erik Jones @ 2007-04-05 18:58 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; +Cc: Xiaoning Ding <dingxn@cse.ohio-state.edu>; pgsql-performance
On Apr 5, 2007, at 1:27 PM, Mark Lewis wrote:
> On Thu, 2007-04-05 at 13:09 -0500, Erik Jones wrote:
>> On Apr 5, 2007, at 12:09 PM, Xiaoning Ding wrote:
>>
>>> Hi,
>>>
>>>
>>> A page may be double buffered in PG's buffer pool and in OS's buffer
>>> cache.
>>> Other DBMS like DB2 and Oracle has provided Direct I/O option to
>>> eliminate
>>> double buffering. I noticed there were discusses on the list. But
>>> I can not find similar option in PG. Does PG support direct I/O now?
>>>
>>>
>>> The tuning guide of PG usually recommends a small shared buffer pool
>>> (compared
>>> to the size of physical memory). I think it is to avoid swapping.
>>> If
>>> there were
>>> swapping, OS kernel may swap out some pages in PG's buffer pool even
>>> PG
>>> want to keep them in memory. i.e. PG would loose full control over
>>> buffer pool.
>>> A large buffer pool is not good because it may
>>> 1. cause more pages double buffered, and thus decrease the
>>> efficiency of
>>> buffer
>>> cache and buffer pool.
>>> 2. may cause swapping.
>>> Am I right?
>>>
>>>
>>> If PG's buffer pool is small compared with physical memory, can I
>>> say
>>> that the
>>> hit ratio of PG's buffer pool is not so meaningful because most
>>> misses
>>> can be
>>> satisfied by OS Kernel's buffer cache?
>>>
>>>
>>> Thanks!
>>
>>
>> To the best of my knowledge, Postgres itself does not have a
>> direct IO
>> option (although it would be a good addition). So, in order to use
>> direct IO with postgres you'll need to consult your filesystem docs
>> for how to set the forcedirectio mount option. I believe it can be
>> set dynamically, but if you want it to be permanent you'll to add it
>> to your fstab/vfstab file.
> Not to hijack this thread, but has anybody here tested the behavior of
> PG on a file system with OS-level caching disabled via
> forcedirectio or
> by using an inherently non-caching file system such as ocfs2?
>
> I've been thinking about trying this setup to avoid double-caching now
> that the 8.x series scales shared buffers better, but I figured I'd
> ask
> first if anybody here had experience with similar configurations.
>
> -- Mark
Rather than repeat everything that was said just last week, I'll
point out that we just had a pretty decent discusson on this last
week that I started, so check the archives. In summary though, if
you have a high io transaction load with a db where the average size
of your "working set" of data doesn't fit in memory with room to
spare, then direct io can be a huge plus, otherwise you probably
won't see much of a difference. I have yet to hear of anybody
actually seeing any degradation in the db performance from it. In
addition, while it doesn't bother me, I'd watch the top posting as
some people get pretty religious about (I moved your comments down).
erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 19:56 Mark Lewis <mark.lewis@mir3.com>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 16+ messages in thread
From: Mark Lewis @ 2007-04-05 19:56 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Xiaoning Ding <dingxn@cse.ohio-state.edu>; pgsql-performance
...
[snipped for brevity]
...
>
> > Not to hijack this thread, but has anybody here tested the behavior
> > of
> > PG on a file system with OS-level caching disabled via forcedirectio
> > or
> > by using an inherently non-caching file system such as ocfs2?
> >
> >
> > I've been thinking about trying this setup to avoid double-caching
> > now
> > that the 8.x series scales shared buffers better, but I figured I'd
> > ask
> > first if anybody here had experience with similar configurations.
> >
> >
> > -- Mark
>
>
> Rather than repeat everything that was said just last week, I'll point
> out that we just had a pretty decent discusson on this last week that
> I started, so check the archives. In summary though, if you have a
> high io transaction load with a db where the average size of your
> "working set" of data doesn't fit in memory with room to spare, then
> direct io can be a huge plus, otherwise you probably won't see much of
> a difference. I have yet to hear of anybody actually seeing any
> degradation in the db performance from it. In addition, while it
> doesn't bother me, I'd watch the top posting as some people get pretty
> religious about (I moved your comments down).
I saw the thread, but my understanding from reading through it was that
you never fully tracked down the cause of the factor of 10 write volume
mismatch, so I pretty much wrote it off as a data point for
forcedirectio because of the unknowns. Did you ever figure out the
cause of that?
-- Mark Lewis
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 20:10 Erik Jones <erik@myemma.com>
parent: Mark Lewis <mark.lewis@mir3.com>
0 siblings, 1 reply; 16+ messages in thread
From: Erik Jones @ 2007-04-05 20:10 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; +Cc: Xiaoning Ding <dingxn@cse.ohio-state.edu>; pgsql-performance
On Apr 5, 2007, at 2:56 PM, Mark Lewis wrote:
> ...
> [snipped for brevity]
> ...
>>
>>> Not to hijack this thread, but has anybody here tested the behavior
>>> of
>>> PG on a file system with OS-level caching disabled via forcedirectio
>>> or
>>> by using an inherently non-caching file system such as ocfs2?
>>>
>>>
>>> I've been thinking about trying this setup to avoid double-caching
>>> now
>>> that the 8.x series scales shared buffers better, but I figured I'd
>>> ask
>>> first if anybody here had experience with similar configurations.
>>>
>>>
>>> -- Mark
>>
>>
>> Rather than repeat everything that was said just last week, I'll
>> point
>> out that we just had a pretty decent discusson on this last week that
>> I started, so check the archives. In summary though, if you have a
>> high io transaction load with a db where the average size of your
>> "working set" of data doesn't fit in memory with room to spare, then
>> direct io can be a huge plus, otherwise you probably won't see
>> much of
>> a difference. I have yet to hear of anybody actually seeing any
>> degradation in the db performance from it. In addition, while it
>> doesn't bother me, I'd watch the top posting as some people get
>> pretty
>> religious about (I moved your comments down).
>
> I saw the thread, but my understanding from reading through it was
> that
> you never fully tracked down the cause of the factor of 10 write
> volume
> mismatch, so I pretty much wrote it off as a data point for
> forcedirectio because of the unknowns. Did you ever figure out the
> cause of that?
>
> -- Mark Lewis
Nope. What we never tracked down was the factor of 10 drop in
database transactions, not disk transactions. The write volume was
most definitely due to the direct io setting -- writes are now being
done in terms of the system's block size where as before they were
being done in terms of the the filesystem's cache page size (as it's
in virtual memory). Basically, we do so many write transactions that
the fs cache was constantly paging.
erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 20:33 david@lang.hm
parent: Xiaoning Ding <dingxn@cse.ohio-state.edu>
1 sibling, 1 reply; 16+ messages in thread
From: david@lang.hm @ 2007-04-05 20:33 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>; +Cc: Erik Jones <erik@myemma.com>; pgsql-performance
On Thu, 5 Apr 2007, Xiaoning Ding wrote:
>>
>> To the best of my knowledge, Postgres itself does not have a direct IO
>> option (although it would be a good addition). So, in order to use direct
>> IO with postgres you'll need to consult your filesystem docs for how to
>> set the forcedirectio mount option. I believe it can be set dynamically,
>> but if you want it to be permanent you'll to add it to your fstab/vfstab
>> file.
>
> I use Linux. It supports direct I/O on a per-file basis only. To bypass OS
> buffer cache,
> files should be opened with O_DIRECT option. I afraid that I have to modify
> PG.
as someone who has been reading the linux-kernel mailing list for 10
years, let me comment on this a bit.
linux does have a direct i/o option, but it has significant limits on when
and how you cna use it (buffers must be 512byte aligned and multiples of
512 bytes, things like that). Also, in many cases testing has shon that
there is a fairly significant performance hit for this, not a perfomance
gain.
what I think that postgres really needs is to add support for write
barriers (telling the OS to make shure that everything before the barrier
is written to disk before anything after the barrier) I beleive that these
are avaiable on SCSI drives, and on some SATA drives. this sort of
support, along with appropriate async I/O support (which is probably going
to end up being the 'syslets' or 'threadlets' stuff that's in the early
experimental stage, rather then the current aio API) has the potential to
be a noticable improvement.
if you haven't followed the syslets discussion on the kernel list,
threadlets are an approach that basicly lets you turn any syscall into a
async interface (if the call doesn't block on anything you get the answer
back immediatly, if it does block it gets turned into a async call by the
kernel)
syslets are a way to combine multiple syscalls into a single call,
avoiding the user->system->user calling overhead for the additional calls.
(it's also viewed as a way to do prototyping of possible new calls, if a
sequence of syscalls end up being common enough the kernel devs will look
at makeing a new, combined, syscall (for example lock, write, unlock could
be made into one if it's common enough and there's enough of a performance
gain)
David Lang
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 20:58 Xiaoning Ding <dingxn@cse.ohio-state.edu>
parent: Alex Deucher <alexdeucher@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Xiaoning Ding @ 2007-04-05 20:58 UTC (permalink / raw)
To: Alex Deucher <alexdeucher@gmail.com>; +Cc: Erik Jones <erik@myemma.com>; pgsql-performance
Alex Deucher wrote:
> On 4/5/07, Erik Jones <erik@myemma.com> wrote:
>>
>> On Apr 5, 2007, at 1:22 PM, Xiaoning Ding wrote:
>>
>> Erik Jones wrote:
>> On Apr 5, 2007, at 12:09 PM, Xiaoning Ding wrote:
>> Hi,
>>
>> A page may be double buffered in PG's buffer pool and in OS's buffer
>> cache.
>> Other DBMS like DB2 and Oracle has provided Direct I/O option to
>> eliminate
>> double buffering. I noticed there were discusses on the list. But
>> I can not find similar option in PG. Does PG support direct I/O now?
>>
>> The tuning guide of PG usually recommends a small shared buffer pool
>> (compared
>> to the size of physical memory). I think it is to avoid swapping. If
>> there were
>> swapping, OS kernel may swap out some pages in PG's buffer pool even PG
>> want to keep them in memory. i.e. PG would loose full control over
>> buffer pool.
>> A large buffer pool is not good because it may
>> 1. cause more pages double buffered, and thus decrease the efficiency of
>> buffer
>> cache and buffer pool.
>> 2. may cause swapping.
>> Am I right?
>>
>> If PG's buffer pool is small compared with physical memory, can I say
>> that the
>> hit ratio of PG's buffer pool is not so meaningful because most misses
>> can be
>> satisfied by OS Kernel's buffer cache?
>>
>> Thanks!
>> To the best of my knowledge, Postgres itself does not have a direct IO
>> option (although it would be a good addition). So, in order to use
>> direct
>> IO with postgres you'll need to consult your filesystem docs for how
>> to set
>> the forcedirectio mount option. I believe it can be set dynamically,
>> but if
>> you want it to be permanent you'll to add it to your fstab/vfstab file.
>>
>> I use Linux. It supports direct I/O on a per-file basis only. To
>> bypass OS
>> buffer cache,
>> files should be opened with O_DIRECT option. I afraid that I have to
>> modify
>> PG.
>>
>> Xiaoning
>> Looks like it. I just did a cursory search of the archives and it seems
>> that others have looked at this before so you'll probably want to start
>> there if your up to it.
>>
>
> Linux used to have (still does?) a RAW interface which might also be
> useful. I think the original code was contributed by oracle so they
> could support direct IO.
>
> Alex
I am more concerned with reads , and how to do direct I/O under Linux here.
Reading raw devices in linux bypasses OS buffer cache. But how can you
mount a raw device( it is a character device) as a file system?
Xiaoning
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 21:06 Alex Deucher <alexdeucher@gmail.com>
parent: Xiaoning Ding <dingxn@cse.ohio-state.edu>
1 sibling, 0 replies; 16+ messages in thread
From: Alex Deucher @ 2007-04-05 21:06 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>; +Cc: Erik Jones <erik@myemma.com>; pgsql-performance
On 4/5/07, Xiaoning Ding <dingxn@cse.ohio-state.edu> wrote:
> Alex Deucher wrote:
> > On 4/5/07, Erik Jones <erik@myemma.com> wrote:
> >>
> >> On Apr 5, 2007, at 1:22 PM, Xiaoning Ding wrote:
> >>
> >> Erik Jones wrote:
> >> On Apr 5, 2007, at 12:09 PM, Xiaoning Ding wrote:
> >> Hi,
> >>
> >> A page may be double buffered in PG's buffer pool and in OS's buffer
> >> cache.
> >> Other DBMS like DB2 and Oracle has provided Direct I/O option to
> >> eliminate
> >> double buffering. I noticed there were discusses on the list. But
> >> I can not find similar option in PG. Does PG support direct I/O now?
> >>
> >> The tuning guide of PG usually recommends a small shared buffer pool
> >> (compared
> >> to the size of physical memory). I think it is to avoid swapping. If
> >> there were
> >> swapping, OS kernel may swap out some pages in PG's buffer pool even PG
> >> want to keep them in memory. i.e. PG would loose full control over
> >> buffer pool.
> >> A large buffer pool is not good because it may
> >> 1. cause more pages double buffered, and thus decrease the efficiency of
> >> buffer
> >> cache and buffer pool.
> >> 2. may cause swapping.
> >> Am I right?
> >>
> >> If PG's buffer pool is small compared with physical memory, can I say
> >> that the
> >> hit ratio of PG's buffer pool is not so meaningful because most misses
> >> can be
> >> satisfied by OS Kernel's buffer cache?
> >>
> >> Thanks!
> >> To the best of my knowledge, Postgres itself does not have a direct IO
> >> option (although it would be a good addition). So, in order to use
> >> direct
> >> IO with postgres you'll need to consult your filesystem docs for how
> >> to set
> >> the forcedirectio mount option. I believe it can be set dynamically,
> >> but if
> >> you want it to be permanent you'll to add it to your fstab/vfstab file.
> >>
> >> I use Linux. It supports direct I/O on a per-file basis only. To
> >> bypass OS
> >> buffer cache,
> >> files should be opened with O_DIRECT option. I afraid that I have to
> >> modify
> >> PG.
> >>
> >> Xiaoning
> >> Looks like it. I just did a cursory search of the archives and it seems
> >> that others have looked at this before so you'll probably want to start
> >> there if your up to it.
> >>
> >
> > Linux used to have (still does?) a RAW interface which might also be
> > useful. I think the original code was contributed by oracle so they
> > could support direct IO.
> >
> > Alex
> I am more concerned with reads , and how to do direct I/O under Linux here.
> Reading raw devices in linux bypasses OS buffer cache. But how can you
> mount a raw device( it is a character device) as a file system?
>
In this case, I guess you'd probably have to do it within pg itself.
Alex
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-05 21:39 Erik Jones <erik@myemma.com>
parent: david@lang.hm
0 siblings, 1 reply; 16+ messages in thread
From: Erik Jones @ 2007-04-05 21:39 UTC (permalink / raw)
To: david@lang.hm; +Cc: Xiaoning Ding <dingxn@cse.ohio-state.edu>; pgsql-performance
On Apr 5, 2007, at 3:33 PM, david@lang.hm wrote:
> On Thu, 5 Apr 2007, Xiaoning Ding wrote:
>
>>>
>>> To the best of my knowledge, Postgres itself does not have a
>>> direct IO
>>> option (although it would be a good addition). So, in order to
>>> use direct
>>> IO with postgres you'll need to consult your filesystem docs for
>>> how to
>>> set the forcedirectio mount option. I believe it can be set
>>> dynamically,
>>> but if you want it to be permanent you'll to add it to your
>>> fstab/vfstab
>>> file.
>>
>> I use Linux. It supports direct I/O on a per-file basis only. To
>> bypass OS buffer cache,
>> files should be opened with O_DIRECT option. I afraid that I have
>> to modify PG.
>
> as someone who has been reading the linux-kernel mailing list for
> 10 years, let me comment on this a bit.
>
> linux does have a direct i/o option,
Yes, I know applications can request direct i/o with the O_DIRECT
flag to open(), but can this be set to be forced for all applications
or for individual applications from "outside" the application (not
that I've ever heard of something like the second)?
> but it has significant limits on when and how you cna use it
> (buffers must be 512byte aligned and multiples of 512 bytes, things
> like that).
That's a standard limit imposed by the sector size of hard drives,
and is present in all direct i/o implementations, not just Linux.
> Also, in many cases testing has shon that there is a fairly
> significant performance hit for this, not a perfomance gain.
Those performance hits have been noticed for high i/o transaction
databases? The idea here is that these kinds of database manage
their own caches and having a separate filesystem cache in virtual
memory that works with system memory page sizes is an unneeded level
of indirection. Yes, you should expect other "normal" utilities will
suffer a performance hit as if you are trying to cp a 500 byte file
you'll still have to work with 8K writes and reads whereas with the
filesystem cache you can just write/read part of a page in memory and
let the cache decide when it needs to write and read from disk. If
there are other caveats to direct i/o on Linux I'd love to hear them.
erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-06 01:47 david@lang.hm
parent: Xiaoning Ding <dingxn@cse.ohio-state.edu>
1 sibling, 0 replies; 16+ messages in thread
From: david@lang.hm @ 2007-04-06 01:47 UTC (permalink / raw)
To: Xiaoning Ding <dingxn@cse.ohio-state.edu>; +Cc: Alex Deucher <alexdeucher@gmail.com>; Erik Jones <erik@myemma.com>; pgsql-performance
On Thu, 5 Apr 2007, Xiaoning Ding wrote:
>> > Xiaoning
>> > Looks like it. I just did a cursory search of the archives and it seems
>> > that others have looked at this before so you'll probably want to start
>> > there if your up to it.
>> >
>>
>> Linux used to have (still does?) a RAW interface which might also be
>> useful. I think the original code was contributed by oracle so they
>> could support direct IO.
>>
>> Alex
> I am more concerned with reads , and how to do direct I/O under Linux here.
> Reading raw devices in linux bypasses OS buffer cache.
it also bypassed OS readahead, not nessasarily a win
> But how can you
> mount a raw device( it is a character device) as a file system?
you can do a makefs on /dev/hda just like you do on /dev/hda2 and then
mount the result as a filesystem.
Postgres wants the OS layer to provide the filesystem, Oracle implements
it's own filesystem, so you would just point it at the drive/partition and
it would do it's own 'formatting'
this is something that may be reasonable for postgres to consider doing
someday, since postgres allocates things into 1m files and then keeps
track of what filename is used for what, it could instead allocate things
in 1m (or whatever size) chunks on the disk, and just keep track of what
addresses are used for what instead of filenames. this would definantly
allow you to work around problems like the ext2/3 indirect lookup
problems. now that the ability for partitioned table spaces it would be an
interesting experiment to be able to define a tablespace that used a raw
device instead of a filesystem to see if there are any noticable
performance gains
David Lang
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-06 04:33 david@lang.hm
parent: Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 16+ messages in thread
From: david@lang.hm @ 2007-04-06 04:33 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Xiaoning Ding <dingxn@cse.ohio-state.edu>; pgsql-performance
On Thu, 5 Apr 2007, Erik Jones wrote:
> On Apr 5, 2007, at 3:33 PM, david@lang.hm wrote:
>
>> On Thu, 5 Apr 2007, Xiaoning Ding wrote:
>>
>> > >
>> > > To the best of my knowledge, Postgres itself does not have a direct IO
>> > > option (although it would be a good addition). So, in order to use
>> > > direct
>> > > IO with postgres you'll need to consult your filesystem docs for how
>> > > to
>> > > set the forcedirectio mount option. I believe it can be set
>> > > dynamically,
>> > > but if you want it to be permanent you'll to add it to your
>> > > fstab/vfstab
>> > > file.
>> >
>> > I use Linux. It supports direct I/O on a per-file basis only. To bypass
>> > OS buffer cache,
>> > files should be opened with O_DIRECT option. I afraid that I have to
>> > modify PG.
>>
>> as someone who has been reading the linux-kernel mailing list for 10 years,
>> let me comment on this a bit.
>>
>> linux does have a direct i/o option,
>
> Yes, I know applications can request direct i/o with the O_DIRECT flag to
> open(), but can this be set to be forced for all applications or for
> individual applications from "outside" the application (not that I've ever
> heard of something like the second)?
no it can't, due to the fact that direct i/o has additional requirements
for what you can user for buffers that don't apply to normal i/o
>> but it has significant limits on when and how you cna use it (buffers must
>> be 512byte aligned and multiples of 512 bytes, things like that).
>
> That's a standard limit imposed by the sector size of hard drives, and is
> present in all direct i/o implementations, not just Linux.
right, but you don't have those limits for normal i/o
>> Also, in many cases testing has shon that there is a fairly significant
>> performance hit for this, not a perfomance gain.
>
> Those performance hits have been noticed for high i/o transaction databases?
> The idea here is that these kinds of database manage their own caches and
> having a separate filesystem cache in virtual memory that works with system
> memory page sizes is an unneeded level of indirection.
ahh, you're proposing a re-think of how postgres interacts with the O/S,
not just an optimization to be applied to the current architecture.
unlike Oracle, Postgres doesn't try to be an OS itself, it tries very hard
to rely on the OS to properly implement things rather then doing it's own
implementation.
> Yes, you should
> expect other "normal" utilities will suffer a performance hit as if you are
> trying to cp a 500 byte file you'll still have to work with 8K writes and
> reads whereas with the filesystem cache you can just write/read part of a
> page in memory and let the cache decide when it needs to write and read from
> disk. If there are other caveats to direct i/o on Linux I'd love to hear
> them.
other then bad interactions with "normal" utilities not compiled for
driect i/o I don't remember them offhand.
David Lang
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: a question about Direct I/O and double buffering
@ 2007-04-18 18:07 Jim C. Nasby <jim@nasby.net>
parent: Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 16+ messages in thread
From: Jim C. Nasby @ 2007-04-18 18:07 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Mark Lewis <mark.lewis@mir3.com>; Xiaoning Ding <dingxn@cse.ohio-state.edu>; pgsql-performance
On Thu, Apr 05, 2007 at 03:10:43PM -0500, Erik Jones wrote:
> Nope. What we never tracked down was the factor of 10 drop in
> database transactions, not disk transactions. The write volume was
> most definitely due to the direct io setting -- writes are now being
> done in terms of the system's block size where as before they were
> being done in terms of the the filesystem's cache page size (as it's
> in virtual memory). Basically, we do so many write transactions that
> the fs cache was constantly paging.
Did you try decreasing the size of the cache pages? I didn't realize
that Solaris used a different size for cache pages and filesystem
blocks. Perhaps the OS was also being too aggressive with read-aheads?
My concern is that you're essentially leaving a lot of your memory
unused this way, since shared_buffers is only set to 1.6G.
BTW, did you ever increase the parameter that controls how much memory
Solaris will use for filesystem caching?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2007-04-18 18:07 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-05 17:09 a question about Direct I/O and double buffering Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-04-05 18:09 ` Erik Jones <erik@myemma.com>
2007-04-05 18:22 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-04-05 18:47 ` Erik Jones <erik@myemma.com>
2007-04-05 18:56 ` Alex Deucher <alexdeucher@gmail.com>
2007-04-05 20:58 ` Xiaoning Ding <dingxn@cse.ohio-state.edu>
2007-04-05 21:06 ` Alex Deucher <alexdeucher@gmail.com>
2007-04-06 01:47 ` david@lang.hm
2007-04-05 20:33 ` david@lang.hm
2007-04-05 21:39 ` Erik Jones <erik@myemma.com>
2007-04-06 04:33 ` david@lang.hm
2007-04-05 18:27 ` Mark Lewis <mark.lewis@mir3.com>
2007-04-05 18:58 ` Erik Jones <erik@myemma.com>
2007-04-05 19:56 ` Mark Lewis <mark.lewis@mir3.com>
2007-04-05 20:10 ` Erik Jones <erik@myemma.com>
2007-04-18 18:07 ` Jim C. Nasby <jim@nasby.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox