MySQL nadal się zawiesza (zapytania utknęły przy wysyłaniu danych)

Mam następującą sytuację:

Około 5 razy w tygodniu (niezwiązane z żadną konkretną sytuacją, taką jak czyszczenie pamięci podręcznej, wzrost natężenia ruchu) niektóre zapytania blokują się podczas wysyłania danych ( 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

Drugi:

> 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

Te zapytania są związane z generowaniem menu nawigacyjnego. Działają bezproblemowo i bardzo szybko przez cały czas.

Kilka razy w miesiącu niektóre inne zapytania blokują się podczas wprowadzania danych lub oczekiwania na blokadę tabeli:

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`)

(związane z wyszukiwaniem)

Dodatkowe informacje:

• core_url_rewrite - rekordy 3M (30 stron internetowych, 100k produktów)
• catalog_category_flat_store_ * - 2000 rekordów (użycie kategorii płaskich jest włączone)

Działa to przy użyciu vmware na dużym sprzęcie (mysql master ma przydzielone 8 rdzeni i 64 GB pamięci RAM, dyski SSD w pamięci SAN), mysql został zoptymalizowany i jest stale monitorowany. W przeszłości występowały pewne problemy związane z We / Wy (niektóre problemy z łączem między serwerami a pamięcią SAN).

Nie mogliśmy wskazać problemu, ponieważ działa na goły metal (bez wirtualizacji, ta sama konfiguracja), co nigdy się nie zdarza, w warunkach dużego obciążenia (scenariusze testowania oblężenia + obciążenia, brak pamięci podręcznej).

Czy ktoś jeszcze ma podobne problemy?

AKTUALIZACJA:

reindexWszystkie wyszukiwanie zostało przeniesione do tabeli tymczasowej (więc nie blokuje głównej tabeli używanej przez produkcję, a następnie zmienia nazwę tabeli tmp). Tak więc proces ponownego indeksowania nie przeszkadza odwiedzającym przeszukującym witrynę. https://github.com/magendooro/magento-fulltext-reindex kudos to carco

Wygląda to na podstawowy błąd / regresję, który widzieliśmy w 1.7, w którym pamięć podręczna bloków i kolekcji nie działała skutecznie w menu nawigacyjnym ( catalog/navigation/top.phtml).

Możesz przetestować, usuwając go lub po prostu tymczasowo przechwytuj dane wyjściowe do pliku za pomocą ob_starti udostępniaj je ze statycznego pliku / pamięci podręcznej.

Ponadto sprzęt, którego używasz, nie brzmi olbrzymie i wygląda poniżej określonego dla wielkości posiadanego sklepu. Prawdopodobnie występuje tam również wąskie gardło We / Wy - pamięć SAN + przeciążona sieć = niska wydajność.

-

Jako prymitywne rozwiązanie, możesz dostosować klasę bloków dla nawigacji (zrzutu get_class($this)), top.phtmlaby ją zidentyfikować.

Umożliwi to buforowanie w całej witrynie, bez buforowania na poziomie kategorii, które wywołała nowa wersja. Warto również usunąć is_activeklasę z renderera drzewa, jeśli to zrobisz, aby uniknąć wybierania losowych elementów menu (i zamiast tego zaimplementuj alternatywę JS).

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;
}

Zamień funkcję o

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;
}

W naszym przypadku sprowadzało się to do tego powolnego zapytania:

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')

z aplikacji / code / core / Mage / Sitemap / Model / Resource / Catalog / Product.php .

Zawiesza się z powodu instrukcji category_id IS NULL . MySQL z jakiegoś powodu nie korzystał z indeksu.

Usunięcie id_kategorii TO NULL i ustawienie id_path REGEXP '^ produkt / [0-9] + $' rozwiązało problem.

Skopiuj aplikację / code / core / Mage / Catalog / Helper / Product / Url / Rewrite.php do app / code / local / Mage / Catalog / Helper / Product / Url / Rewrite.php i dodaj tę funkcję:

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;
}

Następnie skopiuj aplikację / code / core / Mage / Sitemap / Model / Resource / Catalog / Product.php do app / code / local / Mage / Sitemap / Model / Resource / Catalog / Product.php i zmień wiersz 72 na:

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

Pierwotnie pobrany z https://www.goivvy.com/blog/solved-magento-stuck-generating-google-sitemap-large-website