General: Standalone MySQL-5.5.43 in OpenVZ on ProxMox-3.4-6/102d4547 (Debian, 2.6.32-39-pve). Container uses 18Gb of RAM and 100Gb of disk. This container runs this service more than a year.
There's also two tiny containers running on this node, with tiny I/O load, LAN load - etc.
Hardware: 2x Xeon E5649 2.53GHz, 24Gb RAM, Intel RAID controller
Software:
- some kind of web-site and expert system on PHP. About 10-20
connections in second (depends of situaiton - can be 40-50, and in
another time even 0). InnoDB engine, about 20 Gb base size, all in
one file (no
innodb_file_per_tableset) - Apache-2.4.10
my.cnf:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
log_error = /var/log/mysql/error.log
nice = 0
socket = /var/run/mysqld/mysqld.sock
[mysqld]
basedir = /usr
bind-address = 127.0.0.1
datadir = /var/lib/mysql
expire_logs_days = 10
innodb_buffer_pool_size = 14G
innodb_fast_shutdown = 0
innodb_log_file_size = 1024M
key_buffer = 16M
lc-messages-dir = /usr/share/mysql
log_error = /var/log/mysql/error.log
max_allowed_packet = 16M
max_binlog_size = 100M
max_connections = 384
max_heap_table_size = 32M
myisam-recover = BACKUP
port = 3306
pid-file = /var/run/mysqld/mysqld.pid
query_cache_limit = 1M
query_cache_size = 32M
skip-external-locking
socket = /var/run/mysqld/mysqld.sock
table_open_cache = 512
tmpdir = /tmp
thread_stack = 192K
thread_cache_size = 16
tmp_table_size = 32M
user = mysql
[mysqldump]
max_allowed_packet = 16M
quick
quote-names
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
Dump of base is made by command:
/usr/bin/mysqldump --single-transaction --skip-opt -uUSER -pPASS |/bin/gzip -c >dump.gz
During dump sometimes mysql crashes (or restarts - IDK) with next error messages:
error.log
161028 08:07:02 mysqld_safe Number of processes running now: 0
161028 08:07:02 mysqld_safe mysqld restarted
161028 8:07:03 [Note] /usr/sbin/mysqld (mysqld 5.5.43-0+deb8u1) starting as process 5396 ...
161028 8:07:03 [Note] Plugin 'FEDERATED' is disabled.
161028 8:07:03 InnoDB: The InnoDB memory heap is disabled
161028 8:07:03 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161028 8:07:03 InnoDB: Compressed tables use zlib 1.2.8
161028 8:07:03 InnoDB: Using Linux native AIO
161028 8:07:03 InnoDB: Initializing buffer pool, size = 14.0G
161028 8:07:04 InnoDB: Completed initialization of buffer pool
161028 8:07:04 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
161028 8:07:04 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...
161028 8:07:29 InnoDB: Waiting for the background threads to start
161028 8:07:30 InnoDB: 5.5.43 started; log sequence number 124567994871
161028 8:07:30 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
161028 8:07:30 [Note] - '127.0.0.1' resolves to '127.0.0.1';
161028 8:07:30 [Note] Server socket created on IP: '127.0.0.1'.
161028 8:07:30 [Note] Event Scheduler: Loaded 0 events
161028 8:07:30 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.43-0+deb8u1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian)
But there's no system in this crashes: no correlation with system load, network load, no hardware problems.
What was made to determine problem:
- increased
innodb_log_file_sizeto 1024M; - tried mysqldump with and without
--single-transactionoption; - checked tables consistency by
mysqlcheck- no errors. - re-planned backups to make'em in period of minimal server load;
- checked all appropriate system log, like
syslog, messages, daemon.log, mysql.err, ...- no errors, warning or something, that can point to problem - container uzes tmpfs as /tmp - MySQL was re-pointed to use /tmp2 "real" directory.
OFC, i can dump all bases, kill'em, re-init MySQL and hope that everything will be OK. But if not?
So, now I have no idea how to deternine the "root of all evil". So, please, help! =)
mysqld_safe Number of processes running now: 0and no stack trace or "caught signal' message is a classic signature of OOM. Sometimes, like here and here you see different behavior during the recovery attempt because the system is still constrained for memory when the service is trying to restart, but the underlying cause still sounds like memory exhaustion.mysqldumpdemanding large amounts of memory for itself while making the backup with--skip-opt. Why are you using that option?