The Community Forums

Interact with an entire community of cPanel & WHM users.
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

InnoDB seems to be crashing

Discussion in 'Database Discussions' started by Jake B., Oct 21, 2015.

  1. Jake B.

    Jake B. Member

    Joined:
    Oct 21, 2015
    Messages:
    7
    Likes Received:
    2
    Trophy Points:
    3
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    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:

    Code:
    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 :)
     
  2. cPanelJesse

    cPanelJesse Technical Analyst Manager
    Staff Member

    Joined:
    Sep 19, 2007
    Messages:
    51
    Likes Received:
    6
    Trophy Points:
    8
    Location:
    Houston
    cPanel Access Level:
    Root Administrator
    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!
     
  3. Jake B.

    Jake B. Member

    Joined:
    Oct 21, 2015
    Messages:
    7
    Likes Received:
    2
    Trophy Points:
    3
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    Done and sent, thanks!
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  5. Jake B.

    Jake B. Member

    Joined:
    Oct 21, 2015
    Messages:
    7
    Likes Received:
    2
    Trophy Points:
    3
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    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 :)
     
    cPanelJesse and Infopro like this.
  6. cPanelJesse

    cPanelJesse Technical Analyst Manager
    Staff Member

    Joined:
    Sep 19, 2007
    Messages:
    51
    Likes Received:
    6
    Trophy Points:
    8
    Location:
    Houston
    cPanel Access Level:
    Root Administrator
    @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. :)
     
    Infopro likes this.
  7. nickg78

    nickg78 Member

    Joined:
    May 7, 2004
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    Athens, Greece
    cPanel Access Level:
    Root Administrator
    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.
     
  8. cPanelJesse

    cPanelJesse Technical Analyst Manager
    Staff Member

    Joined:
    Sep 19, 2007
    Messages:
    51
    Likes Received:
    6
    Trophy Points:
    8
    Location:
    Houston
    cPanel Access Level:
    Root Administrator
    @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!
     
  9. nickg78

    nickg78 Member

    Joined:
    May 7, 2004
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    Athens, Greece
    cPanel Access Level:
    Root Administrator
    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.
     
  10. cPanelJesse

    cPanelJesse Technical Analyst Manager
    Staff Member

    Joined:
    Sep 19, 2007
    Messages:
    51
    Likes Received:
    6
    Trophy Points:
    8
    Location:
    Houston
    cPanel Access Level:
    Root Administrator
    @nickg78 thanks for providing that! MySQLTuner-perl by major is also a great tool to utilize for setting estimations.
     
  11. nickg78

    nickg78 Member

    Joined:
    May 7, 2004
    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    Athens, Greece
    cPanel Access Level:
    Root Administrator
    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.
     
Loading...

Share This Page