As said, this join can be very bad on large order item collections, cause it can't reference the catalog_category_entity table. Same goes for quote_item to CCE. So we have a join in the middle that can't use an indexed approach to it's outer parts. In worst case, MySQL tries to sort it over and over to get optimal alignment of CCE.
I'd to it like this:
- Join the quotes with quote_item and put the is_active on the join condition and keep the GROUP BY.
- Fetch all quote_item.product_id into an array
- Fetch price info using IN($quote_item_product_ids)
- Fetch times sold till now using order_items.product_id IN($quote_item_product_ids)
And/or create an index table linking sold quantities to product entities. A foreign key there can work, cause in the index table we can make the item_id columns nullable, what can't be done with a primary key.
The only way I am able to solve it is by rebooting the entire server
Fire your hosting provider, immediately. The only time a server should be powered off is to physically upgrade it, or install a new kernel - never to solve a problem. If they can't provide a solution to such a simple issue, they aren't a company worth using.
Your issue is fairly clear, you are lacking instrumentation.
You should be graphing every single application and event on your server in initimate detail, so that you can correlate the sequence of events leading up to a slow query incident to identify a cause.
If everything performs normally, then suddenly slows down, I'd be inclined to review,
- Varnish hit rate - does it plummet at the time of the slowdown?
- Inbound traffic rate - do you see an increase in traffic, do you see an increase from a single IP?
- Cache store utilisation - has someone flushed a cache, or have several entries expired simultaneously?
- Hardware activity - what else is happening on the server at the time, is I/O high, SYS CPU etc. Is it cloud/VPS - if so, what is the hypervisor doing?
There could be dozens of possible causes, all of which should take seconds to identify and fix, but without instrumentation - you can't simply guess your way through a fix. You'll just cause further problems blindly making adjustments.
Best Answer
You are going in the wrong direction completely. If your site is slow, don't "fix" it with a cache, fix it by fixing it.
Redis is a cache store, it won't make a site any faster, it will just improve performance of content fetched from the cache. The process of fetching from the cache isn't slow to begin with, file based caching is fast enough to not be a bottleneck, Redis won't change that (NB. Don't use file based caching, it is broken, Redis is a solution to fix another issue with cache swelling, so keep it - but it won't help your speed).
Why? Its a great utility (along with mysqltuner), but it isn't a miracle worker. The best it can do is analyse data based on what it has seen - not make an accurate or intelligent human decision as to what the bottleneck might be. Following scripts blindly like this, without understanding the consequences of what you are changing will almost certainly lead to problems and by no means give you the performance boost you seek. It could very possibly reduce performance, not improve it.
We've been over cache != speed - but what were you expecting to happen after clearing the cache? It would obviously be slower as a result of an empty cache.
Lock files are entirely unrelated to speed, they are empty files, created during a reindex to prevent it running twice (simultaneously) over itself. FYI. Not once in 6 years have I removed a lock file in any situation.
Deleting sessions. Well, worst case, it actually makes the site faster, and then you are faced with the dilema of a fast store that never has sessions (and thus no sales) - or a slow site that people can buy from. As a diagnostic step, its going to give no tangible/reliable results. Don't delete sessions in any circumstance, ever.
Housekeeping won't improve performance, especially if the site has no traffic/logs to clear down. Its certainly good practice, but isn't your issue.
Fundamentally, the issue comes down to two things.
If your hosting is improperly configured (which from the sounds of things, it is, given the DIY tuning of MySQL), it will dictate the baseline for performance. Your store can only ever be as fast as the infrastructure underneath it. No amount of application level tuning will fix that. Easiest way to tell is to install a demo store with sample data - and see what the performance is like. If that's slow, there's your issue.
The second issue is less clear, but not impossible to fix. Its a case of profiling your code to find the bottleneck and fix it. Start by restoring the default theme, then disabling modules one by one until speed is normal again. That's a fairly high level approach, but it will certainly get you going.