High context switches occurring

Hi,

One of our PG server is experiencing extreme slowness and there are
hundreds of SELECTS building up. I am not sure if heavy context
switching is the cause of this or something else is causing it.

Is this pretty much the final word on this issue?

http://archives.postgresql.org/pgsql-performance/2004-04/msg00249.php

procs memory swap io system
cpu

r b swpd free buff cache si so bi bo in
cs us sy id wa

2 0 20 2860544 124816 8042544 0 0 0 0 0 0 0
0 0 0

2 0 20 2860376 124816 8042552 0 0 0 24 157 115322 13
10 76 0

3 0 20 2860364 124840 8042540 0 0 0 228 172 120003 12
10 77 0

2 0 20 2860364 124840 8042540 0 0 0 20 158 118816 15
10 75 0

2 0 20 2860080 124840 8042540 0 0 0 10 152 117858 12
11 77 0

1 0 20 2860080 124848 8042572 0 0 0 210 202 114724 14
10 76 0

2 0 20 2860080 124848 8042572 0 0 0 20 169 114843 13
10 77 0

3 0 20 2859908 124860 8042576 0 0 0 188 180 115134 14
11 75 0

3 0 20 2859848 124860 8042576 0 0 0 20 173 113470 13
10 77 0

2 0 20 2859836 124860 8042576 0 0 0 10 157 112839 14
11 75 0

The system seems to be fine on iowait/memory side, except the CPU being
busy with the CS. Here's the top output:

11:54:57 up 59 days, 14:11, 2 users, load average: 1.13, 1.66, 1.52

282 processes: 281 sleeping, 1 running, 0 zombie, 0 stopped

CPU states: cpu user nice system irq softirq iowait idle

total 13.8% 0.0% 9.7% 0.0% 0.0% 0.0% 76.2%

cpu00 12.3% 0.0% 10.5% 0.0% 0.0% 0.1% 76.8%

cpu01 12.1% 0.0% 6.1% 0.0% 0.0% 0.1% 81.5%

cpu02 10.9% 0.0% 9.1% 0.0% 0.0% 0.0% 79.9%

cpu03 19.4% 0.0% 14.9% 0.0% 0.0% 0.0% 65.6%

cpu04 13.9% 0.0% 11.1% 0.0% 0.0% 0.0% 74.9%

cpu05 14.9% 0.0% 9.1% 0.0% 0.0% 0.0% 75.9%

cpu06 12.9% 0.0% 8.9% 0.0% 0.0% 0.0% 78.1%

cpu07 14.3% 0.0% 8.1% 0.0% 0.1% 0.0% 77.3%

Mem: 12081720k av, 9273304k used, 2808416k free, 0k shrd,
126048k buff

4686808k actv, 3211872k in_d, 170240k in_c

Swap: 4096532k av, 20k used, 4096512k free 8044072k
cached

PostgreSQL 7.4.7 on i686-redhat-linux-gnu

Red Hat Enterprise Linux AS release 3 (Taroon Update 5)

Linux vl-pe6650-004 2.4.21-32.0.1.ELsmp

This is a Dell Quad XEON. Hyperthreading is turned on, and I am planning
to turn it off as soon as I get a chance to bring it down.

WAL is on separate drives from the OS and database.

Appreciate any inputs please....

Thanks,
Anjan

On Nov 22, 2005, at 11:59 AM, Anjan Dave wrote:
> This is a Dell Quad XEON. Hyperthreading is turned on, and I am
> planning to turn it off as soon as I get a chance to bring it down.
>

You should probably also upgrade to Pg 8.0 or newer since it is a
known problem with XEON processors and older postgres versions.
Upgrading Pg may solve your problem or it may not. It is just a
fluke with XEON processors...

Vivek Khera <vivek(at)khera(dot)org> writes:
> On Nov 22, 2005, at 11:59 AM, Anjan Dave wrote:
>> This is a Dell Quad XEON. Hyperthreading is turned on, and I am
>> planning to turn it off as soon as I get a chance to bring it down.

> You should probably also upgrade to Pg 8.0 or newer since it is a
> known problem with XEON processors and older postgres versions.
> Upgrading Pg may solve your problem or it may not.

PG 8.1 is the first release that has a reasonable probability of
avoiding heavy contention for the buffer manager lock when there
are multiple CPUs. If you're going to update to try to fix this,
you need to go straight to 8.1.

I've recently been chasing a report from Rob Creager that seems to
indicate contention on SubTransControlLock, so the slru code is
likely to be our next bottleneck to fix :-(

regards, tom lane