Shutdown with MySQL 8

On StackExchange for Database Administrators I recently have seen a question which attracted my interest.

The question puzzled me a bit because the answer seems too easy. Further the question was not so clear. An all theses factors smell dangerous...

About time - was, is and will be

How can I find out if the database "was" shutdown slowly? This is quite easy: Look into your MySQL Error Log and there you will find a log sequence similar to the following:

2020-03-30T08:03:36.928017Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.

Ups! There are no more "shutting down ..." messages like in MySQL 5.7:

2020-03-30T08:04:49.898254Z 0 [Note] Giving 1 client threads a chance to die gracefully
2020-03-30T08:04:49.898266Z 0 [Note] Shutting down slave threads
2020-03-30T08:04:51.898389Z 0 [Note] Forcefully disconnecting 1 remaining clients
2020-03-30T08:04:51.898433Z 0 [Warning] bin/mysqld: Forcing close of thread 115  user: 'enswitch'
2020-03-30T08:04:51.898512Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2020-03-30T08:04:51.924644Z 0 [Note] Binlog end
2020-03-30T08:04:51.938518Z 0 [Note] Shutting down plugin 'ngram'
...
2020-03-30T08:04:53.296239Z 0 [Note] Shutting down plugin 'binlog'
2020-03-30T08:04:53.296805Z 0 [Note] bin/mysqld: Shutdown complete

So you cannot find out, when shutdown started any more and thus you cannot say how long it took to shutdown MySQL. So MySQL messed it up somehow in 8.0. Too much clean-up!

If you want to get the old behaviour back you can stop MySQL 8 as follows:

SQL> SET GLOBAL log_error_verbosity = 3;
SQL> SHUTDOWN;

or just add the variable to your MySQL configuration file (my.cnf).

Then you will find the old shutdown sequence in your error log as before:

2020-03-30T08:13:55.071627Z 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.19).
2020-03-30T08:13:55.178119Z 0 [Note] [MY-010067] [Server] Giving 1 client threads a chance to die gracefully
2020-03-30T08:13:55.178210Z 0 [Note] [MY-010117] [Server] Shutting down slave threads
...
2020-03-30T08:13:56.588574Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.

If you want to know where your MySQL Error Log File is located you can find it like this:

SQL> SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| log_error     | /home/mysql/database/mysql-80/log/error.log |
+---------------+---------------------------------------------+

Typical locations are: /var/lib/mysql/<hostname>.log or /var/log/mysqld.log or /var/log/mysql/mysqld.log or /var/log/messages or similar.

Now about the "is"

When you are currently shutting down MySQL it is already to late to find it out when it started. Because you cannot connect to the database any more to change the settings and you do not see anything in the MySQL Error log. Possibly you can look at the error log with stat and you can see when the last message was written to it to find the start of the shutdown.

 shell> stat error.log 
  File: error.log
  Size: 29929           Blocks: 64         IO Block: 4096   regular file
Device: 801h/2049d      Inode: 5373953     Links: 1
Access: (0640/-rw-r-----)  Uid: ( 1001/   mysql)   Gid: ( 1001/   mysql)
Access: 2020-03-30 10:13:59.491446560 +0200
Modify: 2020-03-30 10:13:56.587467485 +0200
Change: 2020-03-30 10:13:56.587467485 +0200
 Birth: -

Symptoms for a working shutdown is either heavy writing to disk (iostat -xk 1) or heavy swapping in (vmstat). You have to wait until finished. Some brave people use a kill -9 in such a case, if they have InnoDB only and if they know exactly what they are doing and how much time a following crash recovery will take.

And finally about the question "how long will the shutdown take" (will be)

This is not so easy to predict. It depends on several things:

• How many memory blocks of your database are swapped out.
• How many pages are dirty and must be written to disk.
• How fast is your I/O system (IOPS).

You can gather these information as follows:

• How much Swap must be swapped in you can find here.
• The number of dirty pages (pages modified but not written to disk yet) you can find with:

  SQL> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

• And about the IOPS you have to ask your hardware spec sheet (150 to 100'000 IOPS).

I hope with this answer I have covered all the possibly questions about shutting down MySQL 8.0?