I recently had an issue with disk space over usage with a client’s Magento installation. After some investigation, I discovered that the log tables (log_url, log_url_info, log_visitor and log_visitor_info) in the Magento database were extremely large.
It doesn’t take long for these tables to get quite bloated, to the point where backing up and maintaining the Magento database can be quite time consuming and resource intense. Not to mention the space required. There is little information available on how to deal with these, besides the odd post online instructing users to truncate all the log tables. I definitely DO NOT recommend truncating these tables, as you are likely to run into referential integrity issues with foreign key constraints and a broken storefront.
Magento has provided the ability to configure log cleaning (via Magento Admin > System > Configuration > Advanced > System > Log cleaning), however, by default “Enable log cleaning” is turned off. At first, enabling log cleaning didn’t seem to work, but with some trial and error I eventually got the log cleaning via cron to run for the first time, using the following settings:
Magento Log Cleaning configuration screen shot 1
As you can see in the screen shot, I set the “Save log, days” to 0 days to force it to clear everything and set the time to 04:05 so that I could go to bed … I also had to set the “Generate schedules every” to 5 and “Schedule ahead for” to 5, to force it to run. Note however that this cron consumes a lot of server resources and will take a long time to actually clear the logs when run for the first time.
When I checked it in the morning, the database was still reporting the same table size (likely due to the index). To fix this, I ran the OPTIMISE TABLE command for each table which cleared them up.
I then set the cron settings back to default and in the log cleaning settings, I set the “Save log, days” to 7.
Magento Log Cleaning configuration screen shot 2
Hopefully this will help others out there wondering how to reduce their Magneto database size.
On a busy site you should set log cleaning to once a day and keep 1 day of history. Cron for EE 1.6 should be set to run every minute (in your cron schedule) for running private sales, for CE every 5 minutes. Default cron settings in Magento should not require changing to clean log tables.
It’s not recommend to TRUNCATE table in phpmyadmin to clean log, but what we can do when the front and backend indicated the following error “SQLSTATE[42000]: Syntax error or access violation: 1142 INSERT command denied to user ” and you can’t acess yo the interface ?
In this case, you may need to empty/truncate these tables. Use phpMyAdmin and empty/truncate the following tables:
log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
Once you have done this, OPTIMISE each table. Once you have logged back into Magento, go to System > Cache Management and give it a refresh and you should be all good to go again.
Magento simply does NOT clean up after itself. You need to implement a script to do this on a daily basis, e.g.:
http://www.crucialwebhost.com/kb/article/log-cache-maintenance-script/
Hi There,
A client of mine has version 1.1.5 of magento installed and they need their log tables emptied… but there is no clear log option on the back end…
Any advice on how to clear the table?
I would just use phpMyAdmin to EMPTY the following tables and then OPTIMISE once that is done:
log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
Believe it or not, I had to do this to a Magento install tonight that I am migrating across for a client. It had 5.5 million records and the database was a whopping 839.3MB. After doing the above, it dropped down to 127.1MB. That made it a little more managable to say the least.
Login to the Cpanel, click on phpmyadmin
find the files above and check the box next to them…then at the bottom in the drop down choose Empty….once that is done…hopefully successfully, go choose them again and select optimize this time…
I also optimize my whole database every once in a while just because it seems to speed my magento a bit for me.