Slow running Magento queries killing site

[RossCC]

Hi Magento peeple

We run a Magento 1.9 store on a top-of the line dedicated server (8 core, 64GB RAM, SSD disks etc...) however the site is still painfully slow to load.  I think we have identified it may be down to Magento (or extensions) running some stupidly unoptimised queries.

Thus far, we have NGINX and Varnish in place, with Cloudflare CDN too.  We also have Memcache in place.

The site has several extensions installed, and has 40,000 products and around 10 - 30 concurrent visitors.

Typical page load is around 5 seconds, this has improved from 15 seconds after we deployed all the above improvements.  Also we had 20 + different product warehouses, reducing this down to 2 has helped.

As it stands, the server is showing MYSQLD averaging around 200% CPU time.  We have deployed a new MySQL configuration to suit the server, however we are seeing lots of slow queries from Magento, for example this one takes around 5 seconds, and seems to load 12 products into view:

SELECT DISTINCT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `cat_index`.`position` AS `cat_index_position`, `e`.`name`, `e`.`description`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`small_image`, `e`.`thumbnail`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`url_key`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`tax_class_id`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`body_protector_size`, `e`.`body_protector_size_value`, `e`.`body_protector_back_length`, `e`.`body_protector_back_length_value`, `e`.`volume_weight`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `brand_products`.`position` FROM `mage_catalog_product_flat_1` AS `e`
 INNER JOIN `mage_catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '7'
 INNER JOIN `mage_catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `mage_catalog_product_index_eav` AS `manufacturer_idx` ON `manufacturer_idx`.entity_id = e.entity_id AND `manufacturer_idx`.attribute_id = '81' AND `manufacturer_idx`.store_id = 1 AND `manufacturer_idx`.value IN (157)
 INNER JOIN `mage_catalog_product_index_eav` AS `colour_for_filter_idx` ON `colour_for_filter_idx`.entity_id = e.entity_id AND `colour_for_filter_idx`.attribute_id = '386' AND `colour_for_filter_idx`.store_id = 1 AND `colour_for_filter_idx`.value IN (4850,4854,4847,4859,4848)
 LEFT JOIN `mage_brand_products` AS `brand_products` ON e.entity_id = brand_products.product_id WHERE (e.entity_id IN(X)) ORDER BY `brand_products`.`position` ASC, `cat_index_position` ASC, `cat_index`.`position` ASC LIMIT 12

Note in the above, I have replace WHERE e.entity_id in X, as X is actually a comma-separated list of over 2400 integers.  This forum wont let me post that many characters!

Analysing the above shows it is using tmp file, (even though MySQL is configured to avoid this).  I presume this is because the query is a SELECT DISTINCT with ORDER BY, which according to a Google search, will always be cached.

The MySQL tmp location is in memory, so it's as fast as it can be.

So my question - why the flip in Magento using a WHERE IN clause, which has over 2400 parameters in it?

Can anyone identify what might be causing this stupid query?  Variations of this query seems to be running constantly, and I assume this is what is tying up MySQL and slowing the site.

[JLHC]

Yes, it does seem like the queries may be slowing down your page load. 

Besides tackling this head on (which may require code changes or removing some extensions), have you considered deploying a Full Page Cache to cache the frontend of your Magento store? This should reduce the number of queries from the frontend and improve the loading speed to your visitors. 

[RossCC]

Thanks James - that's an option, but we always seem to be papering over cracks with Magento!

I would ideally want to sort this at source, as it's an underlying issue which is killing sales on our site.

[JLHC]

One way to see if any extension is causing the problem is to manually disable them one by one while testing out the queries. 

To disable any extension in Magento 1.x:-

1. Head over to the app/etc/modules directory.

2. Edit the .xml file for the extension. 

3. Change the following:-

<active>true</active>

to:-

<active>false</active>

That should disable the extension from running.

Do make sure not to do this for core extensions (those that starts with "Mage_") though as otherwise it may break your Magento installation. 

[RossCC]

Thanks a useful tip to know - thanks James, will give this a run...

[Emipro Tech]

May be, you have very well predicted this: "Magento (or extensions) running some stupidly unoptimised queries."

If you have opted for a sound hosting services and Cache solution, it must speed up the site as expected.

But, if you detect any third-party extension generating unreliable heavy-loaded queries (by deactivating the 3rd party modules as suggested by a gentleman) , do ask your programmers to cut the problem from its root.

<< Snipped >> 

[StevenAntonucci]

Reminds me of the story of the guy walking under a lam post, searching for something.  A cop walks up and asks him what he was doing, so he says "Looking for my wallet".  Cop searches for a bit, but finds nothing so he says "Are you sure you lost it here?"  Guy responds "Oh, no... I lost it over by my car, but the light is better here!"

Whenever I see "performance issues", I want to begin at the beginning.  First, how long does it take for a single user to load?  If a single user is slow, I know I have code to look into.  Bad code manifests itself immediately.

If I clear this hurdle, I run 2 users.  The second user should have no impact on performance IF I have no resource locking issues.  
Next, I run a handful (5-10) if I expect the system to scale.  I'm always watching things like CPU and memory usage, making sure that they grow the way I expect them to grow.

I'll continue to add users until system response time doubles.  If it is slowly creeping up, I know that it is consumption based.  If the response time suddenly jumps up, it usually means that you've hit some limit. As an example, it may be connections, bandwidth, or CPU.  A healthy system produces the same response time as a single user.

After working through this process FOR EACH INDIVIDUAL PROCESS (like browsing catalogs, check-out, etc), I will restart the performance process with the same methodology, but now co-mingling processes.  Does adding shopping cart items step on the performance of browsing?  Does check-out get impacted by too many browsers?  And so on...

Here is the key:  Every time you make a change...you start over from the beginning.  Assume nothing.  Modified your code?  Start from square one.  Upgraded to PHP 7?  Square one.  M1 to M2?  Square one...

Far to many people start with trying to start at the end, and as a result, it takes them far too long to find problems, and often, they either break something that was working because of a wrong diagnosis or they throw hardware at it.