In Ecommerce Applications, Hosting Guides Reducing the size of your Magento database Posted by Stefan OVERVIEW Whilst Magento is a powerful and flexible eCommerce platform, it has the unhealthy disadvantage of not self-maintaining its own log records. These log files are found within your Magento database and they record information about every customer who visits your store and every link that is visited. Over time, and especially if your store receives a lot of traffic, these log files can become very large in size. Quite often here at Nublue we notice bulging log files taking up over 90% of the size of a database! As you can see in the screenshot below, here is an example of a Magento store holding around 6 million records in its log tables, equating to around 750MB of disk space! Therefore, it is important to maintain your Magento database in order to keep it running efficiently. By cleaning out your log files, you not only speed up your Magento store, you also help dramatically reduce your overall disk space usage which can often negate the need to upgrade your existing hosting package. To help you maintain your log files and reduce your overall disk space usage, we have modified a popular script to be compatible with our hosting that does the hard work for you! The script will also save you disk space elsewhere by clearing error reports generated by Magento and other cached files relating to the Magento Connect process. This guide will walk you through the installation and configuration of that script. As always, we recommend taking a full backup of your site before carrying out any work on your web hosting account. Nublue take no responsibility for any damage to your Magento store caused by the use of this script. We advise that all testing of this script should be undertaken in a development environment to ensure it works as expected before deployment in a production environment. We also cannot guarantee that the script won’t do odd things to any modules you have installed within Magento (the script empties a lot of the log database tables which a ‘recently viewed products’ type extension might rely on), although you can of course revert to your backed up solution if it does. METHOD Step 1 Create a new file called ‘nucleanup.php’ using your favourite text editor (we recommend Notepad++), and paste the script here into the new ‘nucleanup.php’ file (you’ll need to manually change the file extension from ‘.txt’ to ‘.php’). Step 2 Upload your ‘nucleanup.php’ file to your Magento root folder either by logging into your Plesk Control Panel with your credentials, or though your FTP client of choice. Step 3 Set the value of the ‘$domain’ variable to your domain name. For example, if your website URL is mydomain.co.uk you would set the value of the ‘$domain’ variable to ‘mydomain.co.uk’. Step 4 The value of the ‘$subdirectory’ variable should be set to the path of your Magento store. For example, if your Magento store is located at mydomain.co.uk/mystore the value of the ‘$subdirectory’ variable would be set to ‘mystore’. Step 5 The value of ‘$cronOnly’ should be set to ‘false’ if you are simply testing the script or if you inexplicably receive a security notice when running as a cron. By setting the value of ‘$cronOnly’ to ‘false’ you will be able to run and test the script directly from your browser. Step 6 From your Plesk Control Panel, select the domain on which the script is being implemented. Now click the ‘Crontab’ button and select the relevant system user. Step 7 We will first schedule a task for purging the log files which belong in the Magento database. Select ‘Schedule a Task for ‘, and enter the following in the ‘Command’ text field: curl -silent -output /dev/null http://www.yourdomain.co.uk//nucleanup.php?clean=log 1 curl -silent -output /dev/null http://www.yourdomain.co.uk//nucleanup.php?clean=log Step 8 Now we need to schedule when and how often the script is run. We recommend that the log files in your Magento database be cleaned daily, at the quietest time for your Magento store (often in the early hours of the morning). To schedule the script to run daily at 04:30 hours every morning enter the following values into the relevant text fields and the click the ‘OK’ button: Minute: 30 Hour: 4 Day of the Month: * Month: * Day of the Week: * Step 9 We can now schedule an additional task for purging the var files (such as error reports) present in your Magento store. To do this, we need to create a new task. Select ‘Schedule a Task for ‘, and enter the following in the ‘Command’ text field: curl --silent --output /dev/null http://www.yourdomain.co.uk/path/to/nucleanup.php?clean=var 1 curl --silent --output /dev/null http://www.yourdomain.co.uk/path/to/nucleanup.php?clean=var Step 10 To configure the script to run and clean the var files within your Magento store every three days at 03:00, enter the following into the relevant text fields and then click ‘OK’: Minute: 0 Hour: 3 Day of the Month: */3 Month: * Day of the Week: * Step 11 Your ‘Crontask’ screen should look something like the screenshot below: Step 12 You can now test the script by running it directly in your web browser. Depending on which function of the script you wish to test, browse to: http://www.yourdomain.co.uk/<;PathToMagentoStore>/nucleanup.php?clean=log 1 http://www.yourdomain.co.uk/<;PathToMagentoStore>/nucleanup.php?clean=log or http://www.yourdomain.co.uk/<;PathToMagentoStore>/nucleanup.php?clean=var 1 http://www.yourdomain.co.uk/<;PathToMagentoStore>/nucleanup.php?clean=var Step 13 The log and var files in your Magento store should now be purged! To check your log files are cleaned out you can log into phpMyAdmin from within your Plesk Control Panel. To do this select your domain from within Plesk and then click ‘Databases’, now select your database, then click ‘DB WebAdmin’. A popup should appear displaying phpMyAdmin, to analyse the size of the tables in your database click the name of your database in the top left hand corner of this popup. Now browse down to ‘log_url’ onwards and you should see that the number of records is reduced to 0 (or near 0 if someone has visited your Magento store since you ran the script!). You can see from the screenshot below, our initial example now has 0 log records: Step 14 Similarly, you can check that the relevant files have also been cleared from your Magento folder by browsing to‘/httpdocs/’ and checking the file size of the downloader/pearlib/cache, downloader/pearlib/download/,var/report/, var/session/, var/tmp/ directories. Step 15 If you are happy with the way the script is running, you can now update the value of ‘$cronOnly’ within the script to ‘true’. … that’s it! You can now enjoy the benefits of a cleaner and faster Magento store without worrying too much about the size of your database spiralling out of control!