Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
CLI script to optimize Sugar mysql tables. Have a look at https://gist.github.com/esimonetti/94cd0c7d966684d7650e471c153d7e9d for a more optimised version according to https://www.percona.com/blog/2010/12/09/mysql-optimize-tables-innodb-stop/
<?php
// Enrico Simonetti
// enricosimonetti.com
//
// 2018-09-20 on Sugar 8.0.0
//
// CLI script to optimize Sugar mysql tables
ini_set('memory_limit', '64M');
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();
}
$tables = $db->getTablesArray();
asort($tables);
foreach ($tables as $table) {
// store initial size
$query = 'select ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size from information_schema.TABLES where TABLE_NAME = ?';
$stmt = $db->getConnection()->executeQuery($query, array($table));
if ($row = $stmt->fetch()) {
$results[$table]['initial'] = $row['size'];
$totals['initial'] += $row['size'];
}
$query = 'OPTIMIZE TABLE ' . $table;
echo 'Running query ' . $query . '... ';
$stmt = $db->getConnection()->executeQuery($query);
echo 'done.';
// check final size
$query = 'select ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size from information_schema.TABLES where TABLE_NAME = ?';
$stmt = $db->getConnection()->executeQuery($query, array($table));
if ($row = $stmt->fetch()) {
$results[$table]['final'] = $row['size'];
$totals['final'] += $row['size'];
}
echo ' Initial est. size: ' . $results[$table]['initial'] . ' MB. Current est size: ' . $results[$table]['final'] . ' MB' . PHP_EOL;
}
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.