MySQL Slave server consume all memory and swap compare to other slave and master
Posted by: Joey Aldrin Cruz
Date: July 20, 2018 08:53AM

Hello,

Our set-up is 1 master and a 4 slave running on MHA. So, we have added this slave and apparently, this slave server is the only server on that eats up too much memory and swapping as well, all of the four servers having the same configuration, version which is 5.6.11 and the same RAM and disk size, but the load is really low compare to other servers.

Filesystem Size Used Avail Use% Mounted on
/dev/mapper/LVMGROUP-lv_root
30G 3.7G 25G 14% /
tmpfs 127G 0 127G 0% /dev/shm
/dev/sda1 1008M 27M 931M 3% /boot
/dev/mapper/LVMGROUP-lv_home
20G 625M 18G 4% /home
/dev/mapper/LVMGROUP-lv_data
99G 60M 94G 1% /mnt/data
/dev/mapper/3624a9370d02f6cbe216f44900001102e
1.1T 820G 209G 80% /mnt/mysql
//10.1.88.128/dbbackup01
15T 14T 1.9T 88% /mnt/storage

total used free shared buffers cached
Mem: 252 251 0 0 0 2
-/+ buffers/cache: 249 2
Swap: 127 117 10

[mysql]

# CLIENT #
port = 3306
socket = /mnt/mysql/data/mysql.sock

[mysqld]

# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /mnt/mysql/data/mysql.sock
pid-file = /mnt/mysql/data/mysql.pid
#event_scheduler = ON
skip_name_resolve = ON
lower_case_table_names = 1
federated
slave_parallel_workers = 2

# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000

# DATA STORAGE #
datadir = /mnt/mysql/data/

# BINARY LOGGING #
log-bin =/mnt/mysql/logbin/mysql-bin
binlog-ignore-db =mysql
binlog-ignore-db =test
binlog-ignore-db =information_schema
binlog_format =mixed
expire-logs-days = 4
sync-binlog = 1

# REPLICATION #
relay-log = /mnt/mysql/logbin/relay-bin
slave-net-timeout = 60
sync-master-info = 10000
sync-relay-log = 10000
sync-relay-log-info = 10000
server-id = 152

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 900
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
group_concat_max_len = 12000
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 32M

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 2G
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 160G
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_write_io_threads = 32
innodb_print_all_deadlocks = 1
innodb-status-file = 1
default-time-zone = '+8:00'

# LOGGING #
log-error = /mnt/mysql/mysqllog/mysql-error.log
log-queries-not-using-indexes = 0
slow-query-log = 1
long_query_time = 2
slow-query-log-file = /mnt/mysql/mysqllog/mysql-slow.log

# CHARACTER SET #
collation-server = utf8_unicode_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
transaction-isolation = READ-COMMITTED

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

ps -A --sort -rss -o comm,pmem | head -n 11
COMMAND %MEM
mysqld 97.7
zabbix_agentd 0.0
rsyslogd 0.0
multipathd 0.0
sshd 0.0
sshd 0.0
sshd 0.0
sshd 0.0
pickup 0.0
sudo 0.0

By the way, I have read a bug that it is about or related with the master_info_repository and relay_log_repository that is set to TABLE. But, this server is already set to FILE.

mysql> show variables like '%repository%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | FILE |
| relay_log_info_repository | FILE |
+---------------------------+-------+
2 rows in set (0.00 sec)

Let me know what other details you need in order for us to fix this.


There should be a way to fix this, as I mentioned, Master and other slaves having the same set-up but only this slave server experiencing this, which is the big question for us.

Looking forward for any response from you.

Cheers,

Joey Cruz

Options: ReplyQuote


Subject
Written By
Posted
MySQL Slave server consume all memory and swap compare to other slave and master
July 20, 2018 08:53AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.