MySQL crashes frequently

MySQL

I am facing frequent MySQL crash problem. Not sure how to fix the problem. Attaching the mysql error log. Suggest me a solution.

Server Config

Digitalocean SSD server
1GB RAM
CentOS 6.5
64-bit kernel
Apache

2 wordpress website hosted
Avg. Daily traffic 2000

MySQL configuration

max_connections=50  
wait_timeout=30  
query_cache_size=15M  
tmp_table_size=46M
max_heap_table_size=46M
thread_cache_size=4
innodb_buffer_pool_size=500M
table_cache=70

slow-query-log=1
slow_query_log_file=/var/log/mysqld/slow-query.log

Error Log

140530 10:56:03 mysqld_safe Number of processes running now: 0
140530 10:56:03 mysqld_safe mysqld restarted
140530 10:56:03  InnoDB: Initializing buffer pool, size = 500.0M
140530 10:56:03  InnoDB: Error: cannot allocate 524304384 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 7994352 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
140530 10:57:03InnoDB: Fatal error: cannot allocate the memory for the buffer pool
140530 10:57:03 [ERROR] Plugin 'InnoDB' init function returned error.
140530 10:57:03 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140530 10:57:03 [ERROR] Unknown/unsupported table type: InnoDB
140530 10:57:03 [ERROR] Aborting

140530 10:57:03 [Note] /usr/libexec/mysqld: Shutdown complete

140530 10:57:03 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140530 10:57:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140530 10:57:08  InnoDB: Initializing buffer pool, size = 500.0M
140530 10:57:08  InnoDB: Error: cannot allocate 524304384 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 7994352 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
140530 10:58:09InnoDB: Fatal error: cannot allocate the memory for the buffer pool
140530 10:58:09 [ERROR] Plugin 'InnoDB' init function returned error.
140530 10:58:09 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140530 10:58:09 [ERROR] Unknown/unsupported table type: InnoDB
140530 10:58:09 [ERROR] Aborting

140530 10:58:09 [Note] /usr/libexec/mysqld: Shutdown complete

140530 10:58:09 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140530 10:58:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140530 10:58:40  InnoDB: Initializing buffer pool, size = 500.0M
140530 10:58:40  InnoDB: Error: cannot allocate 524304384 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 7994352 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
140530 10:59:40InnoDB: Fatal error: cannot allocate the memory for the buffer pool
140530 10:59:40 [ERROR] Plugin 'InnoDB' init function returned error.
140530 10:59:40 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140530 10:59:40 [ERROR] Unknown/unsupported table type: InnoDB
140530 10:59:40 [ERROR] Aborting

140530 10:59:40 [Note] /usr/libexec/mysqld: Shutdown complete

140530 10:59:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140530 10:59:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140530 10:59:53  InnoDB: Initializing buffer pool, size = 500.0M
140530 10:59:53  InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 0 2392450950
140530 10:59:53  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...
InnoDB: Doing recovery: scanned up to log sequence number 0 2392452317
140530 10:59:53  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
140530 10:59:53  InnoDB: Started; log sequence number 0 2392452317
140530 10:59:53 [Note] Event Scheduler: Loaded 0 events
140530 10:59:53 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

Best Answer

You said the total system RAM is:

1GB RAM

Your MySQL error log contains:

140530 10:58:40 InnoDB: Error: cannot allocate 524304384 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 7994352 bytes. Operating system errno: 12

Errno 12 is "Cannot allocate memory."

You have tried to allocate too much memory to InnoDB, leaving only 512MB for Linux kernel, other processes, filesystem cache, etc. That's probably not leaving enough memory for those other systems. You might even be trying to run applications on the same host, putting further pressure on the memory allocation.

You can reduce memory resource competition by allocating a smaller amount to innodb_buffer_pool_size. That's likely to be the largest single use of memory by MySQL. The default for this variable in MySQL 5.1.73 is 128MB, and this is more appropriate for your system with only 1GB of memory.

I recommend you upgrade your server with more memory. Consider the following survey of how much memory is most common for sites using MySQL. The most common answers are 16GB-64GB and 4GB-16GB.

http://www.mysqlperformanceblog.com/2012/11/02/how-much-memory-do-you-use-to-run-mysql/

Of course the size of the buffer pool depends on your database too. If your database is very small, there is little need for a large buffer pool. It's just commonly the case for databases to be larger than the available RAM.

Related Question