[SOLVED] Percona 5.7 won't ever stop

rylan76 · 02-19-2020, 05:20 AM

Hi Guys

I have a Percona 5.7 instance in Centos 7 on an i7 with 16GB RAM with MySQL living on a 1TB SSD - with these my.cnf settings:

Code:

innodb_buffer_pool_size=10148M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=0
innodb_log_file_size=680M
innodb_file_per_table=on
innodb_flush_method=O_DIRECT
innodb_track_changed_pages=1

I can no longer get the MySQL process to stop in an orderly fashion so I can take a backup by copying the database directory.

I do this

Code:

mysql> set global innodb_fast_shutdown = 0;
mysql> set global innodb_max_dirty_pages_pct = 0;

then I wait until the max dirty pages are indicated as 0.

I then do

Code:

# systemctl disable mysqld
# systemctl stop mysqld

However, after five hours I have mysqld.log just endlessly filled with

Code:

2020-02-19T13:05:29.080591+02:00 0 [Note] InnoDB: Buffer pool(s) dump completed at 200219 13:05:29
2020-02-19T13:06:29.104101+02:00 0 [Note] InnoDB: Waiting for purge thread to be suspended
2020-02-19T13:07:29.268639+02:00 0 [Note] InnoDB: Waiting for purge thread to be suspended
2020-02-19T13:08:29.434364+02:00 0 [Note] InnoDB: Waiting for purge thread to be suspended
2020-02-19T13:09:29.600916+02:00 0 [Note] InnoDB: Waiting for purge thread to be suspended
2020-02-19T13:10:29.767106+02:00 0 [Note] InnoDB: Waiting for purge thread to be suspended
2020-02-19T13:11:29.933535+02:00 0 [Note] InnoDB: Waiting for purge thread to be suspended
2020-02-19T13:12:30.099526+02:00 0 [Note] InnoDB: Waiting for purge thread to be suspended
2020-02-19T13:13:30.266302+02:00 0 [Note] InnoDB: Waiting for purge thread to be suspended
2020-02-19T13:14:30.432848+02:00 0 [Note] InnoDB: Waiting for purge thread to be suspended

Even after 5 hours, it still is producing these messages and simply not shutting down.

Ever.

Left if or another hour (total of 6 hours for it to shut down) and it is still busy, posting the above once a minute to /var/log/mysqld.log

Any idea what I can do or what I can look at?

Can I safely kill -9 the MySQLD process?

I also cannot use Percona xtrabackup to do a hot backup as it also fails to backup the server, in its apply-log phase it is endlessly stuck for up to 16 hours at a time (before I abort) with

Code:

InnoDB: Waiting for 1 active transactions to finish
InnoDB: Waiting for 1 active transactions to finish
InnoDB: Waiting for 1 active transactions to finish

forever.

My server passes, in every table, the MySQL check table ... extended command for every table in the DB.

E. g. my DB is not corrupt.

The db is about 400GB in total.

What can I do to get MySQL to shut down in under 6 hours, or possibly just shut down sometime in forever?

Thx!

sevendogsbsd · 02-19-2020, 09:22 AM

Duplicate thread.

rylan76 · 02-21-2020, 09:04 AM

Eventually managed to solve this (e. g. get Percona to stop so it can be backed up) by doing the following.

1. I set innodb_fast_shutdown to 2 (e. g. execute the fastest shutdown possible.)

Code:

set global innodb_fast_shutdown = 2;

in the MySQL CLI.

2. I set innodb_max_dirty_pages_pct to 0

Code:

set global innodb_max_dirty_pages_pct = 0;

3. Wait until innodb_max_dirty_pages is 0:

Code:

show status like '%dirty%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_dirty | 1     |
| Innodb_buffer_pool_bytes_dirty | 16384 |
+--------------------------------+-------+
2 rows in set (0.15 sec)

mysql>

and wait until the dirty pages reach 0.

4. Then, stop the server:

Code:

# systemctl disable mysqld
# systemctl stop mysqld
# systemctl enable mysqld

5. Now copy /var/lib/mysql to backup location:

Code:

# rsync -avh --progress --no-whole-file --partial /var/lib/mysql/* /mnt/backup/mysql_backup_2020_02_19/.

6. Restart Percona:

Code:

# systemctl start mysqld

7. MySQL will now do a recovery due to it being shutdown uncleanly using innodb_fast_shutdown = 2.

However, this recovery is guaranteed to work as it was requested to do a fast shutdown by setting innodb_fast_shutdown = 2 before requesting it to stop itself.

8. Similarly, I then tested the backed-up database on another server by copying it into that server's /var/lib/mysql directory (after removing the old databasse), and then trying to start the same version of Percona / MySQL on it.

That worked as well, and the recovery there was exactly the same as on the live server. It also worked perfectly.

E. g. it is not necessary to shut down the Percona / MySQL DB instance cleanly to be able to back it up. It can be shut down with an emergency shutdown which is 1000s of times faster by first setting innodb_fast_shutdown = 2 in the MySQL CLI, then doing a shutdown, and then copying the database directory.

The only problem is that there MUST be a recovery done first if you ever restore that backup in an emergency when your primary server is blown or you lost the database. Apparently the recovery times can get extreme if you have a very large buffer pool or innodb log file, but mine is relatively tine (10GB buffer pool and 680MB log file) so recovery is virtually instantaneous on an SSD with a Core i7.

Hope this helps somebody - you don't NEED to wait possibly hours and hours for MySQL to shut down with innodb_fast_shutdown = 0, you can shut down uncleanly with innodb_fast_shutdown = 2, and the recovery when you restore the emergency-stopped database is very very fast, vs. the hours and hours you might wait (or waiting forever) for the DB to stop cleanly with a innodb_fast_shutdown = 0 shutdown.