MySQL 5.7 + InnoDB: mysqld is CPU bound, with ever increasing memory usage

Question

[ADDED ANSWER 2019-10-08] I've posted an answer to this question. tl;dr: Force MySQL to use jemalloc or tcmalloc.

---

I recently upgraded from 5.5.25-rc (very old install), through 5.5.62, then 5.7.27. At the same time, I made the decision to migrate most (possibly all) tables from MyISAM to InnoDB.

When mysqld is first started, everything runs as expected. It's a busy server with almost constant inserts. But over time:

• The mysqld process seems to hit a limit, consistently consuming around 100% CPU (there's four physical cores, so 100% suggests a fully loaded single thread).

• Disk activity falls from near 100% to near nil, despite pending inserts continuing to pile up. One insert was manually killed by me at around 40 minutes; it's now 44 hours (nearly 2 days) later, and despite being killed it still appears in SHOW PROCESSLIST, with other processes waiting for it to release the lock.

• The memory usage periodically increases, well beyond the value that I would expect MySQL to use, until memory is exhausted and the OS kills the process.

To give an example of the latter, here's selected fields from top at various points: time since starting mysqld, allocated memory, resident memory, CPU:

10 mins:    2827M   1131M   100.39%
1 hour:     2903M   1421M   100.20%
12 hours:   3696M   3109M   111.18%
24 hours:   3846M   3435M   100.00%
45 hours:   4332M   3699M   100.00%   (now)

Note the difference in allocated memory between 10 minutes and 45 hours is an additional 1505M.

[ADDED 2019-10-04] After upgrading the mainboard/RAM/CPU, the behaviour is the same, just with bigger numbers:

10 mins: 9530 mysql        47  20    0    14G  5866M select  11  10:17 117.09% mysql
1 hour:  9530 mysql        46  20    0    15G    14G select   4  42:46  39.50% mysqld
4 hours: 9530 mysql        45  20    0    22G    21G select   9 122:32  25.78% mysqld
7 hours: 9530 mysql        45  20    0    27G    24G select   2 216:44  24.37% mysqld
<process killed by OS>

With the additional available RAM I configured the pool size to 12000M (~12GB), so the MySQL process eventually using 27GB of RAM (more than double the configured pool size) is still an unexpected level of overhead.

Things I've tried to diagnose the issue:

• Restarting mysqld (plus also trying a clean reboot of the server). Things return to normal for a while, but the long term behaviour does not change.

• Various MySQL memory estimate tools and commands. Some of these are quite old, some do not support FreeBSD, and others do not specify a particular MySQL version or series, so it's difficult to interpret the output. Actual memory usage far exceeds all of the successful estimates.

• Enabling performance/sys schema to track memory usage. The changes in total reported memory usage over time are minor (+/- a couple of hundred MB), and do not explain why the actual memory allocated to MySQL continues to grow.

• Drive speed tests, SMART self-tests, ZFS scrub. No signs of any storage problems.

At this point I'm completely baffled. I cannot find anyone else who has this problem.

This system is fairly old, but it showed no signs of issues until the recent MySQL upgrade. I do have a new mainboard and RAM on order, and I suspect that the extra RAM (32GB vs 8GB) will make a big difference, but I'm concerned it will just mask whatever this problem is. (New mainboard has been installed, and excessive memory consumption has not changed.)

Thank you in advance for any advice.

ADDITIONAL INFORMATION

System config:

• Q8400 (I think) CPU Now Xeon E5-2620
• 8GB RAM Now 32GB ECC RAM
• MySQL 5.7.27, installed via FreeBSD package manager system
• FreeBSD 12.0
• 2 x 2GB 7200RPM HDDs in ZFS mirror: recordsize=16k, atime=off, compression=lz4. Now with SLOG on dedicated 16GB NVMe M.2 SSD.

[ADDED 2019-10-05]

# su mysql
$ ulimit -a
cpu time               (seconds, -t)  unlimited
file size           (512-blocks, -f)  unlimited
data seg size           (kbytes, -d)  33554432
stack size              (kbytes, -s)  524288
core file size      (512-blocks, -c)  unlimited
max memory size         (kbytes, -m)  unlimited
locked memory           (kbytes, -l)  unlimited
max user processes              (-u)  34232
open files                      (-n)  940113
virtual mem size        (kbytes, -v)  unlimited
swap limit              (kbytes, -w)  unlimited
socket buffer size       (bytes, -b)  unlimited
pseudo-terminals                (-p)  unlimited
kqueues                         (-k)  unlimited
umtx shared locks               (-o)  unlimited

my.cnf : https://pastebin.com/q6WQ55bL

Dump of (1) top (memory usage) (2) SHOW GLOBAL STATUS (3) SHOW GLOBAL VARIABLES : https://pastebin.com/DyqUpbyG (queried every 5 mins; this is the last successful dump before server process was killed.)

Answer 1

See this bug report, where others appear to have the same issue:

https://bugs.mysql.com/bug.php?id=83047

Note that the reports here mention Linux, but I use FreeBSD, so it does not appear to be an OS-specific issue.

The suggested workaround is to try a third party malloc implementation, such as jemalloc, or tcmalloc.

FreeBSD already uses jemalloc as its default allocator (although I was unable to confirm 100% that the mysqld executable I have was actually linked against it), so I tried installing and configuring tcmalloc. (See https://github.com/gperftools/gperftools for source; your OS may have it available as a package.)

[mysqld_safe]
malloc-lib=/usr/lib/libtcmalloc_minimal.so

An hour after restart, allocated memory for the mysqld process was at 5954M.

Now, 31 hours after restart, memory is at 6037M, which is an increase of just over 1%. In addition, that value has not changed for the past 15 hours, which suggests that the mysqld process is in a state where all future allocations can be satisfied internally.

It's alarming that somewhere between 5.6 and 5.7, something in MySQL changed that causes the default malloc implementation to go off the charts, and a third party library is required to fix. I cannot find any official acknowledgement of this bug.

Hope this answer helps anyone else experiencing this very frustrating problem.

Answer 2

Rate Per Second=RPS - Suggestions to consider for your my.cnf [mysqld] section

thread_cache_size=64  # from 8 to reduce threads_created (RAM allocations)
innodb_log_buffer_size=256M  # from 16M for ~ 20 minutes of RAM storage
innodb_buffer_pool_size=16G  # from ~ 5G for about 1/2 RAM
innodb_change_buffer_max_size=50  # from 25 percent to support insert RPS of 510
innodb_io_capacity=1000  # from 200 for higher IOPS
read_rnd_buffer_size=256K  # from 8M to reduce handler_read_rnd_next RPS of 9

Observations, A) are you missing CLOSE() when client processing complete (resources not released) B) Open Files near 1 million only consumes RAM for no useful purpose when 10,000 would support your current needs. C) These suggestions are the 'tip of the iceberg' - there are many more needed for your high volume application. Disclaimer: I am the content author of website mentioned in my profile, Network profile where we offer FREE Utility Scripts, additional analysis services and contact info.

