How to Permanently Solve MySQL Error 2002 (HY000)

innodbMySQLUbuntu

I have a problem on my cloud server with 2GB of RAM and 40GB Hard Disk that's hosting a WordPress website that receives around 1000-2000 hits a day. My problem is that MySQL is always crashing and my only option is to reboot the server. I noticed that logs were not enabled so I enabled them and here is what i found:

150207 17:31:42 [Note] Plugin 'FEDERATED' is disabled.
150207 17:31:42 InnoDB: The InnoDB memory heap is disabled
150207 17:31:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150207 17:31:42 InnoDB: Compressed tables use zlib 1.2.3.4
150207 17:31:42 InnoDB: Initializing buffer pool, size = 1.2G
InnoDB: mmap(1285636096 bytes) failed; errno 12
150207 17:31:42 InnoDB: Completed initialization of buffer pool
150207 17:31:42 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150207 17:31:42 [ERROR] Plugin 'InnoDB' init function returned error.
150207 17:31:42 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150207 17:31:42 [ERROR] Unknown/unsupported storage engine: InnoDB
150207 17:31:42 [ERROR] Aborting
150207 17:31:42 [Note] /usr/sbin/mysqld: Shutdown complete

I am guessing this has something to do with memory allocation or some settings in my my.cnf file. These are the contents:

[client]
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                         = /var/run/mysqld/mysqld.sock
log_error                      = /var/log/mysql/mysql_error.log

[mysqld]
user                           = mysql
pid-file                       = /var/run/mysqld/mysql.pid
socket                         = /var/run/mysqld/mysqld.sock
port                           = 3306
datadir                        = /var/lib/mysql
thread_cache_size              = 4
table_open_cache               = 800
table_cache                    = 800
key_buffer                     = 32M
query_cache_type               = 1
query_cache_size               = 64M
query_cache_limit              = 8M
innodb_buffer_pool_size        = 1G
innodb_io_capacity             = 2000
innodb_read_io_threads         = 64
innodb_thread_concurrency      = 0
innodb_write_io_threads        = 64
log_error                      = /var/log/mysql/mysql_error.log
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/ib_slow_log.log

Any help will be greatly appreciated.

Best Answer

You have a memory problem, something very typical in a memory-bound system, and that can be confirmed on the line:

150207 17:31:42 InnoDB: Fatal error: cannot allocate memory for the buffer pool

As InnoDB cannot allocate memory for its buffer pool, it fails, and MySQL cannot start if the InnoDB engine fails (MySQL 5.5).

Nothing on your my.cnf seems out of the ordinary, but given that you only have 2GBs of ram, and if I understand it correctly, that is shared with the OS and a PHP/Wordpress installation, you have to be very conservative. MySQL is already taking 1.2 GB for the buffer pool, you should monitor the memory usage (top, ps) and making sure MySQL is the process to blame (compared to, for example, Apache). If it is, reduce your innodb_buffer_pool_size and/or some of the other buffers, such as the key cache or the query cache. I would set the buffer pool to ~500MB for this non-dedicated server.

You seem to be running 5.5, for other folks running 5.6, one of the main causes I have seen in MySQL support regarding out of memory killings in small-memory systems is the extra static memory needed for the performance_schema. In those cases, I recommend disabling it or reducing max_connections to avoid too much overhead.

Related Question