9

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.

Matt Hamann
  • 193
  • 1
  • 1
  • 8
  • 1
    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? – Zoredache Jan 03 '14 at 02:16
  • 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)? – Ashwin Jan 03 '14 at 09:44
  • 1
    There's a really great answer over on DBA that seems to be helpful: http://dba.stackexchange.com/questions/1927/why-does-mysql-say-im-out-of-memory – Matt Hamann Jan 18 '14 at 05:00
  • Try performance_schema = off ? –  Nov 13 '14 at 16:44

1 Answers1

9

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/

Mojtaba Rezaeian
  • 451
  • 5
  • 14
sheng chi
  • 106
  • 1
  • 2
  • Giving this a try. Will report back soon after I give the service a few days to stay up and running consistently. – Matt Hamann Apr 30 '15 at 16:45
  • Seems to have helped quite a bit. Not sure 100% solved, but it's been running pretty well since I adjusted this. Thanks. – Matt Hamann Jun 04 '15 at 20:44