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

[20 Dec 2016 19:27] Peter Zaitsev
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
[21 Dec 2016 9:07] Umesh Shastry
Hello Peter,

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

Thanks,
Umesh
[21 Dec 2016 9:08] Umesh Shastry
5.6.35 - test results

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

[21 Dec 2016 17:24] Peter Zaitsev
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.