[pgpool-general: 2198] Re: Parallel Query Hangs w/ specific rows

[Yugo Nagata]

Hi,

Thanks for your reporting and analyzing. I'll handle the problem. However, it may
take some time for me to resoleve sinse I have other issues now.

For analysis, could you please tell me the definition of table "my_table",
and contents of dist_def, replicate_def ?

On Mon, 14 Oct 2013 20:08:07 +0800
Fantix King <fantix.king at gmail.com> wrote:

> Hi,
> 
> I met a pretty weird issue in our production use of pgpool-II 3.3.1.
> Executing SELECT through pgpool on some certain rows of a dist_def-ed table
> always hang - you have to kill the "psql".
> 
> It keeps reproducing in our production system while I cannot find a similar
> report anywhere else. I'm still trying to reproduce this issue individually.
> 
> My setup is like this:
> 
>  * 1 pgpool in parallel mode
>  * 2 PostgreSQL 9.1.9 backends
>  * 1 PostgreSQL 9.1.9 system db
> 
> The table "my_table" is partitioned with "character_id" key column, where
> even number characters are on node #0, and odd number characters are on
> node #1. This query hangs:
> 
> statement: SELECT my_table.id, my_table.character_id, my_table.my_col_one,
> my_table.my_col_two, my_table.quality, my_table.properties FROM my_table
> WHERE  my_table.character_id = 100;
> 
> I found that:
> 
>  * All other hanging queries (the same table) are on node #0 - even numbers
>  * If I reduce any column from the SELECT list, the query works
>  * Not all of the queries are hanging, e.g. character_id = 102 works fine
> 
> Then I diff-ed the pgpool log querying row 100(-) and 102(+):
> 
>   1  : ProcessFrontendResponse: kind from frontend Q(51)
>   2  : pool_unset_doing_extended_query_message: done
>   3 -: statement: SELECT my_table.id, my_table.character_id,
> my_table.my_col_one, my_table.my_col_two, my_table.quality,
> my_table.properties FROM my_table WHERE  my_table.character_id = 100;
>   4  : pool_set_query_in_progress: done
>   5  : initSelectStmt: ANALYZE now(0)
>   6  : inside build_range_info num= 1 current_select=0
>   7  : inside build_range_info dist 0
>   8  : inside build_virtual_info dist state=P  my_table
>   9  : append_virtual_table select=0,
> no=0,col=id,type=bigint,table=my_table,state=P,valid=-1
>  10  : append_virtual_table select=0,
> no=1,col=character_id,type=bigint,table=my_table,state=P,valid=-1
>  11  : append_virtual_table select=0,
> no=2,col=my_col_one,type=boolean,table=my_table,state=P,valid=-1
>  12  : append_virtual_table select=0,
> no=3,col=my_col_two,type=integer,table=my_table,state=P,valid=-1
>  13  : append_virtual_table select=0,
> no=4,col=quality,type=integer,table=my_table,state=P,valid=-1
>  14  : append_virtual_table select=0,
> no=5,col=properties,type=bytea,table=my_table,state=P,valid=-1
> ...
>  17  : pool_parallel_query:  0 th FD_SET: 19
>  18  : pool_parallel_query:  1 th FD_SET: 21
>  19  : pool_parallel_query: num_fds: 22
>  20  : read_kind_from_one_backend: read kind from 0 th backend T
>  21  : pool_parallel_exec: kind from backend: T
>  22  : pool_parallel_exec: dummy from backend: C
>  23 +: pool_parallel_query:  1 th FD_SET: 21
>  24 +: pool_parallel_query: num_fds: 22
>  25  : read_kind_from_one_backend: read kind from 1 th backend T
>  26  : pool_parallel_exec: dummy kind from backend: T
>  27 -: pool_parallel_exec: kind from backend: D 3
>  28 +: pool_parallel_exec: kind from backend: D 4
>  29  : pool_parallel_exec: kind from backend: C
>  30  : pool_unset_query_in_progress: done
>  31  : pool_unset_query_in_progress: done
> * 32 -: pool_process_query: discard Z packet from backend 1*
>  33 -: detect_error: kind: Z
>  34 -: detect_error: kind: Z
>  35 -: detect_error: kind: Z
>  36  : read_kind_from_backend: kind: Z from 0 th backend
>  37  : read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2
>  38 +: read_kind_from_backend: kind: Z from 1 th backend
>  39 +: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2
>  40 +: ProcessBackendResponse: kind from backend: Z
>  41 +: pool_read_message_length: slot: 0 length: 5
>  42 +: pool_read_message_length: slot: 1 length: 5
>  43 +: ReadyForQuery: transaction state:I
>  44 +: ReadyForQuery: transaction state:I
>  45 +: ProcessBackendResponse: Ready For Query
> 
> FYI some of the pgpool.conf:
> 
> backend_weight0 = 1
> backend_weight1 = 1
> load_balance_mode = on
> replication_mode = on
> parallel_mode = on
> 
> Please advice or let me know if I'm missing any info, thank you!
> 
> 
> BR,
> Fantix


-- 
Yugo Nagata <nagata at sraoss.co.jp>