Re: Review Request 72249: Rewrite checkLock inner select with JOIN operator

2020-03-19 Thread Denys Kuzmenko via Review Board 

---
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/72249/
---

(Updated March 19, 2020, 12:25 p.m.)


Review request for hive, Peter Vary and Rajesh Balamohan.


Bugs: HIVE-22888
https://issues.apache.org/jira/browse/HIVE-22888


Repository: hive-git


Description
---

Replaced inner select under checkLocks using multiple IN statements with JOIN 
operator;

generated query looks like :

(SELECT EX.*, REQ.HL_LOCK_INT_ID AS REQ_LOCK_INT_ID FROM (
SELECT HL_LOCK_EXT_ID, HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_STATE, 
HL_LOCK_TYPE FROM HIVE_LOCKS
WHERE HL_LOCK_EXT_ID < 333) EX
INNER JOIN (
SELECT HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_TYPE FROM HIVE_LOCKS WHERE 
HL_LOCK_EXT_ID = 333) REQ
ON EX.HL_DB = REQ.HL_DB
AND (EX.HL_TABLE IS NULL OR REQ.HL_TABLE IS NULL OR EX.HL_TABLE = 
REQ.HL_TABLE
AND (EX.HL_PARTITION IS NULL OR REQ.HL_PARTITION IS NULL OR EX.HL_PARTITION 
= REQ.HL_PARTITION))
WHERE  (REQ.HL_TXNID = 0 OR EX.HL_TXNID != REQ.HL_TXNID) 
AND REQ.HL_LOCK_TYPE='e'
   AND !(EX.HL_TABLE IS NULL AND EX.HL_LOCK_TYPE='r' AND REQ.HL_TABLE 
IS NOT NULL )
LIMIT 1)

UNION ALL

(SELECT EX.*, REQ.HL_LOCK_INT_ID AS REQ_LOCK_INT_ID FROM (
SELECT HL_LOCK_EXT_ID, HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_STATE, 
HL_LOCK_TYPE FROM HIVE_LOCKS
WHERE HL_LOCK_EXT_ID < 333) EX
INNER JOIN (
SELECT HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_TYPE FROM HIVE_LOCKS WHERE 
HL_LOCK_EXT_ID = 333) REQ
ON EX.HL_DB = REQ.HL_DB
AND (EX.HL_TABLE IS NULL OR REQ.HL_TABLE IS NULL OR EX.HL_TABLE = 
REQ.HL_TABLE
AND (EX.HL_PARTITION IS NULL OR REQ.HL_PARTITION IS NULL OR EX.HL_PARTITION 
= REQ.HL_PARTITION))
WHERE  (REQ.HL_TXNID = 0 OR EX.HL_TXNID != REQ.HL_TXNID) 
AND REQ.HL_LOCK_TYPE='w' AND EX.HL_LOCK_TYPE IN ('w','e')
LIMIT 1)

UNION ALL

(SELECT EX.*, REQ.HL_LOCK_INT_ID AS REQ_LOCK_INT_ID FROM (
SELECT HL_LOCK_EXT_ID, HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_STATE, 
HL_LOCK_TYPE FROM HIVE_LOCKS
WHERE HL_LOCK_EXT_ID < 333) EX
INNER JOIN (
SELECT HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_TYPE FROM HIVE_LOCKS WHERE 
HL_LOCK_EXT_ID = 333) REQ
ON EX.HL_DB = REQ.HL_DB
AND (EX.HL_TABLE IS NULL OR REQ.HL_TABLE IS NULL OR EX.HL_TABLE = 
REQ.HL_TABLE
AND (EX.HL_PARTITION IS NULL OR REQ.HL_PARTITION IS NULL OR EX.HL_PARTITION 
= REQ.HL_PARTITION))
WHERE  (REQ.HL_TXNID = 0 OR EX.HL_TXNID != REQ.HL_TXNID) 
AND REQ.HL_LOCK_TYPE='r' AND EX.HL_LOCK_TYPE='e'
   AND !(EX.HL_TABLE IS NOT NULL AND REQ.HL_TABLE IS NULL)
LIMIT 1)


Diffs
-

  
standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnDbUtil.java
 5f51cf5 
  
standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java
 2995afa 


Diff: https://reviews.apache.org/r/72249/diff/2/


Testing (updated)
---

performance testing before/after change. results attached 
(acid-lock-perf-test.xlsx)


File Attachments


acid-lock-perf-test.xlsx
  
https://reviews.apache.org/media/uploaded/files/2020/03/19/4cc43e54-407d-43da-b6c9-dc96ff5da828__acid-lock-perf-test.xlsx


Thanks,

Denys Kuzmenko

Re: Review Request 72249: Rewrite checkLock inner select with JOIN operator

