Re: Solid State Drives with PG (was: in RAM DB)

On Fri, Mar 26, 2010 at 10:32 AM, Alan McKay <alan(dot)mckay(at)gmail(dot)com> wrote:
>> Have you considered using one of these:
>> http://www.acard.com/english/fb01-product.jsp?idno_no=270&prod_no=ANS-9010&type1_title=
>> Solid State Drive&type1_idno=13
>
> We did some research which suggested that performance may not be so
> great with them because the PG engine is not optimized to utilize
> those drives.
>
> So, I'll change the subject line to see if anyone has experience using these.

postgres works fine with flash SSD, understanding that:
*) postgres disk block is 8k and ssd erase block is much larger (newer
ssd controllers minimize this penalty though)
*) many flash drives cheat and buffer writes to delay full sync, for
performance reasons and to extend the life of the drive
*) if you have a relatively small database, the big 'win' off SSD,
fast random reads, is of little/no use because the o/s will buffer the
database in ram anywys.

The ideal candidate for flash SSD from database point of view is one
who is having I/O problems coming from OLTP type activity forcing the
disks to constantly seek all over the place to write and (especially)
read data. This happens when your database grows to the point when
its OPERATIONAL (that is, frequently used) data size exceeds ram to a
certain extent and o/s buffering of reads starts to become less
effective. This can crush database performance.

flash SSD 'fixes' this problem because relative to a disk head seek
the cost of random read i/o on flash is basically zero. however flash
has some problems writing, such that you get to choose between
volatility of data (irrespective of fsync) or lousy performance. So
flash isn't yet a general purpose database solution, and wont be until
the write performance problem is fixed in a way that doesn't
compromise on volatility. If/when that happens, and there isn't a
huge price premium to pay vs flash prices today, all my new servers
will be spec'd with flash :-).

merlin