Re: understanding postgres issues/bottlenecks

On Sat, 10 Jan 2009, Gregory Stark wrote:

> david(at)lang(dot)hm writes:
>
>> On Sat, 10 Jan 2009, Markus Wanner wrote:
>>
>>> 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).
>
> That would be fascinating. And frightening. A lot of people have been
> recommending these for WAL disks and this would be make them actually *worse*
> than regular drives.
>
>> 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.
>
> This isn't similar to the way WAL behaves though. What you're testing is the
> behaviour when the bandwidth to the SSD is saturated. At that point some point
> in the stack, whether in the SSD, the USB hardware or driver, or OS buffer
> cache can start to queue up writes. The stalls you see could be the behaviour
> when that queue fills up and it needs to push back to higher layers.
>
> To simulate WAL you want to transfer smaller volumes of data, well below the
> bandwidth limit of the drive, fsync the data, then pause a bit repeat. Time
> each fsync and see whether the time they take is proportional to the amount of
> data written in the meantime or whether they randomly spike upwards.

if you have a specific benchmark for me to test I would be happy to do
this.

the test that I did is basicly the best-case for the SSD (more-or-less
sequential writes where the vendors claim that the drives match or
slightly outperform the traditional disks). for random writes the vendors
put SSDs at fewer IOPS than 5400 rpm drives, let along 15K rpm drives.

take a look at this paper
http://www.imation.com/PageFiles/83/Imation-SSD-Performance-White-Paper.pdf

this is not one of the low-performance drives, they include a sandisk
drive in the paper that shows significantly less performance (but the same
basic pattern) than the imation drives.

David Lang