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

> -----Mensaje original-----
> De: Shiva Raman
> Enviado el: Martes, 22 de Septiembre de 2009 10:55
> Para: pgsql-performance@postgresql.org
> Asunto: [PERFORM] High CPU load on Postgres Server during
> Peak times!!!!
>
> Dear all
>
>   I am having a problem of high cpu loads in my postgres
> server during peak time.


Some quick advice:

>
> clusternode2:~ # rpm -qa | grep postgres
> postgresql-devel-8.1.9-1.2
> postgresql-8.1.9-1.2
> postgresql-docs-8.1.9-1.2
> postgresql-server-8.1.9-1.2
> postgresql-libs-64bit-8.1.9-1.2
> postgresql-libs-8.1.9-1.2
> postgresql-jdbc-8.1-12.2
> postgresql-contrib-8.1.9-1.2
>
>

8.1 is quite old. Consider upgrading as newer versions are faster.
Current Postgres version is 8.4.

>
> High Availability Cluster with two IBM P Series Server and
> one DS4700 Storage
>
> IBM P series P52A with 2-core 2.1 Ghz POWER5+ Processor Card
> , 36 MB L3 Cache ,16 GB of RAM,
> 73.4 GB 10,000 RPM Ultra320 SCSI Drive for Operating System .
>

Sounds you are underpowered on cpu for 500 concurrent users.
Of course this really depends on what they are doing.

>
> IBM SAN DS4700 Storage with Fibre Channel HDD (73.4 GB * 10)
> Two Partitions - 73.4 GB * 3 RAID 5 - 134 GB storage
> partitions (One holding Jakarata tomcat
> application server and other holding Postgresql Database) .
> Four Hard disk RAID 5 with ext3 file systems hold the pgdata on SAN .
> Hard disk rotational speed is 73 GB 15K IBM 2 GB Fibre channel
>

A more suitable partitioning for an OLTP database would be:

2 x 73.4 GB RAID 1 for App Server + Postgresql and pg_xlog
8 x 73.4 GB RAID 10 for pgdata

RAID 5 is strongly discouraged.

>
> Following is the output of TOP command during offpeak time.
>
>
> top - 18:36:56 up 77 days, 20:33,  1 user,  load average:
> 12.99, 9.22, 10.37
> Tasks: 142 total,  12 running, 130 sleeping,   0 stopped,   0 zombie
> Cpu(s): 46.1%us,  1.9%sy,  0.0%ni,  6.1%id,  3.0%wa,  0.0%hi,
>  0.1%si, 42.9%st
> Mem:  16133676k total, 13657396k used,  2476280k free,
> 450908k buffers
> Swap: 14466492k total,      124k used, 14466368k free,
> 11590056k cached
>
>  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+
> COMMAND
> 22458 postgres  19   0 2473m 477m 445m R   40  3.0   0:15.49
> postmaster
> 22451 postgres  15   0 2442m 447m 437m S   33  2.8   0:30.44
> postmaster
> 22464 postgres  17   0 2443m 397m 383m R   28  2.5   0:13.78
> postmaster
> 22484 postgres  16   0 2448m 431m 412m S   20  2.7   0:02.73
> postmaster
> 22465 postgres  17   0 2440m 461m 449m R   15  2.9   0:03.52
> postmaster
> 22452 postgres  16   0 2450m 727m 706m R   13  4.6   0:23.46
> postmaster
> 22476 postgres  16   0 2437m 413m 405m S   13  2.6   0:06.11
> postmaster
> 22485 postgres  16   0 2439m 230m 222m R    7  1.5   0:05.72
> postmaster
> 22481 postgres  15   0 2436m 175m 169m S    7  1.1   0:04.44
> postmaster
> 22435 postgres  17   0 2438m 371m 361m R    6  2.4   1:17.92
> postmaster
> 22440 postgres  17   0 2445m 497m 483m R    5  3.2   1:44.50
> postmaster
> 22486 postgres  17   0 2432m  84m  81m R    4  0.5   0:00.76
> postmaster
>

Are you running several Postgres clusters on this hardware?
Please post Top output showing cmd line arguments (press 'c')


>
> User Access
> Total Number of Users is 500
> Maximum number of Concurrent users will be 500 during peak time
> Off Peak time the maximum number of concurrent user will be
> around 150 to 200.
>

A connection pooler like pgpool or pgbouncer would considerably reduce the
burden on your system.


Regards,
Fernando.