Re[2]: [PERFORM] SMP on a heavy loaded database

[nobody nowhere <devnull@xxxxxxx>]

Пятница,  4 января 2013, 0:42 -07:00 от Scott Marlowe <scott.marlowe@xxxxxxxxx>:

On Thu, Jan 3, 2013 at 4:45 PM, nobody nowhere <devnull@xxxxxxx> wrote:
> Centos 5.X kernel 2.6.18-274
> pgsql-9.1 from pgdg-91-centos.repo
> relatively small database 3.2Gb
> Lot of insert, update, delete.
>
> I see non balanced _User_ usage on 14 CPU, exclusively assigned to the hardware raid controller.
> What I'm doing wrong, and is it possible somehow to fix?
>
> Thanks in advance.
>
> Andrew.
>
> # top -d 10.00 -b -n 2 -U postgres -c
>
> top - 23:18:19 up 453 days, 57 min, 3 users, load average: 0.55, 0.47, 0.42
> Tasks: 453 total, 1 running, 452 sleeping, 0 stopped, 0 zombie
> Cpu0 : 0.6%us, 0.1%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Cpu2 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Cpu3 : 1.2%us, 0.1%sy, 0.0%ni, 98.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Cpu4 : 2.6%us, 0.4%sy, 0.0%ni, 96.8%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
> Cpu5 : 0.8%us, 0.0%sy, 0.0%ni, 99.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Cpu6 : 5.4%us, 0.2%sy, 0.0%ni, 94.2%id, 0.2%wa, 0.0%hi, 0.0%si, 0.0%st
> Cpu7 : 3.3%us, 0.4%sy, 0.0%ni, 96.1%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
> Cpu8 : 1.4%us, 0.3%sy, 0.0%ni, 98.2%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st
> Cpu9 : 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Cpu10 : 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Cpu11 : 1.6%us, 0.6%sy, 0.0%ni, 97.4%id, 0.0%wa, 0.0%hi, 0.4%si, 0.0%st
> Cpu12 : 0.5%us, 0.1%sy, 0.0%ni, 99.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Cpu13 : 1.4%us, 0.2%sy, 0.0%ni, 98.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Cpu14 : 24.2%us, 0.8%sy, 0.0%ni, 74.5%id, 0.3%wa, 0.0%hi, 0.2%si, 0.0%st
> Cpu15 : 0.7%us, 0.1%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.1%hi, 0.1%si, 0.0%st
> Mem: 16426540k total, 16356772k used, 69768k free, 215764k buffers
> Swap: 4194232k total, 145280k used, 4048952k free, 14434356k cached
>

So how many concurrent users are accessing this db? pgsql assigns one
process on one core so to speak. It can't spread load for one user
over all cores.

64 php Fast-cgi processes over the Unix socket and about 20-30 over tcp