This is an automated email from the ASF dual-hosted git repository.
chengpan pushed a commit to branch branch-1.8
in repository https://gitbox.apache.org/repos/asf/kyuubi.git
The following commit(s) were added to refs/heads/branch-1.8 by this push:
new 3a07f3039 [KYUUBI #5327][BATCH] Add priority field in metadata table
definition
3a07f3039 is described below
commit 3a07f303907873b7d958503f201f8bfdc4f6fdb1
Author: zwangsheng <[email protected]>
AuthorDate: Sat Oct 7 14:52:21 2023 +0800
[KYUUBI #5327][BATCH] Add priority field in metadata table definition
### _Why are the changes needed?_
Add `priority` field in `metadata` table definition
In the current batch v2 implementation, Kyuubi supports the FIFO schedule
strategy, we are planning the enhance it with `priority` support, those with a
higher integer value of `priority` have a better opportunity to be scheduled.
It refers priority definition in [Apache Hadoop
YARN](https://hadoop.apache.org/docs/current/hadoop-yarn/hadoop-yarn-site/CapacityScheduler.html#Setup_for_application_priority)
and [Apache
YuniKorn](https://yunikorn.apache.org/docs/user_guide/priorities/#application-priority)
to define the `priority` as:
1. use a signed 4 bits integer to represent priority with the default value
of 10
2. a higher integer value of `priority` has a better opportunity to be
scheduled.
In practice, we face some issues when use MySQL as metastore:
MySQL 5.7 could not benefit from index in query mixes `ASC` and `DESC`, for
example: `SELECT * FROM metadata ORDER BY priority DESC, create_time ASC`,
which is mentioned in [MySQL ORDER BY
optimization](https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html)
> In some cases, MySQL cannot use indexes to resolve the `ORDER BY`,
although it may still use indexes to find the rows that match the `WHERE`
clause. Examples:
> - ...
> - The query mixes `ASC` and `DESC`
> - ...
Fortunately, it was enhanced in MySQL 8.0
> Two columns in an ORDER BY can sort in the same direction (both ASC, or
both DESC) or in opposite directions (one ASC, one DESC). A condition for index
use is that the index must have the same homogeneity, but need not have the
same actual direction.
> If a query mixes ASC and DESC, the optimizer can use an index on the
columns if the index also uses corresponding mixed ascending and descending
columns:
Thus, we create a mixed `ORDER BY` index `ALTER TABLE metadata ADD INDEX
priority_create_time_index(priority DESC, create_time ASC)` to improve the
query efficiency and avoid slowness in the case of large data volumes.
According to [MySQL 5.7
Index](https://dev.mysql.com/doc/refman/5.7/en/create-index.html), this won't
cause SQL error when creating this index in MySQL 5.7 but not take effect.
> A key_part specification can end with ASC or DESC. These keywords are
permitted for future extensions for specifying ascending or descending index
value storage. Currently, they are parsed but ignored; index values are always
stored in ascending order.
Close #5327
### _How was this patch tested?_
- [ ] Add some test cases that check the changes thoroughly including
negative and positive cases if possible
- [ ] Add screenshots for manual tests if appropriate
- [x] [Run
test](https://kyuubi.readthedocs.io/en/master/contributing/code/testing.html#running-tests)
locally before make a pull request
Test MySQL & Derby with DBeaver.
MySQL:
Derby:
### _Was this patch authored or co-authored using generative AI tooling?_
No
Closes #5329 from zwangsheng/KYUUBI#5327.
Closes #5327
8688d6bdc [zwangsheng] fix comments
107221b83 [zwangsheng] fix comments
3e4083deb [zwangsheng] fix comments
313b42ba3 [zwangsheng] EOF
eb6dc9c42 [zwangsheng] [KYUUBI #5327][Umbrella][V2] Kyuubi server pick task
from metadata db with priority
Authored-by: zwangsheng <[email protected]>
Signed-off-by: Cheng Pan <[email protected]>
(cherry picked from commit 2690d6d90ec61f27f24b326e041507d32c542648)
Signed-off-by: Cheng Pan <[email protected]>
---
.../src/main/resources/sql/derby/005-KYUUBI-5327.derby.sql | 3 +++
.../sql/derby/metadata-store-schema-1.8.0.derby.sql | 3 +++
.../src/main/resources/sql/mysql/005-KYUUBI-5327.mysql.sql | 13 +++++++++++++
.../sql/mysql/metadata-store-schema-1.8.0.mysql.sql | 5 ++++-
.../resources/sql/mysql/upgrade-1.7.0-to-1.8.0.mysql.sql | 1 +
.../sql/sqlite/metadata-store-schema-1.8.0.sqlite.sql | 3 +++
6 files changed, 27 insertions(+), 1 deletion(-)
diff --git
a/kyuubi-server/src/main/resources/sql/derby/005-KYUUBI-5327.derby.sql
b/kyuubi-server/src/main/resources/sql/derby/005-KYUUBI-5327.derby.sql
new file mode 100644
index 000000000..32c44d0fb
--- /dev/null
+++ b/kyuubi-server/src/main/resources/sql/derby/005-KYUUBI-5327.derby.sql
@@ -0,0 +1,3 @@
+ALTER TABLE metadata ADD COLUMN priority int NOT NULL DEFAULT 10;
+
+CREATE INDEX metadata_priority_create_time_index ON metadata(priority,
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 8d333bda2..139f70d3b 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
@@ -26,6 +26,7 @@ CREATE TABLE metadata(
engine_state varchar(32), -- the engine application state
engine_error clob, -- the engine application diagnose
end_time bigint, -- the metadata end time
+ priority int NOT NULL DEFAULT 10, -- the application priority, high value
means high priority
peer_instance_closed boolean default FALSE -- closed by peer kyuubi
instance
);
@@ -36,3 +37,5 @@ 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);
+
+CREATE INDEX metadata_priority_create_time_index ON metadata(priority,
create_time);
diff --git
a/kyuubi-server/src/main/resources/sql/mysql/005-KYUUBI-5327.mysql.sql
b/kyuubi-server/src/main/resources/sql/mysql/005-KYUUBI-5327.mysql.sql
new file mode 100644
index 000000000..0637e053d
--- /dev/null
+++ b/kyuubi-server/src/main/resources/sql/mysql/005-KYUUBI-5327.mysql.sql
@@ -0,0 +1,13 @@
+SELECT '< KYUUBI-5327: Introduce priority in metadata' AS ' ';
+
+ALTER TABLE metadata ADD COLUMN priority int NOT NULL DEFAULT 10 COMMENT 'the
application priority, high value means high priority';
+
+-- In MySQL 5.7, A key_part specification can end with ASC or DESC.
+-- These keywords are permitted for future extensions for specifying ascending
or descending index value storage.
+-- Currently, they are parsed but ignored; index values are always stored in
ascending order.
+-- In MySQL 8 this can take effect and this index will be hit if query order
by priority DESC, create_time ASC.
+-- See more detail in:
+-- https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
+-- https://dev.mysql.com/doc/refman/8.0/en/create-index.html
+-- https://dev.mysql.com/doc/refman/5.7/en/create-index.html
+ALTER TABLE metadata ADD INDEX priority_create_time_index(priority DESC,
create_time ASC);
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 77df8fa05..fb2019848 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
@@ -24,9 +24,12 @@ CREATE TABLE IF NOT EXISTS metadata(
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',
+ priority int NOT NULL DEFAULT 10 COMMENT 'the application priority, high
value means high priority',
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 create_time_index(create_time)
+ INDEX create_time_index(create_time),
+ -- See more detail about this index in ./005-KYUUBI-5327.mysql.sql
+ INDEX priority_create_time_index(priority DESC, create_time ASC)
) 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 473997448..0dd3abfda 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,4 +1,5 @@
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;
+SOURCE 005-KYUUBI-5327.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 656de6e5d..aa50267eb 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
@@ -24,6 +24,7 @@ CREATE TABLE IF NOT EXISTS metadata(
engine_state varchar(32), -- the engine application state
engine_error mediumtext, -- the engine application diagnose
end_time bigint, -- the metadata end time
+ priority INTEGER NOT NULL DEFAULT 10, -- the application priority, high
value means high priority
peer_instance_closed boolean default '0' -- closed by peer kyuubi instance
);
@@ -34,3 +35,5 @@ 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);
+
+CREATE INDEX IF NOT EXISTS metadata_priority_create_time_index ON
metadata(priority, create_time);
