You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@oozie.apache.org by Attila Sasvari via Review Board <no...@reviews.apache.org> on 2018/01/29 15:04:21 UTC

Review Request 65385: OOZIE-1717 add indices to speed up db queries

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

Review request for oozie.


Repository: oozie-git


Description
-------

Add indices to speed up SELECT operations.


Index user_name column

for purge service OOZIE-1803:
CREATE INDEX "OOZIEDB"."BUNDLE_JOBS_END_TIME_IDX" ON "BUNDLE_JOBS" ("END_TIME");
CREATE INDEX "OOZIEDB"."COORD_JOBS_END_TIME_IDX" ON "COORD_JOBS" ("END_TIME");
CREATE INDEX "OOZIEDB"."WF_JOBS_END_TIME_IDX" ON "WF_JOBS" ("END_TIME");
CREATE INDEX "OOZIEDB"."COORD_ACTIONS_JOB_ID_IDX" ON "COORD_ACTIONS" ("JOB_ID");
CREATE INDEX "OOZIEDB"."COORD_JOBS_BUNDLE_ID_IDX" ON "COORD_JOBS" ("BUNDLE_ID");
CREATE INDEX "OOZIEDB"."WF_ACTIONS_WF_ID_IDX" ON "WF_ACTIONS" ("WF_ID");
CREATE INDEX "OOZIEDB"."WF_JOBS_PARENT_ID_IDX" ON "WF_JOBS" ("PARENT_ID");
CREATE INDEX "OOZIEDB"."BUNDLE_ACTIONS_BUNDLE_ID_IDX" ON "BUNDLE_ACTIONS" ("BUNDLE_ID");

Composite indices to speed up operations on the web UI


Diffs
-----

  core/src/main/java/org/apache/oozie/BundleJobBean.java b42f12841c2f9cb6d508cd2ac2f393e6407d1cf0 
  core/src/main/java/org/apache/oozie/CoordinatorJobBean.java 99c7d3c474d9e1e22e2f5a927c03d71b9292b663 
  core/src/main/java/org/apache/oozie/WorkflowJobBean.java cd9a6df42e552a0607606a244b81978747d89b41 
  tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java a8b2234966c967f9ad9d722628bd486012b14bd3 


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


Testing
-------


Thanks,

Attila Sasvari


Re: Review Request 65385: OOZIE-1717 add indices to speed up db queries

Posted by Attila Sasvari via Review Board <no...@reviews.apache.org>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/65385/
-----------------------------------------------------------

(Updated Feb. 6, 2018, 1 a.m.)


Review request for oozie.


Changes
-------

verify indexes - create, upgrade db


Repository: oozie-git


Description
-------

Add indices to speed up SELECT operations.


Index user_name column

for purge service OOZIE-1803:
CREATE INDEX "OOZIEDB"."BUNDLE_JOBS_END_TIME_IDX" ON "BUNDLE_JOBS" ("END_TIME");
CREATE INDEX "OOZIEDB"."COORD_JOBS_END_TIME_IDX" ON "COORD_JOBS" ("END_TIME");
CREATE INDEX "OOZIEDB"."WF_JOBS_END_TIME_IDX" ON "WF_JOBS" ("END_TIME");
CREATE INDEX "OOZIEDB"."COORD_ACTIONS_JOB_ID_IDX" ON "COORD_ACTIONS" ("JOB_ID");
CREATE INDEX "OOZIEDB"."COORD_JOBS_BUNDLE_ID_IDX" ON "COORD_JOBS" ("BUNDLE_ID");
CREATE INDEX "OOZIEDB"."WF_ACTIONS_WF_ID_IDX" ON "WF_ACTIONS" ("WF_ID");
CREATE INDEX "OOZIEDB"."WF_JOBS_PARENT_ID_IDX" ON "WF_JOBS" ("PARENT_ID");
CREATE INDEX "OOZIEDB"."BUNDLE_ACTIONS_BUNDLE_ID_IDX" ON "BUNDLE_ACTIONS" ("BUNDLE_ID");

Composite indices to speed up operations on the web UI


Diffs (updated)
-----

  tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java 4bb6dd8606784fc0c4eb7aaedef79c59617f65eb 


Diff: https://reviews.apache.org/r/65385/diff/5/

Changes: https://reviews.apache.org/r/65385/diff/4-5/


Testing
-------


Thanks,

Attila Sasvari


Re: Review Request 65385: OOZIE-1717 add indices to speed up db queries

Posted by Peter Cseh via Review Board <no...@reviews.apache.org>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/65385/#review196803
-----------------------------------------------------------


Ship it!




Ship It!

- Peter Cseh


