[pgpool-general: 3010] pgpool 3.3.3 - stuck connections

[Anna Aronova]

Hi, All

We are using pgpool-II-pg93.x86_64  (3.3.3-1.pgdg) on RedHat 6.4

We are constantly have a problem with pgpool stuck connections.
The only solution is to kill stuck pgpool processes that consequently kill
database processes.

Any ideas, please?

The situation looks as following:

On pgpool machine
====================
 process with status DISCARD:
postgres 13009 22540  0 16:38 ?        00:00:00 pgpool: storm_vm00062_user
results 16.60.15.23(35779) DISCARD

In pgpool.log:
------------------------
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
2014-07-07 16:43:22 ERROR: pid 13009: pool_read: read failed (Connection
reset by peer)
2014-07-07 16:43:22 LOG:   pid 13009: ProcessFrontendResponse: failed to
read kind from frontend. frontend abnormally exited

 strace -p 13009
Process 13009 attached - interrupt to quit
select(10, [9], NULL, [9], {1, 88370})  = 0 (Timeout)
select(10, [9], NULL, [9], {30, 0})     = 0 (Timeout)
select(10, [9], NULL, [9], {30, 0})     = 0 (Timeout)
select(10, [9], NULL, [9], {30, 0})     = 0 (Timeout)

lsof| grep 13009
pgpool    13009  postgres    9u     IPv4            2325348      0t0
TCP pgpool_machine:50832->postgres_machine:postgres (ESTABLISHED)

On database machine:
===================
The process appears in PARSE state and in pg_stat_activity as ACTIVE

ps -ef|grep 1453|grep postgres
postgres  1453  2374  0 16:43 ?        00:00:00 postgres:
storm_vm00062_user results 16.60.157.205(50832) PARSE

pg_stat_activity
--------------------------
 18094 | results  |  1453 |    16641 | storm_vm00062_user |
 | 16.60.157.205 |                 |       50832 | 2014-07-07
16:43:07.917427+03 |
  | 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
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
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
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

Postgres.log
-------------------
2014-07-07 16:43:07 IDT vm00062_user results [1453]: [1-1] ERROR:  relation
"transactions_metrics_1" does not exist at character 91

strace -p 1453
Process 1453 attached - interrupt to quit
recvfrom(10, ^C <unfinished ...>

lsof|gep 1453
postmaste  1453  postgres   10u     IPv4            2990409      0t0
TCP pgpool_machine:postgres->postgres_machine.hp.com:50832 (ESTABLISHED)

Sincerely yours
Anna
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140707/58516adb/attachment.html>