Skip site navigation (1) Skip section navigation (2)

Re: Shared buffers, db transactions commited, and write IO on Solaris

From: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
To: Erik Jones <erik(at)myemma(dot)com>
Cc: Kenneth Marshall <ktm(at)rice(dot)edu>, Dimitri <dimitrik(dot)fr(at)gmail(dot)com>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Shared buffers, db transactions commited, and write IO on Solaris
Date: 2007-04-18 17:19:59
Message-ID: 20070418171959.GU72669@nasby.net (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Mar 30, 2007 at 11:19:09AM -0500, Erik Jones wrote:
> >On Fri, Mar 30, 2007 at 04:25:16PM +0200, Dimitri wrote:
> >>The problem is while your goal is to commit as fast as possible -  
> >>it's
> >>pity to vast I/O operation speed just keeping common block size...
> >>Let's say if your transaction modification entering into 512K -  
> >>you'll
> >>be able to write much more 512K blocks per second rather 8K per  
> >>second
> >>(for the same amount of data)... Even we rewrite probably several
> >>times the same block with incoming transactions - it still costs on
> >>traffic, and we will process slower even H/W can do better. Don't
> >>think it's good, no? ;)
> >>
> >>Rgds,
> >>-Dimitri
> >>
> >With block sizes you are always trading off overhead versus space
> >efficiency. Most OS write only in 4k/8k to the underlying hardware
> >regardless of the size of the write you issue. Issuing 16 512byte
> >writes has much more overhead than 1 8k write. On the light  
> >transaction
> >end, there is no real benefit to a small write and it will slow
> >performance for high throughput environments. It would be better to,
> >and I think that someone is looking into, batching I/O.
> >
> >Ken
> 
> True, and really, considering that data is only written to disk by  
> the bgwriter and at checkpoints, writes are already somewhat  
> batched.  Also, Dimitri, I feel I should backtrack a little and point  
> out that it is possible to have postgres write in 512byte blocks (at  
> least for UFS which is what's in my head right now) if you set the  
> systems logical block size to 4K and fragment size to 512 bytes and  
> then set postgres's BLCKSZ to 512bytes.  However, as Ken has just  
> pointed out, what you gain in space efficiency you lose in  
> performance so if you're working with a high traffic database this  
> wouldn't be a good idea.

Sorry for the late reply, but I was on vacation...

Folks have actually benchmarked filesystem block size on linux and found
that block sizes larger than 8k can actually be faster. I suppose if you
had a workload that *always* worked with only individual pages it would
be a waste, but it doesn't take much sequential reading to tip the
scales.
-- 
Jim Nasby                                            jim(at)nasby(dot)net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2007-04-18 17:47:37
Subject: Re: Foreign Key Deadlocking
Previous:From: Craig A. JamesDate: 2007-04-18 16:05:13
Subject: Re: Basic Q on superfluous primary keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group