Magento Database Optimisation

Magento Database Optimisation

Andy Holland

To make sure that your Magento site is running at the optimal levels, you’ll want to regularly perform optimisation tasks by maintaining and cleaning your old Magento cache and log records. This can help improve performance. Magento databases get pretty huge with large log tables, so a regular spring clean of these can just ensure that everything is ticking over as it should be.

You can simple create a small script which is executed regularly. The script – with thanks to Crucial Web Hosting – is as follows:

  1. <?php
  2. switch($_GET[‘clean’]){
  3.   case ‘log’:
  4.       clean_log_tables();
  5.   break;
  6.   case ‘var’:
  7.       clean_var_directory();
  8.   break;
  9. }
  10. functionclean_log_tables() {
  11.   $xml = simplexml_load_file(‘./app/etc/local.xml’, NULL, LIBXML_NOCDATA);
  12.   if(is_object($xml)) {
  13.       $db[‘host’] = $xml->global->resources->default_setup->connection->host;
  14.       $db[‘name’] = $xml->global->resources->default_setup->connection->dbname;
  15.       $db[‘user’] = $xml->global->resources->default_setup->connection->username;
  16.       $db[‘pass’] = $xml->global->resources->default_setup->connection->password;
  17.       $db[‘pref’] = $xml->global->resources->db->table_prefix;
  18.       $tables = array(
  19.           ‘aw_core_logger’,
  20.           ‘dataflow_batch_export’,
  21.           ‘dataflow_batch_import’,
  22.           ‘log_customer’,
  23.           ‘log_quote’,
  24.           ‘log_summary’,
  25.           ‘log_summary_type’,
  26.           ‘log_url’,
  27.           ‘log_url_info’,
  28.           ‘log_visitor’,
  29.           ‘log_visitor_info’,
  30.           ‘log_visitor_online’,
  31.           ‘index_event’,
  32.           ‘report_event’,
  33.           ‘report_viewed_product_index’,
  34.           ‘report_compared_product_index’,
  35.           ‘catalog_compare_item’,
  36.           ‘catalogindex_aggregation’,
  37.           ‘catalogindex_aggregation_tag’,
  38.           ‘catalogindex_aggregation_to_tag’
  39.       );
  40.       mysql_connect($db[‘host’], $db[‘user’], $db[‘pass’]) or die(mysql_error());
  41.       mysql_select_db($db[‘name’]) or die(mysql_error());
  42.       foreach($tables as $table) {
  43.           @mysql_query(‘TRUNCATE `’.$db[‘pref’].$table.’`’);
  44.       }
  45.   } else {
  46.       exit(‘Unable to load local.xml file’);
  47.   }
  48. }
  49. functionclean_var_directory() {
  50.   $dirs = array(
  51.       ‘downloader/.cache/’,
  52.       ‘downloader/pearlib/cache/*’,
  53.       ‘downloader/pearlib/download/*’,
  54.       ‘media/css/’,
  55.       ‘media/css_secure/’,
  56.       ‘media/import/’,
  57.       ‘media/js/’,
  58.       ‘var/cache/’,
  59.       ‘var/locks/’,
  60.       ‘var/log/’,
  61.       ‘var/report/’,
  62.       ‘var/session/’,
  63.       ‘var/tmp/’
  64.   );
  65.   foreach($dirs as $dir) {
  66.       exec(‘rm -rf ‘.$dir);
  67.   }
  68. }

To Clear Log Tables:

‘adminnotification_inbox’,

‘aw_core_logger’,

‘dataflow_batch_export’,

‘dataflow_batch_import’,

‘log_customer’,

‘log_quote’,

‘log_summary’,

‘log_summary_type’,

‘log_url’,

‘log_url_info’,

‘log_visitor’,

‘log_visitor_info’,

‘log_visitor_online’,

‘index_event’,

‘report_event’,

‘report_viewed_product_index’,

‘report_compared_product_index’,

‘catalog_compare_item’,

‘catalogindex_aggregation’,

‘catalogindex_aggregation_tag’,

‘catalogindex_aggregation_to_tag’

 

To Clear Old Cache and Log Files:

‘downloader/.cache/’,

‘downloader/pearlib/cache/*’,

‘downloader/pearlib/download/*’,

‘var/cache/’,

‘var/locks/’,

‘var/log/’,

‘var/report/’,

‘var/session/’,

‘var/tmp/’

 

To carry out the above tasks, you need to create a file called cleanup.php in your home directory using FTP. Then access the following using your domain name:

http://www.yourdomain.com/cleanup.php?clean=log         –clean log tables

http://www.yourdomain.com/cleanup.php?clean=var         –clean cache files

 

To set this going regularly use cPanel to set a cron job using command:

curl -s -o /dev/null http://www.domain.com/cleanup.php?clean=log

curl -s -o /dev/null http://www.domain.com/cleanup.php?clean=var

 

We hope this helps you keep Magento fresh and fast!

Andy Holland

Author Andy Holland

More posts by Andy Holland