Re: DB on mSATA SSD

Hi

On 04/23/15 14:50, Chris Mair wrote:
>> Dear Postgresql mailing list,
>>
>> we use Postgresql 8.4.x on our Linux firewall distribution.
>> Actually, we are moving from standard SATA disk to mSATA SSD solid
drive, and we noticed that the DB, using lots of indexes, is writing a lot.
>>
>> In some monthes, two test machine got SSD broken, and we are
>> studyinghow to reduce write impact for DB.
>>
>> Are there some suggestions with SSD drives?
>> Putting the DB into RAM and backing up periodically to disk is a
>> valid solutions?
>>
>> Or, is storing indexes on a ram drive possible?
>>
>> Thank you in advance for your appreciated interest!
>>
>> Best regards,
>> Francesco
>
> Hi,
>
> I don't think that today's SSDs - and certainly not the server-grade
> ones - > will break due to write intensive loads.

Exactly. If you want an SSD for a write-intensive database, you need a
reasonably good SSD. Sadly, the OP mentioned they're going for a mSATA
drive, and those suck when used for this purpose.

Theoretically it's possible to improve the lifetime by only allocating
part of the SSD and leaving some additional free space for the wear
leveling - the manufacturer already does that, but allocates a small
amount of space for the cheaper SSDs (say ~10% while the "server-grade"
SSDs may have ~25% of unallocated space for this purpose).

So by allocating only 75% for a filesystem, it may last longer.

> Have a look at the SMART data for you drives, there should be some
> metrics called "wear level count" or similar that gives some
> indications. I wouldn't be surprised if you find that your broken
> drives had failures not related to wear level.

My experience with mSATA drives is rather bad - the SMART data is rather
unreliable, and most of them doesn't even have power-loss protection
(which you need for a database, although a UPS may help a bit here). But
maybe that changed recently.

>
> If you're on Linux use smartctl.
>
> Also, as others have pointed out 8.4 is out of support, so consider
> upgrading.

+1 to this

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services