From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: MusicBrainz postgres performance issues
Date: Mon, 16 Mar 2015 00:29:11 +0100
Message-ID: <550615C7.6040200@2ndquadrant.com> (raw)
In-Reply-To: <CAG95seV8cGYuE-EpW4g8UdSEWFUXBe3eUXcf8QMUgNc1GB1uoQ@mail.gmail.com>
References: <B008F2EE-45D7-4535-9163-56A8CDCA553C@musicbrainz.org>
<CAB6McgXxZdD6yuYRLg4tgDdhqzXKLs63aV+S_nOfZ9arSXUAhA@mail.gmail.com>
<9E71CCBE-4CDF-482E-878D-6C16027B6283@musicbrainz.org>
<20150315135022.GC19792@alap3.anarazel.de>
<CAOR=d=0YwcnVi3kGD21W7jYjE7DcWjp0v0GjncH-c2-oJEP5Rw@mail.gmail.com>
<CAOR=d=1Axcbj2VcUO=5tM1nv6RjuA0D+av5+Mt7RTztgTxaMnA@mail.gmail.com>
<20150315172048.GG9324@alap3.anarazel.de>
<CAG95seXv4EKGhDV04rkk_ARgvz8OQUJy3Xbx5Rw16Wxm3+1j5Q@mail.gmail.com>
<20150315174646.GH9324@alap3.anarazel.de>
<CAG95seV8cGYuE-EpW4g8UdSEWFUXBe3eUXcf8QMUgNc1GB1uoQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 15.3.2015 18:54, Ilya Kosmodemiansky wrote:
> On Sun, Mar 15, 2015 at 8:46 PM, Andres Freund <andres@2ndquadrant.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com
Subject: Re: MusicBrainz postgres performance issues
In-Reply-To: <550615C7.6040200@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox