Re: understanding postgres issues/bottlenecks

On Sat, 10 Jan 2009, Markus Wanner wrote:

> david@lang.hm wrote:
>> On Sat, 10 Jan 2009, Gregory Stark wrote:
>>> I think the idea is that with SSDs or a RAID with a battery backed
>>> cache you
>>> can leave fsync on and not have any significant performance hit since
>>> the seek
>>> times are very fast for SSD. They have limited bandwidth but bandwidth
>>> to the
>>> WAL is rarely an issue -- just latency.
>
> That's also my understanding.
>
>> with SSDs having extremely good read speeds, but poor (at least by
>> comparison) write speeds I wonder if any of the RAID controllers are
>> going to get a mode where they cache writes, but don't cache reads,
>> leaving all ofyour cache to handle writes.
>
> My understanding of SSDs so far is, that they are not that bad at
> writing *on average*, but to perform wear-leveling, they sometimes have
> to shuffle around multiple blocks at once. So there are pretty awful
> spikes for writing latency (IIRC more than 100ms has been measured on
> cheaper disks).

well, I have one of those cheap disks.

brand new out of the box, format the 32G drive, then copy large files to
it (~1G per file). this should do almost no wear-leveling, but it's write
performance is still poor and it has occasional 1 second pauses.

I for my initial tests I hooked it up to a USB->SATA adapter and the write
speed is showing about half of what I can get on a 1.5TB SATA drive hooked
to the same system.

the write speed is fairly comparable to what you can do with slow laptop
drives (even ignoring the pauses)

read speed is much better (and I think limited by the USB)

the key thing with any new storage technology (including RAID controller)
is that you need to do your own testing, treat the manufacturers specs as
ideal conditions or 'we guarentee that the product will never do better
than this' specs

Imation has a white paper on their site about solid state drive
performance that is interesting. among other things it shows that
high-speed SCSI drives are still a significant win in
random-write workloads


at this point, if I was specing out a new high-end system I would be
looking at and testing somthing like the following

SSD for read-mostly items (OS, possibly some indexes)
15K SCSI drives for heavy writing (WAL, indexes, temp tables, etc)
SATA drives for storage capacity (table contents)

David Lang