phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "xwxingyi (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (PHOENIX-1570) Data missing when using local index
Date Mon, 12 Jan 2015 07:33:34 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-1570?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14273267#comment-14273267
] 

xwxingyi edited comment on PHOENIX-1570 at 1/12/15 7:32 AM:
------------------------------------------------------------

Hi James, Xue:
    We tested the latest path v6.

    The table we hold has 627868 rows for q = '102218'. q is index, and a is primary key.

select count(a) FROM MISS_DATA_TABLE where q = '102218';
+------------------------------------------+
|                 COUNT(1)                 |
+------------------------------------------+
| 627868                                   |
+------------------------------------------+

   Then we test the count(b), it is very very slow to return a value.
select count(b) FROM MISS_DATA_TABLE where q = '102218';
+------------------------------------------+
|                 COUNT(B)                 |
+------------------------------------------+
java.lang.RuntimeException: java.sql.SQLException: ERROR 6000 (TIM01): Operation timed out
. Query couldn't be completed in the alloted time: 600000 ms
	at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2440)
	at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
	at sqlline.SqlLine.print(SqlLine.java:1735)
	at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
	at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
	at sqlline.SqlLine.dispatch(SqlLine.java:821)
	at sqlline.SqlLine.begin(SqlLine.java:699)
	at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
	at sqlline.SqlLine.main(SqlLine.java:424)

   Then we test select:

SELECT a FROM MISS_DATA_TABLE where q = '102218'
is still ok, rows start to show one by one immediately.

SELECT r,s,t FROM MISS_DATA_TABLE where q = '102218'
is still ok, rows start to show one by one immediately.

SELECT b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
is still ok, rows start to show one by one immediately.

SELECT distinct a FROM MISS_DATA_TABLE where q = '102218';
as it find data from index table, it is quick.

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
limit 10;
return right results in 0.3 seconds

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
limit 100;
return right results in 0.5 seconds

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
limit 1000;
return right results in 1 seconds

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
limit 10000;
return right results in 13 seconds

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
limit 100000;
not give out any rows in 10 minutes.
java.lang.RuntimeException: java.sql.SQLException: ERROR 6000 (TIM01): Operation timed out
. Query couldn't be completed in the alloted time: 600000 ms
	at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2440)
	at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
	at sqlline.SqlLine.print(SqlLine.java:1735)
	at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
	at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
	at sqlline.SqlLine.dispatch(SqlLine.java:821)
	at sqlline.SqlLine.begin(SqlLine.java:699)
	at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
	at sqlline.SqlLine.main(SqlLine.java:424)

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218';
not give out any rows in 10 minutes.

SELECT distinct r,s,t FROM MISS_DATA_TABLE where q = '102218';
not give out any rows in 10 minutes.


was (Author: xwxingyi):
Hi James, Xue:
    We tested the latest path v6.

    The table we hold has 627868 rows for q = '102218'.
select count(*) FROM MISS_DATA_TABLE where q = '102218';
+------------------------------------------+
|                 COUNT(1)                 |
+------------------------------------------+
| 627868                                   |
+------------------------------------------+

   Then we test the count(b), it is very very slow to return a value.
select count(b) FROM MISS_DATA_TABLE where q = '102218';
+------------------------------------------+
|                 COUNT(B)                 |
+------------------------------------------+
java.lang.RuntimeException: java.sql.SQLException: ERROR 6000 (TIM01): Operation timed out
. Query couldn't be completed in the alloted time: 600000 ms
	at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2440)
	at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
	at sqlline.SqlLine.print(SqlLine.java:1735)
	at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
	at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
	at sqlline.SqlLine.dispatch(SqlLine.java:821)
	at sqlline.SqlLine.begin(SqlLine.java:699)
	at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
	at sqlline.SqlLine.main(SqlLine.java:424)

