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 (noinnodb_file_per_table
set) - 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_size
to 1024M; - tried mysqldump with and without
--single-transaction
option; - 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! =)
Best Answer
See if any of these help: