Overview
The Magento eCommerce Shopping cart contains a logging facility to record visitor interactions with your store. However most store owners use Google Analytics to get metrics on the performance of their store so the inbuilt logging is forgotten about and left enabled, slowly over time the store database grows and performance may degrade due to writing log information.
Using SSH access there is a command line tool to both report the status of internal logs and the ability to clean them up.
1. Log into your VPS and navigate to your Magento installation directory.
2. Change into the "shell" directory and run the shell command "ls", you should get the following output:
# ls abstract.php compiler.php indexer.php log.php #
We can run the log.php script to get a status of the logs kept.
3. Run the "status" command as shown below:
# php -f log.php status -----------------------------------+------------+------------+------------+ Table Name | Rows | Data Size | Index Size | -----------------------------------+------------+------------+------------+ log_customer | 5 | 16.38Kb | 16.38Kb | log_visitor | 978.04K | 77.19Mb | 0 b | log_visitor_info | 1.02M | 133.87Mb | 0 b | log_url | 1.11M | 50.94Mb | 47.32Mb | log_url_info | 1.12M | 189.51Mb | 0 b | log_quote | 12 | 16.38Kb | 0 b | report_viewed_product_index | 72.02K | 3.69Mb | 11.09Mb | report_compared_product_index | 651 | 81.92Kb | 212.99Kb | report_event | 81.61K | 4.73Mb | 8.99Mb | catalog_compare_item | 111 | 16.38Kb | 81.92Kb | -----------------------------------+------------+------------+------------+ Total | 4.38M | 460.06Mb | 67.72Mb | -----------------------------------+------------+------------+------------+
Some of the log tables contain over a million rows! The "log_url" table also has an index file, so each visit requires it to read and process the 47M index to add another entry. We can clearly save some database space which will make our backup smaller and site more responsive by cleaning the log entries out of the database.
Run the following command (it may take a while to run):
# php -f log.php clean Log cleaned # php -f log.php status -----------------------------------+------------+------------+------------+ Table Name | Rows | Data Size | Index Size | -----------------------------------+------------+------------+------------+ log_customer | 5 | 16.38Kb | 16.38Kb | log_visitor | 270.15K | 24.18Mb | 0 b | log_visitor_info | 247.49K | 35.73Mb | 0 b | log_url | 324.66K | 16.79Mb | 17.86Mb | log_url_info | 320.23K | 58.82Mb | 0 b | log_quote | 3 | 16.38Kb | 0 b | report_viewed_product_index | 20.31K | 3.69Mb | 11.09Mb | report_compared_product_index | 743 | 49.15Kb | 81.92Kb | report_event | 22.92K | 2.11Mb | 6.47Mb | catalog_compare_item | 1 | 16.38Kb | 81.92Kb | -----------------------------------+------------+------------+------------+ Total | 1.21M | 141.43Mb | 35.60Mb | -----------------------------------+------------+------------+------------+ #
The script has now removed over 75% of the logging rows, it is suggested you run this every few months or turn off logging in the relevant System Administration menu.