Postgresql – Postgres Write Performance on Intel S3700 SSD

performancepostgresql

I'm not seeing the Postgres write performance increases I thought I would with a single SSD vs a hardware RAID 10 array of (16) 15k RPM SAS drives.

I have a Dell R820 with a PERC H700 hardware RAID card and 16 15k RPM SAS drives in a RAID 10 array, as well as an 800GB Intel s3700 SSD. The server has 128GB of RAM and 64 cores of Xeon E5-4640 at 2.40GHz, running CentOS 6.4 and Postgres 9.2.4.

I'm using pgbench to compare the SAS drives in a RAID 10 array to the single SSD.

15k RPM SAS RAID 10 Results

pgbench -U postgres -p 5432 -T 50 -c 10 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 50 s
number of transactions actually processed: 90992
tps = 1819.625430 (including connections establishing)
tps = 1821.417384 (excluding connections establishing)

Single Intel s3700 SSD Results

pgbench -U postgres -p 5444 -T 50 -c 10 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 50 s
number of transactions actually processed: 140597
tps = 2811.687286 (including connections establishing)
tps = 2814.578386 (excluding connections establishing)

In real world usage we have a very write-intensive process that takes about 7 minutes to complete, and the RAID 10 array and SSD are within 10 or 15 seconds of each other.

I expected far better performance from the SSD.

Here are Bonnie++ results for the SSD:

Version  1.96       ------Sequential Output------ --Sequential Input- --Random-
Concurrency   1     -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
openlink2.rady 252G   532  99 375323  97 183855  45  1938  99 478149  54 +++++ +++
Latency             33382us   82425us     168ms   12966us   10879us   10208us
Version  1.96       ------Sequential Create------ --------Random Create--------
openlink2.radyn.com -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16  5541  46 +++++ +++ +++++ +++ 18407  99 +++++ +++ +++++ +++
Latency              1271us    1055us    1157us     456us      20us     408us

Here are Bonnie++ results for the RAID 10 15k RPM drives:

Version  1.96       ------Sequential Output------ --Sequential Input- --Random-
Concurrency   1     -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
openlink2.rady 252G   460  99 455060  98 309526  56  2156  94 667844  70 197.9  85
Latency             37811us   62175us     393ms   75392us     169ms   17633us
Version  1.96       ------Sequential Create------ --------Random Create--------
openlink2.radyn.com -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16 12045  95 +++++ +++ +++++ +++ 16851  98 +++++ +++ +++++ +++
Latency              7879us     504us     555us     449us      24us     377us

Here are dd results for the SSD:

dd if=/dev/zero of=/path/on/ssd bs=1M count=4096 conv=fdatasync,notrunc
4294967296 bytes (4.3 GB) copied, 12.7438 s, 337 MB/s

And here are dd results for the RAID 10 15k RPM drives:

dd if=/dev/zero of=/path/on/array bs=1M count=4096 conv=fdatasync,notrunc
4294967296 bytes (4.3 GB) copied, 8.45972 s, 508 MB/s

I'd post the Postgres config, but its clear the SSD isn't outperforming the RAID 10 array, so it doesn't seem applicable.

So is the SSD performing as it should be?

Or is the RAID 10 with 16 fast drives just so good that it outperforms a single SSD? A RAID 10 array of the SSD's would be awesome, but at $2,000 each the $8,000 price tag is hard to justify (unless we were sure to see the 2x to 5x gains we were hoping for in real world performance gains).

================= Update =================

It turns out we have 16 SAS drives in the array, not 8. I think the combined throughput is

Here are iozone benchmarks which shed more light. The RAID10 array produces better results pretty consistently. 4 or 8 of the SSD's in RAID 10 would likely beat the SAS array (at a steep price, of course).

SSD benchmark http://pastebin.com/vEMHCQhR

16 drive RAID-10 benchmark http://pastebin.com/LQNrm7tT

Here is the Postgres config for the SSD, in case anyone sees any room for improvement to take advantage of the SSD http://pastebin.com/Qsb3Ks7Y

Best Answer

I'm not really sure this is a problem in itself, because, as you can see, a single SSD drive can outperform an 8 disk RAID 10 setup in many tests.

Almost all the tests point to a better speed of the single SSD drive:

  • better latencies
  • lower CPU usage (if I'm reading correctly in some case it's 44% vs 95%)
  • no of transactions per second is bigger with 55%
  • no of transactions in total is bigger with the same 55%

In a single case that SSD was outperformed, and that was sequential writes. Which, I'd say, is most usual for batches, not for an OLTP style of load. So if you're having mostly these kind of writes, maybe a single SSD is not a solution for you now.

And we're not talking about Fusion-IO drives (which I suspect might bring you that next level you'll expect, but at a next level price).

From the point of view of the DBA that had to work with crappy storage over the years, this is a fair advancement in technology and they seem to work properly, but maybe I have set my expectations too low.

I would expect to see more improvements from your SSD in testing the benchmark with more threads and with higher concurrency, as this is where the SSDs shine. So if you could repeat your tests with way more clients and more threads, I'd be curious about that comparison result.

Related Question