From: Ivan Voras <ivoras@freebsd.org>
To: pgsql-performance@postgresql.org
Subject: Re: high user cpu, massive SELECTs, no io waiting problem
Date: Wed, 16 Feb 2011 02:00:22 +0100
Message-ID: <ijf7j7$cvu$1@dough.gmane.org> (raw)
In-Reply-To: <F1CD4AC5B4A5024AAB60E28A82A8450A03CEB0C7@winexch1.office.turtle-entertainment.de>
References: <F1CD4AC5B4A5024AAB60E28A82A8450A03CEB0C7@winexch1.office.turtle-entertainment.de>
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()).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ivoras@freebsd.org
Subject: Re: high user cpu, massive SELECTs, no io waiting problem
In-Reply-To: <ijf7j7$cvu$1@dough.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox