0

I use MySQL 8.0.

How I can know when MySQL server was slow shutdown?

  1. I start mysql server

    /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf --user=mysql
    
  2. I shutdown mysql server

    /usr/local/mysql/bin/mysqladmin --defaults-file=/usr/local/mysql/my.cnf -pmypassword shutdown
    

Maybe this information is written in log?

5
  • From MySQL Command Prompt, SELECT @@log_error; to reveal your error log filename. Congratulations on using SHUTDOWN, many people do not and then they have no record of the shutdown. Commented Mar 28, 2020 at 20:58
  • @WilsonHauck Thanks. But I do not see the record of slow shutdown: Received SHUTDOWN from user root. Shutting down mysqld /usr/local/mysql/bin/mysqld: Forcing close of thread 9 user: 'root'. Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL. Commented Mar 28, 2020 at 22:21
  • You can choose to shutdown MySQL as an administrator for your own reason. There is no where within MySQL to record the reason you shut it down. You may wish to keep your own 'log' for why it was shutdown. My apologies for misunderstanding your question. If MySQL was shutdown because of slow queries, it would be in this error log. I do not ever recall seeing this situation in 6 years of my use of MySQL. Commented Mar 29, 2020 at 1:46
  • MySQL does have the ability to log Slow Queries. This URL is a 'how to' for version 5.5 but the instructions are still good for today's versions. a2hosting.com/kb/developer-corner/mysql/… Commented Mar 29, 2020 at 1:52
  • @WilsonHauck Thank you. Commented Mar 29, 2020 at 1:52

1 Answer 1

1

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:

  1. How many memory blocks of your database are swapped out.
  2. How many pages are dirty and must be written to disk.
  3. 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?

5
  • Thank you. But if make mysqladmin --defaults-file=/usr/local/mysql/my.cnf -pmypassword shutdown can get record in error log Shutting down mysqld (Version: 8.0.19).. But Shutting down == slow shutdown/clear shutdown? Commented Mar 30, 2020 at 11:30
  • There is only one SHUTDOWN command, no slow or clear/clean. As soon as you have received the message: bin/mysqld: Shutdown complete It was a safe and clean shutdown. The only thing which comes to my mind is innodb_fast_shutdown = {0 | 1 | 2} dev.mysql.com/doc/refman/8.0/en/… to make shutdown slower or faster. But why do you want to do this?
    – shinguz
    Commented Apr 1, 2020 at 10:46
  • I want to know for physical cold backuping. I tested with innodb_fast_shutdown=2 and get this same - Shutdown complete (mysqld 8.0.19) Commented Apr 1, 2020 at 13:08
  • That is completely fine. Just do a physical cold backup when you have received this message AND no more mysqld is running...
    – shinguz
    Commented Apr 1, 2020 at 14:34
  • When appeared record "Shutdown complete (mysqld 8.0.19)" is it == "shutdown clear/cold"? Commented Apr 1, 2020 at 22:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.