1

I have a droplet on DigitalOcean created using Laravel Forge 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 33 of them).

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.

This droplet has been running during 6 months but this problem only started last week. 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.

Is this a MySQL-settings related issue?

TJ is too short
  • 827
  • 3
  • 15
  • 35

2 Answers2

1

Try increasing innodb_buffer_pool_size in my.cnf

The recommendation for a dedicated DB server is 80% - if you're already at that level then you should consider moving to a bigger instance type.

Oliver
  • 11,857
  • 2
  • 36
  • 42
  • Thanks very much for your reply @Oliver! I'm on a server with 16GB RAM, so I changed the innodb_buffer_pool_size to 13GB (before it was with its default value) and made a test: I ran a script to remove all records of a table and add them again, a total of 30k records. Seems better, the number of error messages is lower, but they are still there: "InnoDB: page_cleaner: 1000ms intended loop took 4228ms. The settings might not be optimal." – TJ is too short Nov 15 '18 at 08:39
  • That warning is expected if you churn through a lot of records pages like you seem to do, see e.g. https://stackoverflow.com/questions/41134785 for more details. – Oliver Nov 15 '18 at 15:10
1

in the my.cnf set this value:

innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12
innodb_page_cleaners = 12
Nikolai Stoliarenko
  • 41
  • 5