2020-03-19 Thread Denys Kuzmenko via Review Board 

---
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/72249/
---

(Updated March 19, 2020, 12:23 p.m.)


Review request for hive, Peter Vary and Rajesh Balamohan.


Bugs: HIVE-22888
https://issues.apache.org/jira/browse/HIVE-22888


Repository: hive-git


Description
---

Replaced inner select under checkLocks using multiple IN statements with JOIN 
operator;

generated query looks like :

(SELECT EX.*, REQ.HL_LOCK_INT_ID AS REQ_LOCK_INT_ID FROM (
SELECT HL_LOCK_EXT_ID, HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_STATE, 
HL_LOCK_TYPE FROM HIVE_LOCKS
WHERE HL_LOCK_EXT_ID < 333) EX
INNER JOIN (
SELECT HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_TYPE FROM HIVE_LOCKS WHERE 
HL_LOCK_EXT_ID = 333) REQ
ON EX.HL_DB = REQ.HL_DB
AND (EX.HL_TABLE IS NULL OR REQ.HL_TABLE IS NULL OR EX.HL_TABLE = 
REQ.HL_TABLE
AND (EX.HL_PARTITION IS NULL OR REQ.HL_PARTITION IS NULL OR EX.HL_PARTITION 
= REQ.HL_PARTITION))
WHERE  (REQ.HL_TXNID = 0 OR EX.HL_TXNID != REQ.HL_TXNID) 
AND REQ.HL_LOCK_TYPE='e'
   AND !(EX.HL_TABLE IS NULL AND EX.HL_LOCK_TYPE='r' AND REQ.HL_TABLE 
IS NOT NULL )
LIMIT 1)

UNION ALL

(SELECT EX.*, REQ.HL_LOCK_INT_ID AS REQ_LOCK_INT_ID FROM (
SELECT HL_LOCK_EXT_ID, HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_STATE, 
HL_LOCK_TYPE FROM HIVE_LOCKS
WHERE HL_LOCK_EXT_ID < 333) EX
INNER JOIN (
SELECT HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_TYPE FROM HIVE_LOCKS WHERE 
HL_LOCK_EXT_ID = 333) REQ
ON EX.HL_DB = REQ.HL_DB
AND (EX.HL_TABLE IS NULL OR REQ.HL_TABLE IS NULL OR EX.HL_TABLE = 
REQ.HL_TABLE
AND (EX.HL_PARTITION IS NULL OR REQ.HL_PARTITION IS NULL OR EX.HL_PARTITION 
= REQ.HL_PARTITION))
WHERE  (REQ.HL_TXNID = 0 OR EX.HL_TXNID != REQ.HL_TXNID) 
AND REQ.HL_LOCK_TYPE='w' AND EX.HL_LOCK_TYPE IN ('w','e')
LIMIT 1)

UNION ALL

(SELECT EX.*, REQ.HL_LOCK_INT_ID AS REQ_LOCK_INT_ID FROM (
SELECT HL_LOCK_EXT_ID, HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_STATE, 
HL_LOCK_TYPE FROM HIVE_LOCKS
WHERE HL_LOCK_EXT_ID < 333) EX
INNER JOIN (
SELECT HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_TYPE FROM HIVE_LOCKS WHERE 
HL_LOCK_EXT_ID = 333) REQ
ON EX.HL_DB = REQ.HL_DB
AND (EX.HL_TABLE IS NULL OR REQ.HL_TABLE IS NULL OR EX.HL_TABLE = 
REQ.HL_TABLE
AND (EX.HL_PARTITION IS NULL OR REQ.HL_PARTITION IS NULL OR EX.HL_PARTITION 
= REQ.HL_PARTITION))
WHERE  (REQ.HL_TXNID = 0 OR EX.HL_TXNID != REQ.HL_TXNID) 
AND REQ.HL_LOCK_TYPE='r' AND EX.HL_LOCK_TYPE='e'
   AND !(EX.HL_TABLE IS NOT NULL AND REQ.HL_TABLE IS NULL)
LIMIT 1)


Diffs (updated)
-

  
standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnDbUtil.java
 5f51cf5 
  
standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java
 2995afa 


Diff: https://reviews.apache.org/r/72249/diff/2/

Changes: https://reviews.apache.org/r/72249/diff/1-2/


Testing
---

performance testing before/after change. results attached


File Attachments


acid-lock-perf-test.xlsx
  
https://reviews.apache.org/media/uploaded/files/2020/03/19/4cc43e54-407d-43da-b6c9-dc96ff5da828__acid-lock-perf-test.xlsx


Thanks,

Denys Kuzmenko

Review Request 72249: Rewrite checkLock inner select with JOIN operator

2020-03-19 Thread Denys Kuzmenko via Review Board 

