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?
This question is related to
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:
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/
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;
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”.
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>
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
you can disable or set date and time for log setting.
System > Configuration > Advanced > System > Log Cleaning
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.
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
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;
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.
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;
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;
To get more information http://blog.contus.com/magento-database-maintenance-and-optimization/
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.
Source: Stackoverflow.com