To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: high user cpu, massive SELECTs, no io waiting problem
Date: Wed, 16 Feb 2011 02:00:22 +0100
Lines: 56
Message-ID: <ijf7j7$cvu$1@dough.gmane.org>
References: 
 <F1CD4AC5B4A5024AAB60E28A82A8450A03CEB0C7@winexch1.office.turtle-entertainment.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US;
	rv:1.9.2.12) Gecko/20101102 Thunderbird/3.1.6
In-Reply-To: 
 <F1CD4AC5B4A5024AAB60E28A82A8450A03CEB0C7@winexch1.office.turtle-entertainment.de>

On 15/02/2011 18:19, Thomas P=C3=B6hler wrote:
> Hi list,
>
> first time for me here, hope you=E2=80=99re not dealing too severely wi=
th me
> regarding guidelines. Giving my best.
>
> We are running PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled b=
y
> GCC gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit on a Supermicro SuperServer
> 8026B-6RF.
>
> This version is downloaded from postgresql.org and selfcompiled, runnin=
g
> for over a year now. The Server has 128 GB RAM and Four Intel=C2=AE Xeo=
n=C2=AE
> X7550 with 64 logical cores.

So, 64 logical cores total.

> Operating System is =E2=80=9CLinux database1 2.6.32-bpo.5-amd64 #1 SMP =
Mon Dec
> 13 17:10:39 UTC 2010 x86_64 GNU/Linux=E2=80=9D.
>
> The System boots through iscsi over a Qlogic QLE4062C HBA. Pgdata and
> xlog is logged in over iscsi HBA too. We tried en and disabling jumbo
> frames. Makes no difference.

Are you using 10 Gbit/s Ethernet for iSCSI? Regular 1 Gbit/s Ethernet=20
might be too slow for you.

> Since a few weeks we have really strange peaks on this system. User CPU=

> is increasing up to 100% and we have lots of SELECTs running.

> See ganglia: http://dl.dropbox.com/u/183323/CPUloadprobsdb1.jpg
>
> Has someone made similar experiences? Perhaps there is some issue
> between Postgres 8.4.4 and kernel 2.6.32?

 From your graph it looks like the number of active processes (I'm=20
assuming they are PostgreSQL processes) is going out of control.

There is an old problem (which I've encountered so I'm replying but it=20
may or may not be in your case) in which PostgreSQL starts behaving=20
badly even for SELECT queries if the number of simultaneous queries=20
exceeds the number of logical CPUs. To test this, I'd recommend setting=20
up a utility like pgpool-II (http://pgpool.projects.postgresql.org/) in=20
front of the database to try and limit the number of active connections=20
to nearly 64 (maybe you can have good results with 80 or 100).

You might also experiment with pgsql.max_links setting of PHP but IIRC=20
PHP will just refuse more connections than that instead of waiting for=20
them (but maybe your application can spin-wait for them, possibly while=20
also using usleep()).