ranjeet
By:
ranjeet

Cannot allocate memory for the buffer pool

September 10, 2016 2.1k views
MySQL CentOS

We are facing one very serious issue while starting Mysql. We are Using Digital Ocean.

Droplet
RAM = 8GB 
CPU = 4Core
Mysql Version = 5.6

My.cnf

[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
max_connections                = 300
open_files_limit               = 1024000
#interactive_timeout           = 30
#wait_timeout              = 30
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 64M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sysdate-is-now                 = 1
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire_logs_days               = 10
#expire-logs-days              = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 500M
max-heap-table-size            = 500M
query-cache-type               = 0
query-cache-size               = 64M
#max-connections                = 300
thread-cache-size              = 50
#open_files_limit               = 65535
table-definition-cache         = 1024M
table_open_cache               = 600

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 64M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 12000M
innodb-buffer-pool-instances   = 1
join_buffer_size               = 6M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log
long-query-time            = 5
log-queries-not-using-indexes

We are able to start Mysql without any error.

But when we resize existing Droplet from 8Gb RAM and 4 CPU to 16GB and 8 CPU and did not make any changes in my.cnf but Unable to start MYSQL.

During starting MySql it consume all memory 16GB RAM and 5GB swap.

It show error.

160910 18:15:03 mysqldsafe Starting mysqld daemon with databases from /var/lib/mysql/
2016-09-10 18:15:32 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit
defaultsfortimestamp server option (see documentation for more details).
2016-09-10 18:15:32 0 [Note] /usr/sbin/mysqld (mysqld 5.6.31-log) starting as process 14643 ...
2016-09-10 18:15:32 14643 [Warning] Buffered warning: option 'tabledefinitioncache': unsigned value 1073741824 adjusted to 524288

2016-09-10 18:15:32 14643 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 1024000)

2016-09-10 18:15:32 14643 [Warning] Buffered warning: Changed limits: max_connections: 214 (requested 300)

2016-09-10 18:15:32 14643 [Warning] Buffered warning: Changed limits: tableopencache: 400 (requested 600)

2016-09-10 18:15:32 14643 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2016-09-10 18:15:32 14643 [Note] Plugin 'FEDERATED' is disabled.
2016-09-10 18:15:32 14643 [Warning] The option innodb (skip-innodb) is deprecated and will be removed in a future release
2016-09-10 18:15:32 14643 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-09-10 18:15:32 14643 [Note] InnoDB: The InnoDB memory heap is disabled
2016-09-10 18:15:32 14643 [Note] InnoDB: Mutexes and rwlocks use GCC atomic builtins
2016-09-10 18:15:32 14643 [Note] InnoDB: Memory barrier is not used
2016-09-10 18:15:32 14643 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-09-10 18:15:32 14643 [Note] InnoDB: Using Linux native AIO
2016-09-10 18:15:32 14643 [Note] InnoDB: Using CPU crc32 instructions
2016-09-10 18:15:32 14643 [Note] InnoDB: Initializing buffer pool, size = 7.8G
InnoDB: mmap(8585216000 bytes) failed; errno 12
2016-09-10 18:15:32 14643 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2016-09-10 18:15:32 14643 [ERROR] Plugin 'InnoDB' init function returned error.
2016-09-10 18:15:32 14643 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-09-10 18:15:32 14643 [ERROR] Failed to initialize plugins.
2016-09-10 18:15:32 14643 [ERROR] Aborting
2016-09-10 18:15:32 14643 [Note] Binlog end
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'partition'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'PERFORMANCE
SCHEMA'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBSYSDATAFILES'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBSYSTABLESPACES'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBSYSFOREIGNCOLS'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
SYSFOREIGN'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
SYSFIELDS'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
SYSCOLUMNS'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
SYSINDEXES'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
SYSTABLESTATS'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
SYSTABLES'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
FTINDEXTABLE'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBFTINDEXCACHE'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
FTCONFIG'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
FTBEINGDELETED'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBFTDELETED'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBFTDEFAULTSTOPWORD'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
METRICS'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBBUFFERPOOLSTATS'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
BUFFERPAGELRU'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBBUFFERPAGE'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBCMPPERINDEXRESET'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBCMPPERINDEX'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
CMPMEMRESET'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
CMPMEM'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBCMPRESET'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBCMP'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
LOCKWAITS'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODB
LOCKS'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'INNODBTRX'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'BLACKHOLE'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'ARCHIVE'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'MRG
MYISAM'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'MyISAM'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'MEMORY'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'CSV'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'sha256password'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'mysql
oldpassword'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'mysql
nativepassword'
2016-09-10 18:15:32 14643 [Note] Shutting down plugin 'binlog'
2016-09-10 18:15:32 14643 [Note] /usr/sbin/mysqld: Shutdown complete
160910 18:15:33 mysqld
safe mysqld from pid file /var/lib/mysql/mysql.pid ended

I am not able to understand why mysql is not able to start even there is no change in any setting.

I have 5GB of Swap memory.

According to me I think its issue with DigitalOcean reason same config is working on 8GB ram but it did not work on 16Gb RAM but they are claiming there is no issue on 16GB droplet.

ALso I would to mentioned that we have installed only mysql no other service.

Please help to get it resolved.

Thanks

7 Answers

This is not a DigitalOcean issue. What does free -m say before starting MySQL?

[root@database20160901-4gb-sgp1-01 ~]# free -mt
```

total used free shared buff/cache available
Mem: 16047 123 15845 1 79 15792
Swap: 2047 37 2010
Total: 18095 161 17855

uname -a
Linux database20160901-4gb-sgp1-01 3.10.0-327.10.1.el7.x8664 #1 SMP Tue Feb 16 17:03:50 UTC 2016 x8664 x8664 x8664 GNU/Linux

I recommend tuning down variables such as innodb-buffer-pool-size and slowly working your way up.

Initially I go with innodb-buffer-pool-size = 12000M but it did not work.
I am continuously trying by changing value of innodb-buffer-pool-size but all of them failed.

I try innodb-buffer-pool-size = 3000M but still it did not worked. Ben When I go with
innodb-buffer-pool-size = 8000M on droplet Mysql start.

Have another answer? Share your knowledge.