On Sat, Nov 14, 2009 at 8:47 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> Merlin Moncure wrote:
>> On Sat, Nov 14, 2009 at 6:17 AM, Heikki Linnakangas
>> <heikki.linnakangas@enterprisedb.com> wrote:
>>>> lots of ram doesn't help you if:
>>>> *) your database gets written to a lot and you have high performance
>>>> requirements
>>> When all the (hot) data is cached, all writes are sequential writes to
>>> the WAL, with the occasional flushing of the data pages at checkpoint.
>>> The sequential write bandwidth of SSDs and HDDs is roughly the same.
>>>
>>> I presume the fsync latency is a lot higher with HDDs, so if you're
>>> running a lot of small write transactions, and don't want to risk losing
>>> any recently committed transactions by setting synchronous_commit=off,
>>> the usual solution is to get a RAID controller with a battery-backed up
>>> cache. With a BBU cache, the fsync latency should be in the same
>>> ballpark as with SDDs.
>>
>> BBU raid controllers might only give better burst performance. If you
>> are writing data randomly all over the volume, the cache will overflow
>> and performance will degrade.
>
> We're discussing a scenario where all the data fits in RAM. That's what
> the large amount of RAM is for. The only thing that's being written to
> disk is the WAL, which is sequential, and the occasional flush of data
> pages from the buffer cache at checkpoints, which doesn't happen often
> and will be spread over a period of time.
We are basically in agreement, but regardless of the effectiveness of
your WAL implementation, raid controller, etc, if you have to write
data to what approximates random locations to a disk based volume in a
sustained manner, you must eventually degrade to whatever the drive
can handle plus whatever efficiencies checkpoint, o/s, can gain by
grouping writes together. Extra ram mainly helps only because it can
shave precious iops off the read side so you use them for writing.
merlin