DB2 Express-C 10.1 LUW 64-bit operating on a Dell Optiplex 790 Mini Desktop, i5-2400 Quad Core with 8gb Memory in Windows 7 Pro SP1 64-Bit.
2 x HDD with Automatic Storage Groups Configured with index, data and result tablespaces, each split across both discs. ie: each tablespace has two containers with one on each disc. I have just the one PC, so there is no test or development system.
Database is a data mining warehouse environment, so majority of queries/inserts/updates/deletes result in many thousands of records per txn. Over 300 tables, some in excess of 50m records per table.
I am considering trying to upgrade my system to utilize SSD storage and was hoping to get some first hand experience from any folk that have upgraded systems such as this in the past. I guess the questions are two fold.
Firstly as I only have 4 available SATA ports, 3 are presently in use being 1 x DVD and 2 x HDD, what would the recommended SSD options be ? The overall size of the database is uncompressed approx. 40gb. Mostly split evenly across the results and indexes tablespaces. I am torn between replacing 1 of the HDDs and purchasing 2 x SSDs or simply going with 1 and keeping the 2 x HDD. This raises the point about whether to store the entire database on 1 SSD (or only move say indexes, logs and tempspace to any new SSD) and what that would mean in terms of performance benefits. As the existing tablespaces are each stored in two containers, one on each disc, there is obvious queuing contention when txns try to read from index/result/rawdata tablespaces, and if the entire database is stored on 1 single SSD, what does that mean as far as performance is concerned ?
Secondly, does anyone have any recommendations as to the actual SSD hardware itself. Make, model, size etc. I cannot afford SLC. So in terms of reliability, durability, write cycles etc can anyone give me a pointer to which is best suited for database environments such as mine ? Market is saturated with these now and I am after the most reliable unit rather than emphasis on performance but given that I have a fairly hefty daily write requirement, I am more concerned with write cycles than anything else. Size wise I am assuming (perhaps incorrectly) that 256gb would be better given that I could conceivably assign more space for cycle reuse.
I am not terribly interested in storing OS or DB2 itself on SSD, and would be assigning the entire SSD to just database storage. I have read much on 'HOT' data etc but seeing as I am for the most part somewhat limited in my knowledge of DB2 itself, and given the smallish size of my overall system environment, I think this question of hot/warm/cold data etc is somewhat beyond my capabilities at present.
Any and all recommendations would very gratefully received.