Reading from information_schema.PARAMETERS table in MariaDB is slower than MySQL by a factor of 20x in the best of cases, and deteriorates linearly in MariaDB once the number of reading threads exceeds the number of available cores, whereas in MySQL the deterioration remains more-or-less flat.
The following table shows relative performance running mysqlslap against the dataset provided in the attached sql file cs0401486-test.sql with this series of commands
for i in 1 5 20 100
do
mysqlslap -q "select * from information_schema.parameters where SPECIFIC_NAME = 'aaa_sp50001'" -i 10 -c $i
done
Tests conducted in CentOS 7 machines with 1G RAM and approx. 800 real IOPS with one and four Intel i7-9750H cores @ 2.592GHz, average times reported:
CoresThreads MDB 6.7 MySQL 8 Diff %
1 1 0.193 0.008 2413%
1 5 0.901 0.015 6007%
4 1 0.171 0.004 4275%
4 5 0.296 0.007 4229%
4 20 1.159 0.066 1756%
4 100 19.017 0.340 5593%
Timings all on MariaDB 10.6.7. Confirmed on 10.6.8, 10.7.4, and 10.8.3 in Rocky Linux.
Short overview of how the Data Dictionary architecture has been reworked in MySQL 8.0: https://www.slideshare.net/StleDeraas/data-dictionary-pl17. One of the reasons mentioned is low performance of queries to INFORMATION_SCHEMA tables.
Oleg Smirnov
added a comment - Short overview of how the Data Dictionary architecture has been reworked in MySQL 8.0: https://www.slideshare.net/StleDeraas/data-dictionary-pl17 . One of the reasons mentioned is low performance of queries to INFORMATION_SCHEMA tables.
MDEV-29104 Optimize queries to INFORMATION_SCHEMA.PARAMETERS/ROUTINES
For queries like
"SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME='proc_name'"
and
"SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='proc_name'"
there is a possibility to avoid loading a stored procedure code and
parsing it to retrieve parameters.
If the name of the procedure/function is specified explicitly then
it is possible to filter out routines that do not match at
an early stage.
Oleg Smirnov
added a comment - Implemented an optimization suggested by psergei :
commit ac6d5ff303323a45b0125a02f41c760b30d0e1fd (HEAD -> bb-10.11-MDEV-29104, origin/bb-10.11-MDEV-29104)
Author: Oleg Smirnov <olernov@gmail.com>
Date: Mon Sep 19 21:36:09 2022 +0700
MDEV-29104 Optimize queries to INFORMATION_SCHEMA.PARAMETERS/ROUTINES
For queries like
"SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME='proc_name'"
and
"SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='proc_name'"
there is a possibility to avoid loading a stored procedure code and
parsing it to retrieve parameters.
If the name of the procedure/function is specified explicitly then
it is possible to filter out routines that do not match at
an early stage.
Task
Major
Short overview of how the Data Dictionary architecture has been reworked in MySQL 8.0: https://www.slideshare.net/StleDeraas/data-dictionary-pl17. One of the reasons mentioned is low performance of queries to INFORMATION_SCHEMA tables.