Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4(EOL), 10.5
Description
Queries like these demonstrate performance degatation in 10.4 over 10.3:
SELECT temporal_literal FROM t1; |
SELECT temporal_literal + 1 FROM t1; |
SELECT COUNT(*) FROM t1 WHERE temporal_column = temporal_literal; |
SELECT COUNT(*) FROM t1 WHERE temporal_column = string_literal; |
Also, 10.4 is slower than MySQL-8.0 (tested againts MySQL-8.0.21).
The problem happens because of excessive Time(), Date(), Datetime() instantiation in value methods.
For example:
Item_date_literal
|
{
|
..
|
longlong val_int() { return Date(this).to_longlong(); } |
double val_real() { return Date(this).to_double(); } |
String *val_str(String *to) { return Date(this).to_string(to); } |
my_decimal *val_decimal(my_decimal *to) { return Date(this).to_decimal(to); } |
};
|
Notice, the Date() instance is created in every value method call.
This involves copying from MYSQL_TIME in Item_temporal_literal::cached_time to MYSQL_TIME inside Date().
Changing the universal member Item_temporal_literal::cached_time to data type specific members and using them directly solve the problem:
class Item_date_literal |
{
|
Date cached_time;
|
public: |
longlong val_int() { ... return cached_time.to_longlong(); } |
double val_real() { ... return cached_time.to_double(); } |
String *val_str(String *to) { ... return cached_time.to_string(to); } |
my_decimal *val_decimal(my_decimal *to) { ...return cached_time.to_decimal(to); } |
};
|
Notice, there is no copying any more.
Also, this change makes futher separations of Date, Time, Datetime from each other, which will make it possible not to derive them from MYSQL_TIME, which is too heavy (40 bytes), and replace them to smaller data type specific containers.
Benchmarks
The numbers below are query time in seconds: the smaller - the better.
Tested against the following RelWithDebInfo binaries:
My80 - MySQL-8.0.21, commit f8cdce86448a211511e8a039c62580ae16cb96f5
|
10.3 - MariaDB 10.3.25, commit 90c8d773ed0ea7de5e735e836e1e33a190711f17
|
10.4 (orig) - MariaDB 10.4.15, commit aa6cb7ed03bb41b7ba59b6d7c9197cf24d65a36d
|
10.4 (MDEV-23537) - MariaDB 10.4.15, commit 2e5d86f49e7ee538806fba68dc8c960d6acdd483
|
10.4 (fixed) - MariaDB 10.4.15, the current patch on top of MDEV-23537
|
Benchmark preparation:
### t3 is a table with 10 rows |
### t2 is a table with 1000 rows |
### t1 is a HEAP table created per test, with 1000*1000 rows |
 |
DROP TABLE IF EXISTS t3,t2; |
CREATE TABLE t3 (a INT); |
INSERT INTO t3 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
CREATE TABLE t2 AS SELECT 1 FROM t3 t3a, t3 t3b, t3 t3c; |
datetime_literal
SELECT BENCHMARK(10*1000*1000, TIMESTAMP'2001-01-01 10:20:30'); |
My80: 0.035 -- MySQL caches string representation
|
10.3: 0.218
|
10.4: 0.268 (orig)
|
10.4: 0.275 (MDEV-23537) ???
|
10.4: 0.123 (fixed)
|
datetime_literal + 1
SELECT BENCHMARK(10*1000*1000, TIMESTAMP'2001-01-01 10:20:30'+1); |
My80: 0.160
|
10.3: 0.190
|
10.4: 0.218 (orig)
|
10.4: 0.222 (MDEV-23537) ???
|
10.4: 0.084 (fixed)
|
datetime_column = string_constant
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a DATETIME DEFAULT '2001-01-01 10:20:30') ENGINE=HEAP; |
INSERT INTO t1 SELECT '2001-01-01 10:20:30' FROM t2,t2 t2a; |
SELECT COUNT(*) FROM t1 WHERE a='2001-01-01 10:20:30'; |
My80: 0.035
|
10.3: 0.049
|
10.4: 0.049 (orig)
|
10.4: 0.043 (MDEV-23537)
|
10.4: 0.035 (fixed)
|
datetime_column = datetime_literal
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a DATETIME DEFAULT '2001-01-01 10:20:30') ENGINE=HEAP; |
INSERT INTO t1 SELECT '2001-01-01 10:20:30' FROM t2,t2 t2a; |
SELECT COUNT(*) FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:30'; |
My80: 0.035
|
10.3: 0.053
|
10.4: 0.056 (orig)
|
10.4: 0.046 (MDEV-23537)
|
10.4: 0.035 (fixed)
|
time_literal
SELECT BENCHMARK(10*1000*1000, TIME'10:20:30'); |
My80: 0.035 -- MySQL caches string representation
|
10.3: 0.126
|
10.4: 0.132 (orig)
|
10.4: 0.136 (MDEV-23537) ???
|
10.4: 0.085 (fixed)
|
time_literal + 1
SELECT BENCHMARK(10*1000*1000, TIME'10:20:30'+1); |
My80: 0.114
|
10.3: 0.146
|
10.4: 0.135 (orig)
|
10.4: 0.135 (MDEV-23537)
|
10.4: 0.085 (fixed)
|
time_column = string_constant
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a TIME) ENGINE=HEAP; |
INSERT INTO t1 SELECT '10:20:30' FROM t2,t2 t2a; |
SELECT COUNT(*) FROM t1 WHERE a='10:20:30'; |
My80: 0.034
|
10.3: 0.038
|
10.4: 0.046 (orig)
|
10.4: 0.041 (MDEV-23537)
|
10.4: 0.034 (fixed)
|
time_column = time_literal
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a TIME) ENGINE=HEAP; |
INSERT INTO t1 SELECT '10:20:30' FROM t2,t2 t2a; |
SELECT COUNT(*) FROM t1 WHERE a=TIME'10:20:30'; |
My80: 0.035
|
10.3: 0.038
|
10.4: 0.046 (orig)
|
10.4: 0.040 (MDEV-23537)
|
10.4: 0.035 (fixed)
|
date_literal
SELECT BENCHMARK(10*1000*1000, DATE'2001-01-01'); |
My80: 0.037 -- MySQL caches string representation
|
10.3: 0.181
|
10.4: 0.207 (orig)
|
10.4: 0.209 (MDEV-23537) ???
|
10.4: 0.083 (fixed)
|
date_literal + 1
SELECT BENCHMARK(10*1000*1000, DATE'2001-01-01'+1); |
My80: 0.131
|
10.3: 0.189
|
10.4: 0.201 (orig)
|
10.4: 0.201 (MDEV-23537)
|
10.4: 0.079 (fixed)
|
date_column = string_constant
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a DATE) ENGINE=HEAP; |
INSERT INTO t1 SELECT '2001-01-01' FROM t2,t2 t2a; |
SELECT COUNT(*) FROM t1 WHERE a='2001-01-01'; |
My80: 0.038
|
10.3: 0.045
|
10.4: 0.045 (orig)
|
10.4: 0.040 (MDEV-23537)
|
10.4: 0.034 (fixed)
|
date_column = date_literal
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a DATE) ENGINE=HEAP; |
INSERT INTO t1 SELECT '2001-01-01' FROM t2,t2 t2a; |
SELECT COUNT(*) FROM t1 WHERE a=DATE'2001-01-01'; |
My80: 0.036
|
10.3: 0.051
|
10.4: 0.050 (orig)
|
10.4: 0.044 (MDEV-23537)
|
10.4: 0.035 (fixed)
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-