Facebook
Twitterthinkjarvis
Basic Pleskian
Ok Any ideas how I can investigate further?
Am I looking for a bad PHP process on the website or am I looking for a PHP setting to change the behaviour?
-
Plesk Uservoice will be deprecated by October. Moving forward, all product feature requests and improvement suggestions will be managed through our new platform Plesk Productboard.
To continue sharing your ideas and feedback, please visit features.plesk.com
Question Mysql memory usage and memory used by system reach 90% of available ram and then crash
- Thread starter thinkjarvis
- Start date
Am I looking for a bad PHP process on the website or am I looking for a PHP setting to change the behaviour?
On the other thread I already suggested to look at Apache's RAM usage and to limit it so that it can never exceed a certain value. If Apache is consuming a lot of RAM, it either has spawned too many children or PHP processes executed by it occupy the RAM. So first check Apache, then go after PHP processes. I bet there are either tons of them occupying RAM or some runaway processes in infinite loops that keep requesting more and more.
Here's another magic command line that will let you monitor the most important aspects "live":
(Stop with Ctrl+C)
Also simply counting processes might reveal more, or just looking at PHP RAM usage (all through the # ps directive on the command line).
Here's another magic command line that will let you monitor the most important aspects "live":
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` watch "uptime && echo "\ " && ps aux | sort -nrk 3,3 | head -n 20 && echo "\ " && mysqladmin proc status -u admin"(Stop with Ctrl+C)
Also simply counting processes might reveal more, or just looking at PHP RAM usage (all through the # ps directive on the command line).
thinkjarvis
Basic Pleskian
I can see a few processes with the same PID hanging around but I have no idea how to identify what is actually continuing to run.
The number of users is changing from approx 12 to 21 but has fallen to between 12 and 15 while I have been watching approx..
And threads reasonably stable from 2-10 depending on activity.
Approx 16 our of the 20 displayed records are phpfpm from the one website. Which is right given how much traffic they get.
I cannot see anything particularly unusual looking. The only queries I can really see are action scheduler, and shop filters and then the shop search and filter functions pinging some slightly longer queries.
Is there anything I can send on here that will help you see what I am seeing.
thinkjarvis
Basic Pleskian
Following on from this the PID number does appear to be cycling. So the queries are completing sensibly.
Looking at the physical and virtual memory use - There is quite high memory use per PHPFPM activity so if we can bring this down then it would improve the situation.
Looking at the physical and virtual memory use - There is quite high memory use per PHPFPM activity so if we can bring this down then it would improve the situation.
JeffreyReed
New Pleskian
I'm currently having a similar issue where cached memory spikes around midnight, but everything else stays stable. I'm not exactly sure how much it would’ve built up since I caught it mid-build and stopped it, but no matter what I do, I can’t seem to trigger it again. I’ve tried everything, I’m honestly at a loss for words and out of forum posts to dig through. I’ve even turned off NGINX caching on almost all my sites, but it’s still caching to RAM.
That spike looks like the start of the Plesk Daily Maintenance script:
Linux always tries to cache as much as possible to RAM, that's the way it is designed:
Linux always tries to cache as much as possible to RAM, that's the way it is designed:
thinkjarvis
Basic Pleskian
This may be true - But the server has 128gb of ram the daily script shouldnt have done that.
Does this coincide with a backup via the backup manager?
We have found that the backup manager when backing up databases consumes mysql/mariadb memory and doesnt release it. This can cause problems where the DB memory takes up too much space relative to php-fpm memory. The two then fight eachother which can cause the server to kill mysql/mariadb.
Solution: How did we fix our issues?
We took a multi-pronged approach. The goal being to reduce memory consumption
- We swapped to TCMALLOC for the db cache management which reduced usage significantly
- Reduced the INNODB buffer to a level more relevant to the max table sizes on the biggest website on the server.
- Manually optimised the database on the biggest site - Essentially we halved the size of the database due to redundant records in the clients wordpress installation.
- WP_Options had become a bloated mess over time. We also stripped and deleted other unused tables.
- This process was a double edged sword - Reducing the size of the DB, also reduced the time php-fpm requests take to complete - Bringing down the overally memory usage. By shrinking the Database - We also reduced the size of the open table cache and generla db caches managed by plesk.
- Changed the way http requests work on the site - Enabled script merging for some parts of the site which reduces http requests and also has a knock on effect on php-fpm
- Added REDIS to all sites on the server
- Enabled enhanced DOS in Imunify 360
- Enabled rate limiting in WordFence locally on all sites with a blocking policy
- We also use a DB optimisation stack which we purchased for about £5k on studio license - This reduces the time db and php-fpm actions take.
The result was a drop in mysql memory usage in a 24 hour period of 16gb of ram.
The nightly backup spikes for us have become smaller because the DB is half the size it was. Therefore less alocated ram to backup the DBs.
I hope this helps.
HOWEVER a spike like that could have been a random bot attack. Or an inefficient script being hit by too many real visitors at once.
JeffreyReed
New Pleskian
We are currently using the Google Drive Backup extension on Plesk, which may be causing this.
I went through each site manually and turned off serving static assets via NGINX, which I didn't see a spike last night, but waiting again for tonight since I re-enabled that settings for a bunch of sites. Testing a couple of theories, but either way, the memory is technically still free when I do run: free -h
I thank you all for all this advice!!
thinkjarvis
Basic Pleskian
Here is the process I would take in your current situation. I dont think you have found the cause yet.
If you need to take a backup of your server incase you have a problem with linux/plesk at software level.
Log in via ssh or use the SSH Terminal extension:
Understanding the issue:
You also need to look at the services tab in the Monitoring tool to see which component is using the memory. Looking at the global usage wont tell you the answer to this.
For the examples in my post - The mysql and mariadb memory was spiking - Everything else is stable. You wont get this from the ram usage chart alone!
This can also be seen in the process list from the left hand menu in plesk - Cross reference to help understand the issue
You can do all of this without running top command and other command line functions. That would be the next step if you find the items below dont solve the problem.
Troubleshooting:
You can then be sure if it is or isnt the backup causing the issue
You can also see exactly which part of your system is using virtual ram.
My money is on mysql/mariadb as this problem looks very simillar to what we have seen on our server.
If this is the case you can always run:
sudo systemctl restart mariadb
sudo systemctl restart mysqld
sudo systemctl restart mysql
OR
sudo service mariadb restart
sudo service mysqld restart
sudo service mysql restart
If you need to take a backup of your server incase you have a problem with linux/plesk at software level.
Log in via ssh or use the SSH Terminal extension:
- Sudo apt update
- Sudo apt upgrade
- This will ensure you are on the latest Kernel and versions of everything
- Check the extensions store for updates to used extensions
- Reboot your server to clear any stuck processes.
Understanding the issue:
You also need to look at the services tab in the Monitoring tool to see which component is using the memory. Looking at the global usage wont tell you the answer to this.
For the examples in my post - The mysql and mariadb memory was spiking - Everything else is stable. You wont get this from the ram usage chart alone!
This can also be seen in the process list from the left hand menu in plesk - Cross reference to help understand the issue
You can do all of this without running top command and other command line functions. That would be the next step if you find the items below dont solve the problem.
Troubleshooting:
- Can you try turning off your backup for one night and see if this prevents the spike? (Dont even run it locally just turn off the backup)
- Turning off Nginx wont solve this problem - Turn this back on as you can set a memory usage limit in the settings per domain for NGINX caching.
- After you have turned off the backup and turned NGINX back on - Wait 24 hours and see what the memory spikes look like
- As above you need to check the services tab in monitoring so you can see which service is actually eating your ram
- You also need to check the Process list to see which process is eating your ram
You can then be sure if it is or isnt the backup causing the issue
You can also see exactly which part of your system is using virtual ram.
My money is on mysql/mariadb as this problem looks very simillar to what we have seen on our server.
If this is the case you can always run:
sudo systemctl restart mariadb
sudo systemctl restart mysqld
sudo systemctl restart mysql
OR
sudo service mariadb restart
sudo service mysqld restart
sudo service mysql restart
