Magento maintenance script for IIS

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 records 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 these important Magento maintenance tasks are clearing out and emptying the MySQL database cache and log tables (such as log_visitor and log_url), and Magento's file system cache directories in /var. Most scripts and solutions out there are for Linux and Unix web servers only. I decided to modify a Magento maintenance script to run on Windows Server and IIS too.

To optimize MySQL databases, 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!

Moving on to Magento 2? Read about the always_populate_raw_post_data setting in PHP 5.6 & Magento 2.0.

Clear Magento cache and log tables

Disclaimer: 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 truncated (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 URIs

To perform automatic maintenance of your Magento webshop and clear its cache, schedule the following URIs:

• http://www.example.com/maintenance.php?clean=log
• http://www.example.com/maintenance.php?clean=var

For this 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.

Prefer to optimize your MySQL tables from the command line? See how to check, repair and optimize MySQL tables with mysqlcheck.