Не могу победить mysql...
Раз в сутки или чаще мускул ложится.
Памяти много, 32 гб, нагрузки мизер на сервере.
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 '@ root@you32' 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 но это совсем свежак, прямо после перезагрузки данные
[[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 user@% to user@SpecificDNSorIp
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)
