Re: Raid 10 chunksize

On 4/1/09 9:15 AM, "Stef Telford" <stef(at)ummon(dot)com> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Greg Smith wrote:
>> On Wed, 1 Apr 2009, Stef Telford wrote:
>>
>>> I have -explicitly- enabled sync in the conf...In fact, if I turn
>>> -off- sync commit, it gets about 200 -slower- rather than
>>> faster.
>>
>> You should take a look at
>> http://www.postgresql.org/docs/8.3/static/wal-reliability.html
>>
>> And check the output from "hdparm -I" as suggested there. If
>> turning off fsync doesn't improve your performance, there's almost
>> certainly something wrong with your setup. As suggested before,
>> your drives probably have write caching turned on. PostgreSQL is
>> incapable of knowing that, and will happily write in an unsafe
>> manner even if the fsync parameter is turned on. There's a bunch
>> more information on this topic at
>> http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm
>>
>> Also: a run to run variation in pgbench results of +/-10% TPS is
>> normal, so unless you saw a consistent 200 TPS gain during multiple
>> tests my guess is that changing fsync for you is doing nothing,
>> rather than you suggestion that it makes things slower.
>>
> Hello Greg,
> Turning off fsync -does- increase the throughput noticeably,
> - -however-, turning off synchronous_commit seemed to slow things down
> for me. Your right though, when I toggled the sync_commit on the
> system, there was a small variation with TPS coming out between 1100
> and 1300. I guess I saw the initial run and thought that there was a
> 'loss' in sync_commit = off
>
> I do agree that the benefit is probably from write-caching, but I
> think that this is a 'win' as long as you have a UPS or BBU adaptor,
> and really, in a prod environment, not having a UPS is .. well. Crazy ?

Write caching on SATA is totally fine. There were some old ATA drives that
when paried with some file systems or OS's would not be safe. There are
some combinations that have unsafe write barriers. But there is a standard
well supported ATA command to sync and only return after the data is on
disk. If you are running an OS that is anything recent at all, and any
disks that are not really old, you're fine.

The notion that current SATA systems are unsafe to have write caching (or
SAS for that matter) is not fully informed. You have to pair it with a file
system and OS that doesn't issue the necessary cache flush commands to sync.