On Feb. 5, 2018, 9:52 a.m., Attila Sasvari wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/65385/
> -----------------------------------------------------------
> 
> (Updated Feb. 5, 2018, 9:52 a.m.)
> 
> 
> Review request for oozie.
> 
> 
> Repository: oozie-git
> 
> 
> Description
> -------
> 
> Add indices to speed up SELECT operations.
> 
> 
> Index user_name column
> 
> for purge service OOZIE-1803:
> CREATE INDEX "OOZIEDB"."BUNDLE_JOBS_END_TIME_IDX" ON "BUNDLE_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."COORD_JOBS_END_TIME_IDX" ON "COORD_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."WF_JOBS_END_TIME_IDX" ON "WF_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."COORD_ACTIONS_JOB_ID_IDX" ON "COORD_ACTIONS" ("JOB_ID");
> CREATE INDEX "OOZIEDB"."COORD_JOBS_BUNDLE_ID_IDX" ON "COORD_JOBS" ("BUNDLE_ID");
> CREATE INDEX "OOZIEDB"."WF_ACTIONS_WF_ID_IDX" ON "WF_ACTIONS" ("WF_ID");
> CREATE INDEX "OOZIEDB"."WF_JOBS_PARENT_ID_IDX" ON "WF_JOBS" ("PARENT_ID");
> CREATE INDEX "OOZIEDB"."BUNDLE_ACTIONS_BUNDLE_ID_IDX" ON "BUNDLE_ACTIONS" ("BUNDLE_ID");
> 
> Composite indices to speed up operations on the web UI
> 
> 
> Diffs
> -----
> 
>   core/src/main/java/org/apache/oozie/BundleJobBean.java b42f12841c2f9cb6d508cd2ac2f393e6407d1cf0 
>   core/src/main/java/org/apache/oozie/CoordinatorJobBean.java 99c7d3c474d9e1e22e2f5a927c03d71b9292b663 
>   core/src/main/java/org/apache/oozie/WorkflowJobBean.java cd9a6df42e552a0607606a244b81978747d89b41 
>   tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java a8b2234966c967f9ad9d722628bd486012b14bd3 
> 
> 
> Diff: https://reviews.apache.org/r/65385/diff/3/
> 
> 
> Testing
> -------
> 
> 
> Thanks,
> 
> Attila Sasvari
> 
>


Re: Review Request 65385: OOZIE-1717 add indices to speed up db queries

Posted by Attila Sasvari via Review Board <no...@reviews.apache.org>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/65385/
-----------------------------------------------------------

(Updated Feb. 5, 2018, 9:52 a.m.)


Review request for oozie.


Repository: oozie-git


Description
-------

Add indices to speed up SELECT operations.


Index user_name column

for purge service OOZIE-1803:
CREATE INDEX "OOZIEDB"."BUNDLE_JOBS_END_TIME_IDX" ON "BUNDLE_JOBS" ("END_TIME");
CREATE INDEX "OOZIEDB"."COORD_JOBS_END_TIME_IDX" ON "COORD_JOBS" ("END_TIME");
CREATE INDEX "OOZIEDB"."WF_JOBS_END_TIME_IDX" ON "WF_JOBS" ("END_TIME");
CREATE INDEX "OOZIEDB"."COORD_ACTIONS_JOB_ID_IDX" ON "COORD_ACTIONS" ("JOB_ID");
CREATE INDEX "OOZIEDB"."COORD_JOBS_BUNDLE_ID_IDX" ON "COORD_JOBS" ("BUNDLE_ID");
CREATE INDEX "OOZIEDB"."WF_ACTIONS_WF_ID_IDX" ON "WF_ACTIONS" ("WF_ID");
CREATE INDEX "OOZIEDB"."WF_JOBS_PARENT_ID_IDX" ON "WF_JOBS" ("PARENT_ID");
CREATE INDEX "OOZIEDB"."BUNDLE_ACTIONS_BUNDLE_ID_IDX" ON "BUNDLE_ACTIONS" ("BUNDLE_ID");

Composite indices to speed up operations on the web UI


Diffs (updated)
-----

  core/src/main/java/org/apache/oozie/BundleJobBean.java b42f12841c2f9cb6d508cd2ac2f393e6407d1cf0 
  core/src/main/java/org/apache/oozie/CoordinatorJobBean.java 99c7d3c474d9e1e22e2f5a927c03d71b9292b663 
  core/src/main/java/org/apache/oozie/WorkflowJobBean.java cd9a6df42e552a0607606a244b81978747d89b41 
  tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java a8b2234966c967f9ad9d722628bd486012b14bd3 


Diff: https://reviews.apache.org/r/65385/diff/3/

Changes: https://reviews.apache.org/r/65385/diff/2-3/


Testing
-------


Thanks,

Attila Sasvari


Re: Review Request 65385: OOZIE-1717 add indices to speed up db queries

Posted by Robert Kanter via Review Board <no...@reviews.apache.org>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/65385/#review196444
-----------------------------------------------------------




tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java
Line 64 (original), 64 (patched)
<https://reviews.apache.org/r/65385/#comment276097>

    It looks like we started using DB version 3 in Oozie 4.1.  Instead of ````DB_VERSION_PRE_5_0````, let's rename this to ````DB_VERSION_FOR_4_1````.  
    
    Also, why are these two not ````private````?



tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java
Lines 750 (patched)
<https://reviews.apache.org/r/65385/#comment276100>

    Shouldn't we write the statements to a file in this case?


- Robert Kanter


On Jan. 29, 2018, 3:04 p.m., Attila Sasvari wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/65385/
> -----------------------------------------------------------
> 
> (Updated Jan. 29, 2018, 3:04 p.m.)
> 
> 
> Review request for oozie.
> 
> 
> Repository: oozie-git
> 
> 
> Description
> -------
> 
> Add indices to speed up SELECT operations.
> 
> 
> Index user_name column
> 
> for purge service OOZIE-1803:
> CREATE INDEX "OOZIEDB"."BUNDLE_JOBS_END_TIME_IDX" ON "BUNDLE_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."COORD_JOBS_END_TIME_IDX" ON "COORD_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."WF_JOBS_END_TIME_IDX" ON "WF_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."COORD_ACTIONS_JOB_ID_IDX" ON "COORD_ACTIONS" ("JOB_ID");
> CREATE INDEX "OOZIEDB"."COORD_JOBS_BUNDLE_ID_IDX" ON "COORD_JOBS" ("BUNDLE_ID");
> CREATE INDEX "OOZIEDB"."WF_ACTIONS_WF_ID_IDX" ON "WF_ACTIONS" ("WF_ID");
> CREATE INDEX "OOZIEDB"."WF_JOBS_PARENT_ID_IDX" ON "WF_JOBS" ("PARENT_ID");
> CREATE INDEX "OOZIEDB"."BUNDLE_ACTIONS_BUNDLE_ID_IDX" ON "BUNDLE_ACTIONS" ("BUNDLE_ID");
> 
> Composite indices to speed up operations on the web UI
> 
> 
> Diffs
> -----
> 
>   core/src/main/java/org/apache/oozie/BundleJobBean.java b42f12841c2f9cb6d508cd2ac2f393e6407d1cf0 
>   core/src/main/java/org/apache/oozie/CoordinatorJobBean.java 99c7d3c474d9e1e22e2f5a927c03d71b9292b663 
>   core/src/main/java/org/apache/oozie/WorkflowJobBean.java cd9a6df42e552a0607606a244b81978747d89b41 
>   tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java a8b2234966c967f9ad9d722628bd486012b14bd3 
> 
> 
> Diff: https://reviews.apache.org/r/65385/diff/2/
> 
> 
> Testing
> -------
> 
> 
> Thanks,
> 
> Attila Sasvari
> 
>


Re: Review Request 65385: OOZIE-1717 add indices to speed up db queries

Posted by Attila Sasvari via Review Board <no...@reviews.apache.org>.

> On Jan. 29, 2018, 3:20 p.m., András Piros wrote:
> > tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java
> > Lines 1156 (patched)
> > <https://reviews.apache.org/r/65385/diff/1/?file=1949528#file1949528line1156>
> >
> >     Any ideas how to check automatically which indices have been created?
> 
> Attila Sasvari wrote:
>     I manually checked the SQL script OpenJPA's mapping tool has generated. I do not know a good method. JPA 2.0 (that OpenJPA 2.4.2 supports) have annotations for column indexes, and I verfied that the tool generated those indexes. Composite/multi-columns indexes are not supported. Please let me know what exactly and how you would like to verify about in a db agnostic and testable way. If that check is not abolutely necessary here or the change is too big, I would rather solve it in a separate JIRA.

On a second thought, you are right TestOozieDBCLI should include this kind of check. In Derby, SYSCONGLOMERATES should contain index information according to  https://db.apache.org/derby/docs/10.8/ref/rrefsistabs39391.html It is also possible to get such information via metadata using something like:
```
DatabaseMetaData metaData = connection.getMetaData();
ResultSet rs = metaData.getIndexInfo(null, "my_schema", "my_table", false, true);
```


- Attila


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


