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

Hi

Thanks for your mail.

*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.*

*- Show quoted text -*

*
*

*>*

*> 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')*

NO Only single Postgres instance

>

> 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.*

I am already using connection pooling in tomcat web server, so installing
pgpool

will help enhancing the performance ?Any changes i have to do in my
application to

include pgpool?

Regds

Shiva raman