How to defeat cannot allocate memory for the buffer pool in MYSQL?

Question

linux

I can not defeat mysql ...
Once a day or more often the muscle lies down.
There is a lot of memory, 32 GB, the load is minuscule on the server.

160109 00:26:19 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160109 00:26:19 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160109  0:26:19 [Note] /usr/libexec/mysqld (mysqld 5.5.47) starting as process 19542 ...
160109  0:26:19 [Note] Plugin 'FEDERATED' is disabled.
160109  0:26:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160109  0:26:19 InnoDB: Compressed tables use zlib 1.2.3
160109  0:26:19 InnoDB: Using Linux native AIO
160109  0:26:19 InnoDB: Initializing buffer pool, size = 10.0G
160109  0:26:20 InnoDB: Completed initialization of buffer pool
160109  0:26:20 InnoDB: highest supported file format is Barracuda.
160109  0:26:20  InnoDB: Waiting for the background threads to start
160109  0:26:21 InnoDB: 5.5.47 started; log sequence number 792513317
160109  0:26:21 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160109  0:26:21 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
160109  0:26:21 [Note] Server socket created on IP: '0.0.0.0'.
160109  0:26:21 [Warning] 'proxies_priv' entry '@ [email protected]' ignored in --skip-name-resolve mode.
160109  0:26:21 [Note] Event Scheduler: Loaded 0 events
160109  0:26:21 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.47'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi
160109 22:37:19 mysqld_safe Number of processes running now: 0
160109 22:37:19 mysqld_safe mysqld restarted
160109 22:37:19 [Note] /usr/libexec/mysqld (mysqld 5.5.47) starting as process 14038 ...
160109 22:37:19 [Note] Plugin 'FEDERATED' is disabled.
160109 22:37:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160109 22:37:19 InnoDB: Compressed tables use zlib 1.2.3
160109 22:37:19 InnoDB: Using Linux native AIO
160109 22:37:20 InnoDB: Initializing buffer pool, size = 10.0G
InnoDB: mmap(686817280 bytes) failed; errno 12
160109 22:37:20 InnoDB: Completed initialization of buffer pool
160109 22:37:20 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160109 22:37:20 [ERROR] Plugin 'InnoDB' init function returned error.
160109 22:37:20 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160109 22:37:20 [ERROR] Unknown/unsupported storage engine: innodb
160109 22:37:20 [ERROR] Aborting

160109 22:37:20 [Note] /usr/libexec/mysqld: Shutdown complete

160109 22:37:20 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=200
max_user_connections=30
key_buffer_size=1G
myisam_sort_buffer_size=300M
open_files_limit = 100000
wait_timeout=30
interactive_timeout=50
long_query_time=5
skip-name-resolve
#log-queries-not-using-indexes
#log-slow-queries=/var/log/mysql/log-slow-queries.log

#mysqlTuner recommendations
query_cache_size=15M
tmp_table_size=30M
max_heap_table_size=40M
thread_cache_size=4
table_open_cache=500

innodb=ON 
#innodb_use_native_aio = 0
innodb_file_per_table
#was added by me, down mysql
default-storage-engine=innodb 
innodb_use_sys_malloc=0
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=16

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Mysqltuner but it's quite fresh, right after rebooting the data

[[0;34m--[0m] Data in MyISAM tables: 525M (Tables: 688)
[[0;34m--[0m] Data in InnoDB tables: 42M (Tables: 270)
[[0;34m--[0m] Data in MEMORY tables: 0B (Tables: 2)
[[0;31m!![0m] Total fragmented tables: 59

[[0;34m--[0m] Up for: 1m 16s (3K q [44.132 qps], 100 conn, TX: 7M, RX: 350K)
[[0;34m--[0m] Reads / Writes: 96% / 4%
[[0;34m--[0m] Binary logging is disabled
[[0;34m--[0m] Total buffers: 11.1G global + 2.8M per thread (200 max threads)
[[0;32mOK[0m] Maximum reached memory usage: 11.1G (35.25% of installed RAM)
[[0;32mOK[0m] Maximum possible memory usage: 11.6G (36.95% of installed RAM)
[[0;32mOK[0m] Slow queries: 0% (0/3K)
[[0;32mOK[0m] Highest usage of available connections: 1% (2/200)
[[0;32mOK[0m] Aborted connections: 1.00%  (1/100)
[[0;32mOK[0m] Query cache efficiency: 86.9% (2K cached / 3K selects)
[[0;32mOK[0m] Query cache prunes per day: 0
[[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 62 sorts)
[[0;31m!![0m] Temporary tables created on disk: 29% (33 on disk / 113 total)
[[0;32mOK[0m] Thread cache hit rate: 98% (2 created / 100 connections)
[[0;31m!![0m] Table cache hit rate: 8% (500 open / 5K opened)
[[0;32mOK[0m] Open file limit used: 0% (760/100K)
[[0;32mOK[0m] Table locks acquired immediately: 100% (394 immediate / 394 locks)

-------- MyISAM Metrics -----------------------------------------------------
[[0;31m!![0m] Key buffer used: 18.2% (195M used / 1B cache)
[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 1.0G/31.7M
[[0;31m!![0m] Read Key buffer hit rate: 88.1% (5K cached / 616 reads)
[[0;31m!![0m] Write Key buffer hit rate: 36.5% (159 cached / 101 writes)

-------- InnoDB Metrics -----------------------------------------------------
[[0;34m--[0m] InnoDB is enabled.
[[0;32mOK[0m] InnoDB buffer pool / data size: 10.0G/42.7M
[[0;31m!![0m] InnoDB buffer pool instances: 16
[[0;31m!![0m] InnoDB Used buffer: 0.37% (2409 used/ 655351 total)
[[0;32mOK[0m] InnoDB Read buffer efficiency: 95.83% (55357 hits/ 57764 total)
[[0;31m!![0m] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 1 writes)

    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for [email protected]% to [email protected]
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (100000) variable 
    should be greater than table_open_cache ( 500)

Answer 1

Size of global buffers (key_buffer_size + tmp_table_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size).
Single connection buffer size (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size).
Also, do not forget to multiply the last by max_connections and the sum of all this economy in the ideal should not exceed your memory.

Answer 2

you have innodb_buffer_pool_size=10G buffer which is already a third of the memory
160109 22:37:20 InnoDB: Initializing buffer pool, size = 10.0G
InnoDB: mmap(686817280 bytes) failed; errno 12
and he could not take it from
Are you sure that no one is taking up memory at this moment?

Answer 3

1. What is the bitness of the OS? Which mysql code is 32 or 64 bit? - 32 bit will not allocate 10G
2. Is there a swap on the machine? If it's not there, anything that yearly can eat up memory, even the fs cache can eat up all the memory and cause this error.