10

I have the following situation:

About 5 times a week (not related to any specific situation like cache clear, traffic spike) some queries are stuck on sending data (show processlist):

>     SELECT `main_table`.`entity_id`, `main_table`.`level`, `main_table`.`path`, `main_table`.`position`,
> `main_table`.`is_active`, `main_table`.`is_anchor`,
> `main_table`.`name`, `url_rewrite`.`request_path` FROM
> `catalog_category_flat_store_30` AS `main_table`
>      LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND
> url_rewrite.is_system=1 AND url_rewrite.product_id IS NULL AND
> url_rewrite.store_id='30' AND url_rewrite.id_path LIKE 'category/%'
> WHERE (path LIKE '1/2/%') AND (main_table.store_id = '30') AND
> (is_active = '1') AND (include_in_menu = '1') ORDER BY name ASC

second one:

> SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`,
> `main_table`.`is_active`, `main_table`.`is_anchor`,
> `main_table`.`manually`, `url_rewrite`.`request_path` FROM
> `catalog_category_flat_store_10` AS `main_table`  LEFT JOIN
> `core_url_rewrite` AS `url_rewrite` ON
> url_rewrite.category_id=main_table.entity_id AND
> url_rewrite.is_system=1 AND url_rewrite.product_id IS NULL AND
> url_rewrite.store_id='10' AND url_rewrite.id_path LIKE 'category/%'
> WHERE (main_table.is_active = '1') AND (main_table.include_in_menu =
> '1') AND (main_table.path like '1/2/1528/1569/%') AND (`level` <= 4)
> ORDER BY `main_table`.`position` ASC

These queries are related to generating the navigation menu. They run without any issues and very fast all the time.

Few times a month some other queries get stuck on seding data or waiting for table lock:

INSERT INTO `catalogsearch_result` SELECT 316598 AS `query_id`, `s`.`product_id`, MATCH (s.data_index) AGAINST ('STRING HERE' IN BOOLEAN MODE) AS `relevance` FROM `catalogsearch_fulltext` AS `s`
INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 38) AND (MATCH (s.data_index) AGAINST ('STRING HERE' IN BOOLEAN MODE)) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`)

(search related)

Additional info:

  • core_url_rewrite - 3M records (30 websites, 100k products)
  • catalog_category_flat_store_* - 2000 records (use flat categories is enabled)

This is running on a setup using vmware on some huge hardware (mysql master has 8 cores allocated and 64Gb of RAM, SSD disks on a SAN storage), mysql was optimized and is monitored continuously. There were some issues in the past related to I/O (some isssue with the link between the servers and the SAN storage).

We couldn't pinpoint the issue because running on bare metal (no virtualization, same config) this doesn't happen ever, in high stress conditions (running siege + load testing scenarios, no cache).

Anyone else having similar issues?

UPDATE:

reindexAll search was moved to a temporary table (so it doesn't lock the main table used by the production, then renames the tmp table). So, the reindex process doesn't interfere with visitors searching the website. https://github.com/magendooro/magento-fulltext-reindex kudos to carco

  • Are you sure, they ran fast? The alternative may be the navigation menu is cached. Afaik is the usage of an index not easy because, there is no index over category_ud, is_system and path. And path is a a LIKE, so MySQL has here a real problem afaik. I'm no db export btw ;-) Just 2cents – Fabian Blechschmidt Feb 13 '13 at 13:37
  • 1
    that select runs in under 1s. the queries keep piling up when the first one stays in sending data... – FlorinelChis Feb 13 '13 at 14:53
  • 1
    FWIW I have seen the same issue. – philwinkle Apr 8 '13 at 18:02
  • @philwinkle how is your search setup? fulltext? – FlorinelChis Apr 8 '13 at 20:54
  • 1
    github.com/magendooro/magento-fulltext-reindex this helped us and decided to publish the source code. – FlorinelChis Oct 8 '13 at 17:24
4

It looks like a core bug/regression we saw in 1.7 where the block and collection cache weren't working effectively for the navigation menu (catalog/navigation/top.phtml).

You can test by removing it, or just temporarily capture the output into a file with an ob_start and serve it from a static file/memcache.

Also, the hardware you are using doesn't sound huge and looks under specified for the size of the store you have. There is probably an I/O bottleneck there too - SAN storage + congested network = poor performance.

--

As a crude solution, you can adjust the block class for the navigation (dump get_class($this)) in top.phtml to identify it.

This will allow site-wide caching, without the category level caching that the new version invoked. Its also worth removing the is_active class from the tree renderer if you do this to avoid random menu items appearing selected (and implement a JS alternative instead).

public function getCacheTags()
{
  return parent::getCacheTags();
}
public function getCacheLifetime()
{
  return null;
}
public function getCacheKey()
{
  return parent::getCacheKey();
}
public function getCacheKeyInfo()
{
  $shortCacheId = array(
    'CATALOG_NAVIGATION',
    Mage::app()->getStore()->getId(),
    Mage::getDesign()->getPackageName(),
    Mage::getDesign()->getTheme('template'),
    Mage::getSingleton('customer/session')->getCustomerGroupId(),
    'template' => $this->getTemplate(),
    'name' => $this->getNameInLayout(),
  );
  $cacheId = $shortCacheId;
  $shortCacheId = array_values($shortCacheId);
  $shortCacheId = implode('|', $shortCacheId);
  $shortCacheId = md5($shortCacheId);
  $cacheId['short_cache_id'] = $shortCacheId;
  return $cacheId;
}
  • we previously allocated 32 cores and 92Gb of ram (and changed the mysql config accordingly, same result) - the server has 64 cores and 184 gb of ram (that's why I was saying it's huge)... sorry, I didn't mentioned it's Magento Enterprise 1.12. we monitored the network traffic didn't see anything pointing to a bottleneck (we had an issue before, the fibre connector wasn't working properly, it was replaced). – FlorinelChis Feb 14 '13 at 10:10
  • Enterprise 1.12 is CE 1.7 - they are the same code base. So the bug will affect them both. Try what I said (hard-code the top nav and disable categories on the layered nav) and you can confirm. More hardware isn't going to make a difference if the software isn't properly set up to use it. – Ben Lessani - Sonassi Feb 14 '13 at 14:51
  • I'm going to edit my answer and add some hacky code for you to use to confirm – Ben Lessani - Sonassi Feb 14 '13 at 14:58
  • It's a good place to start, I'll put something in place and see if it still crashes within 1 week :) – FlorinelChis Feb 14 '13 at 16:49
