Reduce MySQL Core Dump Size by Excluding the InnoDB Buffer Pool
When things go horrible wrong and a process crashes, one of the most powerful things to investigate the cause of the crash is a core dump. As the amount of memory allocated to processes such as MySQL has increased – in some cases approaching 1TiB of memory – enabling core dumps can cause problems of their own. MySQL Server 8.0.14 and later supports an option to reduce the size of the core dump which will be discussed in this blog.
Typically the largest single user of memory for MySQL is the InnoDB buffer pool. This is used to cache the data and indexes for tables using the InnoDB storage engine (the default). It is rarely important to know what is stored in the InnoDB buffer pool when investigating a crash, so a good way to reduce the core dump size is to exclude the buffer pool. In MySQL 8.0.14
innodb_buffer_pool_in_core_file configuration option was added for this purpose. It defaults to
ON which means the buffer pool is included in the core dumps (same behaviour as in older releases).
Let's look a bit more into core dumps, how to enable them, why you may want to exclude the InnoDB buffer pool, and an example.
Enabling Core Dumps
Core dumps can be enabled by including the
core-file option in the MySQL configuration. It is disabled by default. However, on Linux/Unix it is in general not enough to enable
core-file to get a core dump in case of a crash as the system will also limit the size of a core dump. Typically this limit is 0 by default.
The details of the steps required to enable core dumps depends on the Linux distribution used. The following will discuss the
core-file option and the limit on the core size. However, the exact steps required may differ for your system and possibly include additional steps.
Enabling Core Dumps in MySQL
As mentioned, the option in MySQL to enable core dumps is
core-file. This can only be set using the MySQL configuration file (
my.ini). For example:
After you have updated the configuration file, the change will take effect the next time MySQL is restarted.
Core Size Limit
Linux includes various limitations on the resources a process can use. This helps improve the stability of the system as a whole, but the default limits may be too restrictive for something like a database host where one process uses most of the resources. One of the limitations is the size of the core dump and typically the default limit is 0.
You can verify the current limits for your process using the proc file system, for example (assuming only a single
mysqld process on the system):
shell$ sudo cat /proc/$(pidof mysqld)/limits Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 10240 12288 processes Max open files 10240 65536 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 15611 15611 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us
There are various ways to change the limits for MySQL and which one to use depends on how you start MySQL. If you start MySQL from the command-line, you can simply use the
ulimit command first:
shell$ ulimit -c unlimited # Start MySQL shell$ sudo cat /proc/$(pidof mysqld)/limits | grep 'core file size' Max core file size unlimited unlimited bytes
This has set the limit for the core file size to unlimited.
You can also configure the limit in the
/etc/security/limits.conf file or in a new file in
/etc/security/limits.d/. This is a better way to persist a setting and allows you for example to configure the limits for a given user. For example to set the core dump file size to unlimited for the
mysql soft core unlimited mysql hard core unlimited
However, on distributions using
systemd (including Oracle Linux 7, Red Hat Enterprise Linux (RHEL) 7, and CentOS 7),
systemd completely ignores
/etc/security/limits.conf. Instead you need to use the service file for the process. For MySQL this is the
/usr/lib/systemd/system/mysqld.service file, or if you have multiple instances on one host,
/usr/lib/systemd/system/mysqld@.service. If you use Debian, replace
mysql. The option to set in the service file is
LimitCore, for example:
[Service] LimitCore = infinity
If you use
mysqld_safe to start MySQL, you can use the core-file-size option (in the
[mysqld_safe] group in the MySQL configuration file or on the command-line) to change the limit. This requires that the hard limit is high enough for the requested limit.
Problems with Core Dumps
Over time the amount of memory allocated to MySQL has grown. This particularly applies to the InnoDB buffer pool – as databases handle more data, there is also a greater need to cache the most used data and indexes in memory. Today, some MySQL instances have total memory allocations approaching 1TiB.
When a core dump happens, the memory allocated to the process is written out to disk. For processes using a large amount of memory, this may take some time. Furthermore, there is a possibility that the system may run out of disk, particularly if repeated crashes occur. The disk space usage can particularly be an issue when MySQL is running as a service as it is likely the core file in that case is written to the data directory. From a support perspective, it is also less than trivial to share a core dump that is several hundred gigabytes large.
The InnoDB buffer pool is in most cases the largest contributor – it can easily contribute 75% or more of the total core dump size. So, excluding the buffer pool can significantly reduce the size of the core dump. I will give an example at the end of the blog.
To finish off this blog, let's look at an example. In this case, MySQL has been configured with
innodb_buffer_pool_size = 2G and data has been loaded, so most of the buffer is in use:
mysql> SELECT SUM(FREE_BUFFERS) FROM information_schema.INNODB_BUFFER_POOL_STATS; +-------------------+ | SUM(FREE_BUFFERS) | +-------------------+ | 0 | +-------------------+ 1 row in set (0.02 sec)
And from the output of
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 11142 mysql 20 0 4121468 2.261g 0 S 4.0 58.6 2:38.50 mysqld
When MySQL is killed with signal 11 (segmentation fault), a core dump is created. Without
innodb_buffer_pool_in_core_file, the core dump is almost 3 gigabytes (see output at the end of the blog).
If the new feature is enabled by adding
innodb_buffer_pool_in_core_file to the configuration file or by persisting it
mysql> SET PERSIST innodb_buffer_pool_in_core_file = OFF; Query OK, 0 rows affected (0.02 sec)
The change takes effect immediately. The core dump will now be much smaller – around the size of the buffer pool smaller:
shell$ ls -lh /var/lib/mysql/core.* -rw-------. 1 mysql mysql 2.8G Feb 11 20:30 /var/lib/mysql/core.11142 -rw-------. 1 mysql mysql 759M Feb 11 20:47 /var/lib/mysql/core.14456
core.11142 is with
innodb_buffer_pool_in_core_file enabled and
core.14456 with the option disabled.