Question Memory usage after backup

[thinkjarvis]

Unfortunately this leads me nicely back in the same circle again
We have not been able to identify anything unusual about the clients website setup that would cause high usage of phpfpm that could be classed as excessive.

Mysql continues to clime continually throughout the day.
The phpfpm is stable and consistent - It is the mysql memory and mariadb memory that is filling uncontrollably.

The only correlation is that process manager is reporting 100% ram usage at the moments leading to mariadb being killed - mariadb is occupying approx 90% of it and system the 0ther 10% inclusive of cache values.

It shouldnt kill mariadb - mariadb shouldnt be occupying the entire ram?

 

[Bitpalast]

The journal file looks like this:
...
Aug 21 09:44:41 servername.ipaddress.plesk.page systemd[1]: mariadb.service: Consumed 10h 5min 17.784s CPU time, 20.0G memory peak, 7.4G memory swap peak.
...

20 GB + 7.4 GB = 27.4 GB.
Your total RAM: 40 GB

The RAM manager does not kill MariaDB, because MariaDB occupies all RAM. It kills MariaDB as one out of several measures, because the total RAM usage exceeds a limit. MariaDB is not your problem.

But I've written it now several times, pointing out that neither the monitoring charts, nor the journalctl log shows that MariaDB is a problem. Instead, the monitoring shows that Apache and PHP are causing the problem. So in some ways, yes, you're running in circles. To me the situation is clear, but there is nothing I can do here if you believe the problem lies in MariaDB.

 

[thinkjarvis]

Thanks Bitpalast I appreciate your input on this.
I can see you have repeatedly stated you think it is PHP. Im repeatedly saying I dont think it is PHP because if this were the case why would running a backup cause an increase in memory consulption and not release it? Doesnt sound like that could be PHP related?

Even if a query is intensive when it finishes it shouldnt cause the memory to stay allocated?

Im still investigating this at the moment.

 

[thinkjarvis]

@Bitpalast

I've just tagged you in another post I raised about this issue as another user replied.

Here is what we have done so far to mitigate the problems:

Temp solution - Working very well
We have set up a cron job to restart mariaDB at 3am every day. This flushes the db caches so we have a clean slate every 24hours.

Mitigation solutions
A deep dive into the website and database has revealed several issues. We have:

1. Reduced the size of the database from 11GB to 5.75GB
2. Ran Optimise Tables on all tables
3. Re-wrote a couple of intensive queries
4. Disabled some background logging processes to reduce php processes
5. Enabled more page caching features to reduce php and db requests
6. Added more bot mitigation rules - including blocking google and bing from query strings using mod_security
7. Added REDIS to all other sites on the server
8. The result is a drop of approx 3-12GB depending on where you measure it from over a 24 hour period
9. We will attempt to disable the mariadb restart soon and see if things overload again or stabalise.

I am exploring a few options for perminent solutiuons:
Changing to Jemalloc or tcmalloc - Currently trialing this on 2 of our servers

https://www.managedserver.eu/Improve-mysql-and-mariadb-performance-with-memory-allocators-like-jemalloc-and-tcmalloc/

Changed the mysqli setting for persistent connections to OFF. Increased the number of pm.max_requests to 100 from 50 - To see if it is the number of demanding processes spawning causing the problem (Lower number means more respawns).

What I am struggling with right now is that the memory allocation happens during a backup but when the backup is finished it doesnt flush that memory allocation so after a period of a few days the mysql memory consumption consumes the server.