All geek questions in one place

How to make Magento save money faster?

Everything below this line is deprecated. Magento is just slow, no less and no more.

Magento is extremely slow as stated in https://stackoverflow.com/questions/12580828/magento-saving-product-is-extremly-slow-but-profiler-shows-it-only-takes-1sec/12583078#12583078

After some battles due to the lack of root privileges on HostGator, I am ending the profiling Magento calls itself.

Here is one of these results:

Blue: timing 1982878436 Mage_Sales_Model_Mysql4_Quote begin <- this is logged when you enter the Mage_Sales_Model_Mysql4_Quote save method.

Blue: timing 1982878436 Mage_Sales_Model_Mysql4_Quote 46 <- and this is logged when exiting.

The number 1982878436 is a random number generated as a call identifier. And the number 46 is the time taken by the seconds.

 2012-09-26T06:36:16+00:00 DEBUG (7): Blue: timing 1982878436 Mage_Sales_Model_Mysql4_Quote begin 2012-09-26T06:36:18+00:00 DEBUG (7): Blue: timing 645597828 Mage_Log_Model_Mysql4_Visitor begin 2012-09-26T06:36:18+00:00 DEBUG (7): Blue: 645597828 Varien_Db_Adapter_Pdo_Mysql 2012-09-26T06:36:18+00:00 DEBUG (7): Blue: timing 645597828 Mage_Log_Model_Mysql4_Visitor 0 2012-09-26T06:36:18+00:00 DEBUG (7): Blue: timing 1712949075 Mage_Sales_Model_Mysql4_Quote begin 2012-09-26T06:36:24+00:00 DEBUG (7): Blue: timing 2103820838 Mage_Sales_Model_Mysql4_Quote begin 2012-09-26T06:36:56+00:00 DEBUG (7): Blue: timing 1999314779 Mage_Log_Model_Mysql4_Visitor begin 2012-09-26T06:36:56+00:00 DEBUG (7): Blue: 1999314779 Varien_Db_Adapter_Pdo_Mysql 2012-09-26T06:36:56+00:00 DEBUG (7): Blue: timing 1999314779 Mage_Log_Model_Mysql4_Visitor 0 2012-09-26T06:36:56+00:00 DEBUG (7): Blue: timing 504509596 Mage_Sales_Model_Mysql4_Quote begin 2012-09-26T06:36:56+00:00 DEBUG (7): Blue: timing 1887845167 Mage_Log_Model_Mysql4_Visitor begin 2012-09-26T06:36:56+00:00 DEBUG (7): Blue: timing 1887845167 Mage_Log_Model_Mysql4_Visitor 0 2012-09-26T06:37:02+00:00 DEBUG (7): Blue: timing 1887308594 Mage_GoogleOptimizer_Model_Mysql4_Code begin 2012-09-26T06:37:02+00:00 DEBUG (7): Blue: timing 1887308594 Mage_GoogleOptimizer_Model_Mysql4_Code 0 2012-09-26T06:37:02+00:00 DEBUG (7): Blue: 504509596 Varien_Db_Adapter_Pdo_Mysql 2012-09-26T06:37:02+00:00 DEBUG (7): Blue: timing 504509596 Mage_Sales_Model_Mysql4_Quote 6 2012-09-26T06:37:02+00:00 DEBUG (7): Blue: 1982878436 Varien_Db_Adapter_Pdo_Mysql 2012-09-26T06:37:02+00:00 DEBUG (7): Blue: timing 1982878436 Mage_Sales_Model_Mysql4_Quote 46 2012-09-26T06:37:02+00:00 DEBUG (7): Blue: 1712949075 Varien_Db_Adapter_Pdo_Mysql 2012-09-26T06:37:02+00:00 DEBUG (7): Blue: timing 1712949075 Mage_Sales_Model_Mysql4_Quote 44 2012-09-26T06:37:02+00:00 DEBUG (7): Blue: 2103820838 Varien_Db_Adapter_Pdo_Mysql 2012-09-26T06:37:02+00:00 DEBUG (7): Blue: timing 2103820838 Mage_Sales_Model_Mysql4_Quote 38

As we can see, 1982878436 , 1712949075 , 2103820838 is called in parallel, and it took several tens of seconds to complete each of them. I suspect there is a blockage problem between the three calls, making them wait for each other. Sometimes, when I save a product, Magento will even report that the action failed because MySQL is not working due to a deadlock.

