Percona 5.7 won't ever stop - even after 5 hours
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 I do this Code:
mysql> set global innodb_fast_shutdown = 0; I then do Code:
# systemctl disable mysqld Code:
2020-02-19T13:05:29.080591+02:00 0 [Note] InnoDB: Buffer pool(s) dump completed at 200219 13:05:29 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 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! |
Duplicate thread.
|
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; 2. I set innodb_max_dirty_pages_pct to 0 Code:
set global innodb_max_dirty_pages_pct = 0; Code:
show status like '%dirty%'; 4. Then, stop the server: Code:
# systemctl disable mysqld Code:
# rsync -avh --progress --no-whole-file --partial /var/lib/mysql/* /mnt/backup/mysql_backup_2020_02_19/. Code:
# systemctl start mysqld 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. |
All times are GMT -5. The time now is 11:01 AM. |