Hi,
I've got a java based web application that uses PostgreSQL 8.0.2.
PostgreSQL runs on its own machine with RHEL 3, ia32e kernel, dual Xeon
processor, 4 Gb ram.
The web application runs on a seperate machine from the database. The
application machine has three tomcat instances configured to use 64
database connections each using DBCP for pooling. Most of the data
access is via Hibernate. The database itself is about 100 meg in size.
We're perf testing the application with Loadrunner. At about 500 virtual
users hitting the web application, the cpu utilization on the database
server is at 100%, PostgreSQL is on its knees. The memory usage isn't
bad, the I/O isn't bad, only the CPU seems to be maxed out.
checking the status of connections at this point ( ps -eaf | grep
"postgres:") where the CPU is maxed out I saw this:
127 idle
12 bind
38 parse
34 select
Hibernate is used in the application and unfortunately this seems to
cause queries not to get logged. (see
http://archives.postgresql.org/pgsql-admin/2005-05/msg00241.php)
I know there has been discussion about problems on Xeon MP systems. Is
this what we are running into? Or is something else going on? Is there
other information I can provide that might help determine what is going on?
Here are the postgresql.conf settings:
# The maximum number of connections.
max_connections = 256
# Standard performance-related settings.
shared_buffers = 16384
max_fsm_pages = 200000
max_fsm_relations = 10000
fsync = false
wal_sync_method = fsync
wal_buffers = 32
checkpoint_segments = 6
effective_cache_size = 38400
random_page_cost = 2
work_mem = 16384
maintenance_work_mem = 16384
# TODO - need to investigate these.
commit_delay = 0
commit_siblings = 5
max_locks_per_transaction = 512
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
