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";}
Improve this question
2
  • by any chance, is the reindexing process running when you get this error? specially the search index.
    – Marius ♦
    Commented Aug 28, 2015 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
    Commented Aug 28, 2015 at 12:36

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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