Having a bloated Magento database can result in a performance hit to your ecommerce site. 9 times out of ten, this is a direct result of database logging. Each time your visitors surf your site, mySQL writes several records to your database.
To-date, I have NOT heard or seen a valid use for the data that this is logging, especially since the same data is usually already being tracked using Google Analytics or something related.
Below are three options you have that can resolve this issue.
Here are REAL examples of database sizes and the result after cleaning out the log data:
Start – 13.4 GB Finish – 456MB
Start – 4.22 GB Finish – 700MB
Start – 8.84 GB Finish – 658 MB
To put this in perspective, the standard Linux kernel is just over 900MB and has over 15 million lines of code. There is almost no reason you should have a database larger than this.
So what can you do? I’ve listed out a few options below.
Option A – Leave the data, but turn on Magento Log Rotation.
Option A leaves the data in place, which means that you’ll still take the performance hit for the unnecessary database writes on each page visit, but your database won’t become large to to log entries.
Step 1: Navigate to System->Configuration->System and expand the Log Cleaning tab.
Step 2: Set the Save Log field to something small like 1 or 2 days
Step 3: Save the configuration. The next time the cron for this runs (specified in the time field), it’ll purge the database and keep it clean.
Option B – Hack the core code? Say what?
Hacking core code is frowned upon in the Magento community. For good reasons. Therefore, I formally recommend using option A or C.
Step 1: Copy /app/code/core/Mage/Log/Model/Visitor.php
Step 2: Edit /app/code/local/Mage/Log/Model/Visitor.php
Modify this line (around line 4)
protected $_skipRequestLogging = false;
protected $_skipRequestLogging = true;
Option C – Disable Logging in your Local Config File
You can place the following code in a configuration file. Most likely you should use app/etc/local.xml due to the loading sequence and order of precedence.
<frontend> <events> <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>
First, to all the internet police out there waiting to find some error somewhere in an article on my blog, get a life. We are a community here and I am always looking to expand my knowledge and share the info with those willing to learn. Please only provide constructive criticism.
Second, here is something cool I learned from one of the reader. Instead of purging all of the log tables by hand after diabling, there is a shell script that you can run, provided you have shell access to your code:
php -f shell/log.php clean