Description:
This report is about epic result of over-allocating buffer pool size(memory):
http://bugs.mysql.com/bug.php?id=79850 (related to overcomitting)
I have a VM:
[root@mysql-57 ~]# free -m
total used free shared buff/cache available
Mem: 3428 377 664 1 2387 2862
Swap: 2047 92 1955
With CentOS 7:
[root@mysql-57 ~]# uname -a
Linux mysql-57 3.10.0-327.4.4.el7.x86_64 #1 SMP Tue Jan 5 16:07:00 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
Installed MySQL 5.7.10 using YUM repo.
Try to start MySQL with:
innodb_buffer_pool_size=20G
innodb_buffer_pool_instances=6
It will start as usual.
The test table:
mysql> show create table sbtest1;
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=25661595 DEFAULT CHARSET=latin1 |
mysql> CREATE TABLESPACE `sbtest1` ADD DATAFILE 'sbtest1.ibd' ENGINE=INNODB;
Query OK, 0 rows affected (0,02 sec)
[root@mysql-57 ~]# ls /var/lib/mysql | grep sbtest
sbtest1.ibd
And then if you try to alter sbtest1 table's tablespace:
mysql> ALTER TABLE sbtest1 TABLESPACE = sbtest1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
Well, there will be no crash as there is no entry in error log.
But you can see from dmesg that OOM killer played with MySQL process :)
Jan 8 03:18:03 mysql-57 kernel: Out of memory: Kill process 7613 (mysqld) score 956 or sacrifice child
Jan 8 03:18:03 mysql-57 kernel: Killed process 7613 (mysqld) total-vm:28896136kB, anon-rss:3331568kB, file-rss:0kB
Jan 8 03:18:03 mysql-57 systemd: mysqld.service: main process exited, code=killed, status=9/KILL
Jan 8 03:18:03 mysql-57 systemd: Unit mysqld.service entered failed state.
Jan 8 03:18:03 mysql-57 systemd: mysqld.service failed.
Jan 8 03:18:03 mysql-57 systemd: mysqld.service holdoff time over, scheduling restart.
Jan 8 03:18:03 mysql-57 systemd: Starting MySQL Server...
Jan 8 03:18:07 mysql-57 systemd: Started MySQL Server.
Jan 8 03:23:34 mysql-57 systemd: Stopping MySQL Server...
Jan 8 03:23:38 mysql-57 systemd: Starting MySQL Server...
Jan 8 03:23:39 mysql-57 systemd: Started MySQL Server.
Result:
#RESULT 1
sbtest1.ibd general tablespace file size will grow after each kill and it will not reclaimed:
[root@mysql-57 ~]# ls -lth /var/lib/mysql | grep sbtest1
-rw-r-----. 1 mysql mysql 1,9G янв 8 02:54 sbtest1.ibd
Also there will be temp files on dbtest(database folder) which are also will not be removed:
[root@mysql-57 ~]# ls -lth /var/lib/mysql/dbtest/
total 8,1G
-rw-r-----. 1 mysql mysql 6,7G янв 8 03:27 sbtest1.ibd
-rw-r-----. 1 mysql mysql 8,5K янв 8 03:24 sbtest1.frm
-rw-r-----. 1 mysql mysql 1,5G янв 8 03:18 #sql-ib41-3573152321.ibd
-rw-r-----. 1 mysql mysql 8,5K янв 8 03:17 #sql-1dbd_2.frm
-rw-r-----. 1 mysql mysql 8,5K янв 8 02:53 #sql-1caa_2.frm
-rw-r-----. 1 mysql mysql 8,5K янв 8 02:49 #sql-835_9cf.frm
-rw-r-----. 1 mysql mysql 65 янв 7 08:23 db.opt
#RESULT2
Messy results in metadata information:
mysql> select * from INNODB_SYS_TABLESPACES where name like '%btest%';
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| 54 | sbtest1 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 2021654528 | 2021658624 |
| 59 | dbtest/#sql-ib41-3573152321 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 1526726656 | 1526730752 |
| 62 | dbtest/sbtest1 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 7092568064 | 7092572160 |
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
3 rows in set (0,00 sec)
mysql> select * from information_schema.INNODB_SYS_DATAFILES where path like '%btest%';
+-------+-----------------------------------+
| SPACE | PATH |
+-------+-----------------------------------+
| 54 | ./sbtest1.ibd |
| 59 | ./dbtest/#sql-ib41-3573152321.ibd |
| 62 | ./dbtest/sbtest1.ibd |
+-------+-----------------------------------+
3 rows in set (0,00 sec)
#RESULT3
Can not drop tablespace:
mysql> drop tablespace sbtest1;
ERROR 1529 (HY000): Failed to drop TABLESPACE sbtest1
How to repeat:
See the description
Suggested fix:
The best approach is to preventing allocating too much memory.
Calculating buffer pool size against available physical memory.
Related to this report -> I can't even find solution how to remove this files and tablespace.
