You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@oozie.apache.org by "Todd Farmer (JIRA)" <ji...@apache.org> on 2016/06/10 16:40:21 UTC
[jira] [Commented] (OOZIE-1717) Index user_name column
[ https://issues.apache.org/jira/browse/OOZIE-1717?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15324762#comment-15324762 ]
Todd Farmer commented on OOZIE-1717:
------------------------------------
There should also be an index added on WF_JOBS.CREATED_TIME. Below are two queries pulled from MySQL slow query log, executed against sample data with the original schema. The proposed index is then added, queries run again, then the index removed and queries run a third time for comparison:
mysql> SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id, t0.parent_id FROM WF_JOBS t0 WHERE ((t0.status = 'FAILED' OR t0.status = 'KILLED' OR t0.status = 'SUCCEEDED') AND t0.status IS NOT NULL) ORDER BY t0.created_time DESC LIMIT 0, 100;
...
100 rows in set (10.57 sec)
mysql> SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id, t0.parent_id FROM WF_JOBS t0 ORDER BY t0.created_time DESC LIMIT 1700, 101;
...
101 rows in set (9.83 sec)
mysql> EXPLAIN SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id, t0.parent_id FROM WF_JOBS t0 ORDER BY t0.created_time DESC LIMIT 1700, 101;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 137227 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0.00 sec)
mysql> explain SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id, t0.parent_id FROM WF_JOBS t0 WHERE ((t0.status = 'FAILED' OR t0.status = 'KILLED' OR t0.status = 'SUCCEEDED') AND t0.status IS NOT NULL) ORDER BY t0.created_time DESC LIMIT 0, 100;
+----+-------------+-------+-------+------------------+------------------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+------------------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | t0 | range | I_WF_JOBS_STATUS | I_WF_JOBS_STATUS | 258 | NULL | 70674 | Using where; Using filesort |
+----+-------------+-------+-------+------------------+------------------+---------+------+-------+-----------------------------+
1 row in set (0.01 sec)
mysql> ALTER TABLE WF_JOBS ADD INDEX idx_created (created_time);
Query OK, 0 rows affected (10.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id, t0.parent_id FROM WF_JOBS t0 WHERE ((t0.status = 'FAILED' OR t0.status = 'KILLED' OR t0.status = 'SUCCEEDED') AND t0.status IS NOT NULL) ORDER BY t0.created_time DESC LIMIT 0, 100;
...
100 rows in set (0.01 sec)
mysql> SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id, t0.parent_id FROM WF_JOBS t0 ORDER BY t0.created_time DESC LIMIT 1700, 101;
...
101 rows in set (0.01 sec)
mysql> EXPLAIN SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id, t0.parent_id FROM WF_JOBS t0 ORDER BY t0.created_time DESC LIMIT 1700, 101;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
| 1 | SIMPLE | t0 | index | NULL | idx_created | 9 | NULL | 1801 | |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id, t0.parent_id FROM WF_JOBS t0 WHERE ((t0.status = 'FAILED' OR t0.status = 'KILLED' OR t0.status = 'SUCCEEDED') AND t0.status IS NOT NULL) ORDER BY t0.created_time DESC LIMIT 0, 100;
+----+-------------+-------+-------+------------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | t0 | index | I_WF_JOBS_STATUS | idx_created | 9 | NULL | 194 | Using where |
+----+-------------+-------+-------+------------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE WF_JOBS DROP INDEX idx_created;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id, t0.parent_id FROM WF_JOBS t0 WHERE ((t0.status = 'FAILED' OR t0.status = 'KILLED' OR t0.status = 'SUCCEEDED') AND t0.status IS NOT NULL) ORDER BY t0.created_time DESC LIMIT 0, 100;
...
100 rows in set (10.64 sec)
mysql> SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time, t0.external_id, t0.parent_id FROM WF_JOBS t0 ORDER BY t0.created_time DESC LIMIT 1700, 101;
...
101 rows in set (9.89 sec)
> Index user_name column
> ----------------------
>
> Key: OOZIE-1717
> URL: https://issues.apache.org/jira/browse/OOZIE-1717
> Project: Oozie
> Issue Type: Bug
> Reporter: Purshotam Shah
> Priority: Minor
>
> User_name is one of the frequently used filter. Adding index should avoid full db scan.
> Index need to be done for BUNDLE_JOBS,COORD_JOBS and WF_JOBS.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)