This is an automated email from the ASF dual-hosted git repository.
chengpan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/kyuubi.git
The following commit(s) were added to refs/heads/master by this push:
new dccb0fe97 [KYUUBI #5131] Create index on `metastore`.`create_time`
dccb0fe97 is described below
commit dccb0fe970816cfaf7fecd3cf94eaad063bd6867
Author: Cheng Pan <[email protected]>
AuthorDate: Thu Aug 3 19:24:39 2023 +0800
[KYUUBI #5131] Create index on `metastore`.`create_time`
### _Why are the changes needed?_
In Batch implementation v2, the following query is frequently executed to
pick the job.
```
SELECT identifier FROM metadata WHERE state='INITIALIZED' ORDER BY
create_time DESC LIMIT 1
```
Create an index for `create_time` could speed up the query and reduce the
pressure on MySQL server.
### _How was this patch tested?_
- [ ] Add some test cases that check the changes thoroughly including
negative and positive cases if possible
- [x] Add screenshots for manual tests if appropriate
Test the MySQL upgrading SQLs
```
mysql> CREATE TABLE IF NOT EXISTS metadata(
-> key_id bigint PRIMARY KEY AUTO_INCREMENT COMMENT 'the auto
increment key id',
-> identifier varchar(36) NOT NULL COMMENT 'the identifier id,
which is an UUID',
-> session_type varchar(32) NOT NULL COMMENT 'the session type, SQL
or BATCH',
-> real_user varchar(255) NOT NULL COMMENT 'the real user',
-> user_name varchar(255) NOT NULL COMMENT 'the user name, might be
a proxy user',
-> ip_address varchar(128) COMMENT 'the client ip address',
-> kyuubi_instance varchar(1024) NOT NULL COMMENT 'the kyuubi
instance that creates this',
-> state varchar(128) NOT NULL COMMENT 'the session state',
-> resource varchar(1024) COMMENT 'the main resource',
-> class_name varchar(1024) COMMENT 'the main class name',
-> request_name varchar(1024) COMMENT 'the request name',
-> request_conf mediumtext COMMENT 'the request config map',
-> request_args mediumtext COMMENT 'the request arguments',
-> create_time BIGINT NOT NULL COMMENT 'the metadata create time',
-> engine_type varchar(32) NOT NULL COMMENT 'the engine type',
-> cluster_manager varchar(128) COMMENT 'the engine cluster
manager',
-> engine_open_time bigint COMMENT 'the engine open time',
-> engine_id varchar(128) COMMENT 'the engine application id',
-> engine_name mediumtext COMMENT 'the engine application name',
-> engine_url varchar(1024) COMMENT 'the engine tracking url',
-> engine_state varchar(32) COMMENT 'the engine application state',
-> engine_error mediumtext COMMENT 'the engine application
diagnose',
-> end_time bigint COMMENT 'the metadata end time',
-> peer_instance_closed boolean default '0' COMMENT 'closed by peer
kyuubi instance',
-> UNIQUE INDEX unique_identifier_index(identifier),
-> INDEX user_name_index(user_name),
-> INDEX engine_type_index(engine_type)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE metadata MODIFY kyuubi_instance varchar(1024) COMMENT
'the kyuubi instance that creates this';
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE metadata ADD INDEX create_time_index(create_time);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table metadata;
+----------+--------------------------------------------------------------------------------+
| Table | Create Table
[...]
+----------+--------------------------------------------------------------------------------+
| metadata | CREATE TABLE `metadata` (
`key_id` bigint NOT NULL AUTO_INCREMENT COMMENT 'the auto increment key
id',
`identifier` varchar(36) NOT NULL COMMENT 'the identifier id, which is an
UUID',
`session_type` varchar(32) NOT NULL COMMENT 'the session type, SQL or
BATCH',
`real_user` varchar(255) NOT NULL COMMENT 'the real user',
`user_name` varchar(255) NOT NULL COMMENT 'the user name, might be a
proxy user',
`ip_address` varchar(128) DEFAULT NULL COMMENT 'the client ip address',
`kyuubi_instance` varchar(1024) DEFAULT NULL COMMENT 'the kyuubi instance
that creates this',
`state` varchar(128) NOT NULL COMMENT 'the session state',
`resource` varchar(1024) DEFAULT NULL COMMENT 'the main resource',
`class_name` varchar(1024) DEFAULT NULL COMMENT 'the main class name',
`request_name` varchar(1024) DEFAULT NULL COMMENT 'the request name',
`request_conf` mediumtext COMMENT 'the request config map',
`request_args` mediumtext COMMENT 'the request arguments',
`create_time` bigint NOT NULL COMMENT 'the metadata create time',
`engine_type` varchar(32) NOT NULL COMMENT 'the engine type',
`cluster_manager` varchar(128) DEFAULT NULL COMMENT 'the engine cluster
manager',
`engine_open_time` bigint DEFAULT NULL COMMENT 'the engine open time',
`engine_id` varchar(128) DEFAULT NULL COMMENT 'the engine application id',
`engine_name` mediumtext COMMENT 'the engine application name',
`engine_url` varchar(1024) DEFAULT NULL COMMENT 'the engine tracking url',
`engine_state` varchar(32) DEFAULT NULL COMMENT 'the engine application
state',
`engine_error` mediumtext COMMENT 'the engine application diagnose',
`end_time` bigint DEFAULT NULL COMMENT 'the metadata end time',
`peer_instance_closed` tinyint(1) DEFAULT '0' COMMENT 'closed by peer
kyuubi instance',
PRIMARY KEY (`key_id`),
UNIQUE KEY `unique_identifier_index` (`identifier`),
KEY `user_name_index` (`user_name`),
KEY `engine_type_index` (`engine_type`),
KEY `create_time_index` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+--------------------------------------------------------------------------------+
```
- [x] [Run
test](https://kyuubi.readthedocs.io/en/master/contributing/code/testing.html#running-tests)
locally before make a pull request
Closes #5131 from pan3793/metastore-create-time-index.
Closes #5131
fc18041f2 [Cheng Pan] ALTER TABLE ADD INDEX
c2261edb2 [Cheng Pan] update upgrade script
4f94be5ca [Cheng Pan] Create index on metastore.create_time
Authored-by: Cheng Pan <[email protected]>
Signed-off-by: Cheng Pan <[email protected]>
---
kyuubi-server/src/main/resources/sql/derby/004-KYUUBI-5131.derby.sql | 1 +
.../src/main/resources/sql/derby/metadata-store-schema-1.8.0.derby.sql | 2 ++
.../src/main/resources/sql/derby/upgrade-1.7.0-to-1.8.0.derby.sql | 1 +
kyuubi-server/src/main/resources/sql/mysql/004-KYUUBI-5131.mysql.sql | 3 +++
.../src/main/resources/sql/mysql/metadata-store-schema-1.8.0.mysql.sql | 3 ++-
.../src/main/resources/sql/mysql/upgrade-1.7.0-to-1.8.0.mysql.sql | 1 +
.../main/resources/sql/sqlite/metadata-store-schema-1.8.0.sqlite.sql | 2 ++
7 files changed, 12 insertions(+), 1 deletion(-)
diff --git
a/kyuubi-server/src/main/resources/sql/derby/004-KYUUBI-5131.derby.sql
b/kyuubi-server/src/main/resources/sql/derby/004-KYUUBI-5131.derby.sql
new file mode 100644
index 000000000..6a3142ffd
--- /dev/null
+++ b/kyuubi-server/src/main/resources/sql/derby/004-KYUUBI-5131.derby.sql
@@ -0,0 +1 @@
+CREATE INDEX metadata_create_time_index ON metadata(create_time);
diff --git
a/kyuubi-server/src/main/resources/sql/derby/metadata-store-schema-1.8.0.derby.sql
b/kyuubi-server/src/main/resources/sql/derby/metadata-store-schema-1.8.0.derby.sql
index b4a66d8d6..8d333bda2 100644
---
a/kyuubi-server/src/main/resources/sql/derby/metadata-store-schema-1.8.0.derby.sql
+++
b/kyuubi-server/src/main/resources/sql/derby/metadata-store-schema-1.8.0.derby.sql
@@ -34,3 +34,5 @@ CREATE UNIQUE INDEX metadata_unique_identifier_index ON
metadata(identifier);
CREATE INDEX metadata_user_name_index ON metadata(user_name);
CREATE INDEX metadata_engine_type_index ON metadata(engine_type);
+
+CREATE INDEX metadata_create_time_index ON metadata(create_time);
diff --git
a/kyuubi-server/src/main/resources/sql/derby/upgrade-1.7.0-to-1.8.0.derby.sql
b/kyuubi-server/src/main/resources/sql/derby/upgrade-1.7.0-to-1.8.0.derby.sql
index b9e6cff91..234510665 100644
---
a/kyuubi-server/src/main/resources/sql/derby/upgrade-1.7.0-to-1.8.0.derby.sql
+++
b/kyuubi-server/src/main/resources/sql/derby/upgrade-1.7.0-to-1.8.0.derby.sql
@@ -1 +1,2 @@
RUN '003-KYUUBI-5078.derby.sql';
+RUN '004-KYUUBI-5131.derby.sql';
diff --git
a/kyuubi-server/src/main/resources/sql/mysql/004-KYUUBI-5131.mysql.sql
b/kyuubi-server/src/main/resources/sql/mysql/004-KYUUBI-5131.mysql.sql
new file mode 100644
index 000000000..e743fc3d7
--- /dev/null
+++ b/kyuubi-server/src/main/resources/sql/mysql/004-KYUUBI-5131.mysql.sql
@@ -0,0 +1,3 @@
+SELECT '< KYUUBI-5131: Create index on metastore.create_time' AS ' ';
+
+ALTER TABLE metadata ADD INDEX create_time_index(create_time);
diff --git
a/kyuubi-server/src/main/resources/sql/mysql/metadata-store-schema-1.8.0.mysql.sql
b/kyuubi-server/src/main/resources/sql/mysql/metadata-store-schema-1.8.0.mysql.sql
index 4c86fbf76..77df8fa05 100644
---
a/kyuubi-server/src/main/resources/sql/mysql/metadata-store-schema-1.8.0.mysql.sql
+++
b/kyuubi-server/src/main/resources/sql/mysql/metadata-store-schema-1.8.0.mysql.sql
@@ -27,5 +27,6 @@ CREATE TABLE IF NOT EXISTS metadata(
peer_instance_closed boolean default '0' COMMENT 'closed by peer kyuubi
instance',
UNIQUE INDEX unique_identifier_index(identifier),
INDEX user_name_index(user_name),
- INDEX engine_type_index(engine_type)
+ INDEX engine_type_index(engine_type),
+ INDEX create_time_index(create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
diff --git
a/kyuubi-server/src/main/resources/sql/mysql/upgrade-1.7.0-to-1.8.0.mysql.sql
b/kyuubi-server/src/main/resources/sql/mysql/upgrade-1.7.0-to-1.8.0.mysql.sql
index a3a4bcdf1..473997448 100644
---
a/kyuubi-server/src/main/resources/sql/mysql/upgrade-1.7.0-to-1.8.0.mysql.sql
+++
b/kyuubi-server/src/main/resources/sql/mysql/upgrade-1.7.0-to-1.8.0.mysql.sql
@@ -1,3 +1,4 @@
SELECT '< Upgrading MetaStore schema from 1.7.0 to 1.8.0 >' AS ' ';
SOURCE 003-KYUUBI-5078.mysql.sql;
+SOURCE 004-KYUUBI-5131.mysql.sql;
SELECT '< Finished upgrading MetaStore schema from 1.7.0 to 1.8.0 >' AS ' ';
diff --git
a/kyuubi-server/src/main/resources/sql/sqlite/metadata-store-schema-1.8.0.sqlite.sql
b/kyuubi-server/src/main/resources/sql/sqlite/metadata-store-schema-1.8.0.sqlite.sql
index 6df7405be..656de6e5d 100644
---
a/kyuubi-server/src/main/resources/sql/sqlite/metadata-store-schema-1.8.0.sqlite.sql
+++
b/kyuubi-server/src/main/resources/sql/sqlite/metadata-store-schema-1.8.0.sqlite.sql
@@ -32,3 +32,5 @@ CREATE UNIQUE INDEX IF NOT EXISTS
metadata_unique_identifier_index ON metadata(i
CREATE INDEX IF NOT EXISTS metadata_user_name_index ON metadata(user_name);
CREATE INDEX IF NOT EXISTS metadata_engine_type_index ON metadata(engine_type);
+
+CREATE INDEX IF NOT EXISTS metadata_create_time_index ON metadata(create_time);
