Clearing Magento Log Data

Question

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?

Have you measured how much this affects performance? It would be interesting to know. — Jonathan Day, Sep 07 '10 at 01:32
Initial stage it was really great response lik in fraction of seconds my products and filter options were working both in froentend,backend, but now its become 2.3 Gb and growing like no end to it. So day by day the total response is become in min and in admin side am getting tired of its response. — Elamurugan, Sep 07 '10 at 13:07

score 80 · Answer 1 · answered Mar 05 '14 at 10:25

80

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.

answered Mar 05 '14 at 10:25

Maniprakash Chinnasamy

2,778
29
55

3

This is the way to go if you need to do things manually. It's particularly perfect if you have table prefixes (like you should). Very nice. – Michael Thompson Jul 03 '14 at 19:27

Alana Storm · Accepted Answer · 2015-07-28T17:55:40.123

78

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.

edited Jul 28 '15 at 17:55

answered Sep 03 '10 at 18:29

Alana Storm

164,128
91
395
599

6

N.B. this assumes that you have setup your cron jobs, right Alan? Instructions are here: http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/how_to_setup_a_cron_job – Jonathan Day Sep 07 '10 at 01:32
Probably Jonathan, I don't do much system maintenance with Magento, but that's typically how something like this is implemented. – Alana Storm Sep 07 '10 at 05:51
5

This definitely assumes that you have setup cron jobs. – Hervé Guétin Sep 07 '10 at 17:08
Přeložit We had problem with this automatic cleaning that it blocked the scheduling of another cron jobs. I don't know why but if we scheduled the cleaning at 5:00 each monday then each monday we found a hole in job list in table 'cron_schedule'. Last job was log_clean at 5:00 and next job was scheduled at 6:00, all jobs between 5:00 and 6:00 was not scheduled. So watch out for it. – Jiří Chmiel Sep 26 '16 at 07:02
Is this just triggering shell/log.php? – Stefan Oct 25 '16 at 09:43

score 56 · Answer 3 · answered Nov 20 '11 at 10:57

56

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

answered Nov 20 '11 at 10:57

MagePsycho

1,944
2
29
60

3

Careful with log_visitor - the catalog_compare_item links to it and you might get exceptions if you have the compare feature enabled. Probably removing all sessions would take care of this problem. – Vinai Feb 02 '12 at 16:39
4

I think that touching the DB manually is highly not recommended. It's better to run "php -f /root/to/magento/shell/log.php clean" instead – Avision Oct 12 '14 at 17:57
Don't forget disable key checks ... SET foreign_key_checks = 0; TRUNCATE .. SET foreign_key_checks = 1; – Martin Aug 26 '16 at 08:29

score 16 · Answer 4 · answered Feb 24 '16 at 11:06

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;

score 6 · Answer 5 · answered Mar 25 '15 at 05:39

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

Cleaning Logs via phpMyAdmin worked wonderfully for me, the DB went from 4GB to 19MB. — Peanuts, Jan 11 '18 at 00:08

score 6 · Answer 6 · edited Jan 18 '13 at 15:07

6

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.

edited Jan 18 '13 at 15:07

Yaroslav

6,476
10
48
89

answered Dec 27 '10 at 07:27

sjolzy

69
1
1

4

Be careful with the log_visitor table - the catalog_compare_item table references it. Guests might get exceptions if you have the compare product feature active on your site. – Vinai Feb 02 '12 at 16:42
1

You can meet the problem with key reference if you do that. You can add this Mysql command before SET foreign_key_checks = 0; and this command after SET foreign_key_checks = 1; – Phuc Jul 05 '14 at 23:03
1

@Phuc I doubt that's wise. The foreign key checks are there for a reason. Disable them and you end up with orphaned records in related tables that could cause all sorts of side effects... and you might not see those side-effects until weeks or months later, making them near-impossible to diagnose. – Doug McLean Aug 10 '15 at 11:09

score 2 · Answer 7 · answered Nov 13 '16 at 14:20

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;

score 1 · Answer 8 · answered Nov 04 '15 at 09:48

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

score 1 · Answer 9 · answered Jan 19 '16 at 14:49

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>

You Shouldn't switch off logs, as if something goes wrong you don't have any way to debug that. — Maciej Paprocki, Mar 03 '16 at 13:39

score 1 · Answer 10 · answered Aug 26 '16 at 08:29

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;

Ramesh M · Answer 11 · 2016-11-11T13:18:31.950

0

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/

edited Nov 11 '16 at 13:18

answered Nov 11 '16 at 13:13

Ramesh M

1
1

score 0 · Answer 12 · answered Mar 22 '17 at 12:49

0

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

System > Configuration > Advanced > System > Log Cleaning

answered Mar 22 '17 at 12:49

Arun

11
2

Clearing Magento Log Data

12 Answers12

Linked