Date: Sun, 15 Mar 2015 14:50:22 +0100
From: Andres Freund <andres@2ndquadrant.com>
To: Robert Kaye <rob@musicbrainz.org>
Cc: Josh Krupka <jkrupka@gmail.com>,
 PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: MusicBrainz postgres performance issues
Message-ID: <20150315135022.GC19792@alap3.anarazel.de>
References: <B008F2EE-45D7-4535-9163-56A8CDCA553C@musicbrainz.org>
 <CAB6McgXxZdD6yuYRLg4tgDdhqzXKLs63aV+S_nOfZ9arSXUAhA@mail.gmail.com>
 <9E71CCBE-4CDF-482E-878D-6C16027B6283@musicbrainz.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <9E71CCBE-4CDF-482E-878D-6C16027B6283@musicbrainz.org>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 2015-03-15 13:07:25 +0100, Robert Kaye wrote:
> 
> > On Mar 15, 2015, at 12:13 PM, Josh Krupka <jkrupka@gmail.com> wrote:
> > 
> > It sounds like you've hit the postgres basics, what about some of the linux check list items?
> > 
> > what does free -m show on your db server?
> 
>              total       used       free     shared    buffers     cached
> Mem:         48295      31673      16622          0          5      12670
> -/+ buffers/cache:      18997      29298
> Swap:        22852       2382      20470

Could you post /proc/meminfo instead? That gives a fair bit more
information.

Also:
* What hardware is this running on?
* Why do you need 500 connections (that are nearly all used) when you
  have a pgbouncer in front of the database? That's not going to be
  efficient.
* Do you have any data tracking the state connections are in?
  I.e. whether they're idle or not? The connections graph on you linked
  doesn't give that information?
* You're apparently not graphing CPU usage. How busy are the CPUs? How
  much time is spent in the kernel (i.e. system)?
* Consider installing perf (linux-utils-$something) and doing a
  systemwide profile.

3.2 isn't the greatest kernel around, efficiency wise. At some point you
might want to upgrade to something newer. I've seen remarkable
differences around this.

You really should upgrade postgres to a newer major version one of these
days. Especially 9.2. can give you a remarkable improvement in
performance with many connections in a read mostly workload.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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