we decided to upgrade DB server, now running on FreeBSD 8.0-STABLE, Intel(R) Xeon(R) CPU E5335 @ 2.00GHz (1995.01-MHz K8-class CPU), 8GB RAM, db running on SAS hdd with slave on another box, there are more than 2000 queries per second to database, DB size is 25 GB.
I am completely new to SSD technology, I was reading a lot of information, but I am still not sure, which SSD hard-drive would be best to buy – 32 GB should be enough, price limit for SSD is up to 1000 USD, for controller same.
I am considering to buy Intel X25-E SSD, but I saw some good results with Patriot or OCZ Vertex…
According http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html there is really big performance step over using "standard" hard drives.
Thanks for any help.
Best Answer
The Intel drives are known to have write caching that violates PostgreSQL (and other database) expectations; see Reliability section of the documentation for details on what those are. They don't flush their write cache out when the OS executes a normal fsync call, which is normal for SATA drives. And this will result in database corruption if the server crashes due to a power interruption. The only real option is to therefore turn the write cache off completely, and if you do that you'll discover the performance is terrible; the expected longevity of the drive plummets too. Some have reported even then it doesn't work right. See SSD, XFS, LVM, fsync, write cache, barrier and lost transactions for more information.
The recently released OCZ Vertex 2 Pro drives using a Sandforce controller and having an Ultracap battery backup are the first relatively inexpensive SSD model to reach the market worth considering here. That's passed all of the reliability tests those of us on the PostgreSQL Performance mailing list have come up for stressing this type of device, the Intel drives failed miserably. See Testing Sandforce SSD for benchmarks and reliability test information. There are some much more expensive "enterprise" SSDs that work fine as well, but those are all multi-thousand dollar devices. Note that you must be careful to get the Vertex 2 Pro, the cheaper Vertex models will not have the required Ultracap that makes this all work.