phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Samarth Jain (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-3133) Investigate why offset queries with reverse scan take a long time
Date Mon, 01 Aug 2016 19:40:20 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-3133?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Samarth Jain updated PHOENIX-3133:
----------------------------------
    Description: 
We need to workaround HBASE-16296 because users of Phoenix won't see the fix until at least
the fix makes it into a release version of HBase. Unfortunately, often times users are forced
to stick to earlier version of HBase, even after a release. PHOENIX-3121 works around the
issue when there's only a LIMIT clause. However, if there's a LIMIT and an OFFSET, the issue
still occurs. 

Repro code courtesy, [~mujtabachohan] 

{code}
DDL:
CREATE TABLE IF NOT EXISTS XYZ.T (
		TENANT_ID CHAR(15) NOT NULL, 
		KEY_PREFIX CHAR(3) NOT NULL,
		CREATED_DATE DATE,
		CREATED_BY CHAR(15),
		LAST_UPDATE DATE,
		LAST_UPDATE_BY CHAR(15),
		SYSTEM_MODSTAMP DATE
		CONSTRAINT PK PRIMARY KEY (
		TENANT_ID, 
		KEY_PREFIX
		)
		) VERSIONS=1, IMMUTABLE_ROWS=true, MULTI_TENANT=true, REPLICATION_SCOPE=1
		
		CREATE VIEW IF NOT EXISTS XYZ.ABC_VIEW (
		ACTIVITY_DATE DATE NOT NULL,
		WHO_ID CHAR(15) NOT NULL,
		WHAT_ID CHAR(15) NOT NULL,
		CHANNEL_TYPE VARCHAR NOT NULL,
		CHANNEL_ACTION_TYPE VARCHAR NOT NULL,
		ENGAGEMENT_HISTORY_POC_ID CHAR(15) ,
		CHANNEL_CONTEXT VARCHAR
		CONSTRAINT PKVIEW PRIMARY KEY
		(
		ACTIVITY_DATE, WHO_ID, WHAT_ID, CHANNEL_TYPE, CHANNEL_ACTION_TYPE
		)
		)
		AS SELECT * FROM XYZ.T WHERE KEY_PREFIX = '08m' 


UPSERT records using this:

