Runaway MySQL memory usage after upgrade from 4.0 to 5.0

[amyjess]

Hi, I'm the Zabbix administrator at a company with multiple Zabbix installations.

About a month ago, I upgraded one of our Zabbix installations from 4.0.x to 5.0.2. It worked without any problems, except for a minor increase in memory usage; I had to bump up the RAM on the VM from 16GB to 32GB.

After running 5.0 on this installation for a month, I upgraded our other installation. This is a more complex system with four proxies (the one I upgraded last month has no proxies), and the data and items are controlled by another team at my company (I have much tighter control over the other one), though I am still the Zabbix administrator. After this upgrade -- to 5.0.3 since it had come out by then -- MySQL memory usage is massively ballooning. It just keeps going up and up and up. The VM started out with 32GB of RAM, then I upped it to 48GB and then 64GB, and MySQL just eats all the RAM. I've tried tweaking MySQL's settings: when this all started, MySQL (or rather, MariaDB... we are running RHEL 7) was just using the default settings for everything. I've spent today throwing everything at the wall to see what sticks, but nothing seems to work. The closest thing that seems to work is choking max_connections, but that introduces its own set of problems: it becomes impossible to access the web GUI after it fills up the maximum number of connections.

Has anyone else here had a similar problem, and if so, how did you solve it?

Thank you!

[tim.mooney]

There's been at least one other report on these forums of a similar phenomenon.

Before you spend more time debugging, it might be worth it if you try Zabbix 5.0.4, which has been out for a week or so.

Also, what's the exact version of MariaDB you're using?

Finally, just to understand more about the problem, what tools or methods have you used to zero in on MariaDB memory use being the main culprit for memory use?

I ask because there have also been problems where the php-fpm workers use far more memory than they should. I'm highly suspicious that's a bug in RHEL's PHP 7.2. There are workarounds for that issue I can point you to if needed, but that would only be relevant if the problem is PHP, not MariaDB.

[amyjess]

The main things I've tried re: MariaDB memory usage is from this article https://tech.labelleassiette.com/how...ql-61ea7d1a9bd plus something I found somewhere else suggesting I reduce MariaDB's wait timeout from the default to something much lower. My experience is that choking max_connections is just a good way to render Zabbix UI unusable even if it results in lower memory consumption. In fact, I had to borrow a config item from our other Zabbix installation and set max_connections to 1500 or it would very quickly hit the point where Zabbix UI would throw up and say it can't connect to the database because of too many connections.

I actually am starting to become increasingly convinced that php-fpm is the problem. I've found that if I leave the php-fpm settings at the default, the memory usage increases more slowly but php-fpm becomes nonresponsive after 20-30 minutes and I can't connect to the Zabbix UI until I restart rh-php72-php-fpm.service. I found some suggestions to tweak php-fpm using the following:

pm = ondemand
pm.max_children = 100
pm.max_requests = 100

(default is dynamic PM, 50 max_children, and nothing specified for max_requests)

This stops the issue where php-fpm needs to be manually restarted, but however it makes MariaDB's memory usage skyrocket even faster. Right now, as a spectacularly ugly hack I've reverted php-fpm back to its default settings and set a cronjob to run systemctl restart rh-php72-php-fpm.service every 10 minutes (for what it's worth, I hate this hack and really wish I didn't have this line in my crontab). So far this has resulted in MariaDB's memory usage levelling off, though at a very high level: it's been sitting at about 73% memory usage all evening and all night, which isn't great but at least nothing is hitting OOM, the UI continues to be available, and I'm not being flooded with Zabbix sending me email alerts about it being low on memory like I was before. I'm tempted to give MariaDB a restart and see if it even climbs to this level at all now that the cronjob is in place (I think it was about 50-60% when I first set it up).

I would be very interested in hearing about your PHP-related workarounds, thank you.

Edit: running MariaDB 5.5.56, by the way

[tim.mooney]

I actually am starting to become increasingly convinced that php-fpm is the problem. I've found that if I leave the php-fpm settings at the default, the memory usage increases more slowly but php-fpm becomes nonresponsive after 20-30 minutes and I can't connect to the Zabbix UI until I restart rh-php72-php-fpm.service. I found some suggestions to tweak php-fpm using the following:

pm = ondemand
pm.max_children = 100
pm.max_requests = 100

Edit: running MariaDB 5.5.56, by the way

First, Zabbix 5.x requires newer MariaDB than that. Most of us that have either upgraded from 4.x or are planning for 5.x have first upgraded our MariaDB installs to some version of 10.x. That's extra complicated on RHEL 7 because of SCL and the default change in location for datadir, though you could work around that with config if needed. There are also a bunch of other potential issues you will run into because of changes in behavior. Read the known issues section of the upgrade notes and watch for the section about MariaDB 10.2, and search these forums for the zabbix issue number referenced there.

The pm.max_requests setting is indeed what we've used with another web product that has had memory issues with PHP FPM 7.2 on RHEL. I've suggested it in a couple other forum posts here, and it seems like it addressed the issue for others too. That setting alone is probably enough, as it forces periodic die-off of workers after they've served 100 connections, before their memory use can grow too large.

Note that under a correctly functioning front end, I really doubt that pm.max_children needs to be adjusted up; I think it could likely be adjusted down, to further reduce memory usage.