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