CODEWITHSUNDEEP
magento
Elamurugan
Elamurugan

Reputation: 3214

Clearing Magento Log Data

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?

Upvotes: 67

Views: 128412

Answers (12)

Arun
Arun

Reputation: 11

you can disable or set date and time for log setting.

System > Configuration > Advanced > System > Log Cleaning

Upvotes: 0

Universal Webtech
Universal Webtech

Reputation: 31

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;

Upvotes: 2

Ramesh M
Ramesh M

Reputation: 1

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/

Upvotes: 0

Martin
Martin

Reputation: 2683

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;

Upvotes: 1

Randhir Yadav
Randhir Yadav

Reputation: 1459

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;

Upvotes: 16

diazwatson
diazwatson

Reputation: 141

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>

Upvotes: 1

Matthias Kleine
Matthias Kleine

Reputation: 1235

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

Upvotes: 1

Alana Storm
Alana Storm

Reputation: 166146

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.

Upvotes: 78

Jignesh Chabhadiya
Jignesh Chabhadiya

Reputation: 321

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”.

Upvotes: 6

Maniprakash Chinnasamy
Maniprakash Chinnasamy

Reputation: 2776

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.

Upvotes: 80

sjolzy
sjolzy

Reputation: 69

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.

Upvotes: 6

MagePsycho
MagePsycho

Reputation: 2004

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

Upvotes: 56

Related Questions