CLI script to optimize Sugar mysql tables, by dropping all non-primary indices, optimizing and finally re-creating indices. Script similar to https://gist.github.com/esimonetti/7c4d57d57464ce6a71973de6fe0da089 but implementing the recommendations described here https://www.percona.com/blog/2010/12/09/mysql-optimize-tables-innodb-stop/ - USE TOOT…

mysql_tables_optimize.php

<?php

// Enrico Simonetti
// enricosimonetti.com
//
// 2019-01-04 on Sugar 8.0.2
//
// CLI script to optimize Sugar mysql tables, by dropping all non-primary indices, optimizing and finally re-creating indices

function usage($error = '') {
    if (!empty($error)) print(PHP_EOL . 'Error: ' . $error . PHP_EOL);
    print('  php ' . __FILE__ . ' --instance /full/path' . PHP_EOL);
    exit(1);
}

// only allow CLI
$sapi_type = php_sapi_name();
if (substr($sapi_type, 0, 3) != 'cli') {
    die(__FILE__ . ' is CLI only.');
}

// get command line params
$o = getopt('', array('instance:'));
if (!$o) usage();

// find directory
if (!empty($o['instance']) && is_dir($o['instance'])) {
    print('Debug: Entering directory ' . $o['instance'] . PHP_EOL);
    chdir($o['instance']);
} else {
    chdir(dirname(__FILE__));
}

if (!file_exists('config.php') || !file_exists('sugar_version.php')) {
    usage('The provided directory is not a Sugar system');
}

// sugar basic setup
define('sugarEntry', true);
require_once('include/entryPoint.php');

if (extension_loaded('xdebug')) {
    echo 'Xdebug is enabled on this system. It is highly recommended to disable Xdebug on PHP CLI before running this script. Xdebug will cause unwanted slowness.'.PHP_EOL;
}

// temporarily stop xdebug, xhprof and tideways if enabled
if (function_exists('xdebug_disable')) {
    xdebug_disable();
}

if (function_exists('xhprof_disable')) {
    xhprof_disable();
    xhprof_sample_disable();
}

if (function_exists('tideways_disable')) {
    tideways_disable();
}

if (empty($current_language)) {
    $current_language = $sugar_config['default_language'];
}

$app_list_strings = return_app_list_strings_language($current_language);
$app_strings = return_application_language($current_language);
$mod_strings = return_module_language($current_language, 'Administration');

global $current_user;
$current_user = BeanFactory::getBean('Users');
$current_user->getSystemUser();

$start_time = microtime(true);

echo 'Running optimize on all database tables' . PHP_EOL;

$totals = ['initial' => 0, 'final' => 0];
$results = [];

$db = DBManagerFactory::getInstance();

if ($db->dbType != 'mysql') {
    echo 'The database in use is not MySQL, stopping.' . PHP_EOL;
    die();
}

$processedTables = array();

global $beanList;
// modules
$fullModuleList = array_merge($beanList, $app_list_strings['moduleList']);
asort($fullModuleList);

$queryDataSizeEstimation = 'select ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size from information_schema.TABLES where TABLE_NAME = ?';

foreach($fullModuleList as $module => $label) {
    $bean = BeanFactory::newBean($module);
    $table = $bean->table_name;
    // if the table exists
    if(!empty($table) && $db->tableExists($table)) {
        if (!isset($processedTables[$table])) {
            $processedTables[$table] = '';
        }

        // some tables are processed multiple times as they are considered storage for multiple beans, and I have to keep it that way or the indices won't be rebuilt properly
        if (!isset($results[$table]['initial'])) {
            // store initial size
            $stmt = $db->getConnection()->executeQuery($queryDataSizeEstimation, array($table));
            if ($row = $stmt->fetch()) {
                $results[$table]['initial'] = $row['size'];
                $totals['initial'] += $row['size'];
            }
        }

        // get current indices
        $indices = $db->get_indices($table);

        if (!empty($indices)) {
            $indicesToRemove = [];
            foreach ($indices as $idx) {
                if ($idx['type'] !== 'primary') {
                    $indicesToRemove[] = $idx;
                }
            }

            // drop all non-primary indices
            $queries = $db->dropIndexes($table, $indicesToRemove);
            $processedTables[$table] .= $queries;

            // running optimize table
            $query = 'OPTIMIZE TABLE ' . $table;
            echo 'Running query ' . $query . '... ';
            $stmt = $db->getConnection()->executeQuery($query);
            echo 'done.' . PHP_EOL;
            $processedTables[$table] .= PHP_EOL . $query . PHP_EOL;

            // repair table
            $queries = $db->repairTable($bean);        
            $processedTables[$table] .= $queries;
        }

        // check final size
        $stmt = $db->getConnection()->executeQuery($queryDataSizeEstimation, array($table));
        if ($row = $stmt->fetch()) {
            $results[$table]['final'] = $row['size'];
            $totals['final'] += $row['size'];
        }
        echo $table . PHP_EOL;
        echo ' Initial est. size: ' . $results[$table]['initial'] . ' MB. Current est size: ' . $results[$table]['final'] . ' MB' . PHP_EOL;
    }
}

print_r($processedTables);

echo PHP_EOL . 'Total initial est. size was: ' . $totals['initial'] . ' MB, total current est. size is: ' . $totals['final'] . ' MB' . PHP_EOL;

echo PHP_EOL . PHP_EOL;
print('Completed in ' . (int)(microtime(true) - $start_time) . ' seconds.' . PHP_EOL);