Re: MusicBrainz postgres performance issues

On 2015-03-15 13:07:25 +0100, Robert Kaye wrote:
>
> > On Mar 15, 2015, at 12:13 PM, Josh Krupka <jkrupka(at)gmail(dot)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