3

Replace function at

app/code/core/Mage/Catalog/Helper/Category/Url/Rewrite.php:

/**
* Join url rewrite to select
*
* @param Varien_Db_Select $select
* @param int $storeId
* @return Mage_Catalog_Helper_Category_Url_Rewrite
*/
public function joinTableToSelect(Varien_Db_Select $select, $storeId)
{
$select->joinLeft(
array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')),
'url_rewrite.category_id=main_table.entity_id'
);
$select->where('url_rewrite.is_system = ?', '1');
$select->where($this->_connection->quoteInto('url_rewrite.store_id = ?', (int)$storeId));
$select->where($this->_connection->prepareSqlCondition('url_rewrite.id_path', array('like' => 'category/%')));
$select->where('request_path = url_rewrite.request_path');

return $this;
}
2

In our case it came down to this slow query:

SELECT `main_table`.`entity_id`
      , `url_rewrite`.`request_path`
FROM `catalog_product_entity` AS `main_table` 
INNER JOIN `catalog_product_website` AS `w`
   ON main_table.entity_id = w.product_id 
LEFT JOIN `core_url_rewrite` AS `url_rewrite`
   ON url_rewrite.product_id = main_table.entity_id
   AND url_rewrite.is_system = 1
   AND url_rewrite.category_id IS NULL
   AND url_rewrite.store_id = 1
   AND url_rewrite.id_path LIKE 'product/%'
WHERE (w.website_id='1')

from app/code/core/Mage/Sitemap/Model/Resource/Catalog/Product.php.

It hangs because of category_id IS NULL statement. MySQL for some reason didn't utilize an index.

Removing category_id IS NULL and setting id_path REGEXP '^product/[0-9]+$' fixed the problem.

Copy app/code/core/Mage/Catalog/Helper/Product/Url/Rewrite.php to app/code/local/Mage/Catalog/Helper/Product/Url/Rewrite.php and add this function:

public function joinTableToSelectPatch(Varien_Db_Select $select, $storeId)
{ 
$select->joinLeft(
    array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')),
    'url_rewrite.product_id = main_table.entity_id AND url_rewrite.is_system = 1 AND ' .
        $this->_connection->quoteInto('url_rewrite.store_id = ? AND ',
            (int)$storeId) .
        $this->_connection->prepareSqlCondition('url_rewrite.id_path', array('regexp' => '^product/[0-9]+$')),
    array('request_path' => 'url_rewrite.request_path'));
return $this;
}

Then copy app/code/core/Mage/Sitemap/Model/Resource/Catalog/Product.php to app/code/local/Mage/Sitemap/Model/Resource/Catalog/Product.php and change line 72 to:

$urlRewrite->joinTableToSelectPatch($this->_select, $storeId);

Originally taken from https://www.goivvy.com/blog/solved-magento-stuck-generating-google-sitemap-large-website

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.