<div dir="ltr">Hi Yugo,<div><br></div><div>Thank you very much for helping! Please allow me to do some cleaning up and send you the dump.</div><div><br></div><div>I'll also try to reverse the partitioning condition and get back to you soon.</div>
<div><br></div><div><br></div><div>BR,</div><div>Fantix</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Oct 22, 2013 at 6:08 PM, Yugo Nagata <span dir="ltr"><<a href="mailto:nagata@sraoss.co.jp" target="_blank">nagata@sraoss.co.jp</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
Thanks for yor providing information. However, I can't still reproduced<br>
this hang, using the data as folowing;<br>
<br>
test=# select * from my_table ;<br>
id | character_id | my_col_one | my_col_two | quality | properties<br>
-----+--------------+------------+------------+---------+------------<br>
2 | 102 | t | 200 | 2000 | \x62626262<br>
22 | 102 | t | 200 | 2000 | \x62626262<br>
0 | 100 | t | 99 | 9999 | \x62626262<br>
10 | 100 | t | 99 | 9999 | \x62626262<br>
100 | 100 | t | 99 | 9999 | \x62626262<br>
333 | 103 | t | 300 | 3000 | \x63636363<br>
33 | 103 | t | 300 | 3000 | \x63636363<br>
3 | 103 | t | 300 | 3000 | \x63636363<br>
1 | 101 | t | 100 | 1000 | \x62626262<br>
<br>
<br>
I can't understand why the hang occurs when only caracter_id is even number.<br>
Maybe, contents of your 'my_table' or backend server #0 which receives<br>
data of even charcter_id.<br>
<br>
So, could you please me contens of 'my_table' using which you can<br>
reproduce the hang in your environment?<br>
<br>
In addition, could you try to reverse the partiaioning condition?<br>
That is, even number characters are on node #1, and odd number characters are on<br>
node #0.<br>
<br>
<br>
<br>
<br>
On Tue, 15 Oct 2013 14:56:50 +0800<br>
<div><div>Fantix King <<a href="mailto:fantix.king@gmail.com" target="_blank">fantix.king@gmail.com</a>> wrote:<br>
<br>
> Oh sure, thank you very much for helping here!<br>
><br>
> Table definition:<br>
><br>
> CREATE TABLE my_table (<br>
> id BIGINT NOT NULL,<br>
> character_id BIGINT NOT NULL,<br>
> my_col_one BOOLEAN NOT NULL,<br>
> my_col_two INTEGER NOT NULL,<br>
> quality INTEGER NOT NULL,<br>
> properties BYTEA,<br>
> PRIMARY KEY (id)<br>
> );<br>
><br>
> dist_def for this table:<br>
><br>
> INSERT INTO pgpool_catalog.dist_def VALUES (<br>
> 'my_db',<br>
> 'public',<br>
> 'my_table',<br>
> 'character_id',<br>
> ARRAY['id', 'character_id', 'my_col_one', 'my_col_two', 'quality',<br>
> 'properties'],<br>
> ARRAY['bigint', 'bigint', 'boolean', 'integer', 'integer', 'bytea'],<br>
> 'pgpool_catalog.dist_def_demo'<br>
> );<br>
> CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_demo(anyelement)<br>
> RETURNS integer AS $$<br>
> SELECT CASE WHEN $1 % 2 = 0 THEN 0<br>
> ELSE 1<br>
> END;<br>
> $$ LANGUAGE sql;<br>
><br>
> There're also some other irrelevant definitions in dist_def and<br>
> replicate_def, but they have nothing to do with this table at all (no<br>
> foreign key, no constraints, etc.). Please let me know if you need the full<br>
> file. :)<br>
><br>
><br>
> BR,<br>
> Fantix<br>
><br>
><br>
> On Tue, Oct 15, 2013 at 1:33 PM, Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp" target="_blank">nagata@sraoss.co.jp</a>> wrote:<br>
><br>
> > Hi,<br>
> ><br>
> > Thanks for your reporting and analyzing. I'll handle the problem. However,<br>
> > it may<br>
> > take some time for me to resoleve sinse I have other issues now.<br>
> ><br>
> > For analysis, could you please tell me the definition of table "my_table",<br>
> > and contents of dist_def, replicate_def ?<br>
> ><br>
> > On Mon, 14 Oct 2013 20:08:07 +0800<br>
> > Fantix King <<a href="mailto:fantix.king@gmail.com" target="_blank">fantix.king@gmail.com</a>> wrote:<br>
> ><br>
> > > Hi,<br>
> > ><br>
> > > I met a pretty weird issue in our production use of pgpool-II 3.3.1.<br>
> > > Executing SELECT through pgpool on some certain rows of a dist_def-ed<br>
> > table<br>
> > > always hang - you have to kill the "psql".<br>
> > ><br>
> > > It keeps reproducing in our production system while I cannot find a<br>
> > similar<br>
> > > report anywhere else. I'm still trying to reproduce this issue<br>
> > individually.<br>
> > ><br>
> > > My setup is like this:<br>
> > ><br>
> > > * 1 pgpool in parallel mode<br>
> > > * 2 PostgreSQL 9.1.9 backends<br>
> > > * 1 PostgreSQL 9.1.9 system db<br>
> > ><br>
> > > The table "my_table" is partitioned with "character_id" key column, where<br>
> > > even number characters are on node #0, and odd number characters are on<br>
> > > node #1. This query hangs:<br>
> > ><br>
> > > statement: SELECT <a href="http://my_table.id" target="_blank">my_table.id</a>, my_table.character_id,<br>
> > my_table.my_col_one,<br>
> > > my_table.my_col_two, my_table.quality, my_table.properties FROM my_table<br>
> > > WHERE my_table.character_id = 100;<br>
> > ><br>
> > > I found that:<br>
> > ><br>
> > > * All other hanging queries (the same table) are on node #0 - even<br>
> > numbers<br>
> > > * If I reduce any column from the SELECT list, the query works<br>
> > > * Not all of the queries are hanging, e.g. character_id = 102 works fine<br>
> > ><br>
> > > Then I diff-ed the pgpool log querying row 100(-) and 102(+):<br>
> > ><br>
> > > 1 : ProcessFrontendResponse: kind from frontend Q(51)<br>
> > > 2 : pool_unset_doing_extended_query_message: done<br>
> > > 3 -: statement: SELECT <a href="http://my_table.id" target="_blank">my_table.id</a>, my_table.character_id,<br>
> > > my_table.my_col_one, my_table.my_col_two, my_table.quality,<br>
> > > my_table.properties FROM my_table WHERE my_table.character_id = 100;<br>
> > > 4 : pool_set_query_in_progress: done<br>
> > > 5 : initSelectStmt: ANALYZE now(0)<br>
> > > 6 : inside build_range_info num= 1 current_select=0<br>
> > > 7 : inside build_range_info dist 0<br>
> > > 8 : inside build_virtual_info dist state=P my_table<br>
> > > 9 : append_virtual_table select=0,<br>
> > > no=0,col=id,type=bigint,table=my_table,state=P,valid=-1<br>
> > > 10 : append_virtual_table select=0,<br>
> > > no=1,col=character_id,type=bigint,table=my_table,state=P,valid=-1<br>
> > > 11 : append_virtual_table select=0,<br>
> > > no=2,col=my_col_one,type=boolean,table=my_table,state=P,valid=-1<br>
> > > 12 : append_virtual_table select=0,<br>
> > > no=3,col=my_col_two,type=integer,table=my_table,state=P,valid=-1<br>
> > > 13 : append_virtual_table select=0,<br>
> > > no=4,col=quality,type=integer,table=my_table,state=P,valid=-1<br>
> > > 14 : append_virtual_table select=0,<br>
> > > no=5,col=properties,type=bytea,table=my_table,state=P,valid=-1<br>
> > > ...<br>
> > > 17 : pool_parallel_query: 0 th FD_SET: 19<br>
> > > 18 : pool_parallel_query: 1 th FD_SET: 21<br>
> > > 19 : pool_parallel_query: num_fds: 22<br>
> > > 20 : read_kind_from_one_backend: read kind from 0 th backend T<br>
> > > 21 : pool_parallel_exec: kind from backend: T<br>
> > > 22 : pool_parallel_exec: dummy from backend: C<br>
> > > 23 +: pool_parallel_query: 1 th FD_SET: 21<br>
> > > 24 +: pool_parallel_query: num_fds: 22<br>
> > > 25 : read_kind_from_one_backend: read kind from 1 th backend T<br>
> > > 26 : pool_parallel_exec: dummy kind from backend: T<br>
> > > 27 -: pool_parallel_exec: kind from backend: D 3<br>
> > > 28 +: pool_parallel_exec: kind from backend: D 4<br>
> > > 29 : pool_parallel_exec: kind from backend: C<br>
> > > 30 : pool_unset_query_in_progress: done<br>
> > > 31 : pool_unset_query_in_progress: done<br>
> > > * 32 -: pool_process_query: discard Z packet from backend 1*<br>
> > > 33 -: detect_error: kind: Z<br>
> > > 34 -: detect_error: kind: Z<br>
> > > 35 -: detect_error: kind: Z<br>
> > > 36 : read_kind_from_backend: kind: Z from 0 th backend<br>
> > > 37 : read_kind_from_backend: read kind from 0 th backend Z<br>
> > NUM_BACKENDS: 2<br>
> > > 38 +: read_kind_from_backend: kind: Z from 1 th backend<br>
> > > 39 +: read_kind_from_backend: read kind from 1 th backend Z<br>
> > NUM_BACKENDS: 2<br>
> > > 40 +: ProcessBackendResponse: kind from backend: Z<br>
> > > 41 +: pool_read_message_length: slot: 0 length: 5<br>
> > > 42 +: pool_read_message_length: slot: 1 length: 5<br>
> > > 43 +: ReadyForQuery: transaction state:I<br>
> > > 44 +: ReadyForQuery: transaction state:I<br>
> > > 45 +: ProcessBackendResponse: Ready For Query<br>
> > ><br>
> > > FYI some of the pgpool.conf:<br>
> > ><br>
> > > backend_weight0 = 1<br>
> > > backend_weight1 = 1<br>
> > > load_balance_mode = on<br>
> > > replication_mode = on<br>
> > > parallel_mode = on<br>
> > ><br>
> > > Please advice or let me know if I'm missing any info, thank you!<br>
> > ><br>
> > ><br>
> > > BR,<br>
> > > Fantix<br>
> ><br>
> ><br>
> > --<br>
> > Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp" target="_blank">nagata@sraoss.co.jp</a>><br>
> ><br>
<br>
<br>
</div></div><span><font color="#888888">--<br>
Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp" target="_blank">nagata@sraoss.co.jp</a>><br>
</font></span></blockquote></div><br></div></div>