Hi, This has become a major problem for us. Thank you in advance for
your help.
OS: SUSE Linux 2.6.5-7.191-bigsmp
PostgreSQL: 7.4.8
Application: ModPerl Web application using DBI.pm
Database size: 100 Gb, 1025 Tables.
Problem: EXPLAIN SELECT ... does not return.
Description:
The Application uses an EXPLAIN cost to determine whether a client's dynamic
request for data is too demanding for the server so it can gracefully deny
them. (Currently, anything over cost=0.00..500000.00).
The system gets about 3000 page requests a day.
Certain SQL Queries, I believe those with many table joins, when run as
EXPLAIN plans, never return. As a result they seem to stay churning in the
system. Once that happens other queries build up and the performance of the
whole database server grinds to a halt. Postgresql never dies, but
eventually, user requests start timing out.
This happens on average two or three times a week. I kill an offending
process and all's well again. I have not been able to identify with
certainty
an offending SQL statement.
Config params, that have changed from default:
tcpip_socket = true
max_connections = 200
shared_buffers = 2000
sort_mem = 1048576
vacuum_mem = 65536
max_fsm_pages = 100000
max_fsm_relations = 1000
max_files_per_process = 1000
fsync = false
wal_sync_method = fsync
wal_buffers = 800
checkpoint_segments = 30
commit_delay = 100
commit_siblings = 50
effective_cache_size = 1000
random_page_cost = 4
geqo = true
geqo_threshold = 14
default_statistics_target = 100
from_collapse_limit = 13
join_collapse_limit = 13
Note: we load lumps of data ea. week. Then primarily it is a readonly
database.
