MySQL crashes often
up vote
0
down vote
favorite
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?
mysql digital-ocean laravel-forge
asked Nov 14 at 21:07
TJ is too short
125214
add a comment |
up vote
0
down vote
favorite
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?
mysql digital-ocean laravel-forge
asked Nov 14 at 21:07
TJ is too short
125214
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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?
mysql digital-ocean laravel-forge
asked Nov 14 at 21:07
TJ is too short
125214
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?
mysql digital-ocean laravel-forge
mysql digital-ocean laravel-forge
asked Nov 14 at 21:07
TJ is too short
125214
asked Nov 14 at 21:07
TJ is too short
125214
asked Nov 14 at 21:07
TJ is too short
125214
asked Nov 14 at 21:07
TJ is too short
125214
asked Nov 14 at 21:07
TJ is too short
125214
125214
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
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.
answered Nov 15 at 1:35
Oliver
3,23811926
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 at 8:39
That warning is expected if you churn through a lot of records pages like you seem to do, see e.g. stackoverflow.com/questions/41134785 for more details.
– Oliver
Nov 15 at 15:10
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
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.
answered Nov 15 at 1:35
Oliver
3,23811926
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 at 8:39
That warning is expected if you churn through a lot of records pages like you seem to do, see e.g. stackoverflow.com/questions/41134785 for more details.
– Oliver
Nov 15 at 15:10
add a comment |
up vote
0
down vote
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.
answered Nov 15 at 1:35
Oliver
3,23811926
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 at 8:39
That warning is expected if you churn through a lot of records pages like you seem to do, see e.g. stackoverflow.com/questions/41134785 for more details.
– Oliver
Nov 15 at 15:10
add a comment |
up vote
0
down vote
up vote
0
down vote
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.
answered Nov 15 at 1:35
Oliver
3,23811926
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.
answered Nov 15 at 1:35
Oliver
3,23811926
answered Nov 15 at 1:35
Oliver
3,23811926
answered Nov 15 at 1:35
Oliver
3,23811926
answered Nov 15 at 1:35
Oliver
3,23811926
3,23811926
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 at 8:39
That warning is expected if you churn through a lot of records pages like you seem to do, see e.g. stackoverflow.com/questions/41134785 for more details.
– Oliver
Nov 15 at 15:10
add a comment |
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 at 8:39
That warning is expected if you churn through a lot of records pages like you seem to do, see e.g. stackoverflow.com/questions/41134785 for more details.
– Oliver
Nov 15 at 15:10
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 at 8:39
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 at 8:39
That warning is expected if you churn through a lot of records pages like you seem to do, see e.g. stackoverflow.com/questions/41134785 for more details.
– Oliver
Nov 15 at 15:10
That warning is expected if you churn through a lot of records pages like you seem to do, see e.g. stackoverflow.com/questions/41134785 for more details.
– Oliver
Nov 15 at 15:10
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53308726%2fmysql-crashes-often%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
