pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
7+ messages / 6 participants
[nested] [flat]
* VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
@ 2011-04-28 03:33 HSIEN-WEN CHU <hsienwen.chu@googlemail.com>
0 siblings, 2 replies; 7+ messages in thread
From: HSIEN-WEN CHU @ 2011-04-28 03:33 UTC (permalink / raw)
To: pgsql-performance; pgsql-hackers@postgresql.org; pgsql-novice@postgresql.org
Dear all
When database files are on a VxFS filesystem, performance can be
significantly improved by setting the VX_CONCURRENT cache advisory on
the file according to vxfs document,
my question is that have any tested by this?
#include <sys/fs/vx_ioctl.h>
ioctl(fd, VX_SETCACHE, VX_CONCURRENT);
Regards
HSIEN WEN
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
@ 2011-04-28 13:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: HSIEN-WEN CHU <hsienwen.chu@googlemail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2011-04-28 13:25 UTC (permalink / raw)
To: HSIEN-WEN CHU <hsienwen.chu@googlemail.com>; +Cc: pgsql-hackers@postgresql.org
HSIEN-WEN CHU <hsienwen.chu@googlemail.com> writes:
> When database files are on a VxFS filesystem, performance can be
> significantly improved by setting the VX_CONCURRENT cache advisory on
> the file according to vxfs document,
Presumably, if whatever behavior this invokes were an unalloyed good,
they'd have just made it the default. The existence of a flag makes
me suppose that there are some clear application-visible downsides.
What are they?
BTW, please do not cross-post the same question to three different lists.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
@ 2011-04-29 00:14 Greg Smith <greg@2ndQuadrant.com>
parent: HSIEN-WEN CHU <hsienwen.chu@googlemail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Greg Smith @ 2011-04-29 00:14 UTC (permalink / raw)
To: pgsql-performance
On 04/27/2011 11:33 PM, HSIEN-WEN CHU wrote:
> When database files are on a VxFS filesystem, performance can be
> significantly improved by setting the VX_CONCURRENT cache advisory on
> the file according to vxfs document,
>
That won't improve performance, and it's not safe either. VX_CONCURRENT
switches the filesystem to use direct I/O. That's usually slower for
PostgreSQL. And it introduces some requirements for both block
alignment and the application avoiding overlapping writes. PostgreSQL
doesn't do either, so I wouldn't expect it to be compatible with
VX_CONCURRENT.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
@ 2011-04-30 04:47 Greg Smith <greg@2ndQuadrant.com>
0 siblings, 1 reply; 7+ messages in thread
From: Greg Smith @ 2011-04-30 04:47 UTC (permalink / raw)
To: Hsien-Wen Chu <chu.hsien.wen@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On 04/30/2011 12:24 AM, Hsien-Wen Chu wrote:
> I'm little bit confuse why it is not safe. and my question is following.
>
> for database application, we need to avoid double cache, PostgreSQL
> shared_buffer will cache the data, so we do not want to file system to
> cache the data right?. so the DIRECT IO is better, right?.
>
No. There are parts of PostgreSQL that expect the operating system to
do write caching. Two examples are the transaction logs and the
processing done by VACUUM. If you eliminate that with direct I/O, the
slowdown can be much, much larger than what you gain by eliminating
double-buffering on reads.
On the read side, PostgreSQL also expects that operating system features
like read-ahead are working properly. While this does introduce some
double-buffering, the benefits for sequential scans are larger than that
overhead, too. You may not get the expected read-ahead behavior if you
use direct I/O.
Direct I/O is not a magic switch that makes things faster; you have to
very specifically write your application to work around what it does,
good and bad, before it is expected to improves things. And PostgreSQL
isn't written that way. It definitely requires OS caching to work well.
> for VXFS, if the we use ioctl(fd,vx_cacheset,vx_concurrent) API,
> according to the vxfs document, it will hold a shared lock for write
> operation, but not the exclusive clock, also it is a direct IO,
>
There are very specific technical requirements that you must follow when
using direct I/O. You don't get direct I/O without also following its
alignment needs. Read the "Direct I/O best practices" section of
http://people.redhat.com/msnitzer/docs/io-limits.txt for a quick intro
to the subject. And there's this additional set of requirements you
mention in order for this particular VXFS feature to work, which I can't
even comment on. But you can be sure PostgreSQL doesn't try to do
either of those things--it's definitely not aligning for direct I/O.
Has nothing to do with ACID or the filesystem.
Now, the VXFS implementation may do some tricks that bypass the
alignment requirements. But even if you got it to work, it would still
be slower for anything but some read-only workloads. Double buffering
is really not that big of a performance problem, you just need to make
sure you don't set shared_buffers to an extremely large value.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
@ 2011-04-30 08:51 Hsien-Wen Chu <chu.hsien.wen@gmail.com>
parent: Greg Smith <greg@2ndQuadrant.com>
0 siblings, 1 reply; 7+ messages in thread
From: Hsien-Wen Chu @ 2011-04-30 08:51 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: pgsql-performance
Hi Mr. Greg Smith
since the block size is 8k for the default, and it consisted with many
tuple/line; as my understand, if any tuple/line is changed(maybe
update, insert, delete). the block will be marked as dirty block. and
then it will be flashed to disk by bgwriter.
so my question is if the data block(8k) is aligned with the file
system block? if it is aligned with file system block, so what's the
potential issue make it is not safe for direct io. (please assume
vxfs, vxvm and the disk sector is aligned ).please correct me if any
incorrect.
thank you very much
Tony
On 4/30/11, Greg Smith <greg@2ndquadrant.com> wrote:
> On 04/30/2011 12:24 AM, Hsien-Wen Chu wrote:
>> I'm little bit confuse why it is not safe. and my question is following.
>>
>> for database application, we need to avoid double cache, PostgreSQL
>> shared_buffer will cache the data, so we do not want to file system to
>> cache the data right?. so the DIRECT IO is better, right?.
>>
>
> No. There are parts of PostgreSQL that expect the operating system to
> do write caching. Two examples are the transaction logs and the
> processing done by VACUUM. If you eliminate that with direct I/O, the
> slowdown can be much, much larger than what you gain by eliminating
> double-buffering on reads.
>
> On the read side, PostgreSQL also expects that operating system features
> like read-ahead are working properly. While this does introduce some
> double-buffering, the benefits for sequential scans are larger than that
> overhead, too. You may not get the expected read-ahead behavior if you
> use direct I/O.
>
> Direct I/O is not a magic switch that makes things faster; you have to
> very specifically write your application to work around what it does,
> good and bad, before it is expected to improves things. And PostgreSQL
> isn't written that way. It definitely requires OS caching to work well.
>
>> for VXFS, if the we use ioctl(fd,vx_cacheset,vx_concurrent) API,
>> according to the vxfs document, it will hold a shared lock for write
>> operation, but not the exclusive clock, also it is a direct IO,
>>
>
> There are very specific technical requirements that you must follow when
> using direct I/O. You don't get direct I/O without also following its
> alignment needs. Read the "Direct I/O best practices" section of
> http://people.redhat.com/msnitzer/docs/io-limits.txt for a quick intro
> to the subject. And there's this additional set of requirements you
> mention in order for this particular VXFS feature to work, which I can't
> even comment on. But you can be sure PostgreSQL doesn't try to do
> either of those things--it's definitely not aligning for direct I/O.
> Has nothing to do with ACID or the filesystem.
>
> Now, the VXFS implementation may do some tricks that bypass the
> alignment requirements. But even if you got it to work, it would still
> be slower for anything but some read-only workloads. Double buffering
> is really not that big of a performance problem, you just need to make
> sure you don't set shared_buffers to an extremely large value.
>
> --
> Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
> "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
@ 2011-05-05 17:53 Robert Haas <robertmhaas@gmail.com>
parent: Hsien-Wen Chu <chu.hsien.wen@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Robert Haas @ 2011-05-05 17:53 UTC (permalink / raw)
To: Hsien-Wen Chu <chu.hsien.wen@gmail.com>; +Cc: Greg Smith <greg@2ndquadrant.com>; pgsql-performance
On Sat, Apr 30, 2011 at 4:51 AM, Hsien-Wen Chu <chu.hsien.wen@gmail.com> wrote:
> since the block size is 8k for the default, and it consisted with many
> tuple/line; as my understand, if any tuple/line is changed(maybe
> update, insert, delete). the block will be marked as dirty block. and
> then it will be flashed to disk by bgwriter.
True...
> so my question is if the data block(8k) is aligned with the file
> system block? if it is aligned with file system block, so what's the
> potential issue make it is not safe for direct io. (please assume
> vxfs, vxvm and the disk sector is aligned ).please correct me if any
> incorrect.
It's not about safety - it's about performance. On a machine with
64GB of RAM, a typical setting for shared_buffers set to 8GB. If you
start reading blocks into the PostgreSQL cache - or writing them out
of the cache - in a way that bypasses the filesystem cache, you're
going to have only 8GB of cache, instead of some much larger amount.
More cache = better performance.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
@ 2011-05-06 10:53 Dimitri <dimitrik.fr@gmail.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dimitri @ 2011-05-06 10:53 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Hsien-Wen Chu <chu.hsien.wen@gmail.com>; Greg Smith <greg@2ndquadrant.com>; pgsql-performance
You should rather consider VxFS tuning - it has an auto-discovery for
DIRECT I/O according the block size. Just change this setting to 8K or
16-32K depending on your workload - then all I/O operations with a
bigger block size will be executed in DIRECT mode and bypass FS cache
(which logical as usually it'll correspond to a full scan or a seq
scan of some data), while I/O requests with smaller blocks will remain
cached which is very useful as it'll mainly cache random I/O (mainly
index access)..
With such a tuning I've got over %35 performance improvement comparing
to any other states (full DIRECT or fully cached).
Rgds,
-Dimitri
Rgds,
-Dimitri
On 5/5/11, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sat, Apr 30, 2011 at 4:51 AM, Hsien-Wen Chu <chu.hsien.wen@gmail.com>
> wrote:
>> since the block size is 8k for the default, and it consisted with many
>> tuple/line; as my understand, if any tuple/line is changed(maybe
>> update, insert, delete). the block will be marked as dirty block. and
>> then it will be flashed to disk by bgwriter.
>
> True...
>
>> so my question is if the data block(8k) is aligned with the file
>> system block? if it is aligned with file system block, so what's the
>> potential issue make it is not safe for direct io. (please assume
>> vxfs, vxvm and the disk sector is aligned ).please correct me if any
>> incorrect.
>
> It's not about safety - it's about performance. On a machine with
> 64GB of RAM, a typical setting for shared_buffers set to 8GB. If you
> start reading blocks into the PostgreSQL cache - or writing them out
> of the cache - in a way that bypasses the filesystem cache, you're
> going to have only 8GB of cache, instead of some much larger amount.
> More cache = better performance.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-05-06 10:53 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-28 03:33 VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql? HSIEN-WEN CHU <hsienwen.chu@googlemail.com>
2011-04-28 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-29 00:14 ` Greg Smith <greg@2ndQuadrant.com>
2011-04-30 04:47 Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql? Greg Smith <greg@2ndQuadrant.com>
2011-04-30 08:51 ` Hsien-Wen Chu <chu.hsien.wen@gmail.com>
2011-05-05 17:53 ` Robert Haas <robertmhaas@gmail.com>
2011-05-06 10:53 ` Dimitri <dimitrik.fr@gmail.com>
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