Skip to main content

InnoDB seems to be crashing

Jake B.
Every few days InnoDB on one of our servers has been crashing, but I can't quite track down why. This is what is in our error log:
151020 13:51:55 InnoDB: Waiting for the background threads to start 151020 13:51:56 InnoDB: 5.5.45 started; log sequence number 37941103 151020 13:51:56 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 151020 13:51:56 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 151020 13:51:56 [Note] Server socket created on IP: '0.0.0.0'. 151020 13:51:56 [Note] Event Scheduler: Loaded 0 events 151020 13:51:56 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.45-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 151021 04:39:37 mysqld_safe Number of processes running now: 0 151021 04:39:37 mysqld_safe mysqld restarted 151021 4:39:38 [Note] /usr/sbin/mysqld (mysqld 5.5.45-cll) starting as process 20861 ... 151021 4:39:38 [Note] Plugin 'FEDERATED' is disabled. 151021 4:39:38 InnoDB: The InnoDB memory heap is disabled 151021 4:39:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins 151021 4:39:38 InnoDB: Compressed tables use zlib 1.2.3 151021 4:39:38 InnoDB: Using Linux native AIO 151021 4:39:38 InnoDB: Initializing buffer pool, size = 128.0M 151021 4:39:39 InnoDB: Completed initialization of buffer pool 151021 4:39:39 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 151021 4:39:39 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 151021 4:39:41 InnoDB: Waiting for the background threads to start 151021 4:39:42 InnoDB: 5.5.45 started; log sequence number 42796583 151021 4:39:42 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 151021 4:39:42 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 151021 4:39:42 [Note] Server socket created on IP: '0.0.0.0'. 151021 4:39:42 [Note] Event Scheduler: Loaded 0 events 151021 4:39:42 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.45-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 151021 04:39:53 mysqld_safe Number of processes running now: 0 151021 04:39:53 mysqld_safe mysqld restarted 151021 4:40:14 [Note] /usr/sbin/mysqld (mysqld 5.5.45-cll) starting as process 21386 ... 151021 4:40:14 [Note] Plugin 'FEDERATED' is disabled. 151021 4:40:14 InnoDB: The InnoDB memory heap is disabled 151021 4:40:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins 151021 4:40:14 InnoDB: Compressed tables use zlib 1.2.3 151021 4:40:14 InnoDB: Using Linux native AIO 151021 4:40:15 InnoDB: Initializing buffer pool, size = 128.0M 151021 4:40:15 InnoDB: Completed initialization of buffer pool 151021 4:40:16 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 151021 4:40:16 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 151021 4:40:20 InnoDB: Waiting for the background threads to start 151021 4:40:21 InnoDB: 5.5.45 started; log sequence number 42798344 151021 4:40:21 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 151021 4:40:21 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 151021 4:40:21 [Note] Server socket created on IP: '0.0.0.0'. 151021 4:40:22 [Note] Event Scheduler: Loaded 0 events 151021 4:40:22 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.45-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 151021 04:40:57 mysqld_safe Number of processes running now: 0 151021 04:40:57 mysqld_safe mysqld restarted 151021 4:40:58 [Note] /usr/sbin/mysqld (mysqld 5.5.45-cll) starting as process 22046 ... 151021 4:40:59 [Note] Plugin 'FEDERATED' is disabled. 151021 4:40:59 InnoDB: The InnoDB memory heap is disabled 151021 4:40:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins 151021 4:40:59 InnoDB: Compressed tables use zlib 1.2.3 151021 4:40:59 InnoDB: Using Linux native AIO 151021 4:40:59 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 151021 4:40:59 InnoDB: Completed initialization of buffer pool 151021 4:40:59 InnoDB: Fatal error: cannot allocate memory for the buffer pool 151021 4:40:59 [ERROR] Plugin 'InnoDB' init function returned error. 151021 4:40:59 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 151021 4:40:59 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 151021 4:40:59 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 151021 4:40:59 [Note] Server socket created on IP: '0.0.0.0'. 151021 4:40:59 [Note] Event Scheduler: Loaded 0 events 151021 4:40:59 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.45-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 151021 18:19:05 [Note] /usr/sbin/mysqld: Normal shutdown 151021 18:19:05 [Note] Event Scheduler: Purging the queue. 0 events 151021 18:19:07 [Warning] /usr/sbin/mysqld: Forcing close of thread 5728 user: 'leechprotect' 151021 18:19:07 [Warning] /usr/sbin/mysqld: Forcing close of thread 112 user: 'eximstats' 151021 18:19:07 [Warning] /usr/sbin/mysqld: Forcing close of thread 90 user: 'modsec' 151021 18:19:07 [Note] /usr/sbin/mysqld: Shutdown complete 151021 18:19:07 mysqld_safe mysqld from pid file /var/lib/mysql/s1.*************.com.pid ended 151021 18:19:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 151021 18:19:08 [Note] /usr/sbin/mysqld (mysqld 5.5.45-cll) starting as process 14151 ... 151021 18:19:08 [Note] Plugin 'FEDERATED' is disabled. 151021 18:19:08 InnoDB: The InnoDB memory heap is disabled 151021 18:19:08 InnoDB: Mutexes and rw_locks use GCC atomic builtins 151021 18:19:08 InnoDB: Compressed tables use zlib 1.2.3 151021 18:19:08 InnoDB: Using Linux native AIO 151021 18:19:08 InnoDB: Initializing buffer pool, size = 128.0M 151021 18:19:08 InnoDB: Completed initialization of buffer pool 151021 18:19:08 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 151021 18:19:08 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 151021 18:19:08 InnoDB: Waiting for the background threads to start 151021 18:19:09 InnoDB: 5.5.45 started; log sequence number 42798344 151021 18:19:09 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 151021 18:19:09 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 151021 18:19:09 [Note] Server socket created on IP: '0.0.0.0'. 151021 18:19:10 [Note] Event Scheduler: Loaded 0 events 151021 18:19:10 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.45-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
Any insight is greatly appreciated :)

