Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id F00361337999 for ; Sat, 30 Apr 2011 05:51:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 13800-10 for ; Sat, 30 Apr 2011 08:51:13 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f181.google.com (mail-qy0-f181.google.com [209.85.216.181]) by mail.postgresql.org (Postfix) with ESMTP id 9CA4C133798E for ; Sat, 30 Apr 2011 05:51:13 -0300 (ADT) Received: by qyg14 with SMTP id 14so2318510qyg.19 for ; Sat, 30 Apr 2011 01:51:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=37d+E+208eUCBLMzsIU2zaT1iliGc4XEGiIv4ckzJWY=; b=TRtWFKyAjcyE7hQKxuUE0rGYS8pnuC3YqhaTRiM35T3tC74P2JId0L0WOveIGPD1Pq CMVj34vBVBNtJwix2MW2H7vLfAdyl3LIwJiFg/XFfz9MsbPgaHr4inMAONWYrLsfD1ij baJ5qmEwqQv4fZSYbV/z3FwiZojtKa9dz4yHo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=n2SZOOsuanC7PEVjqWAw4ihAM8QVqtIX9WhohJ4PObJQOy+f3zwlx1cpIx0sfFuqc0 ukvVoIa50CqA9ngmHec5myXU59FNiHXcXF3Sb66WzKrkUVt2+7rM2yfRaPv5ZI4c87U/ xbF+s5wRmXnWLEzkrEoZhg7fupy7NhvjDxxq4= MIME-Version: 1.0 Received: by 10.229.63.229 with SMTP id c37mr4623826qci.212.1304153472098; Sat, 30 Apr 2011 01:51:12 -0700 (PDT) Received: by 10.229.2.1 with HTTP; Sat, 30 Apr 2011 01:51:11 -0700 (PDT) In-Reply-To: <4DBB9475.7080104@2ndQuadrant.com> References: <4DBB9475.7080104@2ndQuadrant.com> Date: Sat, 30 Apr 2011 16:51:11 +0800 Message-ID: Subject: Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql? From: Hsien-Wen Chu To: Greg Smith Cc: "pgsql-performance@postgresql.org" Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201105/8 X-Sequence-Number: 43517 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 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 > >