DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=lB7xfohKRYTxT5hVpdva6nTxL69ve+sdmBbc1VxlPT805qUYweFJHH9jCEOateFfXm
	E3MNRQ2DS2bCmtRRgYa/y0JYhiq+r0v1encVyFVejNSiMjHKzMIW+FkKbNSBRPTvfTKa
	RHARHR5AiCDGlrFnhINKKePgrsw4bsVjBZK+s=
Message-ID: <4C52E957.8020304@gmail.com>
Date: Fri, 30 Jul 2010 17:01:43 +0200
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
CC: Greg Smith <greg@2ndquadrant.com>
Subject: Re: Testing Sandforce SSD
References: <4C4A9452.9070100@gmail.com> <4C4B1F84.5030306@2ndquadrant.com>
	<4C4B4B41.7000800@2ndquadrant.com>
In-Reply-To: <4C4B4B41.7000800@2ndquadrant.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Greg Smith wrote:
> Greg Smith wrote:
>> Note that not all of the Sandforce drives include a capacitor; I hope 
>> you got one that does!  I wasn't aware any of the SF drives with a 
>> capacitor on them were even shipping yet, all of the ones I'd seen 
>> were the chipset that doesn't include one still.  Haven't checked in 
>> a few weeks though.
>
> Answer my own question here:  the drive Yeb got was the brand spanking 
> new OCZ Vertex 2 Pro, selling for $649 at Newegg for example:  
> http://www.newegg.com/Product/Product.aspx?Item=N82E16820227535 and 
> with the supercacitor listed right in the main production 
> specifications there.  This is officially the first inexpensive 
> (relatively) SSD with a battery-backed write cache built into it.  If 
> Yeb's test results prove it works as it's supposed to under 
> PostgreSQL, I'll be happy to finally have a moderately priced SSD I 
> can recommend to people for database use.  And I fear I'll be out of 
> excuses to avoid buying one as a toy for my home system.
>
Hello list,

After a week testing I think I can answer the question above: does it 
work like it's supposed to under PostgreSQL?

YES

The drive I have tested is the $435,- 50GB OCZ Vertex 2 Pro, 
http://www.newegg.com/Product/Product.aspx?Item=N82E16820227534

* it is safe to mount filesystems with barrier off, since it has a 
'supercap backed cache'. That data is not lost is confirmed by a dozen 
power switch off tests while running either diskchecker.pl or pgbench.
* the above implies its also safe to use this SSD with barriers, though 
that will perform less, since this drive obeys write trough commands.
* the highest pgbench tps number for the TPC-B test for a scale 300 
database (~5GB) I could get was over 6700. Judging from the iostat 
average util of ~40% on the xlog partition, I believe that this number 
is limited by other factors than the SSD, like CPU, core count, core 
MHz, memory size/speed, 8.4 pgbench without threads. Unfortunately I 
don't have a faster/more core machines available for testing right now.
* pgbench numbers for a larger than RAM database, read only was over 
25000 tps (details are at the end of this post), during which iostat 
reported ~18500 read iops and 100% utilization.
* pgbench max reported latencies are 20% of comparable BBWC setups.
* how reliable it is over time, and how it performs over time I cannot 
say, since I tested it only for a week.

regards,
Yeb Havinga

PS: ofcourse all claims I make here are without any warranty. All 
information in this mail is for reference purposes, I do not claim it is 
suitable for your database setup.

Some info on configuration:
BOOT_IMAGE=/boot/vmlinuz-2.6.32-22-server  elevator=deadline
quad core AMD Phenom(tm) II X4 940 Processor on 3.0GHz
16GB RAM 667MHz DDR2

Disk/ filesystem settings.
Model Family:     OCZ Vertex SSD
Device Model:     OCZ VERTEX2-PRO
Firmware Version: 1.10

hdparm: did not change standard settings: write cache is on, as well as 
readahead.
 hdparm -AW /dev/sdc
/dev/sdc:
 look-ahead    =  1 (on)
 write-caching =  1 (on)

Untuned ext4 filesystem.
Mount options
/dev/sdc2 on /data type ext4 
(rw,noatime,nodiratime,relatime,barrier=0,discard)
/dev/sdc3 on /xlog type ext4 
(rw,noatime,nodiratime,relatime,barrier=0,discard)
Note the -o discard: this means use of the automatic SSD trimming on a 
new linux kernel.
Also, per core per filesystem there now is a [ext4-dio-unwrit] process - 
which suggest something like 'directio'? I haven't investigated this any 
further.

Sysctl:
(copied from a larger RAM database machine)
kernel.core_uses_pid = 1
fs.file-max = 327679
net.ipv4.ip_local_port_range = 1024 65000
kernel.msgmni = 2878
kernel.msgmax = 8192
kernel.msgmnb = 65536
kernel.sem = 250 32000 100 142
kernel.shmmni = 4096
kernel.sysrq = 1
kernel.shmmax = 33794121728
kernel.shmall = 16777216
net.core.rmem_default = 262144
net.core.rmem_max = 2097152
net.core.wmem_default = 262144
net.core.wmem_max = 262144
fs.aio-max-nr = 3145728
vm.swappiness = 0
vm.dirty_background_ratio = 3
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.dirty_ratio = 15

Postgres settings:
8.4.4
--with-blocksize=4
I saw about 10% increase in performance compared to 8KB blocksizes.

Postgresql.conf:
changed from default config are:
maintenance_work_mem = 480MB # pgtune wizard 2010-07-25
checkpoint_completion_target = 0.9 # pgtune wizard 2010-07-25
effective_cache_size = 5632MB # pgtune wizard 2010-07-25
work_mem = 512MB # pgtune wizard 2010-07-25
wal_buffers = 8MB # pgtune wizard 2010-07-25
checkpoint_segments = 128 # pgtune said 16 here
shared_buffers = 1920MB # pgtune wizard 2010-07-25
max_connections = 100

initdb with data on sda2 and xlog on sda3, C locale

Read write test on ~5GB database:
$ pgbench -v -c 20 -M prepared -T 3600 test
starting vacuum...end.
starting vacuum pgbench_accounts...end.
transaction type: TPC-B (sort of)
scaling factor: 300
query mode: prepared
number of clients: 20
duration: 3600 s
number of transactions actually processed: 24291875
tps = 6747.665859 (including connections establishing)
tps = 6747.721665 (excluding connections establishing)

Read only test on larger than RAM ~23GB database (server has 16GB 
fysical RAM) :
$ pgbench -c 20 -M prepared -T 300 -S test
starting vacuum...end.
transaction type: SELECT only
*scaling factor: 1500*
query mode: prepared
number of clients: 20
duration: 300 s
number of transactions actually processed: 7556469
tps = 25184.056498 (including connections establishing)
tps = 25186.336911 (excluding connections establishing)

IOstat reports ~18500 reads/s and ~185 read MB/s during this read only 
test on the data partition with 100% util.