Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD

On Aug 10, 2010, at 11:28 AM, Greg Smith wrote:

> Brad Nicholson wrote:
>> What about putting indexes on them?  If the drive fails and drops 
>> writes on those, they could be rebuilt - assuming your system can 
>> function without the index(es) temporarily.
> 
> Dumping indexes on SSD is one of the better uses for them, presuming you 
> can survive what is likely to be an outage from a "can the site handle 
> full load?" perspective while they rebuild after a crash.  As I'm sure 
> Brad is painfully aware of already, index rebuilding in PostgreSQL can 
> take a while.  To spin my broken record here again, the main thing to 
> note when you consider that--relocate indexes onto SSD--is that the ones 
> you are most concerned about the performance of were likely to be 
> already sitting in RAM anyway, meaning the SSD speedup doesn't help 
> reads much.  So the giant performance boost just isn't there in that case.
> 

For an OLTP type system, yeah.  But for DW/OLAP and batch processing the gains are pretty big.  Those indexes get kicked out of RAM and then pulled back in a lot. I'm talking about a server with 72GB of RAM that can't keep enough indexes in memory to avoid a lot of random access. Putting the indexes on an SSD has lowered the random I/O load on the other drives a lot, letting them get through sequential scans a lot faster.

Estimated power failure, once every 18 months (mostly due to human error).  Rebuild indexes offline for 40 minutes every 18 months?  No problem.


> -- 
> Greg Smith  2ndQuadrant US  Baltimore, MD
> PostgreSQL Training, Services and Support
> greg@2ndQuadrant.com   www.2ndQuadrant.us
>