Magento 1.9 CE single store getting "General error: 5 Out of memory"

[Sanket90]

Hi All,

I have a magento 1.9 CE version installed with single store configured.

Site was running properly but then since last week it is giving following error.

SQLSTATE[HY000]: General error: 5 Out of memory (Needed 50331620 bytes), query was: SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '96') AND `at_status_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 1)
 INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '102') AND `at_visibility_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '102') AND (`at_visibility`.`store_id` = 1)
 INNER JOIN `catalog_product_entity_int` AS `at_is_featured` ON (`at_is_featured`.`entity_id` = `e`.`entity_id`) AND (`at_is_featured`.`attribute_id` = '146') AND (`at_is_featured`.`store_id` = 0) WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1') AND (((IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) = '4') OR (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) = '2'))) AND (`e`.`entity_id` IN('2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79')) AND (at_is_featured.value = '1')

Trace:
#0 /home/arthita/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/arthita/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/arthita/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/arthita/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT COUNT(DI...', Array)
#4 /home/arthita/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT COUNT(DI...', Array)
#5 /home/arthita/public_html/lib/Zend/Db/Adapter/Abstract.php(828): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 /home/arthita/public_html/lib/Varien/Data/Collection/Db.php(225): Zend_Db_Adapter_Abstract->fetchOne(Object(Varien_Db_Select), Array)
#7 /home/arthita/public_html/lib/Varien/Data/Collection.php(225): Varien_Data_Collection_Db->getSize()
#8 /home/arthita/public_html/lib/Varien/Data/Collection.php(211): Varien_Data_Collection->getLastPageNumber()
#9 /home/arthita/public_html/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(1034): Varien_Data_Collection->getCurPage()
#10 /home/arthita/public_html/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(871): Mage_Eav_Model_Entity_Collection_Abstract->_loadEntities(false, false)
#11 /home/arthita/public_html/lib/Varien/Data/Collection.php(741): Mage_Eav_Model_Entity_Collection_Abstract->load()
#12 /home/arthita/public_html/app/code/local/Sns/Producttabs/Block/List.php(244): Varien_Data_Collection->getIterator()
#13 /home/arthita/public_html/app/design/frontend/sns_kalolia/default/template/sns/producttabs/items.phtml(14): Sns_Producttabs_Block_List->getProductsOrder('is_featured')
#14 /home/arthita/public_html/app/design/frontend/sns_kalolia/default/template/sns/producttabs/default.phtml(77): include('/home/arthita/p...')
#15 /home/arthita/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/home/arthita/p...')
#16 /home/arthita/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/sns_ka...')
#17 /home/arthita/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#18 /home/arthita/public_html/app/code/local/Sns/Producttabs/Block/List.php(80): Mage_Core_Block_Template->_toHtml()
#19 /home/arthita/public_html/app/code/local/Sns/Producttabs/Block/Grid.php(13): Sns_Producttabs_Block_List->_toHtml()
#20 /home/arthita/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Sns_Producttabs_Block_Grid->_toHtml()
#21 /home/arthita/public_html/app/code/core/Mage/Core/Block/Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#22 /home/arthita/public_html/app/code/core/Mage/Core/Block/Abstract.php(577): Mage_Core_Block_Abstract->_getChildHtml('producttabs.gri...', true)
#23 /home/arthita/public_html/app/code/local/Sns/Kalolia/Block/Usecache.php(37): Mage_Core_Block_Abstract->getChildHtml('', true, true)
#24 /home/arthita/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Sns_Kalolia_Block_Usecache->_toHtml()
#25 /home/arthita/public_html/app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
#26 /home/arthita/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Text_List->_toHtml()
#27 /home/arthita/public_html/app/code/core/Mage/Core/Block/Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#28 /home/arthita/public_html/app/code/core/Mage/Core/Block/Abstract.php(581): Mage_Core_Block_Abstract->_getChildHtml('content', true)
#29 /home/arthita/public_html/app/design/frontend/sns_kalolia/default/template/page/1column.phtml(104): Mage_Core_Block_Abstract->getChildHtml('content')
#30 /home/arthita/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/home/arthita/p...')
#31 /home/arthita/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/sns_ka...')
#32 /home/arthita/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#33 /home/arthita/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#34 /home/arthita/public_html/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#35 /home/arthita/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#36 /home/arthita/public_html/app/code/core/Mage/Cms/Helper/Page.php(137): Mage_Core_Controller_Varien_Action->renderLayout()
#37 /home/arthita/public_html/app/code/core/Mage/Cms/Helper/Page.php(52): Mage_Cms_Helper_Page->_renderPage(Object(Mage_Cms_IndexController), 'kalolia_home_pa...')
#38 /home/arthita/public_html/app/code/core/Mage/Cms/controllers/IndexController.php(45): Mage_Cms_Helper_Page->renderPage(Object(Mage_Cms_IndexController), 'kalolia_home_pa...')
#39 /home/arthita/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Cms_IndexController->indexAction()
#40 /home/arthita/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('index')
#41 /home/arthita/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))
#42 /home/arthita/public_html/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#43 /home/arthita/public_html/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#44 /home/arthita/public_html/index.php(83): Mage::run('', 'store')
#45 {main}

I googled and searched about the issue and everybody says to check and repair tables or change MySql config to allocate more memory. I did all of that and still no luck.

Site has one store with 80 products. Currently the site is in beta stage and still some changes and configuration are in progress. So there are not much visitors and orders.

Please advice and help me what should I do? What minimum configuration I require to up and running site smoothly?

Thank you

[JLHC]

What is your current hosting environment? From the error it shows that your MySQL doesn't have enough Memory allocated which can be due to a configuration problem. Do you have root access to the server to modify the MySQL configurations? 

[Sanket90]

Hi,

I do not have access to change MySQL configuration. But here is the config my hosting provider provided me.

Hosting is shared hosting, with linux os.

MySQL Config Details :

[mysqld]
thread_concurrency=2
read_rnd_buffer_size=8M
# safe-show-database
skip-networking
table_open_cache=8000
table_definition_cache=800
wait_timeout=15
open_files_limit=23544
tmpdir="/home/tmpdir"
max_connections=600
max_heap_table_size=768M
sort_buffer_size=1M
max_allowed_packet=268435456
max_delayed_threads=5
join_buffer_size=1M
connect_timeout=10
interactive_timeout=100
myisam_sort_buffer_size=64M
query_cache_limit=2M
max_tmp_tables=4
innodb_file_per_table=1
read_buffer_size=1M
tmp_table_size=768M
query_cache_size=256M
query_cache_type=1
thread_cache_size=128
key_buffer=64M
local-infile=0
default-storage-engine=MyISAM
innodb_buffer_pool_size=134217728

Please advice me if any changes needed in it, so that I can communicate the same to Hosting provider.

[JLHC · Accepted solution]

Your MySQL configuration doesn't seem to be optimized for Magento at all. 

As you are under shared hosting, are you sure your hosting provider will allow you to change any configurations? 

[Sanket90]

Hi,

I asked my hosting provider to configure my min memory quota and for now it is as per follow.

Php memory limit : 512MB

innodb buffer size : 400MB

When site is live we are gonna go for dedicated hosting. But for now can you please tell me is this configuration enough?

[JLHC]

The PHP Memory Limit should be fine and you should be able to easily increase it if you need to. 

As for the "innodb buffer size", do you mean the InnoDB Buffer Pool Size? As this is most likely shared among many other sites, 400 MB is very low and will most likely not be enough.