> are you sure you can live with data loss in cause of system- powersupply- or
> emergency-shutdown-errors?
> our database is about 42gb. that is the cause why we`re backing up just once
> a day. it has it`s own raid1-volume, and for performance reasons i am
> planning to add a raid5 with 4 fast ssd`s.
>
> i think that could be a better option.

We at BroadView have been investigating/researching SSDs for several years, like most databases our application see a huge portion of read/SELECT operations relative to the number of write operations, so an SSD based solution would provide a significant performance boost. We have purchased many SSD (even a Fusion IO PCI-E SSD solution) and here are a couple of things to be aware of:

- make sure that the RAID card supports the TRIM command, otherwise you could have significant performance issues which would grow over time. As I understand it, TRIM is not command which all RAID controllers pass along to the actual devices.

- the latest 25nm MLC SSDs have cells which have a "life" expectations of 300 to 5000 write operations, due to cell wear out (read ops have no wear cost). Some SSD manufacturers use "over-provisioning" and "wear-levelling" to provide a safety buffer, but a high-write IO environment can be cause for concerns. BTW, SLC SSDs have a much higher wear levels.

- Firebird databases generate at least (*1) 1 write operation per database transaction (even SELECT statements) since the current transaction number in the database header page must be written to disk. So, depending on the amount of transactions activity of the database, you can have a problem.

- Also, the Firebird garbage collection process will also generate write IO operations, so you such think of each row UPDATE/DELETE operation will generate at least 2 disk IO write operations. 1 in the current transaction to write the latest version of the database row (*2). 1 later after the update transaction has been committed and the old record version is cleaned up from the database page (*3). This also needs to be factored into any math were you try to figure out the amount of write IO is taking place to an SSD.

(*1) I am not sure if there is another update to a TIP database page

(*2) separate updates to index pages will also be required for each index which needs to be maintained

(*3) Some of the old record versions can be cleaned in a single disk IO, but that depends on the engine version and the garbage collection mode/settings.


For ourselves, we found that the risk of SSD failure was much too dangerous to much to recommend a RAID SSD solution for our client installs. Especially, when we have found that 4-6 15K RPM SAS 6Gbps HDDs RAID5 disk arrays can get between 400 to 600 MB/s in throughput in the key 4KB to 16KB block sizes (key due to the 4 to 16KB Firebird page sizes).


We have though, determined that there are some uses for which an SSD would be entirely appropriate and would endorse if a client wanted to adopt. Those uses are:

- as a volume for temporary sort files (SELECT with ORDER)

- as a Linux volume to allow for the definition of a large amount of virtual memory (I hope I have that terminology correct)

- as an extended read cache for disk operations. Adaptec and LSI controllers have options which provide for this. The latest Intel Sandy Bridge chipset also includes native support for an SSD read cache. These options would provide the benefit of optimizing disk reads, while any battery backed up cache can be dedicated for caching write IO operations.


Sean