SELECT b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
is still ok, rows start to show one by one immediately.

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
limit 10;
return right results in 0.3 seconds

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
limit 100;
return right results in 0.5 seconds

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
limit 1000;
return right results in 1 seconds

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
limit 10000;
return right results in 13 seconds

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218'
limit 100000;
not give out any rows in 10 minutes.
java.lang.RuntimeException: java.sql.SQLException: ERROR 6000 (TIM01): Operation timed out
. Query couldn't be completed in the alloted time: 600000 ms
	at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2440)
	at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
	at sqlline.SqlLine.print(SqlLine.java:1735)
	at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
	at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
	at sqlline.SqlLine.dispatch(SqlLine.java:821)
	at sqlline.SqlLine.begin(SqlLine.java:699)
	at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
	at sqlline.SqlLine.main(SqlLine.java:424)

SELECT distinct b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t FROM MISS_DATA_TABLE where q = '102218';
not give out any rows in 10 minutes.

> Data missing when using local index
> -----------------------------------
>
>                 Key: PHOENIX-1570
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-1570
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.2.1, 4.2.2
>         Environment: ubuntu 
> HBase 0.98.7
> Hadoop 2.5.1
> OS: ubuntu
>            Reporter: wuchengzhi
>            Priority: Critical
>         Attachments: 1570.patch, FetchDataByLocalIndexIT.java, PHOENIX-1570.patch, PHOENIX-1570_v2.patch,
PHOENIX-1570_v3.patch, PHOENIX-1570_v4.patch, PHOENIX-1570_v5.patch, PHOENIX-1570_v6.patch
>
>
> 1. crate a table by the schema as below:
> CREATE TABLE IF NOT EXISTS Miss_data_table(
> a BIGINT NOT NULL,
> b VARCHAR,
> c INTEGER,
> d INTEGER,
> e INTEGER,
> f INTEGER,
> g VARCHAR,
> h VARCHAR,
> i INTEGER,
> j VARCHAR,
> k INTEGER,
> l VARCHAR,
> m VARCHAR,
> n INTEGER,
> o INTEGER,
> p VARCHAR,
> q VARCHAR,
> r INTEGER,
> s BIGINT,
> t VARCHAR CONSTRAINT pk PRIMARY KEY(a))
> 2.create local index for the table with column: q
> create local index idx_q on Miss_data_table (q);
> 3.upsert data into table.
> upsert into Miss_data_table values(96660688,'hello/TEST-0',156,-1,-1,0,'2013-02-14 18:34:05.0','TEST-1',0,'495839182',0,'50','',0,0,'1818378','102218',0,26,'20141201')
> 4. execute querys...
> select a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t from Miss_data_table where q = '102218';
> +----------+--------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+--------+------+------+----------+
> | A        | B            | C    | D    | E    | F    | G    | H    | I    | J    | K
   | L    | M    | N    | O    | P    | Q      | R    | S    | T        |
> +----------+--------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+--------+------+------+----------+
> | 96660688 | hello/TEST-0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| NULL | NULL | NULL | NULL | NULL | 102218 | NULL | 26   | 20141201 |
> +----------+--------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+--------+------+------+----------+
> select a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t from Miss_data_table where a=96660688;
> +----------+--------------+------+------+------+------+-----------------------+--------+------+-----------+------+------+------+------+------+---------+--------+------+------+----------+
> | A        | B            | C    | D    | E    | F    | G                     | H   
  | I    | J         | K    | L    | M    | N    | O    | P       | Q      | R    | S    |
T        |
> +----------+--------------+------+------+------+------+-----------------------+--------+------+-----------+------+------+------+------+------+---------+--------+------+------+----------+
> | 96660688 | hello/TEST-0 | 156  | -1   | -1   | 0    | 2013-02-14 18:34:05.0 | TEST-1
| 0    | 495839182 | 0    | 50   | NULL | 0    | 0    | 1818378 | 102218 | 0    | 26   | 20141201
|
> +----------+--------------+------+------+------+------+-----------------------+--------+------+-----------+------+------+------+------+------+---------+--------+------+------+----------+
> // execute the query plain ,it shows we fetch data by local index.
> explain select a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t from Miss_data_table where q =
'102218';
> +------------------------------------------+
> |                   PLAN                   |
> +------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_TEST.MISS_DATA_TABLE [-32768,'102218']
|
> | CLIENT MERGE SORT                        |
> +------------------------------------------+



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message