> It is not big database. There are maybe 20 clients connected to database. Doing updates or inserts during the work hours. It is a billing, provisioning and monitoring application that talks to this database. For the rest of the time there is small number of internal processes that generate some transactions but it is a cron like job.
>
> Before 2 years we decided(my customer and I) to use SSD for database storage. Initially everything was ok. During time performance of the SSD was degrading slowly and before a week one of the SSD drives started to take to much time to complete it's write operations. Today we replaced that drive with spare and performance is back to normal level.
>
> During that 2 years period we learned more about how SSD work so I start think that they are not very good for situations where you have multiple writes but instead only for databases that don't change very often.
> I see big difference when I delete some 200 000 records from one table on SSD and deleting same records from same database but on HDD.
>
> When Garbage collection starts it's cleanup job after deletion it takes longer on the SSD also delete process takes longer on SSD.
>
> There is 2 things that I can't count with my configuration. There is no TRIM support in the SSD(it is an old SSD) also because we use RAW device FB has to provide TRIM support. Another problem is that actually SSD is in RAID and so TRIM must be provided in RAID level which is missing in this case I think.
>
> Also as you know SSD has limit on the max number of writes in a single block. This limits it's live so here I'm asking for some tips how to increase the SSD life by reducing the number of writes made by FB server while processing transactions.
>
> I hope this gives enough information.

While I really enjoy a SSD in my development machine, I have no real
long-time experience with higher-prized enterprise class SSDs, but I
guess you are using a normal consumer SSD?

I'm not sure if I would use consumer SSDs as a host for Firebird
databases at all. For sure not in a RAID with no TRIM support etc.
Firebird is known to write very frequently e.g. on the transaction
inventory page (TIP). So, even small, but a lot of writes are going on,
which is a pretty serious scenario for the life-time and performance of
a SSD. And due to similar life-time patterns, SSDs in a RAID might fail
pretty much at the same time, although I don't have any real evidence
for that.

I would rather stay away from a SSD for Firebird databases and use fast
HDDs instead. Ideally RAID 10 with a decent RAID controller and not one
onboard of the motherboard. If you can't afford that and you need good
write performance, I would at least separate the OS, the Firebird
database and temp files onto separate disks.

Possibly you can invest into more RAM and use a RAM disk with the extra
RAM for hosting Firebird temp files.

A long-term test case with consumer SSDs hosting Firebird database would
be interesting though. As I said, I have no real experience with using
SSDs as server disks in production.


--
With regards,
Thomas Steinmaurer

* Upscene Productions - Database Tools for Developers
http://www.upscene.com/

* My Blog
http://blog.upscene.com/thomas/index.php

* Firebird Foundation Committee Member
http://www.firebirdsql.org/en/firebird-foundation/