Re: High CPU load on Postgres Server during Peak times!!!!

For 'idle in transaction' issues, you have to fix your code. I faced this
issue couple of months back. How good is your exception handling? Are you
rollingback/comitting your transactions while exceptions are thrown, during
the course of db operations?

Honestly I wouldn't go for these scripts which kill processes.

On Thu, Sep 24, 2009 at 6:20 PM, Shiva Raman <raman(dot)shivag(at)gmail(dot)com> wrote:

> Hi
>
> Today the load observed very high load . I am pasting the top.
>
> *TOP *
> top - 12:45:23 up 79 days, 14:42, 1 user, load average: 45.84, 33.13,
> 25.84
> Tasks: 394 total, 48 running, 346 sleeping, 0 stopped, 0 zombie
> Cpu(s): 49.2%us, 0.8%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.1%si,
> 50.0%st
> Mem: 16133676k total, 14870736k used, 1262940k free, 475484k buffers
> Swap: 14466492k total, 124k used, 14466368k free, 11423616k cached
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
> COMMAND
> 4152 postgres 17 0 2436m 176m 171m R 16 1.1 0:03.09 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4122 postgres 17 0 2431m 20m 17m R 12 0.1 0:06.38 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4007 postgres 16 0 2434m 80m 75m R 11 0.5 0:26.46 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 3994 postgres 16 0 2432m 134m 132m R 10 0.9 0:43.40 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4166 postgres 16 0 2433m 12m 8896 R 9 0.1 0:02.71 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4110 postgres 15 0 2436m 224m 217m S 8 1.4 0:06.83 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4061 postgres 16 0 2446m 491m 473m R 8 3.1 0:17.32 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4113 postgres 16 0 2432m 68m 65m R 8 0.4 0:11.03 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4071 postgres 16 0 2435m 200m 194m R 7 1.3 0:13.69 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4169 postgres 15 0 2436m 122m 117m R 7 0.8 0:00.93 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4178 postgres 16 0 2432m 77m 75m R 7 0.5 0:00.56 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4108 postgres 16 0 2437m 301m 293m R 6 1.9 0:11.94 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4155 postgres 16 0 2438m 252m 244m S 5 1.6 0:02.80 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4190 postgres 15 0 2432m 10m 8432 R 5 0.1 0:00.71 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 3906 postgres 16 0 2433m 124m 119m R 5 0.8 0:57.28 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 3970 postgres 16 0 2442m 314m 304m R 5 2.0 0:16.43 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4130 postgres 17 0 2433m 76m 72m R 5 0.5 0:03.76 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4179 postgres 16 0 2432m 105m 102m R 5 0.7 0:01.11 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4125 postgres 17 0 2436m 398m 391m R 4 2.5 0:05.62 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4162 postgres 16 0 2432m 125m 122m R 4 0.8 0:01.01 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 217m S 8 1.4 0:06.83 postgres: postgres dbEnterpriser_09_10
> 192.168.10. dbEnterpriser_09_10 192.168.10.
> 4061 postgres 16 0 2446m 491m 473m R 8 3.1 0:17.32 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4113 postgres 16 0 2432m 68m 65m R 8 0.4 0:11.03 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4071 postgres 16 0 2435m 200m 194m R 7 1.3 0:13.69 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4169 postgres 15 0 2436m 122m 117m R 7 0.8 0:00.93 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4178 postgres 16 0 2432m 77m 75m R 7 0.5 0:00.56 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4108 postgres 16 0 2437m 301m 293m R 6 1.9 0:11.94 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4155 postgres 16 0 2438m 252m 244m S 5 1.6 0:02.80 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4190 postgres 15 0 2432m 10m 8432 R 5 0.1 0:00.71 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 3906 postgres 16 0 2433m 124m 119m R 5 0.8 0:57.28 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 3970 postgres 16 0 2442m 314m 304m R 5 2.0 0:16.43 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4130 postgres 17 0 2433m 76m 72m R 5 0.5 0:03.76 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4179 postgres 16 0 2432m 105m 102m R 5 0.7 0:01.11 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4125 postgres 17 0 2436m 398m 391m R 4 2.5 0:05.62 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4162 postgres 16 0 2432m 125m 122m R 4 0.8 0:01.01 postgres:
> postgres dbEnterpriser_09_10 192.168.10.
> 4185 postgres 1
>
> *OUTPUT OF IOSTAT 1 5 (is SAN becoming a bottleneck,shows 50% CPU usage?)
> *
>
> clusternode2:~ # iostat 1 5
> Linux 2.6.16.46-0.12-ppc64 (clusternode2) 09/24/2009 _ppc64_ (4
> CPU)
>
> avg-cpu: %user %nice %system %iowait %steal %idle
> 16.00 0.00 0.68 0.61 10.72 71.99
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 1.08 1.96 22.54 13505448 155494808
> sdb 0.00 0.20 0.45 1410179 3099920
> sdc 0.00 0.05 0.01 357404 78840
> scd0 0.00 0.00 0.00 136 0
> sdd 12.20 77.69 343.49 535925176 2369551848
> sde 0.00 0.00 0.00 1120 0
>
> avg-cpu: %user %nice %system %iowait %steal %idle
> 29.46 0.00 0.25 0.00 7.43 62.87
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 0.00 0.00 0.00 0 0
> sdb 0.00 0.00 0.00 0 0
> sdc 0.00 0.00 0.00 0 0
> scd0 0.00 0.00 0.00 0 0
> sdd 0.00 0.00 0.00 0 0
> sde 0.00 0.00 0.00 0 0
>
> avg-cpu: %user %nice %system %iowait %steal %idle
> 46.17 0.00 0.99 0.00 38.52 14.32
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 0.00 0.00 0.00 0 0
> sdb 0.00 0.00 0.00 0 0
> sdc 0.00 0.00 0.00 0 0
> scd0 0.00 0.00 0.00 0 0
> sdd 3.96 0.00 118.81 0 120
> sde 0.00 0.00 0.00 0 0
>
> avg-cpu: %user %nice %system %iowait %steal %idle
> 48.88 0.00 0.99 0.00 49.88 0.25
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 0.00 0.00 0.00 0 0
> sdb 0.00 0.00 0.00 0 0
> sdc 0.00 0.00 0.00 0 0
> scd0 0.00 0.00 0.00 0 0
> sdd 0.00 0.00 0.00 0 0
> sde 0.00 0.00 0.00 0 0
>
> avg-cpu: %user %nice %system %iowait %steal %idle
> 47.86 0.00 2.14 0.00 50.00 0.00
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 0.00 0.00 0.00 0 0
> sdb 0.00 0.00 0.00 0 0
> sdc 0.00 0.00 0.00 0 0
> scd0 0.00 0.00 0.00 0 0
> sdd 0.00 0.00 0.00 0 0
> sde 0.00 0.00 0.00 0 0
>
>
>
>
>
> Andy Colson Wrote : ,
> *Eww. I think that's bad. A connection that has a transaction open will
> cause lots of row versions, which use up ram, and make it slower to step
> through the table (even with an index). You really need to fix up your code
> and make sure you commit transactions. (any statement (select, insert,
> update) will start a new transaction that you need to explicitly commit).
>
> *
> With reference to this suggestion by Andy Colson, we checked the
> application code and found that onlyINSERT, UPDATE has COMMIT and SELECT
> has no commit, We are using a lot of "Ajax Suggest" in the all the forms
> accessed for fetching the data using SELECT statements which are not
> explicitly commited. We have started updating the code on this.
>
> Thanks for this suggestion.
>
>
> Again thanks to suggestion of Scott Marlowe in reducing the number of
> connections. This was now reducted to 500 .
>
>
> As i mentioned in the mail, i am restarting the database every 30 minutes.
> I found a shell script in the wiki which could the idle in transaction pids.
> This is the code. The code will kill all old pids in the server.
>
> This is the script
>
> /usr/bin/test `/usr/bin/pgrep -f 'idle in transaction' | \
>
>
> /usr/bin/wc -l ` -gt 20 && /usr/bin/pkill -o -f 'idle in transaction'
>
> and this is the link where the script was provided.
>
> http://wiki.dspace.org/index.php/Idle_In_Transaction_Problem
>
> I tried it run it as test in the server, but the script is not executing.
> Even i see many of the "Idle in transaction " PIDs are showing R (RUnning
> status) , but most of them are showing S(Sleep ) status. Please suggest
> anyway i can resolve this idle transaction issue.
>
> Regards
>
> Shiva Raman
>
>
>