Percona 5.7.18-16 memory leak

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hi!
I have 2 servers with Percona Server 5.6.24-72 with master-slave replication. After ugrading to version 5.7.18-16 I’ve got memory leak on the master server(about 3 Gb a day). Before upgrade master used about 70 Gb RAM, after upgrade RAM usage ircreases every day. Maybe there’s problem in my config file?

[client]
#password = pass
port = 3306
socket = /tmp/mysql.sock
#default-character-set=cp1251

Here follows entries for some specific programs

The MySQL server

[mysqld]

NEW

skip-innodb-doublewrite

Logs

log_error = /var/log/mysql/mysql.err
slow_query_log_file = /var/log/mysql/slow.log

sql_mode=‘ALLOW_INVALID_DATES,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION’
back_log=2048
bind-address=192.168.0.5
innodb_file_per_table
port = 3306
socket = /tmp/mysql.sock
datadir=/var/lib/mysql
tmpdir=/tmp/

key_buffer_size = 8M
max_allowed_packet = 512M
slave-pending-jobs-size-max = 512M
table_open_cache=6144
table_open_cache_instances=16
table_definition_cache = 6144
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 16M
thread_cache_size = 4096
query_cache_size = 0
tmp_table_size = 2048M
max_heap_table_size = 4096M

max_connections=6000
max_user_connections=5990
group_concat_max_len=4096
slow_query_log=1
connect_timeout=10
interactive_timeout=28800
wait_timeout=28800
net_write_timeout=30
net_read_timeout=60

#default-character-set=cp1251
init-connect=“SET NAMES cp1251”
long_query_time=1
skip-external-locking
skip-name-resolve
skip-host-cache
skip-slave-start

REPLICATION

BIN-LOGGING

log-bin=db_stats11-bin
binlog_ignore_db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
binlog_ignore_db = test
binlog-format=MIXED
max_binlog_size=1073741824
sync_binlog=0
relay-log=server1_relay
expire_logs_days=4
slave-skip-errors=1032,1062

replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test

Crutch for backup

slave_parallel_workers=10
slave_parallel_type=DATABASE

server-id = 3005

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:100M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 60G
innodb_log_file_size = 1G
innodb_log_buffer_size = 54M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 120
innodb_read_io_threads = 32
innodb_write_io_threads = 32

open_files_limit = 65535

innodb_stats_on_metadata = 0

myisam-recover-options = backup,force

gtid

gtid_mode = on
enforce-gtid-consistency = on
log-slave-updates

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
#key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
#key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

2

I have similar problem, but I don’t think it is a memory leak though. The binary logs accumulated on the server stay in memory cache (not just on the disk). So when you do PURGE BINARY LOGS BEFORE NOW() - INTERVAL 2 DAY, you probably should see some memory freed. I however, couldn’t find a variable that would control of how much of binary logs should stay in RAM. It would be really great if someone from Percona could shed some light into that.

Also at a glance you have my.cnf that allows for enormous memory usage. You can use http://mysqltuner.pl script to get some rough estimates and suggestions on how to improve the config. GTID replication should be run with “ROW” binlog-format, MIXED is unsafe.

Again, that’s what attracts the most of attention, but you probably should do some more work on the config.

3

Just realized the major memory consumer in your case might be the performance schema, once it consumed RAM it won’t release it (PT can re-use consumed memory though). Turn it off by adding (unless you do some debugging):
performance_schema = 0
to your my.cnf.

Otherwise you may limit some of it counters, for example:
performance_schema_accounts_size = 100
performance_schema_hosts_size = 100
performance_schema_users_size = 100

But if you are not using performance_schema or don’t know what stats it enables, probably it is the easiest to turn it off.
Note: mysql restart is required.

4

In my case it was the enabled performance schema in combination with thread_handling=pool-of-threads. Leaving thread_handling on default (one-thread-per-connection) solved my memory leak problem.