[
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)
