Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-3133

Investigate why offset queries with reverse scan take a long time

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      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, Mujtaba Chohan

      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();
                      }
                  }
              }
          }
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                ankit@apache.org Ankit Singhal
                Reporter:
                samarthjain Samarth Jain
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: