Re: understanding postgres issues/bottlenecks

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.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!