Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXHkZ-0003Vd-2d for pgsql-performance@arkaria.postgresql.org; Sun, 15 Mar 2015 23:15:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YXHkX-0002Zz-Uo for pgsql-performance@arkaria.postgresql.org; Sun, 15 Mar 2015 23:15:13 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YXHkV-0002Xp-BS for pgsql-performance@postgresql.org; Sun, 15 Mar 2015 23:15:11 +0000 Received: from mout.perfora.net ([74.208.4.196]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXHkR-0008KN-K9 for pgsql-performance@postgresql.org; Sun, 15 Mar 2015 23:15:09 +0000 Received: from [192.168.1.122] ([98.169.212.173]) by mrelay.perfora.net (mreueus002) with ESMTPSA (Nemesis) id 0MA7R9-1YibS70ntJ-00BHO3; Mon, 16 Mar 2015 00:13:46 +0100 Message-ID: <55061230.7060905@sqlexec.com> Date: Sun, 15 Mar 2015 19:13:52 -0400 From: "michael@sqlexec.com" User-Agent: Postbox 3.0.11 (Windows/20140602) MIME-Version: 1.0 To: Andres Freund CC: Scott Marlowe , ik@postgresql-consulting.com, Robert Kaye , Josh Krupka , PostgreSQL Performance Subject: Re: MusicBrainz postgres performance issues References: <9E71CCBE-4CDF-482E-878D-6C16027B6283@musicbrainz.org> <20150315135022.GC19792@alap3.anarazel.de> <20150315172048.GG9324@alap3.anarazel.de> <20150315174646.GH9324@alap3.anarazel.de> <20150315224756.GE29732@awork2.anarazel.de> In-Reply-To: <20150315224756.GE29732@awork2.anarazel.de> Content-Type: multipart/alternative; boundary="------------070900010205040805070409" X-Provags-ID: V03:K0:PFp9hnj1tJrWd9hJ2pDwlYVGr8Fpz+E3Hu6gwwBjCINIIC4kluF RJzc9z680WXVe2hFlMhX9RykTXRlaPuocDNlbHOCjQJp1le/UgOCFIyPkwV/9VBPRz9DSyQ F1CL+37osRa4JyNleCwDofzPx8Do4t1U/4BidNyE1BqlPigpF40iWtgEg2i4FOA+SH4Olus i7xSEVaBBWXlZXjYHfDvg== X-UI-Out-Filterresults: notjunk:1; X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------070900010205040805070409 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 > 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 > 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 > 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 > Sunday, March 15, 2015 1:42 PM > On Sun, Mar 15, 2015 at 8:20 PM, Andres Freund 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 > 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 > --------------070900010205040805070409 Content-Type: multipart/related; boundary="------------030000010101050406080809" --------------030000010101050406080809 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.

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

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. :)


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

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
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

--------------030000010101050406080809 Content-Type: image/jpeg; x-apple-mail-type=stationery; name="compose-unknown-contact.jpg" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="compose-unknown-contact.jpg" /9j/4AAQSkZJRgABAQEARwBHAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEC AQEBAQEBAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/2wBDAQEBAQEBAQICAgICAgIC AgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/wAAR CAAZABkDAREAAhEBAxEB/8QAGAAAAwEBAAAAAAAAAAAAAAAABgcICQr/xAA0EAABAwMCAgUK BwAAAAAAAAACAQMEBQYRABITIQcUMUF2CBUXIjI2N0JRtVRWkZOV0dL/xAAYAQEAAwEAAAAA AAAAAAAAAAADAAEEAv/EACQRAAICAAQGAwAAAAAAAAAAAAABAhEDMrHREyExM0FxgfDx/9oA DAMBAAIRAxEAPwDuEt+gW/ULet6oVC3rfqNQqFv0OfPn1GhUqfOmzZtKZlS5UqZMaNwzNwiJ VIl7eXLCaZIGwBl3TY8epPx2+jy2ZNPjvkwc9uhW8j7nCPhvOsQliYIeS7cvCpp8o50qwrC4 v3lsNSDbdmTEhvs2tahxpfV3WnmbbozJEw/gwdadbYExVRXKEKoSdvJcaOSqxE7/AAiX0gXx +a69/JSf9alIlste0VzaNpeFrcT9KKymotyiaZ0KRCnzacoE7Kjzn4gi2KqUh3jqDHDHv4mR UfruTWlMzlVUKIVNp9GguEJnAh0+IZjyAiisgyRDnu5azS8miKqjOTVkKqS/psG37fo1Fbab eg25b8eZPeFJBBJSjMG5HjMeyihnaauZwe4OGiju13GAcpOwBeN+U8/IkGbsiS8b7ryogmbz hbyc9REROfZhERO5ETShjPtvpGqTUyLErytS4siSwx5x2tRH4hPOI0DkjZtaJtFxuVEbIUUi yeNujlBUJGbJN6nM/Cyf2Hf60YgjvKA+NPSP4gT7axpcPtr51YWJnYn9dnAQWl722p4ot37y zqnlfp6FrqbwawG8/9k= --------------030000010101050406080809-- --------------070900010205040805070409--