Message-ID: <4C52EC63.8010205@denninger.net>
Date: Fri, 30 Jul 2010 10:14:43 -0500
From: Karl Denninger <karl@denninger.net>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Yeb Havinga <yebhavinga@gmail.com>
CC: pgsql-performance@postgresql.org, Greg Smith <greg@2ndquadrant.com>
Subject: Re: Testing Sandforce SSD
References: <4C4A9452.9070100@gmail.com> <4C4B1F84.5030306@2ndquadrant.com>
	<4C4B4B41.7000800@2ndquadrant.com> <4C52E957.8020304@gmail.com>
In-Reply-To: <4C52E957.8020304@gmail.com>
Content-Type: multipart/mixed; boundary="------------040408010504010001040003"

This is a multi-part message in MIME format.
--------------040408010504010001040003
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

6700tps?!  Wow......

Ok, I'm impressed.  May wait a bit for prices to come somewhat, but that
sounds like two of those are going in one of my production machines
(Raid 1, of course)

Yeb Havinga wrote:
> 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.
>
>

--------------040408010504010001040003
Content-Type: text/x-vcard; charset=utf-8;
 name="karl.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="karl.vcf"

begin:vcard
fn:Karl Denninger
n:Denninger;Karl
email;internet:karl@denninger.net
x-mozilla-html:TRUE
version:2.1
end:vcard


--------------040408010504010001040003--