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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
