Worklog for task "Investigate why MySQL consumes so much memory on HappyBaby"

Diagnosing Abnormal Memory Consumption of MySQL 5.7 in Docker and Fixing the Cause

While working with a Docker environment, an atypical issue was discovered when starting the mysql:5.7 container: before starting normally, the MySQL container rapidly increased its memory consumption over the course of several minutes, reaching about a quarter of the host's available RAM, then released the memory and repeated the cycle. On a machine with ~64 GB of RAM, the spikes reached about 16 GB; in an environment with ~2 GB of RAM, around 500 MB was observed. After several such cycles, consumption stabilized at a normal level.

The initial MySQL configuration was almost minimal:

mysql:
  image: mysql:5.7
  environment:
    - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD:-your_mysql_password}

There were no separate settings for innodb_buffer_pool_size or a memory limit.

Initial Observation

The docker stats graph showed a characteristic "sawtooth" pattern: the process quickly accumulated gigabytes of memory, reached a certain limit, then almost completely released the memory, after which the cycle repeated. At the same time, there were almost no logs at the beginning of the startup, creating the impression that MySQL was simply taking a long time to initialize without any messages.

After starting with a cleared datadir, it was possible to see the exact sequence of events. Before the fix, the log looked like this:

03:33:05 Entrypoint script for MySQL Server 5.7.44 started
        ... 37 seconds of silence ...
03:33:42 Switching to dedicated user 'mysql'
03:33:42 Entrypoint script for MySQL Server 5.7.44 started
        ... another 37 seconds of silence ...
03:34:19 Initializing database files

That is, almost all the abnormal time was spent not on InnoDB initialization and not on creating database files, but on the actions of docker-entrypoint.sh performed before the full server startup.

At the same time, MySQL itself clearly reported:

InnoDB: Initializing buffer pool, total size = 128M

This ruled out the hypothesis that the huge memory consumption was related to the InnoDB buffer pool. By default in this configuration, it was only 128 MB.

Cause

The problem turned out to be related to the well-known behavior of MySQL 5.7 with an excessively large open files limit (RLIMIT_NOFILE).

The official Docker entrypoint for MySQL makes service calls to mysqld before actually starting the server, specifically in a mode like:

mysqld --verbose --help

This is used to check the configuration and get values like datadir, socket, and other parameters.

In MySQL 5.7, there is a known issue: if RLIMIT_NOFILE is abnormally large, the service startup of mysqld --verbose --help can trigger a very large temporary memory allocation. In Docker, this manifests especially noticeably if the container inherits a huge nofile from the Docker daemon / systemd.

Because of this, the following sequence occurred:

docker-entrypoint.sh
    ↓
service startup of mysqld --verbose --help
    ↓
huge temporary allocation due to RLIMIT_NOFILE
    ↓
increase in RAM and CPU over tens of seconds
    ↓
service process terminates
    ↓
memory is freed
    ↓
entrypoint performs the next similar call
    ↓
repeated spike

This explained several observations at once:

• large memory spikes occurred before normal MySQL startup;
• memory returned to the system after each spike;
• there were multiple cycles of equal duration;
• there were almost no normal server logs at this time;
• after completing the bootstrap process, MySQL operated with normal memory consumption.

Solution

A normal open files limit was explicitly specified in Docker Compose:

mysql:
  image: mysql:5.7
  environment:
    - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD:-your_mysql_password}

  ulimits:
    nofile:
      soft: 65536
      hard: 65536

Important: this parameter is not a container memory limit. It only limits the number of file descriptors that a process can open simultaneously. Descriptors include files, sockets, and connections.

That is, nofile: 65536 does not mean 64 MB, 64 GB, or any other amount of memory and does not directly set the container's memory limit in any way.

The value 65536 was chosen as a normal and sufficiently large limit for a regular MySQL instance in a web project. It leaves a large margin for the number of files/sockets, but does not allow MySQL 5.7 to fall into the problematic scenario with a huge RLIMIT_NOFILE.

Verifying the Result on an Existing Database

After adding ulimits.nofile, restarting the existing datadir changed radically:

03:38:54 Entrypoint started
03:38:54 Switching to dedicated user 'mysql'
03:38:54 Entrypoint started
03:38:54 mysqld starting
03:38:54 mysqld: ready for connections

Both previous pauses of approximately 37 seconds disappeared completely. The server became ready for connections almost instantly.

Verification on a Completely Clean Datadir

To properly verify the initial initialization, the MySQL data was actually cleaned, taking into account that bind mounts were used:

volumes:
  - ./mysql/data:/var/lib/mysql
  - ./mysql/conf.d:/etc/mysql/conf.d

After that, a full cold start was performed. The new log:

03:41:42 Entrypoint started
03:41:42 Switching to dedicated user 'mysql'
03:41:42 Entrypoint started
03:41:43 Initializing database files
03:41:45 Database files initialized
03:41:45 Starting temporary server
03:41:49 MySQL init process done. Ready for start up
03:41:49 mysqld: ready for connections

Thus, the complete first startup with an empty database took about 7 seconds instead of several minutes.

At the same time, InnoDB still used the standard buffer:

InnoDB: Initializing buffer pool, total size = 128M

That is, the fix did not reduce MySQL's working memory with an artificial limit—it specifically eliminated the erroneous temporary allocation at the bootstrap stage.

Actual Memory Consumption After the Fix

After normal startup, docker stats showed:

173.4MiB / 61.5GiB

That is, the container actually used about 173 MB of RAM. The number 61.5GiB on the right is the host memory available to the container in the absence of a separate memory limit, not the MySQL reservation.

ulimits.nofile does not directly affect this value. To limit the container's RAM, a separate Docker memory limit would be required (mem_limit, resource limits, etc.), but within the scope of this issue, that was not necessary.

Summary

The cause of the long startup and huge memory spikes was not MySQL's actual working consumption, not the InnoDB buffer pool, and not the data volume. The problem was caused by MySQL 5.7 during service startups from the Docker entrypoint when RLIMIT_NOFILE was excessively large.

Fix:

ulimits:
  nofile:
    soft: 65536
    hard: 65536

Result:

• repeated gigabyte RAM spikes disappeared;
• ~37-second pauses on entrypoint service calls disappeared;
• cold start of an empty database was reduced to about 7 seconds;
• working memory consumption stabilized at around 170–180 MB;
• the container memory limit was neither introduced nor modified;
• nofile=65536 was left as a permanent workaround for mysql:5.7 in the current Docker environment.