Extrordinarily Poor Performance.... - Mailing list pgsql-hackers

I am running Postgresql 7.1 on a dedicated Redhat 7.0 box with 512meg ram
and an IDE hard drive.

All day long queries that usually seem to execute instantaneously have been
taking up to 10 second to run!  I generally have about 6 postmasters
running, utilizing anywhere from 1% to 96% CPU utilization.  Another server
running Apache and PHP is performing all the queries.  I am not using
persistant connections.  I vaccum daily, usually has little to no impact on
the server.  The database is roughly 60 megs, there are no usually wide
tables... the is one table (user tracking) that has about 200,000 rows.  It
is indexed.

We are running a proprietary e-commerce package.  Right now I am getting
roughly one query per second.

Any input would be helpful!  If you need additional info let me know.

BTW, Thanks to Lamar for some great tips today!

Here is the status from pg_ctl:
/usr/local/pgsql/bin/postmaster '-d2' '-N' '48' '-B' '10000' '-i' '-D'
'/usr/local/pgsql/data'

Here is a sample from the log:
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT sale_price FROM ec_sale_prices WHERE sale_begins <=
CURRENT_TIMESTAMP AND sale_ends >= CURRENT_TIMESTAMP AND p
roduct_id = 137
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 958 exited with status 0
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading 5
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading 5
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling writing 5
/usr/local/pgsql/bin/postmaster: BackendStartup: pid 959 user postgres db
pa_commerce socket 5
/usr/local/pgsql/bin/postmaster child[959]: starting with (postgres -d2
-v131072 -p pa_commerce )
FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
DEBUG:  connection: host=216.239.233.44 user=postgres database=pa_commerce
DEBUG:  InitPostgres
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT web_user_id FROM pa_web_users WHERE session_id =
'34978ae91facc5fc9abb8e21db609b4c'
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT web_user_id FROM pa_partner_user_map WHERE
web_user_id = 221256
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: UPDATE pa_partner_user_map SET last_visited =
CURRENT_TIMESTAMP, partner_id = 'OmdxViUZtwA-*HFh50XeaHBc70n42b4iXA' WH
ERE web_user_id = 221256
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT order_id FROM pa_orders WHERE web_user_id = 221256
AND order_state = 'in_basket'
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading 5
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading 5
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling writing 5
/usr/local/pgsql/bin/postmaster: BackendStartup: pid 960 user postgres db
pa_commerce socket 5
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 959 exited with status 0
/usr/local/pgsql/bin/postmaster child[960]: starting with (postgres -d2
-v131072 -p pa_commerce )
FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01