Anyone have any ideas on this?

+4
source share
2 answers

As a result, all other requests are blocked. These problems have two major slowdowns.

One is in Mage / Sales / Model / Mysql4 / Quote.php, there are two subqueries. I don’t know how MySQL cache works and how to configure MySQL on HostGator, so I end up caching the query result:

 public function markQuotesRecollectOnCatalogRules() { /* $this->_getWriteAdapter()->query(" UPDATE {$this->getTable('sales/quote')} SET trigger_recollect = 1 WHERE entity_id IN ( SELECT DISTINCT quote_id FROM {$this->getTable('sales/quote_item')} WHERE product_id IN (SELECT DISTINCT product_id FROM {$this->getTable('catalogrule/rule_product_price')}) )" ); */ $products = $this->_getReadAdapter()->fetchCol("SELECT DISTINCT product_id FROM {$this->getTable('catalogrule/rule_product_price')}"); $ids = $this->_getReadAdapter()->fetchCol(" SELECT DISTINCT quote_id FROM {$this->getTable('sales/quote_item')} WHERE product_id IN (?)", implode(',', $products) ); if (count($ids) > 0) { $this->_getWriteAdapter()->query(" UPDATE {$this->getTable('sales/quote')} SET trigger_recollect = 1 WHERE entity_id IN (?)", implode(',', $ids) ); } }

and

 public function markQuotesRecollect($productIds) { /* $this->_getWriteAdapter()->query(" UPDATE `{$this->getTable('sales/quote')}` SET `trigger_recollect` = 1 WHERE `entity_id` IN ( SELECT DISTINCT `quote_id` FROM `{$this->getTable('sales/quote_item')}` WHERE `product_id` IN (?) )", $productIds ); */ $ids = $this->_getReadAdapter()->fetchCol(" SELECT DISTINCT quote_id FROM {$this->getTable('sales/quote_item')} WHERE product_id IN (?)", $productIds ); if (count($ids) > 0) { $this->_getWriteAdapter()->query(" UPDATE {$this->getTable('sales/quote')} SET trigger_recollect = 1 WHERE entity_id IN (?)", implode(',', $ids) ); } return $this; }

And there is Mage / CatalogRule / Model / Rule.php. Inside, this seems to be a well-known re-indexing issue.

 public function applyAllRulesToProduct($product) { $this->_getResource()->applyAllRulesForDateRange(NULL, NULL, $product); $this->_invalidateCache(); /* $indexProcess = Mage::getSingleton('index/indexer')->getProcessByCode('catalog_product_price'); if ($indexProcess) { $indexProcess->reindexAll(); } */ if ($product instanceof Mage_Catalog_Model_Product) { $id = $product->getId(); } else { $id = $product; } if ($id) { $indexer = Mage::getResourceSingleton('catalog/product_indexer_price'); if ($indexer) { $indexer->reindexProductIds(array($id)); } } }

I think that making the answer to global settings better. But I don’t have time for this, so I copied this solution.

+2
source

When executing inloing IN queries, MySQL always makes a leading external table.

This means that this request:

  UPDATE {$this->getTable('sales/quote')} SET trigger_recollect = 1 WHERE entity_id IN ( SELECT DISTINCT quote_id FROM {$this->getTable('sales/quote_item')} WHERE product_id IN (SELECT DISTINCT product_id FROM {$this->getTable('catalogrule/rule_product_price')})

you will have to scan each entry in sales/quote and check it for sales/quote_item , which, in turn, will check each matching record for catalogrule/rule_product_price .

If there are significantly more entries in sales/quote than returning to a subquery, this will be slow.

You can rewrite it as a connection:

 UPDATE {$this->getTable('catalogrule/rule_product_price')} crpp JOIN {$this->getTable('sales/quote_item')} sqi ON sqi.product_id = crpp.product_id JOIN {$this->getTable('sales/quote')} sq ON sq.entity_id = sqi.quote_id SET sq.trigger_recollect = 1

Thus, the optimizer can choose which table to make leading (when each join field is indexed, it should be the smallest table).

+4
source

Source: https://habr.com/ru/post/1436298/

More articles:


All Articles