Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
It seems there is an issue when querying on I_S.PARAMETERS table (used in java metadata) :
when doing this kind of query (one result) :
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA = 'testj' AND SPECIFIC_NAME = 'p2_512'
It takes 15ms if 400 parameters in I_S.PARAMETERS table
It takes 65ms if 4K parameters
It takes 350ms if 20K parameters
It seems to execute a full table scan in INFORMATION_SCHEMA.PARAMETERS
The same query takes 6ms on mysql 8 with 20K same parameters
Attachments
Issue Links
- causes
-
CONJ-684 Performance of con.getMetaData().getProcedureColumns(null, null, name, "%") is much lower than mysql jdbc
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- is part of
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
Performance looks good when compared to 10.6 version.
|
10.11.0 9f7d2824779785506d1bb316540428990e815245 (Optimized) |
for i in 1 5 20 100; do /test/mtest/MD280922-mariadb-10.11.0-linux-x86_64-opt/bin/mysqlslap -uroot -S/test/mtest/MD280922-mariadb-10.11.0-linux-x86_64-opt/socket.sock -q "select * from information_schema.parameters where SPECIFIC_NAME = 'aaa_sp50001'; " -i 10 -c $i; done
|
Benchmark
|
Average number of seconds to run all queries: 0.021 seconds
|
Minimum number of seconds to run all queries: 0.010 seconds
|
Maximum number of seconds to run all queries: 0.039 seconds
|
Number of clients running queries: 1
|
Average number of queries per client: 1
|
|
|
Benchmark
|
Average number of seconds to run all queries: 0.080 seconds
|
Minimum number of seconds to run all queries: 0.058 seconds
|
Maximum number of seconds to run all queries: 0.095 seconds
|
Number of clients running queries: 5
|
Average number of queries per client: 1
|
|
|
Benchmark
|
Average number of seconds to run all queries: 0.434 seconds
|
Minimum number of seconds to run all queries: 0.411 seconds
|
Maximum number of seconds to run all queries: 0.450 seconds
|
Number of clients running queries: 20
|
Average number of queries per client: 1
|
|
|
Benchmark
|
Average number of seconds to run all queries: 2.793 seconds
|
Minimum number of seconds to run all queries: 2.754 seconds
|
Maximum number of seconds to run all queries: 2.859 seconds
|
Number of clients running queries: 100
|
Average number of queries per client: 1
|
Performance comparison using Perl and Python mysql connector
perl mysql connector
|
10.6.10
|
-----------
|
Queries: 1 | Execution time(seconds) : 0.237706
|
Queries: 5 | Execution time(seconds) : 1.392153
|
Queries: 20 | Execution time(seconds) : 5.468022
|
Queries: 100 | Execution time(seconds) : 27.572478
|
|
|
10.7.6
|
----------
|
Queries: 1 | Execution time(seconds) : 0.206260
|
Queries: 5 | Execution time(seconds) : 0.917974
|
Queries: 20 | Execution time(seconds) : 3.775994
|
Queries: 100 | Execution time(seconds) : 18.507874
|
|
|
10.11 Bug fix branch
|
-------------------------------
|
Queries: 1 | Execution time(seconds) : 0.010314
|
Queries: 5 | Execution time(seconds) : 0.049723
|
Queries: 20 | Execution time(seconds) : 0.195309
|
Queries: 100 | Execution time(seconds) : 0.975044
|
|
|
python mysql connector
|
10.6.10
|
----------
|
Queries: 5 Execution time(seconds) : 1.1055042743682861
|
Queries: 20 Execution time(seconds) : 5.25916600227356
|
Queries: 100 Execution time(seconds) : 27.54954743385315
|
|
|
10.7.6
|
---------
|
Queries: 5 Execution time(seconds) : 0.7787322998046875
|
Queries: 20 Execution time(seconds) : 3.5663089752197266
|
Queries: 100 Execution time(seconds) : 18.440176963806152
|
|
|
10.11 Bug fix branch
|
-------------------------------
|
Queries: 5 Execution time(seconds) : 0.04043316841125488
|
Queries: 20 Execution time(seconds) : 0.18743038177490234
|
Queries: 100 Execution time(seconds) : 0.9795618057250977
|
Just for confirmation: correction has been benchmarked using java connector and that shows that performance doesn't decrease using 400/4K/20K parameters.