Connection con = DriverManager.getConnection("jdbc:phoenix:samarthjai-ltm3.internal.salesforce.com",
new Properties());
		PreparedStatement pStatement;
		pStatement = con.prepareStatement("upsert into XYZ.ABC_VIEW (ACTIVITY_DATE,CHANNEL_ACTION_TYPE,CHANNEL_TYPE,TENANT_ID,WHAT_ID,WHO_ID)
values (TO_DATE('2010-11-11 00:00:00.000'),?,'ABC','00Dx0000000GyYS','701x00000000dzp','00Qx0000001S2qa')");

		for (int i=0; i<10000000;i++) {
			pStatement.setString(1, UUID.randomUUID().toString());
			pStatement.execute();
			
			if (i % 10000 == 0) {
				con.commit();
				System.out.println(i);
			}
		}

Sample query:

@Test
    public void testLimitCacheQuery() throws Exception {
        String url = "jdbc:phoenix:localhost:2181";
        try (Connection conn = DriverManager.getConnection(url)) {
            PreparedStatement stmt = conn.prepareStatement("select * from XYZ.ABC_VIEW where
who_id = '00Qx0000001S2qa' and TENANT_ID='00Dx0000000GyYS' order by activity_date desc LIMIT
18 OFFSET 2");
            stmt.setFetchSize(10);
            try (ResultSet rs = stmt.executeQuery()) {
                long startTime = System.currentTimeMillis();
                int record = 0;
                while (rs.next()) {
                    System.out.println("Record "+ (++record) + " Time: " + (System.currentTimeMillis()
- startTime));
                    startTime = System.currentTimeMillis();
                }
            }
        }
    }
{code}


  was:We need to workaround HBASE-16296 because users of Phoenix won't see the fix until at
least the fix makes it into a release version of HBase. Unfortunately, often times users are
forced to stick to earlier version of HBase, even after a release. PHOENIX-3121 works around
the issue when there's only a LIMIT clause. However, if there's a LIMIT and an OFFSET, the
issue still occurs. See PHOENIX-3121 for a repro.


> Investigate why offset queries with reverse scan take a long time
> -----------------------------------------------------------------
>
>                 Key: PHOENIX-3133
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-3133
>             Project: Phoenix
>          Issue Type: Bug
>            Reporter: Samarth Jain
>             Fix For: 4.8.1
>
>
> We need to workaround HBASE-16296 because users of Phoenix won't see the fix until at
least the fix makes it into a release version of HBase. Unfortunately, often times users are
forced to stick to earlier version of HBase, even after a release. PHOENIX-3121 works around
the issue when there's only a LIMIT clause. However, if there's a LIMIT and an OFFSET, the
issue still occurs. 
> Repro code courtesy, [~mujtabachohan] 
> {code}
> DDL:
> CREATE TABLE IF NOT EXISTS XYZ.T (
> 		TENANT_ID CHAR(15) NOT NULL, 
> 		KEY_PREFIX CHAR(3) NOT NULL,
> 		CREATED_DATE DATE,
> 		CREATED_BY CHAR(15),
> 		LAST_UPDATE DATE,
> 		LAST_UPDATE_BY CHAR(15),
> 		SYSTEM_MODSTAMP DATE
> 		CONSTRAINT PK PRIMARY KEY (
> 		TENANT_ID, 
> 		KEY_PREFIX
> 		)
> 		) VERSIONS=1, IMMUTABLE_ROWS=true, MULTI_TENANT=true, REPLICATION_SCOPE=1
> 		
> 		CREATE VIEW IF NOT EXISTS XYZ.ABC_VIEW (
> 		ACTIVITY_DATE DATE NOT NULL,
> 		WHO_ID CHAR(15) NOT NULL,
> 		WHAT_ID CHAR(15) NOT NULL,
> 		CHANNEL_TYPE VARCHAR NOT NULL,
> 		CHANNEL_ACTION_TYPE VARCHAR NOT NULL,
> 		ENGAGEMENT_HISTORY_POC_ID CHAR(15) ,
> 		CHANNEL_CONTEXT VARCHAR
> 		CONSTRAINT PKVIEW PRIMARY KEY
> 		(
> 		ACTIVITY_DATE, WHO_ID, WHAT_ID, CHANNEL_TYPE, CHANNEL_ACTION_TYPE
> 		)
> 		)
> 		AS SELECT * FROM XYZ.T WHERE KEY_PREFIX = '08m' 
> UPSERT records using this:
> Connection con = DriverManager.getConnection("jdbc:phoenix:samarthjai-ltm3.internal.salesforce.com",
new Properties());
> 		PreparedStatement pStatement;
> 		pStatement = con.prepareStatement("upsert into XYZ.ABC_VIEW (ACTIVITY_DATE,CHANNEL_ACTION_TYPE,CHANNEL_TYPE,TENANT_ID,WHAT_ID,WHO_ID)
values (TO_DATE('2010-11-11 00:00:00.000'),?,'ABC','00Dx0000000GyYS','701x00000000dzp','00Qx0000001S2qa')");
> 		for (int i=0; i<10000000;i++) {
> 			pStatement.setString(1, UUID.randomUUID().toString());
> 			pStatement.execute();
> 			
> 			if (i % 10000 == 0) {
> 				con.commit();
> 				System.out.println(i);
> 			}
> 		}
> Sample query:
> @Test
>     public void testLimitCacheQuery() throws Exception {
>         String url = "jdbc:phoenix:localhost:2181";
>         try (Connection conn = DriverManager.getConnection(url)) {
>             PreparedStatement stmt = conn.prepareStatement("select * from XYZ.ABC_VIEW
where who_id = '00Qx0000001S2qa' and TENANT_ID='00Dx0000000GyYS' order by activity_date desc
LIMIT 18 OFFSET 2");
>             stmt.setFetchSize(10);
>             try (ResultSet rs = stmt.executeQuery()) {
>                 long startTime = System.currentTimeMillis();
>                 int record = 0;
>                 while (rs.next()) {
>                     System.out.println("Record "+ (++record) + " Time: " + (System.currentTimeMillis()
- startTime));
>                     startTime = System.currentTimeMillis();
>                 }
>             }
>         }
>     }
> {code}



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

Mime
View raw message