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. Following are the
details of my setup (details as per the postgres wiki) .

** PostgreSQL version
o Run "select pg_version();" in psql or PgAdmin III and provide the
full, exact output.*

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

* *A description of what you are trying to achieve and what results you
expect.*

To keep the CPU Load below 10 , Now during peak times the load is nearing to
40
At that time , it is not possible to access the data.

** The EXACT text of the query you ran, if any

* The EXACT output of that query if it's short enough to be reasonable to
post
o If you think the output is wrong, what you think should've been
produced instead

* The EXACT error message you get, if there is one*

As of now , i am unable to locate the exact query, the load shoots up
abnormally during
peak time is the main problem .

* * What program you're using to connect to PostgreSQL*

Jakarta Tomcat - Struts with JSP

** What version of the ODBC/JDBC driver you're using, if any*

postgresql-jdbc-8.1-12.2

* *What you were doing when the error happened / how to cause the error.
Describe in as much detail as possible, step by step, including command
lines, SQL output, etc.*

When certain tables with more than 3 lakh items are concurrently accessed by
more than 300
users, the CPU load shoots up .

* * Is there anything remotely unusual in the PostgreSQL server logs?
o On Windows these are in your data directory. On a default
PostgreSQL install that'll be in C:\Program Files\PostgreSQL\8.4\data\pg_log
(assuming you're using 8.4)
*
The log file /var/log/postgresql has no data .

* o On Linux this depends a bit on distro, but you'll usually find
them in /var/log/postgresql/.
* Operating system and version
o Linux users:
+ Linux distro and version
+ Kernel details (run "uname -a" on the terminal) *

SLES 10 SP3
clusternode2:~ # uname -a
Linux clusternode2 2.6.16.46-0.12-ppc64 #1 SMP Thu May 17 14:00:09 UTC 2007
ppc64 ppc64 ppc64 GNU/Linux

*
* What kind of hardware you have.
o CPU manufacturer and model, eg "AMD Athlon X2" or "Intel Core 2
Duo"
o Amount and size of RAM installed, eg "2GB RAM"
*
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 .

* o Storage details (important for performance and corruption
questions)
+ Do you use a RAID controller? If so, what type of
controller? eg "3Ware Escalade 8500-8"
# Does it have a battery backed cache module?
# Is write-back caching enabled?
+ Do you use software RAID? If so, what software and what
version? eg "Linux software RAID (md) 2.6.18-5-686 SMP mod_unload 686
REGPARM gcc-4.1".
# In the case of Linux software RAID you can get the
details from the "modinfo md_mod" command
+ Is your PostgreSQL database on a SAN?
# Who made it, what kind, etc? Provide what details you
can.
+ How many hard disks are connected to the system and what
types are they? You need to say more than just "6 disks". At least give
maker, rotational speed and interface type, eg "6 15,000rpm Seagate SAS
disks".
+ How are your disks arranged for storage? Are you using
RAID? If so, what RAID level(s)? What PostgreSQL data is on what disks /
disk sets? What file system(s) are in use?
# eg: "Two disks in RAID 1, with all PostgreSQL data
and programs stored on one ext3 file system."
# eg: "4 disks in RAID 5 holding the pg data directory
on an ext3 file system. 2 disks in RAID 1 holding pg_clog, pg_xlog, the
temporary tablespace, and the sort scratch space, also on ext3.".
# eg: "Default Windows install of PostgreSQL"
+ In case of corruption data reports:
# Have you had any unexpected power loss lately?
# Have you run a file system check? (chkdsk / fsck)
# Are there any error messages in the system logs?
(unix/linux: "dmesg", "/var/log/syslog" ; Windows: Event Viewer in Control
Panel -> Administrative Tools ) *

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

No power loss, filesystem check also fine, No errors on /var/log/syslog

*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

3 root 34 19 0 0 0 R 0 0.0 1:47.50 ksoftirqd/0

4726 root 15 0 29540 8776 3428 S 0 0.1 140:02.98 X

24950 root 15 0 0 0 0 S 0 0.0 0:30.96 pdflush

1 root 16 0 812 316 280 S 0 0.0 0:13.29 init

2 root RT 0 0 0 0 S 0 0.0 0:01.46 migration/0

4 root RT 0 0 0 0 S 0 0.0 0:00.78 migration/1

5 root 34 19 0 0 0 S 0 0.0 1:36.79 ksoftirqd/1

6 root RT 0 0 0 0 S 0 0.0 0:01.46 migration/2

7 root 34 19 0 0 0 R 0 0.0 1:49.83 ksoftirqd/2

8 root RT 0 0 0 0 S 0 0.0 0:00.79 migration/3

9 root 34 19 0 0 0 S 0 0.0 1:38.18 ksoftirqd/3

10 root 10 -5 0 0 0 S 0 0.0 1:02.11 events/0

11 root 10 -5 0 0 0 S 0 0.0 1:03.27 events/1

12 root 10 -5 0 0 0 S 0 0.0 1:01.76 events/2

13 root 10 -5 0 0 0 S 0 0.0 1:02.29 events/3

14 root 10 -5 0 0 0 S 0 0.0 0:00.01 khelper

1016 root 10 -5 0 0 0 S 0 0.0 0:00.00 kthread

1054 root 10 -5 0 0 0 S 0 0.0 0:03.08 kblockd/0

1055 root 10 -5 0 0 0 S 0 0.0 0:02.83 kblockd/1

1056 root 10 -5 0 0 0 S 0 0.0 0:03.19 kblockd/2

The CPU Load shoots upto 40 during peak time.
*
Following is my postgresql.conf (without comments) *

hba_file = '/var/lib/pgsql/data/pg_hba.conf'
listen_addresses = '*'
port = 5432
max_connections = 1800
shared_buffers = 300000
max_fsm_relations = 1000
effective_cache_size = 200000
log_destination = 'stderr'
redirect_stderr = on
log_rotation_age = 0
log_rotation_size = 10240
silent_mode = onlog_line_prefix = '%t %d %u '
autovacuum = on
datestyle = 'iso, dmy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

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

Please let me know your suggestions to improve the performance.

Regards

Shiva Raman