Clearing Magento Log Data

Magento

Magento Problem Overview

I have a question regarding clearing of the log data in Magento.

I have more than 2.3GB of data in Magento 1.4.1, and now I want to optimize the database, because it's too slow due to the size of the data. I checked the log info (URL,Visitors) and it shows more than 1.9 GB. If I directly clear those records, will it affect any functionality in the site? How can I clear the log details? By clearing those data will I have or lose any data on my site?

Magento Solutions

Solution 1 - Magento

No need to do this yourself, the Magento system has a built-in for cleaning up log information. If you go to

System > Configuration > Advanced > System > Log Cleaning

You can configure your store to automatically clean up these logs.

Solution 2 - Magento

Cleaning the Magento Logs using SSH :

login to shell(SSH) panel and go with root/shell folder.

execute the below command inside the shell folder

php -f log.php clean

enter this command to view the log data's size

> php -f log.php status

This method will help you to clean the log data's very easy way.

Solution 3 - Magento

Try:

TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;

You can also refer to following tutorial:
http://www.crucialwebhost.com/kb/article/log-cache-maintenance-script/

Thanks

Solution 4 - Magento

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;
SET FOREIGN_KEY_CHECKS=1;

Solution 5 - Magento

there are some other tables you can clear out: documented here : https://dx3webs.com/blog/house-keeping-for-your-magento-database

hope this helps Andy

Solution 6 - Magento

TRUNCATE `log_url_info`;
TRUNCATE `log_visitor_info`;
TRUNCATE `index_event`;
TRUNCATE `log_visitor`;
TRUNCATE `log_url`;
TRUNCATE `report_event`;
TRUNCATE `dataflow_batch_import`;
TRUNCATE `dataflow_batch_export`;

I just use it.

Solution 7 - Magento

Cleaning Logs via Magento Admin Panel

This method is easier for non technical store owners who don’t want’ to mess directly with the Magento store’s database. To activate log cleaning option in Magento just do the following:

Log on to your Magento Admin Panel. Go to System => Configuration. On the left under Advanced click on System (Advanced = > System). Under system you will see “Log Cleaning” option. Fill the desired “Log Cleaning” option values and click Save.

Cleaning Logs via phpMyAdmin

If you are comfortable with mysql and queries then this method is more efficient and quicker than default Magento Log Cleaning tool. This method also allows your to clean whatever you like, you can even clean tables which aren’t included in default Magento’s Log Cleaning tool.

Open the database in phpMyAdmin In the right frame, click on the boxes for the following tables: dataflow_batch_export

dataflow_batch_import

log_customer

log_quote

log_summary

log_summary_type

log_url

log_url_info

log_visitor

log_visitor_info

log_visitor_online

report_viewed_product_index

report_compared_product_index

report_event

Look to the bottom of the page, then click the drop down box that says “with selected” and click empty. Click Yes on confirmation screen, and this will truncate all the selected tables.

or you can use script to run

TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;

Keep in mind that we are here to empty (Truncate) selected tables are not drop them. Be very careful when you do this.

Performing this regularly will definitely improve your Magento store’s performance and efficiency. You can setup up scripts to do this automatically at regular intervals too using “CRON”.

Solution 8 - Magento

This script is neat and tidy. Set it up as a cron job and relax:

http://www.crucialwebhost.com/kb/magneto-log-and-cache-maintenance-script/

Solution 9 - Magento

Login to your c-panel goto phpmyadmin using SQL run below query to clear logs

TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;

Solution 10 - Magento

You may check good article here:

http://blog.magalter.com/magento-database-size

It has instructions how to check database size, truncate some tables and how to configure automatic table cleaning.

Solution 11 - Magento

Further hint: If you don't need those information you can change the Table Type to "Blackhole" (instead of InnoDB). This means, nothing will be stored in the future and the insert statements are still working fine.

You can also disable the logs by setting all events to disabled:

https://gist.github.com/tegansnyder/189f1c3ce897b4ab494d

Solution 12 - Magento

After clean the logs using any of the methods described above you can also disable them in your app/etc/local.xml

...
<frontend>
<events>
 <frontend>
  <events>
    <!-- disable Mage_Log -->
    <controller_action_predispatch>
        <observers><log><type>disabled</type></log></observers>
    </controller_action_predispatch>
    <controller_action_postdispatch>
        <observers><log><type>disabled</type></log></observers>
    </controller_action_postdispatch>
    <customer_login>
      <observers>
        <log>
          <type>disabled</type>
        </log>
      </observers>
    </customer_login>
    <customer_logout>
      <observers>
        <log>
          <type>disabled</type>
        </log>
      </observers>
    </customer_logout>
    <sales_quote_save_after>
       <observers>
          <log>
              <type>disabled</type>
          </log>
        </observers>
   </sales_quote_save_after>
   <checkout_quote_destroy>
     <observers>
       <log>
         <type>disabled</type>
       </log>
     </observers>
   </checkout_quote_destroy>
</events>
</frontend>
</config>

Solution 13 - Magento

SET foreign_key_checks = 0;
TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;
SET foreign_key_checks = 1;

Solution 14 - Magento

How Magento log cleaning can be done both manually, automatically and other Magento database maintenance. Below the three things are most important of Magento database maintenance and optimization techniques;

  • Log Cleaning
  • Smart use of MySQL updated versions
  • Buffer pool size settings

To get more information http://blog.contus.com/magento-database-maintenance-and-optimization/

Solution 15 - Magento

you can disable or set date and time for log setting. >System > Configuration > Advanced > System > Log Cleaning

Categories
Recommended Magento Solutions

Previous Solution:

Android: Insert text into EditText at current position

AndroidInsertAndroid Edittext

Next Solution:

Cannot install thin on windows

RubyThin

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionElamuruganView Question on Stackoverflow
Solution 1 - MagentoAlan StormView Answer on Stackoverflow
Solution 2 - MagentoManiprakash ChinnasamyView Answer on Stackoverflow
Solution 3 - MagentoMagePsychoView Answer on Stackoverflow
Solution 4 - MagentoRandhir YadavView Answer on Stackoverflow
Solution 5 - MagentoAndyView Answer on Stackoverflow
Solution 6 - MagentosjolzyView Answer on Stackoverflow
Solution 7 - MagentoJIGNESHView Answer on Stackoverflow
Solution 8 - Magentomr-euroView Answer on Stackoverflow
Solution 9 - MagentoUniversal WebtechView Answer on Stackoverflow
Solution 10 - MagentofreentoView Answer on Stackoverflow
Solution 11 - MagentoMatthias KleineView Answer on Stackoverflow
Solution 12 - MagentodiazwatsonView Answer on Stackoverflow
Solution 13 - MagentoMartinView Answer on Stackoverflow
Solution 14 - MagentoRamesh MView Answer on Stackoverflow
Solution 15 - MagentoArunView Answer on Stackoverflow