Bug #95895 Shutdown takes long time when table uses AUTOINC and FOREIGN KEY constraints

Submitted: 20 Jun 2019 10:40 Modified: 25 Jun 2019 6:46
Reporter: Satya Bodapati (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7, 5.7.26, 5.7.27, 8.0.17 OS:Any
Assigned to: CPU Architecture:Any

[20 Jun 2019 10:40] Satya Bodapati
Description:
1. Create 2 Million tables ensure all have AUTOINC and FOREIGN KEY constraints.
2. Do SELECT on all the  tables. Or just LOCK TABLE t1 FOR READ; UNLOCK TABLES
3. Shutdown the server

Shutdown takes 15mins (in my testing environment) 

How to repeat:
Attaching mtr testcase. Ensure this is tried on big machine which has huge amount of RAM.

Buffer pool of 20GB is used and InnoDB table cache uses another 13-15GB.

Suggested fix:
Reduce shutdown time
[20 Jun 2019 10:42] Satya Bodapati
mtr testcase

Attachment: percona-slow-shutdown-master.opt (application/octet-stream, text), 329 bytes.

[20 Jun 2019 10:43] Satya Bodapati
mtr testcase

Attachment: percona-slow-shutdown.test (application/octet-stream, text), 2.03 KiB.

[20 Jun 2019 10:51] Satya Bodapati
Testcase needs around 40GB RAM and 100GB disk atleast.
[20 Jun 2019 13:50] Satya Bodapati
Also to calculate shutdown time from error log, I wrote a simple shell script.

Sample usage: /extract_shutdown_time.sh /ssd/satya/data/tmp/satya-mysqld.err 
STARTTIME string is 2019-06-20T10:32:39.120341Z
ENDTIME string is 2019-06-20T10:33:16.566888Z
differnce is 37 seconds
[20 Jun 2019 13:50] Satya Bodapati
shutdown time extraction

Attachment: extract_shutdown_time.sh (application/x-shellscript, text), 486 bytes.

[20 Jun 2019 13:53] Satya Bodapati
This script is OK because above testcase creates separate error log for its run.

--let $restart_parameters= "restart: --log-error=$MYSQL_TMP_DIR/satya-mysqld.err"
[20 Jun 2019 14:08] Satya Bodapati
"InnoDB table cache uses another 13-15GB." --> Why? See https://bugs.mysql.com/bug.php?id=95898

Short answer: table has FK
[21 Jun 2019 8:23] Satya Bodapati
This is reported as percona bug: See https://jira.percona.com/browse/PS-5639 for more details, analysis, fix etc.
[21 Jun 2019 12:36] MySQL Verification Team
Hello Satya,

Thank you for the report and test case.
Verified as described with 5.7.26 build.

regards,
Umesh
[25 Jun 2019 6:46] Satya Bodapati
Patch contributed : https://github.com/mysql/mysql-server/pull/274
[25 Jun 2019 15:13] OCA Admin
Contribution submitted via Github - BUG#95895 - Shutdown takes long time when table uses AUTOINC and FORE… 
(*) Contribution by Satya Bodapati (Github satya-bodapati, mysql-server/pull/274#issuecomment-505309650): "I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it."

Contribution: git_patch_291409149.txt (text/plain), 8.26 KiB.

[13 Aug 2019 10:11] lalit Choudhary
Also exists in 8.0 version, tested with mysql 8.0.17

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

Page generated in 0.068 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.