An issue you may encounter on larger sites is massive DB load spikes when there is a high order volume and large number of customers stored in the DB. The problem stems from the Enterprise Edition’s Customer Segments module and the way it checks to add a new customer into a segment.
The main culprit is the Enterprise_CustomerSegment_Model_Segment_Condition_Combine_Root class, specifically the _prepareConditionsSql method. This is what generates the SQL that will be run to verify if a customer should be added to this segment when they place an order or sign up. It generates a query like:
SELECT 1 FROM `customer_entity` AS `root` WHERE
((
IF((
SELECT 1 FROM `sales_flat_order_item` AS `item`
INNER JOIN `sales_flat_order` AS `sales_order` ON item.order_id = sales_order.entity_id
INNER JOIN `core_store` AS `store` ON sales_order.store_id=store.store_id
WHERE
(sales_order.customer_id = '11111111')
AND (store.website_id='1')
AND (EXISTS (SELECT `main`.`entity_id` FROM `catalog_product_entity` AS `main` WHERE (main.sku = 'ITEM1')
AND (main.entity_id = item.product_id))) LIMIT 1
), 1, 0) = 1
))
which has a major issue, the main outer query has no limit! Even worse, it has no where condition to limit the number of rows being returned so what you get is a HUGE amount of rows containing “1” whenever the inner customer+item match is true. When you have 1 million or more customers, this can lead to the DB creating temporary tables on disk since it no longer fits in the maximum memory temp table size which leads to load spikes if you are getting any sizable amount of orders being placed.
The fix here is pretty simple, the rest of the code only ever grabs the first row to see if the customer matched the conditions so we just need to limit it to 1 row. This will allow the DB to keep the temporary table in memory and reduces a 3s+ query (have seen it go to 2 minutes and higher during high order volume) into a .01s query.
The first step is to extend this class and add in our limit to the select query. You need to make a new module to extend it by adding the following config.xml
<?xml version="1.0"?> <config> <global> <models> <enterprise_customersegment> <rewrite> <segment_condition_combine_root>MyCompany_MyModule_Model_Segment_Condition_Combine_Root</segment_condition_combine_root> </rewrite> </enterprise_customersegment> </models> </global> </config>
and then create the class we are extending it with:
<?php
class MyCompany_MyModule_Model_CustomerSegment_Condition_Combine_Root extends Enterprise_CustomerSegment_Model_Segment_Condition_Combine_Root {
protected function _prepareConditionsSql($customer, $website)
{
$select = parent::_prepareConditionsSql($customer, $website);
if ($customer) {
$select->limit(1);
}
return $select;
}
}
You will notice we only apply the limit when $customer is set. The other side of that conditional is actually returning the entity_id of the customer so we don’t want to limit that, it is used to refresh the entire segment. We only care when it is checking a single customer since it either returns 1 or nothing from the query.
After you apply this change, you will also need to go into each segment and re-save the segment. The actual SQL is stored in the DB as part of the segment row so re-saving will update that row with the new limit. If you are familiar with SQL then you can also craft a query to update the enterprise_customersegment_segment.condition_sql values to append “LIMIT 1” to all of them.
After that, you should not get any DB spikes due to using customer segments in Magento EE with a high volume site.
FYI Magento have a patch for exactly this fix, download it from their portal, it is called: PATCH_SUPEE-4926_EE_1.13.1.0_v1.sh
Is this something you have to request from the Magento support team before it will appear? Where are you seeing it in the portal area? I scanned all the patch sections in the downloads area but didn’t see anything related to customer segments or having the SUPEE-4926 file name, if I had seen it before then I could have saved a lot of time debugging and fixing it myself.