The newest release of MySQL 8 presents a new active system variable @@innodb_buffer_pool_in_core_file which lets you forget the Buffer Pool’s memory content when making a core file.
This change is an edition of a patch donated by the Facebook team. We would like to acknowledge and admit this significant and timely input by Facebook.
A Core File
A core file or core tip is a file that archives the memory image of a in succession process and its process status (record values etc.). Its primary use is inquest debugging of a program that stopped while it ran outdoor a debugger.
To enable core file formation in case MySQL clangs, you have to specify --core-file command line choice when successively mysqld, which changes the worth of @@core_file read-only system variable to ON from its defaulting value of OFF.
For instance, what if you occur to be using Linux, and you’ve run
./bin/mysqld --core-file --datadir=/var/mysql/data
and it stopped due to some bug (which you can put on using kill -s SIGABRT $pid where $pid is the id of your mysqld method). Then you can review the state just before the database has stopped using:
gdb ./bin/mysqld /var/mysql/data/core.$pid
The precise filename and position of the core file rest on your particular system configuration – our example accepts that cat /proc/sys/kernel/core_pattern yields core or core.%p and that cat /proc/sys/kernel/core_uses_pid outputs 1, and that /var/mysql/data is your information directory (which is used as current occupied directory by mysqld process, and that’s why the core file is by defaulting created in it).
The Buffer Pool
The InnoDB Buffer Pool is a storing area for hiding data and indexes in memory. Together with InnoDB Redo Log and the data pages persevered on disk, they form a low-level concept of I/O: data can be thought of as separated into pages, where each page is recognized by its tablespace id and page id, and InnoDB can load, alter, and store such pages in an atomic way. Only on top of this concept the more complex structures of many primary and secondary indexes are built, which use these low-level pages to store nodes of trees for sample.
To do any work on a page, the page needs to be transported from disk to memory, and the place in memory where we save such pages is called the Buffer Pool. Following usages of the same page can be helped from the Buffer Pool as long as the page was not detached from it (a.k.a. dispossessed) which may occur if there is not sufficient space in memory to hold all the pages which are opened. In this respect the Buffer Pool helps as a cache for pages on disk. Also, to evade writing a page to disk each time it is altered, a page is only noticeable as muted in memory, but the write to disk is delayed until it is essential, and only the info needed to reconstruct the state of the page after bang is written to the add only write in advance log (called the Redo Log). One can see from this irregular explanation that the bigger the Buffer Pool the rarer are states in which we have to do costly disk I/O processes. Thus, the Buffer Pool is often constructed to eat a substantial fraction of accessible RAM.
Since the Buffer Pool exist in in main memory, and the memory of a procedure is deserted to a core file, it follows that a huge Buffer Pool results in a enormous core file. This can be difficult for several reasons:
a big file eats space on disk, which can create a torrent of problems if there is not sufficient space
a big file receipts longer to write
a big file is more problematic to move from one place to another, in specific when one needs to send it to an important person else for analysis
Also, the Buffer Pool covers pages of the database, which poses some safety thoughts when it gets deserted to a file.
There are however cases, where examining the crash would profit from having access to the precise content of pages at the instant of crash.
So, there are good details to eliminate the Buffer Pool from a core file, but also there are situations where you would somewhat favor to have the data.
Advising operating system about our intention
On Linux 3.4+ a computer programmer can use a non-POSIX delay to madvise() interface by calling madvise(ptr,size,MADV_DONTDUMP) to let the operating system know, that size bytes of memory jagged by ptr should not be deserted to a core file.
In the cover donated by Facebook madvise() was used on all large barriers owed by MySQL to make core files smaller.
We have ported this patch to MySQL 8.0 thinning it down to the Buffer Pool sides only.
The innodb_buffer_pool_in_core_file mutable
Striving for progressive compatibility, we’ve presented a new structure variable @@innodb_buffer_pool_in_core_file, which by default is set to ON , in order to mimic the old performance. Also, this new mutable only affects performance if @@core_file is ON, as then there will be no core file produced at all.
Only when this mutable is set to OFF (for example by passing --skip--innodb-buffer-pool-in-core-file via command line) we transformation the behavior. If all following circumstances hold:
@@innodb_buffer_pool_in_core_file is OFF and
@@core_file is set to ON, and
the operating system cares madvise(ptr,size,MADV_DONTDUMP)
the OS will be directed to exclude the Buffer Pool pages from a core file.
When to some degree goes wrong, we’ve definite to err on the safe side. If the user didn’t want the Buffer Pool data to be comprised in a core file, but the operating system does not completely care that meaning, we make sure that core file will not be made at all. We have confidence in that this is a better choice than writing the core file anyway, which might depiction complex data or excess the disk. Thus, if @@innodb_buffer_pool_in_core_file is inactivated but an madvise() failure occurs or pattern Buffer Pool pages as MADV_DONTDUMP is unconfirmed by the operating system, an error is written to server’s error log and the @@core_file variable is restricted to prevent core file from existence written.
This may sound a bit complex, so here is a table casing all cases:
In an effort to make run-time formation as flat and humble as possible, this new @@innodb_buffer_pool_in_core_file system mutable is go-ahead, so you can alteration its value whenever you like, for instance using this command:
SET GLOBAL innodb_buffer_pool_in_core_file = OFF;
(Keep in attention, that as clarified above, on systems which do not provision MADV_DONTDUMP above command will set @@core_file to OFF, and meanwhile @@core_file is read-only there is no way to set it back to ON deprived of resuming the server. You can use ./mtr --mem innodb.mysqld_core_dump_without_buffer_pool to checked if your system provisions this feature.)
To reinstate the old conduct, use:
SET GLOBAL innodb_buffer_pool_in_core_file = ON;
You can checked the present value using:
SELECT @@global.innodb_buffer_pool_in_core_file;
You capacity also want to checked if @@core_file is enabled:
SELECT @@global.core_file;
Results
How much you improvement by allowing this option is clearly reliant on on how large your Buffer Pool was in the first place, but to give you some uneven idea here’s a table with instance results for --innodb-buffer-pool-size=1G:
as you can understand the InnoDB page size itself influences the size of core file, since the lesser the page, the greater the number of pages, and thus more metadata for these pages. The change amongst ON and OFF is not exactly 1GB as there is some alteration of core file size between many runs and the exact instant of deafening the server, and clearly you can’t easily din the same process further than once.