Know when MySQL server was slow shutdown?
Priviet Юрий

You question puzzled me a bit first. The answer to your question was too easy and this is 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 MariaDB/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. So MySQL messed it up somehow in 8.0. Too much clean-up!

If you want to get the old behaviour 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/.log or /var/log/mysqld.log or /var/log/mysql/mysqld.log or similar.


Now about the "is"


When you are currently shutting down MySQL it is already to late to find it out. 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).


How much Swap must be swapped in you can find here: https://fromdual.com/mariadb-and-mysql-swap-analysis

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.

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