SMP on a heavy loaded database

From: nobody nowhere <devnull(at)mail(dot)ua>
To: pgsql-performance(at)postgresql(dot)org
Subject: SMP on a heavy loaded database
Date: 2013-01-03 23:45:08
Message-ID: 1357256708.822075673@f352.mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6513 postgres 16 0 4329m 235m 225m S 3.1 1.5 0:02.24 postgres: XXXX_DB [local] idle
6891 postgres 16 0 4331m 223m 213m S 1.7 1.4 0:01.44 postgres: XXXX_DB [local] idle
6829 postgres 16 0 4329m 219m 210m S 1.6 1.4 0:01.56 postgres: XXXX_DB [local] idle
6539 postgres 16 0 4330m 319m 308m S 1.5 2.0 0:03.64 postgres: XXXX_DB [local] idle
6487 postgres 16 0 4329m 234m 224m S 1.2 1.5 0:02.95 postgres: XXXX_DB [local] idle
6818 postgres 16 0 4328m 224m 215m S 1.2 1.4 0:02.00 postgres: XXXX_DB [local] idle
6831 postgres 16 0 4328m 215m 206m S 1.2 1.3 0:01.41 postgres: XXXX_DB [local] idle
6868 postgres 16 0 4330m 223m 213m S 1.2 1.4 0:01.46 postgres: XXXX_DB [local] idle
6899 postgres 15 0 4328m 220m 211m S 1.2 1.4 0:01.61 postgres: XXXX_DB [local] idle
6515 postgres 15 0 4331m 233m 223m S 1.0 1.5 0:02.66 postgres: XXXX_DB [local] idle
6890 postgres 16 0 4331m 279m 268m S 1.0 1.7 0:02.01 postgres: XXXX_DB [local] idle
7083 postgres 15 0 4328m 207m 199m S 1.0 1.3 0:00.77 postgres: XXXX_DB [local] idle
6374 postgres 16 0 4329m 245m 235m S 0.9 1.5 0:04.30 postgres: XXXX_DB [local] idle
6481 postgres 15 0 4328m 293m 285m S 0.9 1.8 0:03.17 postgres: XXXX_DB [local] idle
6484 postgres 16 0 4329m 236m 226m S 0.9 1.5 0:02.82 postgres: XXXX_DB [local] idle
6509 postgres 16 0 4332m 237m 225m S 0.9 1.5 0:02.90 postgres: XXXX_DB [local] idle
6522 postgres 15 0 4330m 238m 228m S 0.9 1.5 0:02.35 postgres: XXXX_DB [local] idle
6812 postgres 16 0 4329m 283m 274m S 0.9 1.8 0:02.19 postgres: XXXX_DB [local] idle
7086 postgres 15 0 4328m 202m 194m S 0.9 1.3 0:00.70 postgres: XXXX_DB [local] idle
6494 postgres 15 0 4329m 317m 306m S 0.8 2.0 0:03.98 postgres: XXXX_DB [local] idle
6542 postgres 16 0 4330m 309m 299m S 0.8 1.9 0:02.79 postgres: XXXX_DB [local] idle
6550 postgres 15 0 4329m 287m 277m S 0.8 1.8 0:02.80 postgres: XXXX_DB [local] idle
6777 postgres 16 0 4329m 229m 219m S 0.8 1.4 0:02.13 postgres: XXXX_DB [local] idle
6816 postgres 16 0 4329m 230m 220m S 0.8 1.4 0:01.61 postgres: XXXX_DB [local] idle
6822 postgres 15 0 4329m 305m 295m S 0.8 1.9 0:02.09 postgres: XXXX_DB [local] idle
6897 postgres 15 0 4328m 219m 210m S 0.8 1.4 0:01.69 postgres: XXXX_DB [local] idle
6926 postgres 16 0 4328m 209m 200m S 0.8 1.3 0:00.81 postgres: XXXX_DB [local] idle
6473 postgres 16 0 4329m 236m 226m S 0.7 1.5 0:02.81 postgres: XXXX_DB [local] idle
6826 postgres 16 0 4330m 226m 216m S 0.7 1.4 0:02.14 postgres: XXXX_DB [local] idle
6834 postgres 16 0 4331m 282m 271m S 0.7 1.8 0:03.06 postgres: XXXX_DB [local] idle
6882 postgres 15 0 4330m 222m 212m S 0.7 1.4 0:01.83 postgres: XXXX_DB [local] idle
6885 postgres 16 0 4328m 104m 96m S 0.6 0.7 0:00.94 postgres: XXXX_DB [local] idle
6878 postgres 15 0 4319m 2992 1472 S 0.4 0.0 40:20.10 postgres: wal sender process postgres 555.555.555.555(47880) streaming 21B/2BFE82F8
6519 postgres 16 0 4330m 249m 240m S 0.3 1.6 0:03.14 postgres: XXXX_DB [local] idle
6477 postgres 16 0 4331m 239m 228m S 0.2 1.5 0:02.75 postgres: XXXX_DB [local] idle
6500 postgres 16 0 4328m 227m 219m S 0.2 1.4 0:01.84 postgres: XXXX_DB [local] idle
6576 postgres 16 0 4331m 289m 278m S 0.2 1.8 0:03.01 postgres: XXXX_DB [local] idle
6637 postgres 16 0 4330m 230m 220m S 0.2 1.4 0:02.13 postgres: XXXX_DB [local] idle
6773 postgres 16 0 4330m 225m 214m S 0.2 1.4 0:02.98 postgres: XXXX_DB [local] idle
6838 postgres 16 0 4329m 224m 215m S 0.2 1.4 0:01.30 postgres: XXXX_DB [local] idle
7283 postgres 16 0 4326m 24m 18m S 0.2 0.2 0:00.08 postgres: XXXX_DB [local] idle
6378 postgres 16 0 4329m 267m 258m S 0.1 1.7 0:03.74 postgres: XXXX_DB [local] idle
6439 postgres 15 0 4330m 256m 244m S 0.1 1.6 0:03.62 postgres: XXXX_DB [local] idle
6535 postgres 15 0 4330m 289m 279m S 0.1 1.8 0:03.14 postgres: XXXX_DB [local] idle
6538 postgres 15 0 4330m 231m 221m S 0.1 1.4 0:02.17 postgres: XXXX_DB [local] idle
6544 postgres 15 0 4329m 226m 216m S 0.1 1.4 0:01.86 postgres: XXXX_DB [local] idle
6546 postgres 15 0 4329m 229m 219m S 0.1 1.4 0:02.40 postgres: XXXX_DB [local] idle
6552 postgres 16 0 4330m 246m 236m S 0.1 1.5 0:02.49 postgres: XXXX_DB [local] idle
6555 postgres 15 0 4328m 226m 217m S 0.1 1.4 0:02.05 postgres: XXXX_DB [local] idle
6558 postgres 16 0 4329m 233m 223m S 0.1 1.5 0:02.59 postgres: XXXX_DB [local] idle
6572 postgres 16 0 4328m 227m 218m S 0.1 1.4 0:01.69 postgres: XXXX_DB [local] idle
6580 postgres 16 0 4329m 229m 220m S 0.1 1.4 0:02.34 postgres: XXXX_DB [local] idle
6724 postgres 16 0 4331m 231m 220m S 0.1 1.4 0:01.80 postgres: XXXX_DB [local] idle
6804 postgres 16 0 4328m 115m 106m S 0.1 0.7 0:01.48 postgres: XXXX_DB [local] idle
6811 postgres 15 0 4329m 223m 214m S 0.1 1.4 0:01.51 postgres: XXXX_DB [local] idle
6821 postgres 16 0 4331m 306m 295m S 0.1 1.9 0:02.19 postgres: XXXX_DB [local] idle
6836 postgres 16 0 4329m 226m 216m S 0.1 1.4 0:01.72 postgres: XXXX_DB [local] idle
6879 postgres 16 0 4330m 222m 212m S 0.1 1.4 0:01.84 postgres: XXXX_DB [local] idle
6888 postgres 16 0 4328m 216m 208m S 0.1 1.4 0:01.32 postgres: XXXX_DB [local] idle
6896 postgres 16 0 4328m 213m 206m S 0.1 1.3 0:01.07 postgres: XXXX_DB [local] idle
14999 postgres 15 0 115m 1840 808 S 0.1 0.0 29:59.16 postgres: stats collector process
830 postgres 15 0 4319m 8396 6420 S 0.0 0.1 0:00.06 postgres: XXXX_DB 192.168.0.1(42974) idle
6808 postgres 15 0 4328m 222m 214m S 0.0 1.4 0:01.80 postgres: XXXX_DB [local] idle
6873 postgres 15 0 4329m 222m 213m S 0.0 1.4 0:01.92 postgres: XXXX_DB [local] idle
6875 postgres 16 0 4329m 228m 219m S 0.0 1.4 0:02.46 postgres: XXXX_DB [local] idle
6906 postgres 16 0 4328m 216m 208m S 0.0 1.4 0:00.83 postgres: XXXX_DB [local] idle
7274 postgres 15 0 4344m 534m 531m S 0.0 3.3 0:00.37 postgres: autovacuum worker process XXXX_DB
7818 postgres 15 0 4319m 6640 4680 S 0.0 0.0 0:00.06 postgres: postgres XXXX_DB 193.8.246.6(1032) idle
10553 postgres 15 0 4319m 6940 5000 S 0.0 0.0 0:00.01 postgres: XXXX_DB 192.168.0.1(35402) idle
10600 postgres 15 0 4319m 6780 4848 S 0.0 0.0 0:00.01 postgres: XXXX_DB 192.168.0.1(35612) idle
11146 postgres 15 0 4319m 7692 5744 S 0.0 0.0 0:00.01 postgres: XXXX_DB 192.168.0.1(39366) idle
12291 postgres 15 0 4319m 6716 4784 S 0.0 0.0 0:00.00 postgres: XXXX_DB 192.168.0.1(49540) idle
12711 postgres 15 0 4319m 8048 5984 S 0.0 0.0 0:00.02 postgres: XXXX_DB 192.168.0.1(51440) idle
12717 postgres 15 0 4319m 6768 4836 S 0.0 0.0 0:00.00 postgres: XXXX_DB 192.168.0.1(51616) idle
12815 postgres 15 0 4319m 6540 4608 S 0.0 0.0 0:00.00 postgres: XXXX_DB 192.168.0.1(52989) idle
13140 postgres 15 0 4319m 7736 5660 S 0.0 0.0 0:00.01 postgres: XXXX_DB 192.168.0.1(55225) idle
14378 postgres 15 0 4320m 7324 4928 S 0.0 0.0 0:00.03 postgres: postgres postgres 222.222.222.222(1030) idle
14979 postgres 15 0 4316m 104m 103m S 0.0 0.6 6:46.80 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data
14981 postgres 15 0 112m 1368 728 S 0.0 0.0 0:00.06 postgres: logger process
14995 postgres 15 0 4320m 2.0g 2.0g S 0.0 12.7 4:44.31 postgres: writer process
14996 postgres 15 0 4318m 17m 16m S 0.0 0.1 0:12.76 postgres: wal writer process
14997 postgres 15 0 4319m 3312 1568 S 0.0 0.0 0:10.14 postgres: autovacuum launcher process
14998 postgres 15 0 114m 1444 756 S 0.0 0.0 0:13.06 postgres: archiver process last was 000000010000021B0000002A
15027 postgres 15 0 4319m 80m 77m S 0.0 0.5 31:35.48 postgres: monitor XXXX_DB 10.0.0.0 (55433) idle
15070 postgres 15 0 4319m 82m 80m S 0.0 0.5 28:39.80 postgres: monitor XXXX_DB 10.10.0.1 (59360) idle
15808 postgres 15 0 4324m 15m 10m S 0.0 0.1 0:00.27 postgres: postgres XXXX_DB 222.222.222.222 (1031) idle
18787 postgres 15 0 4319m 8004 5932 S 0.0 0.0 0:00.02 postgres: XXXX_DB 192.168.0.1(46831) idle
18850 postgres 15 0 4319m 7364 5304 S 0.0 0.0 0:00.01 postgres: XXXX_DB 192.168.0.1(48843) idle
20331 postgres 15 0 4319m 6592 4660 S 0.0 0.0 0:00.00 postgres: XXXX_DB 192.168.0.1(60573) idle
26950 postgres 15 0 4319m 8172 6136 S 0.0 0.0 0:00.03 postgres: XXXX_DB 192.168.0.1(47890) idle
27599 postgres 15 0 4319m 8220 6200 S 0.0 0.1 0:00.04 postgres: XXXX_DB 192.168.0.1(49566) idle
28039 postgres 15 0 4319m 6644 4696 S 0.0 0.0 0:00.00 postgres: XXXX_DB 192.168.0.1(38329) idle
30450 postgres 15 0 4319m 8412 6316 S 0.0 0.1 0:00.03 postgres: XXXX_DB 192.168.0.1(49490) idle
31327 postgres 15 0 4319m 8508 6412 S 0.0 0.1 0:00.03 postgres: XXXX_DB 192.168.0.1(57064) idle
31363 postgres 15 0 4319m 8428 6364 S 0.0 0.1 0:00.03 postgres: XXXX_DB 192.168.0.1(58128) idle
32624 postgres 15 0 4319m 7356 5340 S 0.0 0.0 0:00.01 postgres: XXXX_DB 192.168.0.1(38002) idle
32651 postgres 15 0 4319m 8540 6572 S 0.0 0.1 0:00.07 postgres: XXXX_DB 192.168.0.1(38544) idle

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Stefan Andreatta 2013-01-04 04:33:09 Re: Simple join doesn't use index
Previous Message Jeremy Harris 2013-01-03 23:11:40 Re: Simple join doesn't use index