[debian-mysql] Bug#804214: mysql-server-core-5.5: Mysql 5.5.46 freezes under load

[Eriksson, Ulric]

Package: mysql-server-core-5.5
Version: 5.5.44-0+deb7u1
Severity: important

Dear Maintainer,

Last week, we upgraded two of our database servers from version
5.5.44-0+deb7u1 to 5.5.46-0+deb7u1. After the upgrade, we started
experiencing unpredictable freezes, where the database became
unresponsive. In this state, it was not possible to connect to it,
list processes or even shut it down normally. The only way to get
out of this state was to kill -9 mysqld, start up again and let it
do crash recovery. This happened several times on both servers,
usually at least once per day, usually at heavy load. The longest
a server went without a crash was 40 hours.

We established a 24/7 schedule to babysit the system, so that it
was possible to detect outages early. We also worked with consultants
from Percona who suggested several options for tuning the system,
none of which were effective in preventing the freezing.

There were no performance problems before or during the outages.

Eventually we decided that a downgrade to 5.5.44 was necessary.
This was done on November 3rd and the databases have been stable
since.

Mysql configuration files:

ueriksson at gib-ca-db01:~$ more /etc/mysql/my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[isamchk]
key_buffer_size = 16M

[mysql]
default-character-set = utf8

[mysqld]
basedir = /usr
bind-address = 0.0.0.0
character-set-server = utf8
datadir = /var/lib/mysql
default-storage-engine = INNODB
expire_logs_days = 14
innodb_file_per_table
key_buffer_size = 16M
log-error = /var/log/mysql/mysql.log
long_query_time = 1
max_allowed_packet = 16M
max_binlog_size = 100M
max_connections = 151
myisam_recover = BACKUP
table_open_cache = 4000
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
query_cache_limit = 1M
#query_cache_size = 16M
skip-external-locking
slow_launch_time = 1
socket = /var/run/mysqld/mysqld.sock
#thread_cache_size = 8
thread_stack = 256K
tmpdir = /tmp
transaction-isolation = READ-COMMITTED
user = mysql
#thread_cache_size=64


####new_changes

innodb_buffer_pool_instances=8
innodb_stats_on_metadata=OFF
thread_cache_size=1000
query_cache_size=0
query_cache_type=0
innodb_flush_method=O_DIRECT

[mysqld_safe]
log-error = /var/log/mysql/mysql.log
nice = 0
socket = /var/run/mysqld/mysqld.sock

[mysqldump]
max_allowed_packet = 16M
quick
quote-names


!includedir /etc/mysql/conf.d/

ueriksson at gib-ca-db01:~$ more /etc/mysql/conf.d/common.cnf
[mysqld]
bind-address = 0.0.0.0
datadir = /data/mysql
tmpdir = /data/tmp
max_connections = 2000
expire_logs_days = 14
#thread_cache_size = 64
log-bin-trust-function-creators = 1

ueriksson at gib-ca-db01:~$ more /etc/mysql/conf.d/compress.cnf
[mysqld]
innodb_file_format = Barracuda
innodb_old_blocks_time = 1000

ueriksson at gib-ca-db01:~$ more /etc/mysql/conf.d/memory.cnf
[mysqld]
# innodb tweak params
innodb_buffer_pool_size = 51G
innodb_log_file_size = 1800M
innodb_log_buffer_size = 16M

ueriksson at gib-ca-db01:~$ more /etc/mysql/conf.d/replication.cnf
[mysqld]
server-id = 11
auto-increment-increment = 1
auto-increment-offset = 1

log-slave-updates = true
log_bin = /data/log/mysql-bin.log
binlog-format = row
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = performance_schema.%
# Skip some errors
# 1396 = user error
# 1133 = set password error


-- System Information:
Debian Release: 7.9
  APT prefers oldstable-updates
  APT policy: (500, 'oldstable-updates'), (500, 'oldstable')
Architecture: amd64 (x86_64)

Kernel: Linux 3.2.0-4-amd64 (SMP w/10 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash

Versions of packages mysql-server-core-5.5 depends on:
ii  libaio1     0.3.109-3
ii  libc6       2.13-38+deb7u8
ii  libgcc1     1:4.7.2-5
ii  libstdc++6  4.7.2-5
ii  libwrap0    7.6.q-24
ii  zlib1g      1:1.2.7.dfsg-13

mysql-server-core-5.5 recommends no packages.

mysql-server-core-5.5 suggests no packages.

-- no debconf information