Answer 3

In my experience, InnoDB inserts take more CPU time than MyISAM. You may get an idea for your table by preparing two mysqldump files differing only in Engine setting (and maybe having only partial data if the table in question is big). They could be tested on another host, not necessarily the live. Restore the two dumps one after another, monitoring if CPU usage is really around 100% (it should be) and compare the times it took to complete. InnoDB dump may take times longer to restore, which would mean it takes more CPU cycles to create one InnoDB record (it also depends greatly on field count and types).

If the above test really shows InnoDB takes more CPU per insertion, then you may want to get a server with better single-core performance or convert the tables back into MyISAM.

Later Addition (Sep. 29, 2019):

After some thought I decided my explanation doesn't fit the situation - it doesn't look like the system in question processes tens of thousands of inserts per second so even an older CPU should be capable of serving it.

But I found a complaint on the internet that may explain both this and my own problems with InnoDB doing something CPU-intensive in backgroud.

So my newer suggestion would be to try disabling innodb_stats_auto_recalc and maybe innodb_stats_persistent settings.

Addition (Oct. 05, 2019):

I see in the recent dump statistics recalculation is already off so it doesn't explain memory usage. I have another idea to check: maybe a big table got broken, the engine finds it at random times and starts repair process but never manages to complete it because the server gets restarted. Unfortunately, I don't have exact experience about diagnosing this situation but searching MySQL error log for "repair" may probably help. I found this kind of entry in mine:

[ERROR] /usr/sbin/mysqld: Table './<db_name>/<table_name>' is marked as crashed and last (automatic?) repair failed

Also, the following query may help identify problematic tables:

show table status where comment <> '';

Addition Oct. 06, 2019:

I also just discovered MySQL 5.7 has memory profiling. It would be interesting to try the following selects during the time of memory consumption growth. (Though I don't have any idea of what to expect.)

select sum(SUM_NUMBER_OF_BYTES_ALLOC) from performance_schema.memory_summary_global_by_event_name;
select * from performance_schema.memory_summary_global_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC DESC limit 3\G

Answer 4

We had similar issue, Mysql never stop eating RAM, he needed a month to eat all 16GB. Of course it was beyond the maximum possible memory usage set in .cnf file.

We used ubuntu 18.02 with mysql 5.7 version.

After few weeks of fight we found that performance_schema was caused. In my.cnf file, we add:

 performance_schema=0

that was solved our issue.

If will not help in your case maybe for someonelse this information will be helpfull. Good luck !