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 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
Read this too:   How to determine if a SQL Server backup is compressed?

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.


Did this post helped you solve a problem? Or dit you find it interesting? Support Sysadmins of the North with a direct donation via Paypal of by bank wire transfer IBAN: NL31 ABNA 0432217258 (Jan Reilink). Just $ 5,- or € 5,- is more than enough, thanks!

Support SAOTN.ORG


85 views

Leave a Reply

Your email address will not be published. Required fields are marked *