Same with MySQL 5.5, 5.7.
Also, same is true for MariaDB 10.1 compound statements, so it's not related to the SP call as such.
Here is some noticeable difference from P_S (although maybe it's not the difference that causes the performance problem, but it might be):
select event_name, count_star, sum_timer_wait from
|
performance_schema.events_waits_summary_global_by_event_name order by
|
sum_timer_wait desc limit 5;
|
BENCHMARK
|
|
+--------------------------------------------------------+------------+------------------+
|
| event_name | count_star | sum_timer_wait |
|
+--------------------------------------------------------+------------+------------------+
|
| idle | 155 | 1978525729000000 |
|
| wait/synch/mutex/sql/THD::LOCK_thd_data | 2000119 | 3819579725654 |
|
| wait/synch/mutex/sql/TABLE_SHARE::tdc.LOCK_table_share | 169 | 449129871 |
|
| wait/synch/rwlock/sql/MDL_lock::rwlock | 55 | 207316591 |
|
| wait/io/socket/sql/client_connection | 4 | 154879998 |
|
+--------------------------------------------------------+------------+------------------+
|
(these values appear in the table right after TRUNCATE, they are barely modified by the BENCHMARK)
Compound statement
|
+--------------------------------------------------------+------------+------------------+
|
| event_name | count_star | sum_timer_wait |
|
+--------------------------------------------------------+------------+------------------+
|
| idle | 174 | 2082422485000000 |
|
| wait/synch/mutex/sql/THD::LOCK_thd_data | 4000119 | 7561752296880 |
|
| wait/synch/mutex/sql/TABLE_SHARE::tdc.LOCK_table_share | 66 | 124610181 |
|
| wait/io/file/sql/FRM | 12 | 113567436 |
|
| wait/synch/rwlock/sql/MDL_lock::rwlock | 55 | 106337591 |
|
+--------------------------------------------------------+------------+------------------+
|
Stored procedure
|
+--------------------------------------------------------+------------+------------------+
|
| event_name | count_star | sum_timer_wait |
|
+--------------------------------------------------------+------------+------------------+
|
| idle | 194 | 2441441158000000 |
|
| wait/synch/mutex/sql/THD::LOCK_thd_data | 4000121 | 7569150979606 |
|
| wait/synch/mutex/sql/TABLE_SHARE::tdc.LOCK_table_share | 93 | 186116029 |
|
| wait/io/file/sql/FRM | 16 | 134974565 |
|
| wait/synch/rwlock/sql/MDL_lock::rwlock | 57 | 117695843 |
|
+--------------------------------------------------------+------------+------------------+
|
select event_name, count_star, sum_timer_wait from
|
performance_schema.events_stages_summary_global_by_event_name order by
|
sum_timer_wait desc limit 5;
|
BENCHMARK
|
+--------------------------------+------------+----------------+
|
| event_name | count_star | sum_timer_wait |
|
+--------------------------------+------------+----------------+
|
| stage/sql/Opening tables | 16 | 2494021000 |
|
| stage/sql/init | 19 | 1333234000 |
|
| stage/sql/Sorting result | 1 | 1263361000 |
|
| stage/sql/checking permissions | 14 | 1005380000 |
|
| stage/sql/Unlocking tables | 14 | 312488000 |
|
+--------------------------------+------------+----------------+
|
Compound statement
|
+--------------------------------+------------+----------------+
|
| event_name | count_star | sum_timer_wait |
|
+--------------------------------+------------+----------------+
|
| stage/sql/closing tables | 2000018 | 13645989740000 |
|
| stage/sql/Opening tables | 2000016 | 4573616825000 |
|
| stage/sql/After opening tables | 2000016 | 4293346982000 |
|
| stage/sql/query end | 1000017 | 2311569542000 |
|
| stage/sql/Sorting result | 1 | 1145411000 |
|
+--------------------------------+------------+----------------+
|
Stored procedure
|
+--------------------------------+------------+----------------+
|
| event_name | count_star | sum_timer_wait |
|
+--------------------------------+------------+----------------+
|
| stage/sql/closing tables | 2000019 | 13698547142000 |
|
| stage/sql/Opening tables | 2000018 | 4561133838000 |
|
| stage/sql/After opening tables | 2000018 | 4291648574000 |
|
| stage/sql/query end | 1000017 | 2313252823000 |
|
| stage/sql/Sorting result | 1 | 1117100000 |
|
+--------------------------------+------------+----------------+
|
I'll leave it to serg to decide whether there is a bug in here.
Same with MySQL 5.5, 5.7.
Also, same is true for MariaDB 10.1 compound statements, so it's not related to the SP call as such.
Here is some noticeable difference from P_S (although maybe it's not the difference that causes the performance problem, but it might be):
BENCHMARK
+--------------------------------------------------------+------------+------------------+
| event_name | count_star | sum_timer_wait |
+--------------------------------------------------------+------------+------------------+
| idle | 155 | 1978525729000000 |
| wait/synch/mutex/sql/THD::LOCK_thd_data | 2000119 | 3819579725654 |
| wait/synch/mutex/sql/TABLE_SHARE::tdc.LOCK_table_share | 169 | 449129871 |
| wait/synch/rwlock/sql/MDL_lock::rwlock | 55 | 207316591 |
| wait/io/socket/sql/client_connection | 4 | 154879998 |
+--------------------------------------------------------+------------+------------------+
(these values appear in the table right after TRUNCATE, they are barely modified by the BENCHMARK)
Compound statement
+--------------------------------------------------------+------------+------------------+
| event_name | count_star | sum_timer_wait |
+--------------------------------------------------------+------------+------------------+
| idle | 174 | 2082422485000000 |
| wait/synch/mutex/sql/THD::LOCK_thd_data | 4000119 | 7561752296880 |
| wait/synch/mutex/sql/TABLE_SHARE::tdc.LOCK_table_share | 66 | 124610181 |
| wait/io/file/sql/FRM | 12 | 113567436 |
| wait/synch/rwlock/sql/MDL_lock::rwlock | 55 | 106337591 |
+--------------------------------------------------------+------------+------------------+
Stored procedure
+--------------------------------------------------------+------------+------------------+
| event_name | count_star | sum_timer_wait |
+--------------------------------------------------------+------------+------------------+
| idle | 194 | 2441441158000000 |
| wait/synch/mutex/sql/THD::LOCK_thd_data | 4000121 | 7569150979606 |
| wait/synch/mutex/sql/TABLE_SHARE::tdc.LOCK_table_share | 93 | 186116029 |
| wait/io/file/sql/FRM | 16 | 134974565 |
| wait/synch/rwlock/sql/MDL_lock::rwlock | 57 | 117695843 |
+--------------------------------------------------------+------------+------------------+
BENCHMARK
+--------------------------------+------------+----------------+
| event_name | count_star | sum_timer_wait |
+--------------------------------+------------+----------------+
| stage/sql/Opening tables | 16 | 2494021000 |
| stage/sql/init | 19 | 1333234000 |
| stage/sql/Sorting result | 1 | 1263361000 |
| stage/sql/checking permissions | 14 | 1005380000 |
| stage/sql/Unlocking tables | 14 | 312488000 |
+--------------------------------+------------+----------------+
Compound statement
+--------------------------------+------------+----------------+
| event_name | count_star | sum_timer_wait |
+--------------------------------+------------+----------------+
| stage/sql/closing tables | 2000018 | 13645989740000 |
| stage/sql/Opening tables | 2000016 | 4573616825000 |
| stage/sql/After opening tables | 2000016 | 4293346982000 |
| stage/sql/query end | 1000017 | 2311569542000 |
| stage/sql/Sorting result | 1 | 1145411000 |
+--------------------------------+------------+----------------+
Stored procedure
+--------------------------------+------------+----------------+
| event_name | count_star | sum_timer_wait |
+--------------------------------+------------+----------------+
| stage/sql/closing tables | 2000019 | 13698547142000 |
| stage/sql/Opening tables | 2000018 | 4561133838000 |
| stage/sql/After opening tables | 2000018 | 4291648574000 |
| stage/sql/query end | 1000017 | 2313252823000 |
| stage/sql/Sorting result | 1 | 1117100000 |
+--------------------------------+------------+----------------+
I'll leave it to serg to decide whether there is a bug in here.