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
- 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
[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:
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:
- tried mysqldump with and without
- 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! =)