2

My site is constantly getting bogged down my lock wait timeouts. I'm using a virtual server with 6 cores and 6gb of ram. There's about 1,500 products on my site and it's a multi-domain installation. I'm also using the combined Like/Fulltext search. Any help would be greatly appreciated in what to do with this, as I've had 4 "mysql experts" look at it and they've done nothing. Here's an example from my var/reports folder:

a:5:{i:0;s:513:"SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: INSERT INTO `mg_catalogsearch_result` SELECT 8029 AS `query_id`, `s`.`product_id`, MATCH (s.data_index) AGAINST (:query IN BOOLEAN MODE) AS `relevance` FROM `mg_catalogsearch_fulltext` AS `s`
 INNER JOIN `mg_catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 4) AND (MATCH (s.data_index) AGAINST (:query IN BOOLEAN MODE)) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`)";i:1;s:5011:"#0 /home/mysite/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/mysite/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/mysite/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/mysite/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `mg...', Array)
#4 /home/mysite/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `mg...', Array)
#5 /home/mysite/public_html/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(393): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `mg...', Array)
#6 /home/mysite/public_html/app/code/core/Mage/CatalogSearch/Model/Fulltext.php(136): Mage_CatalogSearch_Model_Resource_Fulltext->prepareResult(Object(Mage_CatalogSearch_Model_Fulltext), 'liz', Object(Mage_CatalogSearch_Model_Query))
#7 /home/mysite/public_html/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext/Collection.php(55): Mage_CatalogSearch_Model_Fulltext->prepareResult()
#8 /home/mysite/public_html/app/code/local/Smartwave/Ajaxcatalog/Model/Catalogsearch/Layer.php(15): Mage_CatalogSearch_Model_Resource_Fulltext_Collection->addSearchFilter('liz')
#9 /home/mysite/public_html/app/code/core/Mage/CatalogSearch/Model/Layer.php(42): Smartwave_Ajaxcatalog_Model_Catalogsearch_Layer->prepareProductCollection(Object(Mage_CatalogSearch_Model_Resource_Fulltext_Collection))
#10 /home/mysite/public_html/app/code/core/Mage/Catalog/Model/Layer/Filter/Category.php(101): Mage_CatalogSearch_Model_Layer->getProductCollection()
#11 /home/mysite/public_html/app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(91): Mage_Catalog_Model_Layer_Filter_Category->apply(Object(Mage_Core_Controller_Request_Http), Object(Mage_Catalog_Block_Layer_Filter_Category))
#12 /home/mysite/public_html/app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(73): Mage_Catalog_Block_Layer_Filter_Abstract->_initFilter()
#13 /home/mysite/public_html/app/code/core/Mage/Catalog/Block/Layer/View.php(117): Mage_Catalog_Block_Layer_Filter_Abstract->init()
#14 /home/mysite/public_html/app/code/core/Mage/Core/Block/Abstract.php(293): Mage_Catalog_Block_Layer_View->_prepareLayout()
#15 /home/mysite/public_html/app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Smartwave_All_Model_Core_Layout))
#16 /home/mysite/public_html/app/code/core/Mage/Core/Model/Layout.php(472): Mage_Core_Model_Layout->createBlock('catalogsearch/l...', 'catalogsearch.l...')
#17 /home/mysite/public_html/app/code/core/Mage/Core/Model/Layout.php(239): Mage_Core_Model_Layout->addBlock('catalogsearch/l...', 'catalogsearch.l...')
#18 /home/mysite/public_html/app/code/local/Smartwave/All/Model/Core/Layout.php(40): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#19 /home/mysite/public_html/app/code/core/Mage/Core/Model/Layout.php(205): Smartwave_All_Model_Core_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#20 /home/mysite/public_html/app/code/local/Smartwave/All/Model/Core/Layout.php(31): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#21 /home/mysite/public_html/app/code/core/Mage/Core/Model/Layout.php(210): Smartwave_All_Model_Core_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#22 /home/mysite/public_html/app/code/local/Smartwave/All/Model/Core/Layout.php(31): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#23 /home/mysite/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(344): Smartwave_All_Model_Core_Layout->generateBlocks()
#24 /home/mysite/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(269): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#25 /home/mysite/public_html/app/code/local/Smartwave/Ajaxcatalog/controllers/CatalogSearch/ResultController.php(96): Mage_Core_Controller_Varien_Action->loadLayout()
#26 /home/mysite/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Smartwave_Ajaxcatalog_CatalogSearch_ResultController->indexAction()
#27 /home/mysite/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('index')
#28 /home/mysite/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#29 /home/mysite/public_html/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#30 /home/mysite/public_html/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#31 /home/mysite/public_html/switchflats/index.php(183): Mage::run('lindsay*****...', 'website')
#32 {main}";s:3:"url";s:52:"/catalogsearch/result/index/?cat=129&color=301&q=liz";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:19:"lindsay*****_com";}
  • by any chance, is the reindexing process running when you get this error? specially the search index. – Marius Aug 28 '15 at 6:24
  • I don't believe so, but it is possible. I use the M2Epro Ebay/Amazon connection, so it's syncing/indexing inventory constantly throughout the day. – Eric Fox Aug 28 '15 at 12:36
2

Try increasing the timeout

[mysqld]
innodb_lock_wait_timeout=100

Restart mysql and try running that query again

INSERT INTO `mg_catalogsearch_result` SELECT 8029 AS `query_id`, `s`.`product_id`, MATCH (s.data_index) AGAINST (:query IN BOOLEAN MODE) AS `relevance` FROM `mg_catalogsearch_fulltext` AS `s`
 INNER JOIN `mg_catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 4) AND (MATCH (s.data_index) AGAINST (:query IN BOOLEAN MODE)) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`)";

You may need a higher timeout, if this fails you might need to look at your mysql config

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.