I've created a droplet on DigitalOcean created using Laravel Forge 6 months ago. Two weeks ago, we decided it was time to upgrade the droplet and we moved from a 4GB RAM/2CPUs to a 16GB RAM/6CPUs droplet and since a few days ago the MySQL server just crashes and the only way to make it work again is by rebooting the server (MySQL makes the server unresponsive).
When I type htop to see the list of processes is showing a few of /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysql.pid
(currently is showing more than 30 entries like that).
The error log is bigger than 1GB (yes, I know!) and shows this message hundreds of times:
[Warning] InnoDB: Difficult to find free blocks in the buffer pool (21 search iterations)! 21 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 167678974 OS file reads, 2271392 OS file writes, 758043 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.
The only thing that changed recently is now we send weekly notifications to customers (only the ones that subscribed to it) to let them know about certain events happening in the current week. This is kind of a intensive process, because we have a few thousands of customers, but we take advantage of Laravel Queues in order to process everything.
I've tried to change innodb_buffer_pool_size
from the default value to 80% of available RAM (~13GB) and instead of the previous message, now it's showing:
"InnoDB: page_cleaner: 1000ms intended loop took 4228ms. The settings might not be optimal."
.
And this change made the database run slower. For example, to process 30k records (the notifications thing that I mentioned) it took 6 hours but before the change it was taking around 3 (when it didn't crashed).
Is this a MySQL-settings related issue?
EDIT: Global Status and Variables after innodb_* suggested changes
SHOW VARIABLES LIKE 'innodb%';
TRUNCATE
against a MyISAM table or an InnoDB table? How did you do the reinsert?LOAD DATA
? `INSERT one row at a time? Batch insert?delete from where condition
. It was made against a InnoDB table. The reinsert was a script that loads information from an external system (in JSON format), parses the information and inserts the records one row at a time. Thanks!