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);