[pgpool-general: 3027] Re: pgpool 3.3.3 - stuck connections

[Tatsuo Ishii]

Seems like it does not actually stuck.

See pgpool-general: 3005.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> 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