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