On Feb. 5, 2018, 9:52 a.m., Attila Sasvari wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/65385/
> -----------------------------------------------------------
> 
> (Updated Feb. 5, 2018, 9:52 a.m.)
> 
> 
> Review request for oozie.
> 
> 
> Repository: oozie-git
> 
> 
> Description
> -------
> 
> Add indices to speed up SELECT operations.
> 
> 
> Index user_name column
> 
> for purge service OOZIE-1803:
> CREATE INDEX "OOZIEDB"."BUNDLE_JOBS_END_TIME_IDX" ON "BUNDLE_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."COORD_JOBS_END_TIME_IDX" ON "COORD_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."WF_JOBS_END_TIME_IDX" ON "WF_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."COORD_ACTIONS_JOB_ID_IDX" ON "COORD_ACTIONS" ("JOB_ID");
> CREATE INDEX "OOZIEDB"."COORD_JOBS_BUNDLE_ID_IDX" ON "COORD_JOBS" ("BUNDLE_ID");
> CREATE INDEX "OOZIEDB"."WF_ACTIONS_WF_ID_IDX" ON "WF_ACTIONS" ("WF_ID");
> CREATE INDEX "OOZIEDB"."WF_JOBS_PARENT_ID_IDX" ON "WF_JOBS" ("PARENT_ID");
> CREATE INDEX "OOZIEDB"."BUNDLE_ACTIONS_BUNDLE_ID_IDX" ON "BUNDLE_ACTIONS" ("BUNDLE_ID");
> 
> Composite indices to speed up operations on the web UI
> 
> 
> Diffs
> -----
> 
>   core/src/main/java/org/apache/oozie/BundleJobBean.java b42f12841c2f9cb6d508cd2ac2f393e6407d1cf0 
>   core/src/main/java/org/apache/oozie/CoordinatorJobBean.java 99c7d3c474d9e1e22e2f5a927c03d71b9292b663 
>   core/src/main/java/org/apache/oozie/WorkflowJobBean.java cd9a6df42e552a0607606a244b81978747d89b41 
>   tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java a8b2234966c967f9ad9d722628bd486012b14bd3 
> 
> 
> Diff: https://reviews.apache.org/r/65385/diff/3/
> 
> 
> Testing
> -------
> 
> 
> Thanks,
> 
> Attila Sasvari
> 
>


Re: Review Request 65385: OOZIE-1717 add indices to speed up db queries

Posted by András Piros via Review Board <no...@reviews.apache.org>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/65385/#review196432
-----------------------------------------------------------




tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java
Lines 756 (patched)
<https://reviews.apache.org/r/65385/#comment276072>

    I think you should never have `null` as `stmt`. This will cause subsequent errors.
    
    I'd rather fail when `conn` is `null`.



tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java
Lines 1156 (patched)
<https://reviews.apache.org/r/65385/#comment276074>

    Any ideas how to check automatically which indices have been created?


- András Piros


On Jan. 29, 2018, 3:04 p.m., Attila Sasvari wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/65385/
> -----------------------------------------------------------
> 
> (Updated Jan. 29, 2018, 3:04 p.m.)
> 
> 
> Review request for oozie.
> 
> 
> Repository: oozie-git
> 
> 
> Description
> -------
> 
> Add indices to speed up SELECT operations.
> 
> 
> Index user_name column
> 
> for purge service OOZIE-1803:
> CREATE INDEX "OOZIEDB"."BUNDLE_JOBS_END_TIME_IDX" ON "BUNDLE_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."COORD_JOBS_END_TIME_IDX" ON "COORD_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."WF_JOBS_END_TIME_IDX" ON "WF_JOBS" ("END_TIME");
> CREATE INDEX "OOZIEDB"."COORD_ACTIONS_JOB_ID_IDX" ON "COORD_ACTIONS" ("JOB_ID");
> CREATE INDEX "OOZIEDB"."COORD_JOBS_BUNDLE_ID_IDX" ON "COORD_JOBS" ("BUNDLE_ID");
> CREATE INDEX "OOZIEDB"."WF_ACTIONS_WF_ID_IDX" ON "WF_ACTIONS" ("WF_ID");
> CREATE INDEX "OOZIEDB"."WF_JOBS_PARENT_ID_IDX" ON "WF_JOBS" ("PARENT_ID");
> CREATE INDEX "OOZIEDB"."BUNDLE_ACTIONS_BUNDLE_ID_IDX" ON "BUNDLE_ACTIONS" ("BUNDLE_ID");
> 
> Composite indices to speed up operations on the web UI
> 
> 
> Diffs
> -----
> 
>   core/src/main/java/org/apache/oozie/BundleJobBean.java b42f12841c2f9cb6d508cd2ac2f393e6407d1cf0 
>   core/src/main/java/org/apache/oozie/CoordinatorJobBean.java 99c7d3c474d9e1e22e2f5a927c03d71b9292b663 
>   core/src/main/java/org/apache/oozie/WorkflowJobBean.java cd9a6df42e552a0607606a244b81978747d89b41 
>   tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java a8b2234966c967f9ad9d722628bd486012b14bd3 
> 
> 
> Diff: https://reviews.apache.org/r/65385/diff/1/
> 
> 
> Testing
> -------
> 
> 
> Thanks,
> 
> Attila Sasvari
> 
>