CODEWITHSUNDEEP
mysqlsqlperformancemagento
BlueWanderer
BlueWanderer

Reputation: 2691

How to make Magento saving product faster?

Everything below this line is out of date. Magento is just slow, nothing less and nothing more.

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

After some struggling due to lack of root privilege on HostGator, I end up profiling Magento calls myself.

Here's one of those results:

Blue: timing 1982878436 Mage_Sales_Model_Mysql4_Quote begin <- this is logged when entering Mage_Sales_Model_Mysql4_Quote's 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 the id of the call. And number 46 is the time taken in 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 are called in parallel, and each took several tens of seconds to finish. I suspect that there is some lock issue among the three calls make them waiting for each other. Sometimes when I'm saving a product, Magento will even report action being failed because MySQL is failed due to deadlock.

Anyone has any ideas about this?

Upvotes: 4

Views: 2468

Answers (2)

Quassnoi
Quassnoi

Reputation: 425411

When running queries inloving IN, MySQL always makes the outermost table leading.

This means that this 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')})

will have to scan each record in sales/quote and check it against sales/quote_item which in its turn will have to check each matched record against catalogrule/rule_product_price.

If there are significantly more records in sales/quote than the subquery returns, this will be slow.

You may want to rewrite it as a join:

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

This way, optimizer may choose which table to make the leading (when every join field is indexed, it should be the smallest table).

Upvotes: 4

BlueWanderer
BlueWanderer

Reputation: 2691

It ends up just other queries blocked these queries. There two major slow down in this issues.

One is in Mage/Sales/Model/Mysql4/Quote.php, there are two nested queries. I know neither how MySQL's cache works nor how to configure MySQL on HostGator, so I end up caching the query result myself:

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 the Mage/CatalogRule/Model/Rule.php. Inside, it seems to be a well known reindexing problem.

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 making it response to the global setting should be a better way. But I've got no time for it, so I copied this solution.

Upvotes: 2

Related Questions