MySQL Data Size Increasing to 36 GB or More – Causes and Solutions

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

The lex.you need to create a model using a custom module.

Step: You have define model class and resource type but not define resource Class

<models>
        <smsnotification>
            <class>VivasIndustries_SmsNotification_Model</class> <!-- model prefix class -->
            <resourceModel>vivasindustries_smsnotification_resource</resourceModel>
<!-- model resource type -->

        </smsnotification>
    </models>

So you need to add resource class which is interact the model with database.

  <models>
......
    <vivasindustries_smsnotification_resource>
        <class>VivasIndustries_SmsNotification_Model_Resource</class>
        <entities>
            <custommodule>
            <table>VivasIndustries_SmsNotification</table> <!-- VivasIndustries_SmsNotification is Your db tabale -->
            </custommodule>
        </entities>
        </vivasindustries_smsnotification_resource>
</models>

VivasIndustries_SmsNotification_Model_Resource VivasIndustries_SmsNotification

you need to define class VivasIndustries_SmsNotification_Model_Smsnotification

code is

<?php
class VivasIndustries_SmsNotification_Model_Smsnotification extends extends Mage_Core_Model_Abstract
{
    public function _construct()
    {
        $this->_init('smsnotification/smsnotification');
    }

}

Resource class model class:

<?php
class VivasIndustries_SmsNotification_Model_Resource_Smsnotification extends Mage_Core_Model_Resource_Db_Abstract
{
    /**
     * Initialize resource model
     *
     * @return void
     */
    public function _construct()
    {
        $this->_init('smsnotification/smsnotification','id');
    }
}

Resource Collection model

<?php 
class VivasIndustries_SmsNotification_Model_Resource_Smsnotification_Collection 
extends Mage_Core_Model_Resource_Db_Collection_Abstract{
    protected function _constuct(){
        $this->_init('smsnotification/smsnotification');    
    }
}

[Please study at how create model][1]

Now you get a collection mode.

Now on preapareCollection

you need change code:

$collection =Mage::getResourceModel('smsnotification/smsnotification_collection);

You grid code is:

<?php

class VivasIndustries_SmsNotification_Block_Adminhtml_Smsnotification_Grid extends Mage_Adminhtml_Block_Widget_Grid
{

        public function __construct()
        {
                parent::__construct();
                $this->setId("smsnotificationGrid");
                $this->setDefaultSort("id");
                $this->setDefaultDir("DESC");
                $this->setSaveParametersInSession(true);
        }

        protected function _prepareCollection()
        {
                $collection = Mage::getModel("smsnotification/smsnotification")->getCollection();
                $this->setCollection($collection);
                return parent::_prepareCollection();
        }
        protected function _prepareColumns()
        {
                $this->addColumn("id", array(
                "header" => Mage::helper("smsnotification")->__("ID"),
                "align" =>"right",
                "width" => "50px",
                "type" => "number",
                "index" => "id",
                ));

                $this->addColumn("receiver", array(
                "header" => Mage::helper("smsnotification")->__("Receiver"),
                "index" => "receiver",
                ));
                $this->addColumn("phone", array(
                "header" => Mage::helper("smsnotification")->__("Phome"),
                "index" => "phone",
                ));
                    $this->addColumn('date', array(
                        'header'    => Mage::helper('smsnotification')->__('Data'),
                        'index'     => 'date',
                        'type'      => 'datetime',
                    ));
            $this->addExportType('*/*/exportCsv', Mage::helper('sales')->__('CSV')); 
            $this->addExportType('*/*/exportExcel', Mage::helper('sales')->__('Excel'));

                return parent::_prepareColumns();
        }

        public function getRowUrl($row)
        {
               return $this->getUrl("*/*/edit", array("id" => $row->getId()));
        }



        protected function _prepareMassaction()
        {
            $this->setMassactionIdField('id');
            $this->getMassactionBlock()->setFormFieldName('ids');
            $this->getMassactionBlock()->setUseSelectAll(true);
            $this->getMassactionBlock()->addItem('remove_smsnotification', array(
                     'label'=> Mage::helper('smsnotification')->__('Remove Smsnotification'),
                     'url'  => $this->getUrl('*/adminhtml_smsnotification/massRemove'),
                     'confirm' => Mage::helper('smsnotification')->__('Are you sure?')
                ));
            return $this;
        }


}

Update code at config.xml

<global>
...
<models>
        <smsnotification>
            <class>VivasIndustries_SmsNotification_Model</class> <!-- model prefix class -->
            <resourceModel>vivasindustries_smsnotification_resource</resourceModel>
<!-- model resource type -->

        </smsnotification>
    <vivasindustries_smsnotification_resource>
        <class>VivasIndustries_SmsNotification_Model_Resource</class>
        <entities>
            <custommodule>
            <table>VivasIndustries_SmsNotification</table> <!-- VivasIndustries_SmsNotification is Your db tabale -->
            </custommodule>
        </entities>
        </vivasindustries_smsnotification_resource>    
    </models>
    <resources>
      <smsnotification_setup>
        <setup>
          <module>VivasIndustries_SmsNotification</module>
        </setup>
        <connection>
          <use>core_setup</use>
        </connection>
      </smsnotification_setup>
      <smsnotification_write>
        <connection>
          <use>core_write</use>
        </connection>
      </smsnotification_write>
      <smsnotification_read>
        <connection>
          <use>core_read</use>
        </connection>
      </smsnotification_read>
    </resources>
.....
</global>


  [1]: http://www.amitbera.com/create-an-magento-extension-with-custom-database-table/