Bug #88069 Innodb buffer pool cause OUT OF MEMORY
Submitted: 12 Oct 2017 6:24 Modified: 13 Oct 2017 13:16
Reporter: yx jiang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.7.18 OS:CentOS (6.4)
Assigned to: CPU Architecture:Any
Tags: oom innodb buffer pool

[12 Oct 2017 6:24] yx jiang
Description:
Analyze tables cause MySQL oom.

How to repeat:
step 1
Generate 20k tables and 1TB data.

step 2
install 32G total memory

set 24G innodb buffer pool size (innodb_buffer_pool_size=24G)

step 3
pre-occupy memory 

build c++ code below

#include <sys/mman.h>
#include <iostream>
#include <string.h>

#ifndef  NULL
#define NULL (0)
#endif

using namespace std;
const long long page_size = 4 * 1024 * 1024 * 1024L;

int main(int argc, char *argv[])
{
  void *ptr;
  long long size = 16 * 1024 * 1024 * 1024L;

  ptr = mmap(NULL, size, PROT_READ | PROT_WRITE,
             MAP_PRIVATE | MAP_ANONYMOUS, -1, 0);
  if (ptr == 0)
  {
    cout << "fail mmap." << endl;
    return -1;
  }

  long long position = 0;
  while (true)
  {
    if ((position + page_size) > size)
    {
      position = 0;
    }
    cout << position << endl;
    memset((char*)ptr + position, 0, page_size);
    position += page_size;
  }
  cout << "init finished." << endl;
  string ok;
  cin >> ok;

  return 0;
}

g++ mmap2.cc -o mmap2

./mmap2

step 4
run mysqlcheck tool like below
mysqlcheck --login-path=local -A -a --skip-database=mysql --skip-write-binlog

Suggested fix:
I tried to memset the allocate memory by mmap. This cause os allocate real space to Innodb buffer pool. This make Innodb has enough buffer space.

changes as below
============
diff --git a/storage/innobase/os/os0proc.cc b/storage/innobase/os/os0proc.cc
index ac44571..74ca99e 100644
--- a/storage/innobase/os/os0proc.cc
+++ b/storage/innobase/os/os0proc.cc
@@ -155,6 +155,7 @@ skip:
                os_atomic_increment_ulint(
                        &os_total_large_mem_allocated, size);
                UNIV_MEM_ALLOC(ptr, size);
+        memset(ptr, 0, size);
        }
 #endif
        return(ptr);
[13 Oct 2017 13:16] MySQL Verification Team
Hi!

Thank you for your bug report, but this is expected behaviour. InnoDB buffer pool gets 24 G, plus other buffers will by default take at least another 2 Gb, plus 16 Gb of memory allocated by test program takes a total of 42 Gb, while you have 32 Gb.

Not a bug.