From: michael@sqlexec.com <michael@sqlexec.com>
To: Andres Freund <andres@2ndquadrant.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: ik@postgresql-consulting.com, Robert Kaye <rob@musicbrainz.org>
Cc: Josh Krupka <jkrupka@gmail.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: MusicBrainz postgres performance issues
Date: Sun, 15 Mar 2015 19:13:52 -0400
Message-ID: <55061230.7060905@sqlexec.com> (raw)
In-Reply-To: <20150315224756.GE29732@awork2.anarazel.de>
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>
<CAOR=d=2rC8dYr=tY7JQTNK+eszx2J1pRHuqYFHPbqxVDXiN1sQ@mail.gmail.com>
<20150315224756.GE29732@awork2.anarazel.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
How many CPUs in play here on the PG Cluster Server,
cat /proc/cpuinfo | grep processor | wc -l
I see you got pg_stat_statements enabled, what are the SQL you
experience during this heavy load time? And does explain on them show a
lot of sorting activity that requires more work_mem.
Please enable log_checkpoints, so we can see if your checkpoint_segments
is adequate.
> Andres Freund <mailto:andres@2ndquadrant.com>
> Sunday, March 15, 2015 6:47 PM
>
> IMNSHO that's tackling things from the wrong end. If 12GB of shared
> buffers drive your 48GB dedicated OLTP postgres server into swapping out
> actively used pages, the problem isn't the 12GB of shared buffers, but
> that you require so much memory for other things. That needs to be
> fixed.
>
> But! We haven't even established that swapping is an actual problem
> here. The ~2GB of swapped out memory could just as well be the java raid
> controller management monstrosity or something similar. Those pages
> won't ever be used and thus can better be used to buffer IO.
>
> You can check what's actually swapped out using:
> grep ^VmSwap /proc/[0-9]*/status|grep -v '0 kB'
>
> For swapping to be actually harmful you need to have pages that are
> regularly swapped in. vmstat will tell.
>
> In a concurrent OLTP workload (~450 established connections do suggest
> that) with a fair amount of data keeping the hot data set in
> shared_buffers can significantly reduce problems. Constantly searching
> for victim buffers isn't a nice thing, and that will happen if your most
> frequently used data doesn't fit into s_b. On the other hand, if your
> data set is so large that even the hottest part doesn't fit into memory
> (perhaps because there's no hottest part as there's no locality at all),
> a smaller shared buffers can make things more efficient, because the
> search for replacement buffers is cheaper with a smaller shared buffers
> setting.
>
> Greetings,
>
> Andres Freund
>
> Scott Marlowe <mailto:scott.marlowe@gmail.com>
> Sunday, March 15, 2015 2:25 PM
>
> Here's the problem with a large shared_buffers on a machine that's
> getting pushed into swap. It starts to swap BUFFERs. Once buffers
> start getting swapped you're not just losing performance, that huge
> shared_buffers is now working against you because what you THINK are
> buffers in RAM to make things faster are in fact blocks on a hard
> drive being swapped in and out during reads. It's the exact opposite
> of fast. :)
>
>
> Andres Freund <mailto:andres@2ndquadrant.com>
> Sunday, March 15, 2015 1:46 PM
>
> 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. But, besides influencing the default for
> wal_buffers, a larger shared_buffers doesn't change the amount of
> synchronous writes.
>
> Greetings,
>
> Andres Freund
>
> Ilya Kosmodemiansky <mailto:ilya.kosmodemiansky@postgresql-consulting.com>
> Sunday, March 15, 2015 1:42 PM
> On Sun, Mar 15, 2015 at 8:20 PM, Andres Freund<andres@2ndquadrant.com> wrote:
>> On 2015-03-15 11:09:34 -0600, Scott Marlowe wrote:
>>> shared_mem of 12G is almost always too large. I'd drop it down to ~1G or so.
>> I think that's a outdated wisdom, i.e. not generally true.
>
> Quite agreed. With note, that proper configured controller with BBU is needed.
>
>
>> A new enough kernel, a sane filesystem
>> (i.e. not ext3) and sane checkpoint configuration takes care of most of
>> the other disadvantages.
>
> Most likely. And better to be sure that filesystem mounted without barrier.
>
> And I agree with Scott - 64MB work mem AND max_connections = 500 is a
> recipe for disaster. The problem could be in session mode of
> pgbouncer. If you can work with transaction mode - do it.
>
>
> Best regards,
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> ik@postgresql-consulting.com
>
>
> Andres Freund <mailto:andres@2ndquadrant.com>
> Sunday, March 15, 2015 1:20 PM
>
> I think that's a outdated wisdom, i.e. not generally true. I've now seen
> a significant number of systems where a larger shared_buffers can help
> quite massively. The primary case where it can, in my experience, go
> bad are write mostly database where every buffer acquiration has to
> write out dirty data while holding locks. Especially during relation
> extension that's bad. A new enough kernel, a sane filesystem
> (i.e. not ext3) and sane checkpoint configuration takes care of most of
> the other disadvantages.
>
> Greetings,
>
> Andres Freund
>
Attachments:compose-unknown-contact.jpg (image/jpeg, 770B) downloadview image
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: michael@sqlexec.com, andres@2ndquadrant.com, scott.marlowe@gmail.com, rob@musicbrainz.org, jkrupka@gmail.com
Subject: Re: MusicBrainz postgres performance issues
In-Reply-To: <55061230.7060905@sqlexec.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