---
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/72249/
---

Review request for hive, Peter Vary and Rajesh Balamohan.


Bugs: HIVE-22888
https://issues.apache.org/jira/browse/HIVE-22888


Repository: hive-git


Description
---

Replaced inner select under checkLocks using multiple IN statements with JOIN 
operator;

generated query looks like :

(SELECT EX.*, REQ.HL_LOCK_INT_ID AS REQ_LOCK_INT_ID FROM (
SELECT HL_LOCK_EXT_ID, HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_STATE, 
HL_LOCK_TYPE FROM HIVE_LOCKS
WHERE HL_LOCK_EXT_ID < 333) EX
INNER JOIN (
SELECT HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_TYPE FROM HIVE_LOCKS WHERE 
HL_LOCK_EXT_ID = 333) REQ
ON EX.HL_DB = REQ.HL_DB
AND (EX.HL_TABLE IS NULL OR REQ.HL_TABLE IS NULL OR EX.HL_TABLE = 
REQ.HL_TABLE
AND (EX.HL_PARTITION IS NULL OR REQ.HL_PARTITION IS NULL OR EX.HL_PARTITION 
= REQ.HL_PARTITION))
WHERE  (REQ.HL_TXNID = 0 OR EX.HL_TXNID != REQ.HL_TXNID) 
AND REQ.HL_LOCK_TYPE='e'
   AND !(EX.HL_TABLE IS NULL AND EX.HL_LOCK_TYPE='r' AND REQ.HL_TABLE 
IS NOT NULL )
LIMIT 1)

UNION ALL

(SELECT EX.*, REQ.HL_LOCK_INT_ID AS REQ_LOCK_INT_ID FROM (
SELECT HL_LOCK_EXT_ID, HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_STATE, 
HL_LOCK_TYPE FROM HIVE_LOCKS
WHERE HL_LOCK_EXT_ID < 333) EX
INNER JOIN (
SELECT HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_TYPE FROM HIVE_LOCKS WHERE 
HL_LOCK_EXT_ID = 333) REQ
ON EX.HL_DB = REQ.HL_DB
AND (EX.HL_TABLE IS NULL OR REQ.HL_TABLE IS NULL OR EX.HL_TABLE = 
REQ.HL_TABLE
AND (EX.HL_PARTITION IS NULL OR REQ.HL_PARTITION IS NULL OR EX.HL_PARTITION 
= REQ.HL_PARTITION))
WHERE  (REQ.HL_TXNID = 0 OR EX.HL_TXNID != REQ.HL_TXNID) 
AND REQ.HL_LOCK_TYPE='w' AND EX.HL_LOCK_TYPE IN ('w','e')
LIMIT 1)

UNION ALL

(SELECT EX.*, REQ.HL_LOCK_INT_ID AS REQ_LOCK_INT_ID FROM (
SELECT HL_LOCK_EXT_ID, HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_STATE, 
HL_LOCK_TYPE FROM HIVE_LOCKS
WHERE HL_LOCK_EXT_ID < 333) EX
INNER JOIN (
SELECT HL_DB, HL_TABLE, HL_PARTITION, HL_LOCK_TYPE FROM HIVE_LOCKS WHERE 
HL_LOCK_EXT_ID = 333) REQ
ON EX.HL_DB = REQ.HL_DB
AND (EX.HL_TABLE IS NULL OR REQ.HL_TABLE IS NULL OR EX.HL_TABLE = 
REQ.HL_TABLE
AND (EX.HL_PARTITION IS NULL OR REQ.HL_PARTITION IS NULL OR EX.HL_PARTITION 
= REQ.HL_PARTITION))
WHERE  (REQ.HL_TXNID = 0 OR EX.HL_TXNID != REQ.HL_TXNID) 
AND REQ.HL_LOCK_TYPE='r' AND EX.HL_LOCK_TYPE='e'
   AND !(EX.HL_TABLE IS NOT NULL AND REQ.HL_TABLE IS NULL)
LIMIT 1)


Diffs
-

  common/src/java/org/apache/hadoop/hive/conf/HiveConf.java 4393a2825e 
  ql/src/java/org/apache/hadoop/hive/ql/txn/compactor/Initiator.java 7a0e32463d 
  ql/src/test/org/apache/hadoop/hive/ql/txn/compactor/TestInitiator.java 
564839324f 


Diff: https://reviews.apache.org/r/72249/diff/1/


Testing
---

performance testing before/after change. results attached


File Attachments


acid-lock-perf-test.xlsx
  
https://reviews.apache.org/media/uploaded/files/2020/03/19/4cc43e54-407d-43da-b6c9-dc96ff5da828__acid-lock-perf-test.xlsx


Thanks,

Denys Kuzmenko