MySQL (MariaDB) crashes frequently

22,054

I hope it will be helpful to you.

try

performance_schema = off

in [mysqld] section of your configuration.

https://mariadb.com/resources/blog/starting-mysql-on-low-memory-virtual-machines/

Share:
22,054

Related videos on Youtube

Matt Hamann
Author by

Matt Hamann

Updated on September 18, 2022

Comments

  • Matt Hamann
    Matt Hamann over 1 year

    I recently migrated an old server running MySQL to a new VPS running MariaDB 5.5. I don't have too much running on the server (just a few PHP sites) and free memory seems to be OK, but the DB keeps crashing--sometimes every few days, other times within a few hours.

    I receive the following errors in the logs:

    131231  1:43:04 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
    131231  1:43:04 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
    131231  1:43:04 [ERROR] mysqld: Out of memory (Needed 72499200 bytes)
    131231  1:43:04 [ERROR] mysqld: Out of memory (Needed 54362112 bytes)
    131231  1:43:04 InnoDB: The InnoDB memory heap is disabled
    131231  1:43:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    131231  1:43:04 InnoDB: Compressed tables use zlib 1.2.3.4
    131231  1:43:04 InnoDB: Using Linux native AIO
    131231  1:43:04 InnoDB: Initializing buffer pool, size = 128.0M
    InnoDB: mmap(137756672 bytes) failed; errno 12
    131231  1:43:04 InnoDB: Completed initialization of buffer pool
    131231  1:43:04 InnoDB: Fatal error: cannot allocate memory for the buffer pool
    131231  1:43:04 [ERROR] Plugin 'InnoDB' init function returned error.
    131231  1:43:04 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    131231  1:43:04 [Note] Plugin 'FEEDBACK' is disabled.
    131231  1:43:04 [ERROR] Unknown/unsupported storage engine: InnoDB
    131231  1:43:04 [ERROR] Aborting
    
    131231  1:43:04 [Note] /usr/sbin/mysqld: Shutdown complete
    

    I've played with the my.cnf settings for InnoDB Heap, which does not seem to help. Here's the relevant portion:

    innodb_buffer_pool_size = 128M
    innodb_log_buffer_size  = 8M
    innodb_file_per_table   = 1
    innodb_open_files       = 400
    innodb_io_capacity      = 400
    innodb_flush_method     = O_DIRECT
    

    I seem to have "plenty" of free RAM, and I have some swap available as well:

    root@phoenix:~# free -m
                 total       used       free     shared    buffers     cached
    Mem:           994        923         71          0         19        417
    -/+ buffers/cache:        486        508
    Swap:         1023        131        892
    

    How can I solve / troubleshoot this issue? I've scoured the interwebz for clues, but nothing has helped.

    • Zoredache
      Zoredache over 10 years
      What does the memory look like right before it crashes? The [ERROR] mysqld: Out of memory sure looks like a strong hint. Did you check your system logs to see if the kernel (OOM) decided to kill mysql?
    • Ashwin
      Ashwin over 10 years
      You should log the file handles used and the memory used both before and after the crash (maybe even the dump of top command every interval) to know what could cause the crash. Any other services run in your servers like mysql (mariadb)?
    • Matt Hamann
      Matt Hamann over 10 years
      There's a really great answer over on DBA that seems to be helpful: dba.stackexchange.com/questions/1927/…
    • Admin
      Admin over 9 years
      Try performance_schema = off ?
  • Matt Hamann
    Matt Hamann about 9 years
    Giving this a try. Will report back soon after I give the service a few days to stay up and running consistently.
  • Matt Hamann
    Matt Hamann almost 9 years
    Seems to have helped quite a bit. Not sure 100% solved, but it's been running pretty well since I adjusted this. Thanks.