Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1F1E59FBC18 for ; Fri, 30 Mar 2007 02:23:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 90258-06 for ; Fri, 30 Mar 2007 02:23:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtp150.iad.emailsrvr.com (smtp150.iad.emailsrvr.com [207.97.245.150]) by postgresql.org (Postfix) with ESMTP id C14E29FB755 for ; Fri, 30 Mar 2007 02:23:16 -0300 (ADT) Received: from [192.168.1.7] (c-71-228-218-92.hsd1.tn.comcast.net [71.228.218.92]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) (Authenticated sender: erik@myemma.com) by relay5.relay.iad.emailsrvr.com (SMTP Server) with ESMTP id 996115FDE75; Fri, 30 Mar 2007 01:23:07 -0400 (EDT) In-Reply-To: <5482c80a0703291515i7c05ba28l6f7240ebb5d2b837@mail.gmail.com> References: <9B7F4B34-7E96-4501-BCE0-D0BED739530A@myemma.com> <23286.1175184996@sss.pgh.pa.us> <5482c80a0703291041gbf95cd9v95e17e271293ae8e@mail.gmail.com> <606E8491-AAFB-493E-8FFA-FF26D6DD5ACE@myemma.com> <5482c80a0703291515i7c05ba28l6f7240ebb5d2b837@mail.gmail.com> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: multipart/alternative; boundary=Apple-Mail-3--643523123 Message-Id: <84D24979-AF34-4875-9AFB-DD4A7814B99A@myemma.com> Cc: "Tom Lane" , "PostgreSQL Performance" From: Erik Jones Subject: Re: Shared buffers, db transactions commited, and write IO on Solaris Date: Fri, 30 Mar 2007 00:22:52 -0500 To: Dimitri X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/432 X-Sequence-Number: 23837 --Apple-Mail-3--643523123 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed On Mar 29, 2007, at 5:15 PM, Dimitri wrote: >> >> >> > Erik, >> > >> > using 'forcedirectio' simply brings your write operations to the >> > *real* volume - means while you need to write 10 bytes you'll >> write 10 >> > bytes (instead of UFS block size (8K)). So it explains me why your >> > write volume became slower. > > I men 'lower' (not slower) > >> >> Sorry, that's not true. Google "ufs forcedirectio" go to the first >> link and you will find: >> >> "forcedirectio >> >> The forcedirectio (read "force direct IO") UFS option causes data to >> be buffered in kernel address whenever data is transferred between >> user address space and the disk. In other words, it bypasses the file >> system cache. For certain types of applications -- primarily database >> systems -- this option can dramatically improve performance. In fact, >> some database experts have argued that a file using the forcedirectio >> option will outperform a raw partition, though this opinion seems >> fairly controversial. >> >> The forcedirectio improves file system performance by eliminating >> double buffering, providing a small, efficient code path for file >> system reads and writes and removing pressure on memory." > > Erik, please, don't take me wrong, but reading Google (or better > man pages) > don't replace brain and basic practice... Direct IO option is not a > silver > bullet which will solve all your problems (try to do 'cp' on the > mounted in > 'forcedirectio' filesystem, or use your mailbox on it - you'll quickly > understand impact)... > >> >> However, what this does mean is that writes will be at the actual >> filesystem block size and not the cache block size (8K v. 512K). > > while UFS filesystem mounted normally, it uses its own cache for all > operations (read and write) and saves data modifications on per > page basis, means: when a process writes 200 bytes there will be 200 > bytes modified in cache, then whole page is written (8K) once data > demanded to be flushed (and WAL is writing per each commit)... > > Now, mounted with 'forcedirectio' option UFS is free of page size > constraint > and will write like a raw device an exactly demanded amount of > data, means: > when a process writes 200 bytes it'll write exactly 200 bytes to > the disk. = You are right in that the page size constraint is lifted in that directio cuts out the VM filesystem cache. However, the Solaris kernel still issues io ops in terms of its logical block size (which we have at the default 8K). It can issue io ops for fragments as small as 1/8th of the block size, but Postgres issues its io requests in terms of the block size which means that io ops from Postgres will be in 8K chunks which is exactly what we see when we look at our system io stats. In fact, if any io request is made that isn't a multiple of 512 bytes (the disk sector size), the file system switches back to the buffered io. > > However, to understand TX number mystery I think the only possible > solution > is to reproduce a small live test: > > (I'm sure you're aware you can mount/unmount forcedirectio > dynamically?) > > during stable workload do: > > # mount -o remount,logging /path_to_your_filesystem > > and check if I/O volume is increasing as well TX numbers > than come back: > > # mount -o remount,forcedirectio /path_to_your_filesystem > > and see if I/O volume is decreasing as well TX numbers... That's an excellent idea and I'll run it by the rest of our team tomorrow. erik jones software developer 615-296-0838 emma(r) --Apple-Mail-3--643523123 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1
On Mar 29, 2007, at = 5:15 PM, Dimitri wrote:

>>
> Erik,
> using 'forcedirectio' = simply brings your write operations to the
> = *real* volume - means while you need to write 10 bytes you'll write = 10
> bytes (instead of UFS block size (8K)). So = it explains me why your
> write = volume became slower.

I men 'lower' (not = slower)

=
Sorry, that's not true.=A0 Google "ufs forcedirectio" go = to the first
link and you will = find:

"forcedirectio

The forcedirectio (read "force = direct IO") UFS option causes data to
be = buffered in kernel address whenever data is transferred = between
user address space and the disk. = In other words, it bypasses the file
system cache. = For certain types of applications -- primarily database
systems -- this option can dramatically improve = performance. In fact,
some database experts have = argued that a file using the forcedirectio
option = will outperform a raw partition, though this opinion seems
fairly controversial.

The = forcedirectio improves file system performance by eliminating
double buffering, providing a small, efficient code = path for file
system reads and writes and = removing pressure on memory."

Erik, please, = don't take me wrong, but reading Google (or better man pages)
don't replace brain and basic practice... Direct IO = option is not a silver
bullet which will solve all = your problems (try to do 'cp' on the mounted in
'forcedirectio' filesystem, or use your mailbox on = it - you'll quickly
understand = impact)...

=
However, what this does mean is = that writes will be at the actual
filesystem = block size and not the cache block size (8K v. 512K).
=

while UFS filesystem mounted normally, it uses its = own cache for all
operations (read and write) and = saves data modifications on per
page basis, = means: when a process writes 200 bytes there will be 200
bytes modified in cache, then whole page is written = (8K) once data
demanded to be flushed (and WAL = is writing per each commit)...

Now, mounted with = 'forcedirectio' option UFS is free of page size constraint
and will write like a raw device an exactly demanded = amount of data, means:
when a process writes 200 = bytes it'll write exactly 200 bytes to the disk. = =3D

You are right in that the = page size constraint is lifted in that directio cuts out the VM = filesystem cache.=A0 However, the Solaris kernel still issues io ops in = terms of its logical block size (which we have at the default 8K).=A0 It = can issue io ops for fragments as small as 1/8th of the block size, but = Postgres issues its io requests in terms of the block size which means = that io ops from Postgres will be in 8K chunks which is exactly what we = see when we look at our system io stats.=A0 In fact, if any io request = is made that isn't a multiple of 512 bytes (the disk sector size), the = file system switches back to the buffered = io.


However, = to understand TX number mystery I think the only possible = solution
is to reproduce a small live = test:

(I'm sure you're aware you can mount/unmount = forcedirectio dynamically?)

during stable workload = do:

=A0 # = mount -o remount,logging=A0 = /path_to_your_filesystem

and check if I/O volume is = increasing as well TX numbers
than come = back:

=A0 # = mount -o remount,forcedirectio=A0 = /path_to_your_filesystem

and see if I/O volume is = decreasing as well TX numbers...
=

That's an excellent idea and I'll run it by = the rest of our team tomorrow.

erik jones = <erik@myemma.com>
software= developer
615-296-0838
emma(r)


=

= --Apple-Mail-3--643523123--