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
Just for confirmation: correction has been benchmarked using java connector and that shows that performance doesn't decrease using 400/4K/20K parameters.
Diego Dupin
added a comment - - edited Just for confirmation: correction has been benchmarked using java connector and that shows that performance doesn't decrease using 400/4K/20K parameters.
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
Ramesh Sivaraman
added a comment - 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
Task
Critical
CONJ-684 Performance of con.getMetaData().getProcedureColumns(null, null, name, "%") is much lower than mysql jdbc
Just for confirmation: correction has been benchmarked using java connector and that shows that performance doesn't decrease using 400/4K/20K parameters.