Please see below for config. We have recently switched to using pgpool-2 for connection pooling here and have come across a strange issue; this was done mainly to make it easier to point our apps at the master or slave db server when doing maintenance on either machine (both machines are sync data via slony). Please note that we're not using pgpool for parallel querying, or replication.
In the past few weeks, I've noticed that quite randomly, some of our web scripts have been leaving 'idle in transaction' (IIT) queries open on the db server which 1) causes slony to react very angrily, and 2) causes 500 errors to come back for any subsequent page requests by clients/staff (not site-wide, only for the data generated by the scripts in question). One script in particular runs a query which can take 90-120 seconds to complete and is run through our CherryPy service -- no matter what the load on the db machine is, this script seems to leave timed-out, IIT queries open. Restarting CherryPy does not get rid of them. The only way to clean these open connections seems to be to restart pgpool or kill the pgpool child that's hung. I am trying to eliminate components which may be causing the problem and would like opinions on whether there's something I can change in my config which may be what is the root of the problem. Lastly, is there a way to have pgpool dump out statements sent to it w/out enabling debug logging? Dumping full debug data generates very large log files which causes the system to run out of space rather quickly on /var. If you require any other info, please let me know. Config starts here: ----------------------------------------------------------- listen_addresses = '*' port = 9999 pcp_port = 9898 socket_dir = '/tmp' pcp_socket_dir = '/tmp' backend_socket_dir = '/tmp' pcp_timeout = 10 num_init_children = 250 max_pool = 1 child_life_time = 300 connection_life_time = 300 child_max_connections = 100 client_idle_limit = 0 authentication_timeout = 60 logdir = '/var/run/pgpool' replication_mode = false replication_timeout = 5000 load_balance_mode = false replication_stop_on_mismatch = false replicate_select = false reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' print_timestamp = true master_slave_mode = false connection_cache = true health_check_timeout = 20 health_check_period = 0 health_check_user = 'nobody' failover_command = '' failback_command = '' insert_lock = false ignore_leading_white_space = true log_statement = true log_connections = true log_hostname = true parallel_mode = false enable_query_cache = false pgpool2_hostname = '' #db03 backend_hostname0 = 'x.x.x.x' #db01 #backend_hostname0 = 'x.x.x.x' backend_port0 = 5432 enable_pool_hba = false recovery_user = 'nobody' recovery_password = '' recovery_1st_stage_command = '' recovery_2nd_stage_command = '' ----------------------------------------------------------- _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
