Knowledgebase

How to clean up the database logs of your Magento

With time, as more and more users and customers visit your store, the log tables of your Magento database gradually increase in size. This slows down your whole Magento store and also slows the process of backing up the database. By cleaning these logs regularly you can optimize your store and improve its performance and speed.

There are two ways you can clean up the database logs: through the Magento admin panel or manually with phpMyAdmin.

Automatic Log Cleaning

Magento has a built-in function in the admin panel for cleaning up the logs regularly. This is essentially a cron job. It's, however, turned off by default. You can enable it from you store's Dashboard. After you log in to your admin panel, go to System>Configuration. In the Advanced section on the left side click on System and from the panels that appear in the middle of the screen select Log Cleaning. Set the Enable Log Cleaning drop-down menu to Yes.

From the other log cleaning options you can select the starting time for the execution of the cron job, the frequency of the cleaning (daily, weekly, monthly), the error email recipient, etc. It's also recommended to decrease the value in the field for Save Log, Days. By default it's 180. If you enable log cleaning it's pointless to leave the default value because even if the cron job is executed daily or even monthly, the logs will still be saved for 180 days. So depending on your needs and preferences and on the frequency of cron job execution, decrease the value for Save Log, Days (e.g. 30 days, 7 days). After that click on the Save Config button in the upper right corner.

In order for this automatic log cleaning to function you also need to have a working cron job that executes the cron.php file that's in the root Magento folder on your Magento hosting account. You can add and manage cron jobs from the Cron Jobs section of the Pixie control panel. You also have to make sure that scheduled tasks that are executed with the help of cron jobs are not missed. You can do this from the admin panel of your Magento by going to System menu>Configuration>System tab in the Advanced section on the left>Cron (Scheduled Tasks) panel on the right and configuring the value for the option Missed If Not Run Within. Set it to a value that's at least equal to the frequency of cron job execution (e.g. if the cron job is executed once every hour, set it to 60). For more information on cron jobs and tasks performed with cron jobs check out the tutorial on how to manage cron jobs and tasks in Magento.

Manual Log Cleaning

You can also clean up the logs manually using phpMyAdmin. You can access phpMyAdmin from the Databases section of the Pixie control panel (there's a link on the right). Inside phpMyAdmin, in the left frame click on the name of your Magento database to view its tables. Then mark the checkboxes for the tables:

log_url
log_url_info
log_visitor
log_visitor_info
dataflow_batch_import
dataflow_batch_export
index_event
report_event

After that from the With selected drop-down menu at the bottom of the page select Empty. Be careful not to select Drop or you'll delete the tables. After you empty the tables it's a good idea to optimize them. Mark the checkboxes for the same tables you just emptied and from the With selected drop-down menu choose Optimize table.

It's also a good idea, if you've never cleaned the logs and/or the database tables have become quite big, to do this manually the first time.

Also Read