1

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_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! =)

  • 1
    did you check dmesg ? i smell OOM somehow – jerichorivera Oct 28 '16 at 10:17
  • Agreed: mysqld_safe Number of processes running now: 0 and 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. – Michael - sqlbot Oct 28 '16 at 16:42
  • The memory exhaustion is probably triggered by mysqldump demanding large amounts of memory for itself while making the backup with --skip-opt. Why are you using that option? – Michael - sqlbot Oct 28 '16 at 16:44
  • Yep, you're right, it's OOM. – Someone Oct 31 '16 at 6:15
  • --skip-opt is used to don't lock tables during dump. Yes, dump script was made in great rush, but it worked for time, when we started using MySQL. Now, I'm planning to use something like xtrabackup – Someone Oct 31 '16 at 6:41
1

It was OOM.

The first thing I was suspect was memory. But:

  • VM's dmesg didn't output any messages until I reboot container. Only after that I saw messages, like

    [0.000000] Out of memory in UB 101: OOM killed process 779 (mysqld) score 0 vm:16724384kB, rss:13130908kB, swap:2250952kB
    
    • MySQL error log (and another system logs of VM and node) didn't complain about not enough memory state;

    • error was completely "unstable".

So now, I re-calculated memory, used by applications in container, tuned innodb_buffer_pool_size and till now everything is OK.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.