SSD Drive: OCZ Vertex EX 60 GB SATA II 2.5 Inch SSD
By all accounts it is the most popular/best reviewed single-level cell SSD drive available for 'Enterprise' applications. Their literature claims for the 60GB model:
- Read: Up to 260 MBs
- Write: Up to 200MBs
- Sustained Write Up to 100MBs
- Seek Time: Less Than .1ms
There is a really excellent benchmark and review here.
HDD Drive: Western Digital Blue Caviar 80GB SATA II 7200 RPM Drive
This is a typical drive we'd find in our systems. Literature claims:
- Sustained Read & Write up to 70MB/s
- Average Seek Time: 4.2ms
The test machine is a Dell SC1425, 2x3.6GHz Xeons, 16GB of RAM.
Created fileio test data thus (data on both HDD and SSD drives):
sysbench --test=fileio --max-time=60 --max-requests=1000000 --file-num=10 --file-extra-flags=direct --file-fsync-freq=0 --file-total-size=50G prepare
Created mysql test data thus (data on both HDD and SSD drives):
sysbench --test=oltp --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-db=test --mysql-table-engine=innodb prepare
Note: a 2.5" in a 3.5" adapter chassis (makes 2.5" drive fit in 3.5" slot) will not work with Dell 3.5" drive sled as connector alignment is off. Assume you will need a 2.5" back plane when using these SSDs, we'll probably use the R610.
Note: I tested zfs, xfs and ext4 too, as well as two SSDs in a software RAID0... but there is no room to post them here and for the most part a single ext3 SSD performed best. I'll do more tests, but it didn't seem to like software raid.
**FILE IO - RANDOM READ - 1 THREAD**
`sysbench --init-rng=on --test=fileio --max-time=60 --max-requests=1000000 --file-num=10 --file-extra-flags=direct --file-fsync-freq=0 --file-total-size=50G --num-threads=1 --file-test-mode=rndrd run`
SSD EXT3
Operations performed: 161750 Read, 0 Write, 0 Other = 161750 Total
Read 2.4681Gb Written 0b Total transferred 2.4681Gb (42.122Mb/sec)
2695.81 Requests/sec executed
Test execution summary:
total time: 60.0006s
total number of events: 161750
total time taken by event execution: 59.1342
per-request statistics:
min: 0.0003s
avg: 0.0004s
max: 0.0046s
approx. 95 percentile: 0.0004s
Threads fairness:
events (avg/stddev): 161750.0000/0.00
execution time (avg/stddev): 59.1342/0.00
HDD EXT3
Operations performed: 2874 Read, 0 Write, 0 Other = 2874 Total
Read 44.906Mb Written 0b Total transferred 44.906Mb (766.33Kb/sec)
47.90 Requests/sec executed
Test execution summary:
total time: 60.0055s
total number of events: 2874
total time taken by event execution: 59.9896
per-request statistics:
min: 0.0029s
avg: 0.0209s
max: 0.1042s
approx. 95 percentile: 0.0331s
Threads fairness:
events (avg/stddev): 2874.0000/0.00
execution time (avg/stddev): 59.9896/0.00
----------
**FILE IO - RANDOM READ - 64 THREADS**
`sysbench --init-rng=on --test=fileio --max-time=60 --max-requests=1000000 --file-num=10 --file-extra-flags=direct --file-fsync-freq=0 --file-total-size=50G --num-threads=64 --file-test-mode=rndrd run`
SSD EXT3
Operations performed: 158385 Read, 0 Write, 0 Other = 158385 Total
Read 2.4168Gb Written 0b Total transferred 2.4168Gb (41.228Mb/sec)
2638.61 Requests/sec executed
Test execution summary:
total time: 60.0260s
total number of events: 158385
total time taken by event execution: 3839.5569
per-request statistics:
min: 0.0003s
avg: 0.0242s
max: 6.4591s
approx. 95 percentile: 0.0004s
Threads fairness:
events (avg/stddev): 2474.7656/592.31
execution time (avg/stddev): 59.9931/0.01
HDD EXT3
Operations performed: 2945 Read, 0 Write, 0 Other = 2945 Total
Read 46.016Mb Written 0b Total transferred 46.016Mb (768.25Kb/sec)
48.02 Requests/sec executed
Test execution summary:
total time: 61.3345s
total number of events: 2945
total time taken by event execution: 3885.4618
per-request statistics:
min: 0.0040s
avg: 1.3193s
max: 9.9964s
approx. 95 percentile: 6.6392s
Threads fairness:
events (avg/stddev): 46.0156/10.71
execution time (avg/stddev): 60.7103/0.39
----------
**FILE IO - RANDOM WRITE - 1 THREAD**
`sysbench --init-rng=on --test=fileio --max-time=60 --max-requests=1000000 --file-num=10 --file-extra-flags=direct --file-fsync-freq=0 --file-total-size=50G --num-threads=1 --file-test-mode=rndwr run`
SSD EXT3
Operations performed: 0 Read, 169804 Write, 0 Other = 169804 Total
Read 0b Written 2.591Gb Total transferred 2.591Gb (44.219Mb/sec)
2830.04 Requests/sec executed
Test execution summary:
total time: 60.0005s
total number of events: 169804
total time taken by event execution: 59.0867
per-request statistics:
min: 0.0003s
avg: 0.0003s
max: 0.0031s
approx. 95 percentile: 0.0007s
Threads fairness:
events (avg/stddev): 169804.0000/0.00
execution time (avg/stddev): 59.0867/0.00
HDD EXT3
Operations performed: 0 Read, 3796 Write, 0 Other = 3796 Total
Read 0b Written 59.312Mb Total transferred 59.312Mb (1012.2Kb/sec)
63.26 Requests/sec executed
Test execution summary:
total time: 60.0017s
total number of events: 3796
total time taken by event execution: 59.9806
per-request statistics:
min: 0.0003s
avg: 0.0158s
max: 1.0514s
approx. 95 percentile: 0.0455s
Threads fairness:
events (avg/stddev): 3796.0000/0.00
execution time (avg/stddev): 59.9806/0.00
**FILE IO - RANDOM WRITE - 64 THREADS**
`sysbench --init-rng=on --test=fileio --max-time=60 --max-requests=1000000 --file-num=10 --file-extra-flags=direct --file-fsync-freq=0 --file-total-size=50G --num-threads=64 --file-test-mode=rndwr run`
SSD EXT3
Operations performed: 0 Read, 179247 Write, 0 Other = 179247 Total
Read 0b Written 2.7351Gb Total transferred 2.7351Gb (46.659Mb/sec)
2986.14 Requests/sec executed
Test execution summary:
total time: 60.0262s
total number of events: 179247
total time taken by event execution: 3839.6346
per-request statistics:
min: 0.0005s
avg: 0.0214s
max: 0.1395s
approx. 95 percentile: 0.0610s
Threads fairness:
events (avg/stddev): 2800.7344/44.56
execution time (avg/stddev): 59.9943/0.01
HDD EXT3
Operations performed: 0 Read, 4358 Write, 0 Other = 4358 Total
Read 0b Written 68.094Mb Total transferred 68.094Mb (1.1182Mb/sec)
71.56 Requests/sec executed
Test execution summary:
total time: 60.8978s
total number of events: 4358
total time taken by event execution: 3870.8012
per-request statistics:
min: 0.0006s
avg: 0.8882s
max: 4.9283s
approx. 95 percentile: 3.4779s
Threads fairness:
events (avg/stddev): 68.0938/9.09
execution time (avg/stddev): 60.4813/0.26
----------
**FILE IO - COMBINE RANDOM READ/WRITE - 1 THREAD**
`sysbench --init-rng=on --test=fileio --max-time=60 --max-requests=1000000 --file-num=10 --file-extra-flags=direct --file-fsync-freq=0 --file-total-size=50G --num-threads=1 --file-test-mode=rndrw run`
SSD EXT3
Operations performed: 67246 Read, 44830 Write, 0 Other = 112076 Total
Read 1.0261Gb Written 700.47Mb Total transferred 1.7101Gb (29.186Mb/sec)
1867.92 Requests/sec executed
Test execution summary:
total time: 60.0004s
total number of events: 112076
total time taken by event execution: 59.3859
per-request statistics:
min: 0.0003s
avg: 0.0005s
max: 0.0411s
approx. 95 percentile: 0.0013s
Threads fairness:
events (avg/stddev): 112076.0000/0.00
execution time (avg/stddev): 59.3859/0.00
HDD EXT3
Operations performed: 2195 Read, 1463 Write, 0 Other = 3658 Total
Read 34.297Mb Written 22.859Mb Total transferred 57.156Mb (975.12Kb/sec)
60.94 Requests/sec executed
Test execution summary:
total time: 60.0215s
total number of events: 3658
total time taken by event execution: 60.0010
per-request statistics:
min: 0.0003s
avg: 0.0164s
max: 0.3715s
approx. 95 percentile: 0.0429s
Threads fairness:
events (avg/stddev): 3658.0000/0.00
execution time (avg/stddev): 60.0010/0.00
----------
**FILE IO - COMBINE RANDOM READ/WRITE - 64 THREADS**
`sysbench --init-rng=on --test=fileio --max-time=60 --max-requests=1000000 --file-num=10 --file-extra-flags=direct --file-fsync-freq=0 --file-total-size=50G --num-threads=64 --file-test-mode=rndrw run`
RESULTS
SSD EXT3
Operations performed: 44558 Read, 29710 Write, 0 Other = 74268 Total
Read 696.22Mb Written 464.22Mb Total transferred 1.1332Gb (19.321Mb/sec)
1236.52 Requests/sec executed
Test execution summary:
total time: 60.0623s
total number of events: 74268
total time taken by event execution: 3841.9213
per-request statistics:
min: 0.0003s
avg: 0.0517s
max: 0.4341s
approx. 95 percentile: 0.1348s
Threads fairness:
events (avg/stddev): 1160.4375/50.60
execution time (avg/stddev): 60.0300/0.02
HDD EXT3
Operations performed: 2632 Read, 1789 Write, 0 Other = 4421 Total
Read 41.125Mb Written 27.953Mb Total transferred 69.078Mb (1.1385Mb/sec)
72.87 Requests/sec executed
Test execution summary:
total time: 60.6723s
total number of events: 4421
total time taken by event execution: 3863.3418
per-request statistics:
min: 0.0005s
avg: 0.8739s
max: 4.5743s
approx. 95 percentile: 2.4240s
Threads fairness:
events (avg/stddev): 69.0781/6.34
execution time (avg/stddev): 60.3647/0.20
----------
**MYSQL OLTP - READ ONLY - 1 THREAD**
`sysbench --test=oltp --oltp-table-size=1000000 --max-requests=100000 --db-driver=mysql --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --mysql-socket=/tmp/mysql.sock --mysql-db=test --num-threads=1 --oltp-read-only run`
SSD EXT3
OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (492.93 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (6900.95 per sec.)
other operations: 200000 (985.85 per sec.)
Test execution summary:
total time: 202.8706s
total number of events: 100000
total time taken by event execution: 201.7883
per-request statistics:
min: 0.0018s
avg: 0.0020s
max: 0.0984s
approx. 95 percentile: 0.0024s
Threads fairness:
events (avg/stddev): 100000.0000/0.00
execution time (avg/stddev): 201.7883/0.00
HDD EXT3
OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (256.34 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (3588.74 per sec.)
other operations: 200000 (512.68 per sec.)
Test execution summary:
total time: 390.1088s
total number of events: 100000
total time taken by event execution: 389.0534
per-request statistics:
min: 0.0019s
avg: 0.0039s
max: 0.1454s
approx. 95 percentile: 0.0047s
Threads fairness:
events (avg/stddev): 100000.0000/0.00
execution time (avg/stddev): 389.0534/0.00
----------
**MYSQL OLTP - READ ONLY - 64 THREADS**
`sysbench --test=oltp --oltp-table-size=1000000 --max-requests=100000 --db-driver=mysql --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --mysql-socket=/tmp/mysql.sock --mysql-db=test --num-threads=16 --oltp-read-only run`
SSD EXT3
OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (1055.34 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (14774.81 per sec.)
other operations: 200000 (2110.69 per sec.)
Test execution summary:
total time: 94.7559s
total number of events: 100000
total time taken by event execution: 1512.7384
per-request statistics:
min: 0.0018s
avg: 0.0151s
max: 2.1117s
approx. 95 percentile: 0.0165s
Threads fairness:
events (avg/stddev): 6250.0000/590.00
execution time (avg/stddev): 94.5461/0.06
HDD EXT3
OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (497.50 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (6964.94 per sec.)
other operations: 200000 (994.99 per sec.)
Test execution summary:
total time: 201.0067s
total number of events: 100000
total time taken by event execution: 3183.9863
per-request statistics:
min: 0.0020s
avg: 0.0318s
max: 27.8799s
approx. 95 percentile: 0.0060s
Threads fairness:
events (avg/stddev): 6250.0000/1301.96
execution time (avg/stddev): 198.9991/2.79
----------
**MYSQL OLTP - READ/WRITE - 1 THREAD**
`sysbench --test=oltp --oltp-table-size=1000000 --max-requests=100000 --db-driver=mysql --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --mysql-socket=/tmp/mysql.sock --mysql-db=test --num-threads=1 run`
SSD EXT3
OLTP test statistics:
queries performed:
read: 1400000
write: 500000
other: 200000
total: 2100000
transactions: 100000 (218.72 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900000 (4155.60 per sec.)
other operations: 200000 (437.43 per sec.)
Test execution summary:
total time: 457.2148s
total number of events: 100000
total time taken by event execution: 455.9071
per-request statistics:
min: 0.0023s
avg: 0.0046s
max: 0.0779s
approx. 95 percentile: 0.0059s
Threads fairness:
events (avg/stddev): 100000.0000/0.00
execution time (avg/stddev): 455.9071/0.00
HDD EXT3
OLTP test statistics:
queries performed:
read: 1400000
write: 500000
other: 200000
total: 2100000
transactions: 100000 (204.85 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900000 (3892.23 per sec.)
other operations: 200000 (409.71 per sec.)
Test execution summary:
total time: 488.1525s
total number of events: 100000
total time taken by event execution: 486.8584
per-request statistics:
min: 0.0026s
avg: 0.0049s
max: 0.1927s
approx. 95 percentile: 0.0060s
Threads fairness:
events (avg/stddev): 100000.0000/0.00
execution time (avg/stddev): 486.8584/0.00
----------
**MYSQL OLTP - READ/WRITE - 64 THREADS**
`sysbench --test=oltp --oltp-table-size=1000000 --max-requests=100000 --db-driver=mysql --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --mysql-socket=/tmp/mysql.sock --mysql-db=test --num-threads=64 run`
SSD EXT3
OLTP test statistics:
queries performed:
read: 1400000
write: 500000
other: 200000
total: 2100000
transactions: 100000 (403.34 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900000 (7663.38 per sec.)
other operations: 200000 (806.67 per sec.)
Test execution summary:
total time: 247.9324s
total number of events: 100000
total time taken by event execution: 3964.1628
per-request statistics:
min: 0.0027s
avg: 0.0396s
max: 0.7854s
approx. 95 percentile: 0.1446s
Threads fairness:
events (avg/stddev): 6250.0000/147.62
execution time (avg/stddev): 247.7602/0.05
HDD EXT3
OLTP test statistics:
queries performed:
read: 1400000
write: 500000
other: 200000
total: 2100000
transactions: 100000 (392.70 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900000 (7461.35 per sec.)
other operations: 200000 (785.41 per sec.)
Test execution summary:
total time: 254.6456s
total number of events: 100000
total time taken by event execution: 4069.8699
per-request statistics:
min: 0.0027s
avg: 0.0407s
max: 0.6835s
approx. 95 percentile: 0.1478s
Threads fairness:
events (avg/stddev): 6250.0000/139.23
execution time (avg/stddev): 254.3669/0.17
Potential Issues
I have a couple points of issue with using SSDs for production databases at the present time
- The majority of database transactions on a the majority of websites are reads not writes. As Dave Markle said, you maximize this performance with RAM first.
- SSDs are new to the mainstream and enterprise markets and no admin worth his salt is going to move a production database that currently requires 15K RPM U320 disks in RAID5 communicating via fibrechannel to an unproven technology.
- The cost of the research and testing of moving to this new technology, vetting it in their environment, updating the operating procedures, and so forth is a larger up front cost, both in terms of time and money, than most shops have to spare.
Proposed Benefits
That said, there are a number of items, at least on paper, in favor of SSDs in the future:
- Lower power consumption compared to a HDD
- Much lower heat generation
- Higher performance per watt compared to a HDD
- Much higher throughput
- Much lower latency
- Most current generation SSDs have on the order of millions of cycles of write endurance, so write endurance is not an issue as it once was. See a somewhat dated article here
So for a given performance benchmark, when you factor total cost of ownership including direct power and indirect cooling costs, the SSDs could become very attractive. Additionally, depending on the particulars of your environment, the reduction in the number of required devices for a given level of performance could also result in a reduction of staffing requirements, reducing labor costs.
Cost and Performance
You've added that you have a cost constraint under $50K USD and you really want to keep it under $10K. You've also stated in a comment that you can get some "cheap" SSDs, eluding that the SSDs will be cheaper than the DBAs or consultants. This may be true depending on the number of hours you would need a DBA and whether it is a reoccuring cost or not. I can't do the cost analysis for you.
However, one thing you must be very careful of is the kind of SSD you get. Not all SSDs are created equal. By and large the "cheap" SSDs you see for sale in the $200-400 dollar (2008/11/20) are intended for low power/heat environments like laptops. These drives actually have lower performance levels compared to a 10K or 15K RPM HDD - especially for writes. The enterprise level drives that have the killer performance you speak of - like the Mtron Pro series - are quite expensive. Currently they are around:
- 400 USD for 16GB
- 900 USD for 32GB
- 1400 USD for 64GB
- 3200 USD for 128GB
Depending on your space, performance, and redundancy requirements, you could easily blow your budget.
For example, if your requirements necessitated a total of 128GB of available storage then RAID 0+1/10 or RAID 5 with 1 hotspare would be ~$5600
If you needed a TB of available storage however, then RAID 0+1/10 would be ~$51K and RAID 5 with 2 hotspares would be ~$32K.
Big Picture
That said, the installation, configuration, and maintenance of a large production database requires a highly skilled individual. The data within the DB and the services provided from that data are of extremely high value to companies with this level of performance requirements. Additionally, there are many things that just cannot be solved by throwing hardware at the problem. An improperly configured DBMS, a poor database schema or indexing strategy can /wreck/ a DB's performance. Just look at the issues Stackoverflow experienced in their migration to SQL Server 2008 here and here. The fact of the matter is, a database is a strenuous application on not only disk but RAM and CPU as well. Balancing the multi-variate performance issue along with data integrity, security, redundancy, and backup is a tricky bit.
In summary, while I do think any and all improvements to both the hardware and software technology are welcomed by the community, large scale database administration - like software development - is a hard problem and will continue to require skilled workers. A given improvement may not reap the labor reduction costs you or a company might hope for.
A good jumping point for some research might be Brent Ozar's website/blog here. You might recognize his name - he's the one who has assisted the stackoverflow crew with their MS SQL Server 2008 performance issues. His blog and resources he links to offer quite a bit of breadth and depth.
Update
Stackoverflow themselves are going the consumer SSD-based route for their storage. Read about it here: http://blog.serverfault.com/post/our-storage-decision/
References
Best Answer
You may be referring to my post here:
Third-party SSD solutions in ProLiant Gen8 servers
This all depends on which Smart Array RAID controller is in the system, and what drive cage option you have (4 x 2.5" or 2 x 3.5").
Do you have the specific model of the server you're planning to purchase?
I can say that I'm happy with most SAS SSDs on Gen8 controllers as long as you use quality carriers.