Re: Raid 10 chunksize - Mailing list pgsql-performance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Stef Telford wrote:
> Mark Kirkwood wrote:
>> Scott Carey wrote:
>>> 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.
>>>
>>>
>> [Looks at  Stef's  config - 2x 7200 rpm SATA RAID 0]  I'm still
>> highly suspicious of such a system being capable of outperforming
>>  one with the same number of (effective) - much faster - disks
>> *plus* a dedicated WAL disk pair... unless it is being a little
>> loose about fsync! I'm happy to believe ext4 is better than ext3
>> - but not that much!
>
>> However, its great to have so many different results to compare
>> against!
>
>> Cheers
>
>> Mark
>
> postgres@rob-desktop:~$ /usr/lib/postgresql/8.3/bin/pgbench -c 24
> -t 12000 test_db 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 = 3662.200088 (including connections
> establishing) tps = 3664.823769 (excluding connections
> establishing)
>
>
> (Nb; Thread here;
> http://www.ocztechnologyforum.com/forum/showthread.php?t=54038 )
Fyi, I got my intel x25-m in the mail, and I have been benching it for
the past hour or so. Here are some of the rough and ready figures.
Note that I don't get anywhere near the vertex benchmark. I did
hotplug it and made the filesystem using Theodore Ts'o webpage
directions (
http://thunk.org/tytso/blog/2009/02/20/aligning-filesystems-to-an-ssds-erase-block-size/
) ; The only thing is, ext3/4 seems to be fixated on a blocksize of
4k, I am wondering if this could be part of the 'problem'. Any
ideas/thoughts on tuning gratefully received.

Anyway, benchmarks (same system as previously, etc)

(ext4dev, 4k block size, pg_xlog on 2x7.2krpm raid-0, rest on SSD)

root@debian:~# /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db
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 = 1407.254118 (including connections establishing)
tps = 1407.645996 (excluding connections establishing)

(ext4dev, 4k block size, everything on SSD)

root@debian:~# /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db
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 = 2130.734705 (including connections establishing)
tps = 2131.545519 (excluding connections establishing)

(I wanted to try and see if random_page_cost dropped down to 2.0,
sequential_page_cost = 2.0 would make a difference. Eg; making the
planner aware that a random was the same cost as a sequential)

root@debian:/var/lib/postgresql/8.3/main#
/usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db
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 = 1982.481185 (including connections establishing)
tps = 1983.223281 (excluding connections establishing)


Regards
Stef
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAknTxccACgkQANG7uQ+9D9XoPgCfRwWwh0jTIs1iDQBVVdQJW/JN
CBcAn3zoOO33BnYC/FgmFzw1I+isWvJh
=0KYa
-----END PGP SIGNATURE-----