Message-ID: <4DBB9475.7080104@2ndQuadrant.com>
Date: Sat, 30 Apr 2011 00:47:49 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110307 Icedove/3.0.11
MIME-Version: 1.0
To: Hsien-Wen Chu <chu.hsien.wen@gmail.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for
	postgresql?
References: <BANLkTi=u7zhbVf=Kz+VhmOG92ESnE=JjBA@mail.gmail.com>
In-Reply-To: <BANLkTi=u7zhbVf=Kz+VhmOG92ESnE=JjBA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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