<div dir="ltr">Hi, All<div><br></div><div>We are using pgpool-II-pg93.x86_64 (3.3.3-1.pgdg) on RedHat 6.4</div><div><br></div><div>We are constantly have a problem with pgpool stuck connections.</div><div>The only solution is to kill stuck pgpool processes that consequently kill database processes.</div>
<div><br></div><div>Any ideas, please?</div><div><br></div><div>The situation looks as following:</div><div><br></div><div>On pgpool machine </div><div>====================</div><div> process with status DISCARD:</div><div>
postgres 13009 22540 0 16:38 ? 00:00:00 pgpool: storm_vm00062_user results 16.60.15.23(35779) DISCARD<br></div><div><br></div><div>In pgpool.log:<br></div><div>------------------------</div><div><div>2014-07-07 16:43:22 LOG: pid 13009: pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 1453 statement: WITH relevant_runs AS (SELECT load_test_run_id, transaction_name, transaction_status FROM transactions_metrics_1 WHERE load_test_run_id::integer >= $1),aggregated_status AS (SELECT load_test_run_id, transaction_name, 1-avg(transaction_status) AS fail_rate FROM relevant_runs GROUP BY load_test_run_id, transaction_name),ranked_status AS (SELECT load_test_run_id, transaction_name, fail_rate, row_number() OVER (PARTITION BY load_test_run_id ORDER BY fail_rate DESC) rn FROM aggregated_status )SELECT load_test_run_id, transaction_name AS name, fail_rate FROM ranked_status WHERE rn <= $2 AND fail_rate > 0 ORDER BY load_test_run_id DESC message: relation "transactions_metrics_1" does not exist</div>
<div>2014-07-07 16:43:22 ERROR: pid 13009: pool_read: read failed (Connection reset by peer)</div><div>2014-07-07 16:43:22 LOG: pid 13009: ProcessFrontendResponse: failed to read kind from frontend. frontend abnormally exited</div>
</div><div><br></div><div><div> strace -p 13009</div><div>Process 13009 attached - interrupt to quit</div><div>select(10, [9], NULL, [9], {1, 88370}) = 0 (Timeout)</div><div>select(10, [9], NULL, [9], {30, 0}) = 0 (Timeout)</div>
<div>select(10, [9], NULL, [9], {30, 0}) = 0 (Timeout)</div><div>select(10, [9], NULL, [9], {30, 0}) = 0 (Timeout)</div></div><div><br></div><div>lsof| grep 13009</div><div>pgpool 13009 postgres 9u IPv4 2325348 0t0 TCP pgpool_machine:50832->postgres_machine:postgres (ESTABLISHED)<br>
</div><div><br></div><div>On database machine:<br></div><div>===================</div><div>The process appears in PARSE state and in pg_stat_activity as ACTIVE</div><div><br></div><div>ps -ef|grep 1453|grep postgres</div>
<div>postgres 1453 2374 0 16:43 ? 00:00:00 postgres: storm_vm00062_user results 16.60.157.205(50832) PARSE<br></div><div><br></div><div>pg_stat_activity</div><div>--------------------------</div><div><div> 18094 | results | 1453 | 16641 | storm_vm00062_user | | 16.60.157.205 | | 50832 | 2014-07-07 16:43:07.917427+03 |</div>
<div> | 2014-07-07 16:43:07.931036+03 | 2014-07-07 16:43:07.931045+03 | f | active | WITH relevant_runs AS (SELECT load_test_run_id, transaction_name, transaction_status FROM transactio</div><div>ns_metrics_1 WHERE load_test_run_id::integer >= $1),aggregated_status AS (SELECT load_test_run_id, transaction_name, 1-avg(transaction_status) AS fail_rate FROM relevant_runs GROUP BY loa</div>
<div>d_test_run_id, transaction_name),ranked_status AS (SELECT load_test_run_id, transaction_name, fail_rate, row_number() OVER (PARTITION BY load_test_run_id ORDER BY fail_rate DESC) rn FROM</div><div>aggregated_status )SELECT load_test_run_id, transaction_name AS name, fail_rate FROM ranked_status WHERE rn <= $2 AND fail_rate > 0 ORDER BY load_test_run_id DESC</div>
</div><div><br></div><div>Postgres.log</div><div>-------------------</div><div>2014-07-07 16:43:07 IDT vm00062_user results [1453]: [1-1] ERROR: relation "transactions_metrics_1" does not exist at character 91<br>
</div><div><br></div><div><div>strace -p 1453</div><div>Process 1453 attached - interrupt to quit</div><div>recvfrom(10, ^C <unfinished ...></div></div><div><br></div><div>lsof|gep 1453</div><div>postmaste 1453 postgres 10u IPv4 2990409 0t0 TCP pgpool_machine:postgres-><a href="http://postgres_machine.hp.com:50832">postgres_machine.hp.com:50832</a> (ESTABLISHED)<br>
</div><div><br></div><div><div>Sincerely yours</div></div><div>Anna</div><div><br></div><div><br></div></div>