Bug #87827 | Performance regression in "create table" speed and scalability in 8.0.3 | ||
---|---|---|---|
Submitted: | 21 Sep 2017 14:09 | Modified: | 19 Jan 2018 20:28 |
Reporter: | Alexander Rubin | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
Version: | 8.0.3 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[21 Sep 2017 14:09]
Alexander Rubin
[22 Sep 2017 13:39]
MySQL Verification Team
Hello Alexander, Thank you for the report and test case. With the provided test case I'm observing a noticeable drop in 8.0.3 but not that much high which you had noticed. I'm joining activity log shortly. Regards, Umesh
[22 Sep 2017 13:42]
MySQL Verification Team
Test results - 8.0.0, 8.0.1, 8.0.2,8.0.3
Attachment: 87827_8_0_1_2_3.results (application/octet-stream, text), 105.27 KiB.
[25 Sep 2017 19:58]
Alexander Rubin
Can it be related to this: "InnoDB: Serialized Dictionary Information (SDI) is now present in all InnoDB tablespace files except for temporary tablespace and undo tablespace files. SDI is serialized metadata for schema, table, and tablespace objects. The presence of SDI data provides metadata redundancy. For example, dictionary object metadata may be extracted from tablespace files if the data dictionary becomes unavailable. SDI extraction is performed using the ibd2sdi tool. SDI data is stored in JSON format. The inclusion of SDI data in tablespace files increases tablespace file size. An SDI record requires a single index page, which is 16k in size by default. However, SDI data is compressed when it is stored to reduce the storage footprint." https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-3.html#mysqld-8-0-3-data-dictiona...
[2 Oct 2017 6:26]
MySQL Verification Team
Alex's related article, see https://www.percona.com/blog/2017/10/01/one-million-tables-mysql-8-0/
[2 Oct 2017 22:07]
Alexander Rubin
Running "Show engine innodb status" shows the most frequent one is: | InnoDB | rwlock: dict0dict.cc:1101 | waits=1296771 |
[9 Oct 2017 8:19]
Bin Su
Posted by developer: From my previous testing, this should be due to new DDL log written under the dict mutex and lock, which makes the dict_operation_lock too hot. I'm having a patch to remove the dict_operation_lock for CREATE TABLE at hand. The performance result looks promising, I will test it a bit more.
[19 Jan 2018 20:28]
Daniel Price
Posted by developer: Fixed as of the upcoming 8.0.5, 9.0.0 release, and here's the changelog entry: Locks related to data dictionary operations caused a degradation in CREATE TABLE performance.