Re: high user cpu, massive SELECTs, no io waiting problem

On 15/02/2011 18:19, Thomas Pöhler wrote:
> Hi list,
>
> first time for me here, hope you’re not dealing too severely with me
> regarding guidelines. Giving my best.
>
> We are running PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by
> 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, running
> for over a year now. The Server has 128 GB RAM and Four Intel® Xeon®
> X7550 with 64 logical cores.

So, 64 logical cores total.

> Operating System is “Linux database1 2.6.32-bpo.5-amd64 #1 SMP Mon Dec
> 13 17:10:39 UTC 2010 x86_64 GNU/Linux”.
>
> 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
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
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
may or may not be in your case) in which PostgreSQL starts behaving
badly even for SELECT queries if the number of simultaneous queries
exceeds the number of logical CPUs. To test this, I'd recommend setting
up a utility like pgpool-II (http://pgpool.projects.postgresql.org/) in
front of the database to try and limit the number of active connections
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
PHP will just refuse more connections than that instead of waiting for
them (but maybe your application can spin-wait for them, possibly while
also using usleep()).