2

[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.)

  • 1
    I've made several edits to the OP, including adding extra data showing a remarkable improvement in performance once mysqld was restarted, but also a drastic increase in the continually upward allocations of memory after about 25 minutes. I've had to shut down mysqld manually, because there's only a few hundred megs of memory left. Came close to hitting 100% swap before it shut down gracefully. The server is completely unusable in this state. – rowan194 Sep 29 '19 at 17:08
  • 1
    @WilsonHauck I did run mysql_upgrade, and had to manually delete+DROP several sys tables, then recreate them with (from memory) mysql_system_tables.sql. Server uptime of 24 hours is pretty much impossible right now - it runs for anywhere from 40 minutes to 5 or 6 hours before exhausting memory. Some of the data you've requested is difficult (BSD does not have the -m option for iostat, SHOW FULL PROCESSLIST would include bulk inserts with tens of thousands of items) but I'll see what I can come up with. Thanks. – rowan194 Sep 30 '19 at 4:12
  • 1
    @WilsonHauck For some clarification: by "not edited" my.cnf do you mean you also want to see other areas such as [mysql] and [mysqldump], or do you think I've trimmed out directives I think don't apply? What I posted above contains all [mysqld] directives; all I did was filter out comments and blank lines, and changed a couple of paths. – rowan194 Sep 30 '19 at 9:08
  • 1
    @WilsonHauck I appreciate your assistance, and haven't forgotten your requests. Now that the new hardware has arrived, I have updated the post with some additional memory consumption information, and the current my.cnf. (tl;dr throwing more RAM at MySQL doesn't fix the problem). Next step is to try downgrading to a slightly older version of MySQL. – rowan194 Oct 4 '19 at 3:25
  • 1
    I wrote a simple program to periodically dump out variables and have updated the end of the OP with this information. This is the only way to get data near to the crash point, because memory allocation (and subsequent exhaustion) is not consistent. SHOW FULL PROCESSLIST contains proprietary data and will be massive (some inserts supply 100000 items). Mysqltuner is also difficult since currently the server has been running out of memory 5 to 8 hours after started. Thanks again. – rowan194 Oct 5 '19 at 10:32
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.

|improve this answer|||||
0

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.

|improve this answer|||||
  • 1
    Thanks for the suggested tweaks, but unfortunately, the memory problem remains. With 16GB already dedicated to the InnoDB pool, memory exhaustion occurred within 2 hours. Even setting it to something ridiculously low like 500MB only prolongs the inevitable by about 6 to 8 hours. How can a 0.5GB pool end up using 23GB of RAM?! I've had to resort to a script which sends a KILL signal as soon as mysqld uses any swap, in the hope that there are sufficient resources remaining to shut down cleanly. I'm going to tackle building an earlier version from source. – rowan194 Oct 6 '19 at 5:43
  • 1
    Re close - do you mean mysql client sessions? Clients doing bulk inserts will keep the session open for some time (often hours or days). As an experiment, I manually killed the 4 sessions currently active (clients are robust and will pause then reconnect and retry), but allocated memory did not change. Unsure about file limits - they're just the default. The previous version of MySQL (and any other process running on that server) will be allocated the exact same limits. – rowan194 Oct 6 '19 at 5:55
  • Could we use Skype? Syntax errors can cause memory leak when query parser runs. Could you post text results of free -h from OS so we can see 'swap space' available. RedHat recommends 20% of RAM for swap space configuration - unless 'hibernate' server is available (which I can not imagine on a db server). If OS 'hibernate' is enabled, RedHat recommends RAM * 1.2 for swap space. Could you post last complete 400 lines of your error log so we can see what has been recorded recently? Yes, I do mean whenever client is done, do you have CLOSE() function running to release resources? – Wilson Hauck Oct 6 '19 at 13:43
  • @rowan194 I will be back on line in about 10 hours. Thanks, Wilson – Wilson Hauck Oct 6 '19 at 13:52
  • @rowan194 Along with your tcmalloc, are you using my Suggestions of Oct 5, 2019? If they were helpful, please upvote/accept as part of your improvements. – Wilson Hauck Oct 10 '19 at 12:08
0

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
|improve this answer|||||
  • 1
    Bear in mind that I am new to InnoDB, so it's possible I need to tweak my config. Perhaps I've missed something obvious. Your hypothesis sounds plausible, but it doesn't explain 1) Why the server works as expected when restarted, for at least a few hours and 2) The apparently uncontained memory increase. – rowan194 Sep 29 '19 at 13:41
  • I believe it would be useful to inspect show full processlist output during the slowdown. Is it really some kind of bulk insert that is active and other threads are waiting for it to complete? – ichalov Sep 29 '19 at 14:23
  • 1
    There are multiple databases for different things, so it's not like a single client is hogging a table for an extended period. One database is updated by two clients which attempt an insert every 15 minutes, and the insert typically takes a few seconds. Currently, there's an insert which has been in "update" status for 48665 seconds. – rowan194 Sep 29 '19 at 14:33
  • Sorry, I don't have reliable explanations for insert being in "update" status. Is there a chance it rebuilds indexes on the whole table for some reason? I saw similar behavior on InnoDB tables after large inserts but can't find any descriptions of this process in docs. That would also explain memory usage (it would need sort areas in RAM). – ichalov Sep 29 '19 at 14:44
  • In my experiments, I couldn't find config item to make inserts faster when there are no bottlenecks apart from CPU. The only thing I can suggest is to use higher key_buffer_size in comparative testing (e.g. half of innodb_buffer_pool_size). Otherwise, it wouldn't be fair to MyISAM (and would probably be visible through increased disk usage). – ichalov Sep 29 '19 at 14:45
0

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 !

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.