Comments

10 comments

Date Votes
  • cPanelJesse
    Jake, Would you mind submitting a ticket at tickets.cpanel.net with logins to this machine so we could take a closer look? It's hard to gauge what might be causing the issue with just that log. It appears InnoDB ends up starting okay after a crash recovery, so it may very well be a setting you're using needs to be adjusted (or a new one added). I would also recommend the use of MariaDB (should be able to upgrade in WHM) as it has better crash handling and utilizes XtraDB (drop-in replacement of InnoDB). If/once you open a ticket, please email me jesse[at]cpanel.net the ticket number and we will take a closer look. Thanks!
    0
  • Jake B.
    Done and sent, thanks!
    0
  • cPanelMichael
    Hello :) To update, it looks like this happened due to a lack of available memory. The user installed a system monitoring application to help determine the cause of that issue the next time it happens. Thank you.
    0
  • Jake B.
    Hello :) To update, it looks like this happened due to a lack of available memory. The user installed a system monitoring application to help determine the cause of that issue the next time it happens. Thank you.

    I ended up making some changes to the InnoDB configuration and it hasn't happened since as well. Will probably migrate all of our servers to MariaDB soon as @cPanelJesse suggested :)
    0
  • cPanelJesse
    I ended up making some changes to the InnoDB configuration and it hasn't happened since as well. Will probably migrate all of our servers to MariaDB soon as @cPanelJesse suggested :)

    @Jake B. that is great to hear! Would you mind sharing the configuration values you utilized, it may benefit someone else who searches our forums in the future. :)
    0
  • nickg78
    I had the same issue even with MariaDB. InnoDB storage engine was crashing randomly every 5-10 days and every InnoDB table was unavailable. Tweaking some settings in my.cnf seems to have solved the problem.
    0
  • cPanelJesse
    I had the same issue even with MariaDB. InnoDB storage engine was crashing randomly every 5-10 days and every InnoDB table was unavailable. Tweaking some settings in my.cnf seems to have solved the problem.

    @nickg78 yeah it's not an ultimate solution, just simply a means for better handling and less possible corruption as it's optimized/improved over MySQL. Glad you were able to get your individual issue resolved by tweaking settings - feel free to share your success settings if you'd like!
    0
  • nickg78
    @nickg78 yeah it's not an ultimate solution, just simply a means for better handling and less possible corruption as it's optimized/improved over MySQL. Glad you were able to get your individual issue resolved by tweaking settings - feel free to share your success settings if you'd like!

    Actually it has to do with the innodb_buffer_pool_size and other settings tmp_table_size max_heap_table_size query_cache_limit query_cache_size If they are set too high, InnoDB may crash after a while, due to lack of free RAM. Setting innodb_buffer_pool_size to 1/4 of the server RAM and the other values to 1/8 of the server RAM seems to be OK. I have also created a script which checks if InnoDB crashes and if so, it restarts MySQL automatically.
    0
  • cPanelJesse
    Actually it has to do with the innodb_buffer_pool_size and other settings tmp_table_size max_heap_table_size query_cache_limit query_cache_size If they are set too high, InnoDB may crash after a while, due to lack of free RAM. Setting innodb_buffer_pool_size to 1/4 of the server RAM and the other values to 1/8 of the server RAM seems to be OK. I have also created a script which checks if InnoDB crashes and if so, it restarts MySQL automatically.

    @nickg78 thanks for providing that! MySQLTuner-perl by major is also a great tool to utilize for setting estimations.
    0
  • nickg78
    Yes, the funny thing is that I had set the settings according to mysqltuner's suggestions which caused the random InnoDB crashes. After setting the values to lower values than what mysqltuner advised, the problem was resolved.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post