Description:
I tried two different community version:
5.6.34 and 5.6.48 and observed physical memory accumulation in OS level.
How to repeat:
Amazon linux version:
$ uname -a
Linux testxxxx 4.14.177-139.254.amzn2.x86_64 #1 SMP Thu May 7 18:48:23 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
Installation steps:
Install MySQL 5.6.34
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-5.6.34-1.el7.x86_64.rpm-bundle.tar
tar -xvf MySQL-5.6.34-1.el7.x86_64.rpm-bundle.tar
sudo yum -y install *.rpm
sudo yum -y install perl-Data-Dumper
sudo mysql_install_db
sudo chown -R mysql:mysql /var/lib/mysql
Edit /etc/my.cnf
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
sudo service mysql start #start mysqld instance
Install MySQL 5.6.48
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-5.6.48-1.el7.x86_64.rpm-bundle.tar
tar -xvf MySQL-5.6.48-1.el7.x86_64.rpm-bundle.tar
sudo yum -y install *.rpm
sudo yum -y install perl-Data-Dumper
sudo mysql_install_db
sudo chown -R mysql:mysql /var/lib/mysql
Edit /etc/my.cnf
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
sudo service mysql start #start mysqld instance
Then I create one table, use the following steps to add column to table, drop it's column, adding column back and update all records in added column every minute:
CREATE TABLE `big` (
`A` int(11) primary key,
`B` int(11) DEFAULT NULL,
UNIQUE KEY `big_idx` (`B`) USING BTREE,
KEY `COL_A_B` (`A`,`B`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
use test;
delimiter $$
CREATE PROCEDURE myFunction()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i<=5000) DO
INSERT INTO test.big VALUES(i,i);
SET i=i+1;
commit;
END WHILE;
END$$
DELIMITER ;
call myFunction;
delimiter $$
CREATE PROCEDURE myBFunction()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i<=5000) DO
update test.big set B=i where A=i;
SET i=i+1;
commit;
END WHILE;
END$$
Edit the shell script file "test.sh" and grant execution privilege by "chmod +x test.sh" :
mysql -u user1 <<MYEOF
use test;
alter table test.big drop column B;
alter table test.big add column (B int);
call myBFunction;
MYEOF
Set to cron table to repeat the operations (add column/ drop column/ update records) every 1 mintue:
* * * * * /home/test-user/test.sh
Then I verify the utilization of physical memory gradually increasing(Please see RSS field):
1.Collect RSS (physical memory) and memory allocated utilization by "show engine innodb status;" command
$ ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql"
PID COMMAND RSS VSZ USER COMMAND
3216 mysqld_s 3148 124188 root /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/ip-172-31-34-56.ap-southeast-2.compute.internal.pid
3476 mysqld 546020 1309608 mysql /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysqld.log --pid-file=/var/lib/mysql/ip-172-31-34-56.ap-southeast-2.compute.internal.pid --socket=/var/lib/mysql/mysql.sock
mysql> show engine innodb status;
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 65702
Buffer pool size 8191
Free buffers 2559
Database pages 5620
Old database pages 2054
Modified db pages 0
Pending reads 0
$sleep 300
2.Collect again:
PID COMMAND RSS VSZ USER COMMAND
3216 mysqld_s 3148 124188 root /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/ip-172-31-34-56.ap-southeast-2.compute.internal.pid
3476 mysqld 553412 1309608 mysql /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysqld.log --pid-file=/var/lib/mysql/ip-172-31-34-56.ap-southeast-2.compute.internal.pid --socket=/var/lib/mysql/mysql.sock
9750 mysql 6028 140604 ec2-user mysql -u root -p
10236 grep 984 119420 ec2-user grep --color=auto -e RSS -e mysql
You have new mail in /var/spool/mail/ec2-user
$ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql"
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 65702
Buffer pool size 8191
Free buffers 2265
Database pages 5914
Old database pages 2163
Modified db pages 0
I observed the RSS increased from 546020 (KB) to 553412 (KB) and this could be reproduced on 5.6.34 and 5.6.48.
However, the same test on 5.7.26, the RSS (resident set size, the non-swapped physical memory that a task has used (in kiloBytes)), keeps the same, the mysqld daemon would not use physical memory increasingly.
PID COMMAND RSS VSZ USER COMMAND
4030 mysqld 188004 1116512 mysql /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
4135 grep 960 119420 ec2-user grep --color=auto -e RSS -e mysql
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 111039
Buffer pool size 8192
Free buffers 7795
Database pages 394
Old database pages 0
Modified db pages 0
Pending reads 0
after 120 seconds and collecting again, RSS still keep the same.
PID COMMAND RSS VSZ USER COMMAND
4030 mysqld 188004 1116512 mysql /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
4190 grep 920 119420 ec2-user grep --color=auto -e RSS -e mysql
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 111039
Buffer pool size 8192
Free buffers 7795
Database pages 394
Old database pages 0
Modified db pages 0
Pending reads 0
