Re: MusicBrainz postgres performance issues - Mailing list pgsql-performance

From Andres Freund
Subject Re: MusicBrainz postgres performance issues
Date
Msg-id 20150315135022.GC19792@alap3.anarazel.de
Whole thread Raw
In response to Re: MusicBrainz postgres performance issues  (Robert Kaye <rob@musicbrainz.org>)
Responses Re: MusicBrainz postgres performance issues  (Scott Marlowe <scott.marlowe@gmail.com>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Ilya Kosmodemiansky
Date:
Subject: Re: MusicBrainz postgres performance issues
Next
From: Scott Marlowe
Date:
Subject: Re: MusicBrainz postgres performance issues