Re: MusicBrainz postgres performance issues

On 15.3.2015 18:54, Ilya Kosmodemiansky wrote:
> On Sun, Mar 15, 2015 at 8:46 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> That imo doesn't really have anything to do with it. The primary
>> benefit of a BBU with writeback caching is accelerating
>> (near-)synchronous writes. Like the WAL.
>
> My point was, that having no proper raid controller (today bbu
> surely needed for the controller to be a proper one) + heavy writes
> of any kind, it is absolutely impossible to live with large
> shared_buffers and without io problems.

That is not really true, IMHO.

The benefit of the write cache is that it can absorb certain amount of
writes, equal to the size of the cache (nowadays usually 512MB or 1GB),
without forcing them to disks.

It however still has to flush the dirty data to the drives later, but
that side usually has much lower throughput - e.g. while you can easily
write several GB/s to the controller, the drives usually handle only
~1MB/s of random writes each (I assume rotational drives here).

But if you do a lot of random writes (which is likely the case for
write-heavy databases), you'll fill the write cache pretty soon and will
be bounded by the drives anyway.

The controller really can't help with sequential writes much, because
the drives already handle that quite well. And SSDs are a completely
different story of course.

That does not mean the write cache is useless - it can absorb short
bursts of random writes, fix the write hole with RAID5, the controller
may compute the parity computation etc. Whenever someone asks me whether
they should buy a RAID controller with write cache for their database
server, my answer is "absolutely yes" in 95.23% cases ...

... but really it's not something that magically changes the limits for
write-heavy databases - the main limit are still the drives.

regards
Tomas

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services