I'm running a Digital Ocean droplet with 32GB Memory which runs an online store that uses Magento as a CMS/ERP.
Lately I've noticed that the load average for the droplet is extremely high, sometimes the 1 minute spike goes up to 60, but even more scary is that the 15 minute average as been around 40.
Site traffic is around 150 users at any given time, which is pretty standard.
php-fpm processes seem to be the cause, and my guess is it's due to a module that's loaded in Magento where PHP pulls all the products for a category. Another reason I think this may be the cause is as seen in the image below, the processes are using high cpu but low memory, so maybe a DB query?
I checked the php-fpm status page and all the listings just specify the index.php because that's the entry point for the Magento app.
There is one sale category called "All Sale" which the e-commerce team has been pushing lately, and added about 400 products to it. Each of these products has configurable options for colours and sizes. Also, it displays every product in a mass listing, not in batches. I checked the site traffic and nearly half of it was in this category. My guess would be that when they added all these products to the category, and then marketed the sale, traffic increased, and the DB queries for all of these configurable sale items eats up the CPU, causing the entire web store to go slower.
We do have varnish caching and magento caching set-up, so once a new visitor has visited this category, it "appears" to go a lot faster.
I should also mention that our DB is hosted on a separate droplet from the web store, could this increase the load average as it has to access the DB remotely for each query?
Am I correct in assuming this is the cause in the high load average? If I am correct in assuming this is the cause, is there any way to increase performance for this type of situation?