Not the answer you need?
Register and ask your own question!

Percona 5.7.18-16 memory leak

nigel2006nigel2006 EntrantInactive User Role Beginner
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

Comments

  • tarasotaraso Entrant Current User Role Beginner
    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.
  • tarasotaraso Entrant Current User Role Beginner
    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.
  • zjagustzjagust Entrant Current User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.