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] MySQL Verification Team
Hello Peter,

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

Thanks,
Umesh
[21 Dec 2016 9:08] MySQL Verification Team
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.