Important Magento maintenance, now for IIS too: optimize the speed and performance of your Magento ecommerce webshop by carrying out important maintenance. Remove old MySQL database log files and Magento cache data on a regular basis.

Magento eCommerce Maintenance: Improve Webshop Speed & Performance

Clear Magento cache and MySQL log tables, perform regular Magento maintenance on IIS web servers for better performance. Magento Community Edition is a very popular ecommerce and webshop solution. And very bloated as we all know. Anywhere you run your Magento webshop, it’s important to perform maintenance. Carrying out maintenance on a regular basis optimizes Magento performance.

Two of such important Magento maintenance tasks are clearing out and emptying the MySQL database cache and log tables (such as log_visitor, log_url), and Magento’s file system cache directories in /var. Most scripts and solutions out there are for Linux- and Unix webservers only. I decided to modify a Magento maintenance script, to run on Windows Server and IIS too.

For MySQL database optimization, it utilizes my MySQLi multi_query statement to optimize all MySQL tables in one statement.

Ask your hosting provider to schedule this script as a Windows Server scheduled task, for instance once a day, and you’ll notice a speed improvement of your Magento webshop. Next, add support for WinCache and your Magento webshop is very, very fast. Even on IIS!

magento

Magento. Photo credit: wmcclure333 via Flickr

Clear magento cache and log tables

Disclamer: this script is provided “AS-IS” and should not be put into production without testing! Save the following PHP code as maintenance.php and upload it to your web-root.

<?php
/**
 * maintenance.php:
 * IIS Magento Maintenance Script
 * 
 * Run this script to optimize your Magento ecommerce webshop's MySQL 
 * database, by performing regular maintenance. For instance, 
 * schedule a Windows scheduled task to run 
 * www.example.com/maintenance.php?clean=log and 
 * www.example.com/maintenance.php?clean=var on a daily basis
 * 
 * Other versions exist @
 * http://www.crucialwebhost.com/kb/magneto-log-and-cache-maintenance-script/
 * http://www.magentocommerce.com/wiki/groups/227/maintenance_script
 * 
 * Modified 2014-07-25, Jan Reilink
 *  Converted ext/mysql statements to mysqli
 *  Added mysqli->multi_query to optimize all tables in one query
 * Modified 2014-07-01, Jan Reilink
 *  Fix for Windows IIS web servers, on which exec('rm -rf '.$dir) 
 *  doesn't function
 * 
 */

if(isset($_GET['clean'])) {
    switch($_GET['clean']) {
        case 'log':
            // perform maintenance on MySQL database log tables
            clean_log_tables();
        break;
        case 'var':
            // Clears Magento cache in var directory
            clean_var_directory();
        break;
        default:
            default_action();
        break;
    }
}
else {
    default_action();
}

function clean_log_tables() {
    $xml = simplexml_load_file('./app/etc/local.xml', NULL, LIBXML_NOCDATA);
    
    if(is_object($xml)) {
        $db['host'] = $xml->global->resources->default_setup->connection->host;
        $db['name'] = $xml->global->resources->default_setup->connection->dbname;
        $db['user'] = $xml->global->resources->default_setup->connection->username;
        $db['pass'] = $xml->global->resources->default_setup->connection->password;
        $db['pref'] = $xml->global->resources->db->table_prefix;
        
        // the following MySQL database tables will be trucated (cleared) 
        // during maintenance
        $tables = array(
            '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'
        );
        
        $mysqli = new mysqli($db['host'], $db['user'], $db['pass'], $db['name']);
        if($mysqli->connect_error) {
            die('Connect Error (' . $mysqli->connect_errno . ') '
                . $mysqli->connect_error);
        }
        
        foreach($tables as $table) {
            @mysqli->query('TRUNCATE `'.$db['pref'].$table.'`');
        }

    $query = "SELECT CONCAT('OPTIMIZE TABLE ',
        GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') 
        INTO @optimizecmd FROM information_schema.tables
        WHERE table_schema=database(); 
        PREPARE s1 FROM @optimizecmd; EXECUTE s1;";
    $mysqli->multi_query($query);
    $mysqli->close();
        
    } else {
        exit('Unable to load local.xml file');
    }
}

function clean_var_directory() {
   // the following website directories are emptied or removed
    $dirs = array(
        'downloader/.cache/',
        'downloader/pearlib/cache/*',
        'downloader/pearlib/download/*',
        'media/css/',
        'media/css_secure/',
        'media/import/',
        'media/js/',
        'var/cache/',
        'var/locks/',
        'var/log/',
        'var/report/',
        'var/session/',
        'var/tmp/'
    );
    
    foreach($dirs as $dir) {
        delete_files($dir);
    }
}

function delete_files($target) {
    if(is_dir($target)) {
        $files = glob($target . '*', GLOB_MARK);
        
        foreach($files as $file)
        {
            delete_files($file);      
        }
      
        rmdir($target);
    }
    elseif(is_file($target))
    {
        unlink($target);  
    }
}

function default_action() {
    // Just return an empty 403 Forbidden response
    return http_response_code(403);
}
?>

Magento Maintenance Schedule URI’s

To perform automatic maintenance of your Magento webshop, to clear its cache, schedule the following URI’s:

  • http://www.example.com/maintenance.php?clean=log
  • http://www.example.com/maintenance.php?clean=var
This may interest you:   Create your own PHP based Origin Pull CDN

for which you can use wget or curl.

This Magento maintenance script is tested with Magento 1.4.2.0 and Magento 1.7.0.2 without problems.