OOM killer vs. MySQL 5.7.10 epic results with over-allocating memory

Recently i have figured out that we can over-allocate InnoDB buffer pool size as I have already reported:

#79850

So as a result I can start MySQL with 300GB buffer pool size in my 16G laptop.

Config:

innodb_buffer_pool_size=300G
innodb_buffer_pool_instances=64

Started as:

2016-01-05 15:23:58 16211 [Note] InnoDB: Initializing buffer pool, size = 300.0G
2016-01-05 15:24:31 16211 [Note] InnoDB: Completed initialization of buffer pool

If you think that it is not harmful, let’s examine another report:

#79880

With MySQL 5.7.10 in my VM on CentOS 7:

I have:

[root@mysql-57 ~]# free -m
total used free shared buff/cache available
Mem: 3428 377 664 1 2387 2862
Swap: 2047 92 1955

[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.

My test table:

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 |

And now i am creating a general tablespace:

mysql> CREATE TABLESPACE `sbtest1` ADD DATAFILE 'sbtest1.ibd' ENGINE=INNODB;
Query OK, 0 rows affected (0,02 sec)

Created as sbtest1.ibd file on MySQL datadir:

[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, if you think this is a crash. NO! OOM killer has some fun with mysqld 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.

The tragic results for us:

#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.ib
d

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 output from 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

And even we can not drop tablespace:

mysql> drop tablespace sbtest1;
ERROR 1529 (HY000): Failed to drop TABLESPACE sbtest1

The golden rule for us, allocating memory based on available physical memory of our servers. Do not rely on Virtual Memory of OS.
Rely on real life.
Dear MySQL community thanks for attention.

3 thoughts on “OOM killer vs. MySQL 5.7.10 epic results with over-allocating memory”

  1. Hi,
    And sorry for late reply.
    Is it because you have over-allocated innodb buffer pool size? Or you think it is because of some other issue?

Leave a Reply

Your email address will not be published. Required fields are marked *