From: Scott Carey <scott@richrelevance.com>
To: Scott Carey <scott@richrelevance.com>, Mark Kirkwood
	<markir@paradise.net.nz>, Stef Telford <stef@ummon.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Date: Thu, 26 Mar 2009 15:02:08 -0700
Subject: Re: Raid 10 chunksize
Thread-Topic: [PERFORM] Raid 10 chunksize
Thread-Index: Acmt1iJi4+QueXhMTveVVHfAK1mZUwAhd+U1AACf5MY=
Message-ID: <C5F14970.3CA1%scott@richrelevance.com>
In-Reply-To: <C5F1453F.3C99%scott@richrelevance.com>
Accept-Language: en-US
Content-Language: en
acceptlanguage: en-US
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0


On 3/26/09 2:44 PM, "Scott Carey" <scott@richrelevance.com> wrote:

>=20
>=20
> On 3/25/09 9:43 PM, "Mark Kirkwood" <markir@paradise.net.nz> wrote:
>=20
>> Stef Telford wrote:
>>>=20
>>> Hello Mark,
>>>     Okay, so, take all of this with a pinch of salt, but, I have the
>>> same config (pretty much) as you, with checkpoint_Segments raised to
>>> 192. The 'test' database server is Q8300, 8GB ram, 2 x 7200rpm SATA
>>> into motherboard which I then lvm stripped together; lvcreate -n
>>> data_lv -i 2 -I 64 mylv -L 60G (expandable under lvm2). That gives me
>>> a stripe size of 64. Running pgbench with the same scaling factors;
>>>=20
>>> starting vacuum...end.
>>> transaction type: TPC-B (sort of)
>>> scaling factor: 100
>>> number of clients: 24
>>> number of transactions per client: 12000
>>> number of transactions actually processed: 288000/288000
>>> tps =3D 1398.907206 (including connections establishing)
>>> tps =3D 1399.233785 (excluding connections establishing)
>>>=20
>>>     It's also running ext4dev, but, this is the 'playground' server,
>>> not the real iron (And I dread to do that on the real iron). In short,
>>> I think that chunksize/stripesize is killing you. Personally, I would
>>> go for 64 or 128 .. that's jst my 2c .. feel free to
>>> ignore/scorn/laugh as applicable ;)
>>>=20
>>>=20
>> Stef - I suspect that your (quite high) tps is because your SATA disks
>> are not honoring the fsync() request for each commit. SCSI/SAS disks
>> tend to by default flush their cache at fsync - ATA/SATA tend not to.
>> Some filesystems (e.g xfs) will try to work around this with write
>> barrier support, but it depends on the disk firmware.
>=20
> This has not been very true for a while now.  SATA disks will flush their
> write cache when told, and properly adhere to write barriers.  Of course,
> not all file systems send the right write barrier commands and flush
> commands to SATA drives (UFS for example, and older versions of ext3).
>=20
> It may be the other way around, your SAS drives might have the write cach=
e
> disabled for no good reason other than to protect against file systems th=
at
> don't work right.
>=20

A little extra info here >>  md, LVM, and some other tools do not allow the
file system to use write barriers properly.... So those are on the bad list
for data integrity with SAS or SATA write caches without battery back-up.
However, this is NOT an issue on the postgres data partition.  Data fsync
still works fine, its the file system journal that might have out-of-order
writes.  For xlogs, write barriers are not important, only fsync() not
lying.

As an additional note, ext4 uses checksums per block in the journal, so it
is resistant to out of order writes causing trouble.  The test compared to
here was on ext4, and most likely the speed increase is partly due to that.

>>=20
>> Thanks for your reply!
>>=20
>> Mark
>>=20
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or=
g)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>=20
>=20
>=20
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org=
)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>=20