MySQL Bugs: #84286: Permission checking does not scale with large number of tables

Bug #84286	Permission checking does not scale with large number of tables
Submitted:	20 Dec 2016 19:27	Modified:	21 Dec 2016 17:24
Reporter:	Peter Zaitsev	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.6, 5.6.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have been testing MySQL with many tables (1M) and while generally it works well there is an issue with permission check taking a lot of time while doing mysqldump of the table

With perf top  I can see there is a lot of time spent sorting data in MySQL:

◆
  30.90%  libc-2.23.so             [.] __strcmp_sse2_unaligned                                                                                                 ▒
  16.21%  mysqld                   [.] my_qsort                                                                                                                ▒
  10.00%  libc-2.23.so             [.] strrchr      

In the processlist I see:

mysql> select time,state,info from information_schema.processlist;
+------+----------------------+------------------------------------------------------------+
| time | state                | info                                                       |
+------+----------------------+------------------------------------------------------------+
|    0 | executing            | select time,state,info from information_schema.processlist |
|    2 | checking permissions | SHOW TRIGGERS LIKE 'sbtest100013'                          |
+------+----------------------+------------------------------------------------------------+
2 rows in set (0.00 sec)

Note it spends 1-2 seconds per table which is WAY too much

How to repeat:
Prepare tables with sysbench  (SSD recommended)

sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua   --report-interval=1 --oltp-tables-count=1000000 --oltp-table-size=10 --max-time=0 --oltp-read-only=off --max-requests=0 --num-threads=64 --rand-type=pareto --db-driver=mysql --mysql-user=sbtest --mysql-password=sbtest --mysql-db=sbtest prepare

Run MySQLDump to dump the database

mysqldump --single-transaction sbtest > backup.sql

After mysqldump completes its information_schema queries we will see results above

Hello Peter,

Thank you for the report and steps.
Observed this with 5.6.35 build.

Thanks,
Umesh

5.6.35 - test results

Attachment: 84286.results (application/octet-stream, text), 233.17 KiB.

I wanted to point out Performance Schema in 5.6 has a lot of issues with large number of tables as well. However even disabling performance schema completely still shows the problem.