db_maintenance.module in DB Maintenance 5

Optimizes database tables during cron runs.

@author David Kent Norman @link http://deekayen.net/

@todo

• some sort of backup dump saver/emailer/ftp/etc
• is there a postrgres OPTIMIZE equivalent?
• option to not execute on EVERY cron run
• check to make sure selected tables still exist before executing OPTIMIZE

File

db_maintenance.module

<?php

/**
 * @file
 * Optimizes database tables during cron runs.
 *
 * @author David Kent Norman
 * @link http://deekayen.net/
 *
 * @todo
 * - some sort of backup dump saver/emailer/ftp/etc
 * - is there a postrgres OPTIMIZE equivalent?
 * - option to not execute on EVERY cron run
 * - check to make sure selected tables still exist before executing OPTIMIZE
 */

/**
 * Implementation of hook_help().
 *
 * @param $section string
 * @return string
 */
function db_maintenance_help($section = 'admin/help#db_maintenance') {
  $output = '';
  switch ($section) {
    case 'admin/help#db_maintenance':
      $output = t('DB maintenance performs an OPTIMIZE TABLE query on selected tables. For MyISAM tables,
                  OPTIMIZE TABLE repairs a table if it has deleted or split rows, sorts table indexes,
                  and updates table statistics. For BDB and InnoDB, OPTIMIZE rebuilds the table. Note, MySQL
                  locks tables during the time OPTIMIZE TABLE is running. OPTIMIZE works best on tables with
                  large deletions (e.g. cache or watchdog), however MySQL will reuse old record positions,
                  therefore in most setups, OPTIMIZE TABLE is unnecessary unless you just like defragmenting.
                  The Overhead column in phpMyAdmin\'s database view is the most common way to determine the
                  need of an OPTIMIZE TABLE query. It essentially shows the amount of disk space you would
                  recover by running an optimize/defragmentation query.');
      break;
    case 'admin/settings/db_maintenance':
      $output = t('Executes an OPTIMIZE TABLE query on MyISAM, InnoDB, or BerkeleyDB database tables');
      break;
  }
  return $output;
}

/**
 * Implementation of hook_menu().
 *
 * @param $may_cache boolean
 * @return array
 */
function db_maintenance_menu($may_cache) {
  $items = array();
  if ($may_cache) {
    $items[] = array(
      'path' => 'admin/settings/db_maintenance',
      'title' => t('DB maintenance'),
      'description' => t('Executes a cron-based OPTIMIZE TABLE query on MyISAM, InnoDB, or BerkeleyDB database tables.'),
      'callback' => 'drupal_get_form',
      'callback arguments' => array(
        'db_maintenance_admin_settings',
      ),
      'access' => user_access('administer site configuration'),
      'type' => MENU_NORMAL_ITEM,
    );
    $items[] = array(
      'path' => 'db_maintenance/optimize',
      'title' => t('Optimize tables'),
      'callback' => 'db_maintenance_optimize_tables_page',
      'access' => user_access('administer site configuration'),
      'type' => MENU_CALLBACK,
    );
  }
  return $items;
}

/**
 * Callback page for manually optimizing tables.
 *
 */
function db_maintenance_optimize_tables_page() {
  db_maintenance_optimize_tables();
  drupal_set_message(t('Database tables optimized'));
  drupal_goto('admin/settings/db_maintenance');
}

/**
 * Get a list of all the tables in a database.
 *
 * @param $db The name of the database connection to query for tables.
 * @return An array representing the tables in the specified database.
 */
function _db_maintenance_list_mysql_tables() {
  $table_names = array();

  // Set the database to query.
  $previous = db_set_active($db);
  $result = db_query('SHOW TABLES');

  // Return to the previously set database.
  db_set_active($previous);
  while ($table_name = db_fetch_array($result)) {
    $table_name = current($table_name);
    $table_names[$table_name] = $table_name;
  }
  return $table_names;
}

/**
 * Implementation of hook_cron().
 *
 */
function db_maintenance_cron() {
  $last_run = variable_get('db_maintenance_cron_last', 0);
  $interval = time() - variable_get('db_maintenance_cron_frequency', 86400);

  // Only run cron if enough time has elapsed.
  if ($interval > $last_run) {
    db_maintenance_optimize_tables();
  }
}

/**
 * Perform the maintenance.
 *
 */
function db_maintenance_optimize_tables() {
  global $db_url;

  // Set the databases array if not already set in $db_url.
  if (is_array($db_url)) {
    $databases = $db_url;
  }
  else {
    $databases['default'] = $db_url;
  }

  // Loop through each database optimizing any selected tables.
  foreach ($databases as $db => $connection) {
    $config_tables = variable_get('db_maintenance_table_list_' . $db, NULL);

    // Only proceed if tables are selected for this database.
    if (is_array($config_tables) && sizeof($config_tables) > 0) {
      $db_name = $db == 'default' ? 'Drupal' : $db;

      // Only proceed if it's a MySQL database.
      if (strpos($connection, 'mysql://') !== 0 && strpos($connection, 'mysqli://') !== 0) {
        watchdog('db_maintenance', t('Tables are configured to be optimized, but the !db database is not using MySQL', array(
          '!db' => $db_name,
        )));
      }
      else {
        $tables = implode(', ', $config_tables);

        // Set the database to query.
        $previous = db_set_active($db);
        db_query('OPTIMIZE TABLE %s', $tables);

        // Return to the previously set database.
        db_set_active($previous);
        if (variable_get('db_maintenance_log', 0)) {
          watchdog('db_maintenance', t('Optimized tables in !db database: ', array(
            '!db' => $db_name,
          )) . $tables);
        }
      }
    }
  }
  variable_set('db_maintenance_cron_last', time());
}

/**
 * Administration settings
 *
 * options: log each optimization
 *          multi-select list of tables to optimize
 *
 * @return array
 */
function db_maintenance_admin_settings() {
  global $db_url;
  $form['db_maintenance_log'] = array(
    '#type' => 'checkbox',
    '#title' => 'Log OPTIMIZE queries',
    '#default_value' => variable_get('db_maintenance_log', 0),
    '#description' => t('If enabled, a watchdog entry will be made each time tables are optimized, containing information which tables were involved.'),
  );
  $options = array(
    3600 => t('Hourly'),
    86400 => t('Daily'),
    604800 => t('Weekly'),
    2592000 => t('Monthly'),
  );
  $form['db_maintenance_cron_frequency'] = array(
    '#type' => 'select',
    '#title' => t('Optimize tables'),
    '#options' => $options,
    '#default_value' => variable_get('db_maintenance_cron_frequency', 86400),
    '#description' => t('Select how often database tables should be optimized.') . ' ' . l(t('Optimize now.'), 'db_maintenance/optimize'),
  );

  // Set the databases array if not already set in $db_url.
  if (is_array($db_url)) {
    $databases = $db_url;
  }
  else {
    $databases['default'] = $db_url;
  }

  // Loop through each database and list the possible tables to optimize.
  foreach ($databases as $db => $connection) {
    $options = _db_maintenance_list_mysql_tables($db);
    $form['db_maintenance_table_list_' . $db] = array(
      '#type' => 'select',
      '#title' => t('Tables in the !db database', array(
        '!db' => $db == 'default' ? 'Drupal' : $db,
      )),
      '#options' => $options,
      '#default_value' => variable_get('db_maintenance_table_list_' . $db, ''),
      '#description' => t('Selected tables will be optimized during cron runs.'),
      '#multiple' => true,
      '#attributes' => array(
        'size' => count($options),
      ),
    );
  }
  return system_settings_form($form);
}

/**
 * Implementation of hook_block().
 *
 * @param string $op
 * @param integer $delta
 * @return string or array
 */
function db_maintenance_block($op = 'list', $delta = 0, $edit = array()) {
  switch ($op) {
    case 'list':
      $blocks[0]['info'] = 'Database size';
      return $blocks;
      break;
    case 'configure':
    case 'save':
      break;
    case 'view':
      if (user_access('access content')) {
        return array(
          'content' => theme('db_maintenance_block'),
        );
      }
      break;
  }
}

/**
 * MySQL size status by pcs305
 */
function theme_db_maintenance_block() {

  // Database size = table size + index size:
  $rows = db_query("SHOW TABLE STATUS");
  $dbssize = 0;
  while ($row = mysql_fetch_array($rows)) {
    $dbssize += $row['Data_length'] + $row['Index_length'];
  }
  $dbssize = db_maintenance_db_size_info($dbssize);
  return "{$dbssize['size']} {$dbssize['type']}";
}

/**
 * Turn bytes into something more readable like KB, MB, etc.
 *
 * @return array
 */
function db_maintenance_db_size_info($dbsize) {
  $bytes = array(
    'KB',
    'KB',
    'MB',
    'GB',
    'TB',
  );
  if ($dbsize < 1024) {
    $dbsize = 1;
  }
  for ($i = 0; $dbsize > 1024; $i++) {
    $dbsize /= 1024;
  }
  $db_size_info['size'] = ceil($dbsize);
  $db_size_info['type'] = $bytes[$i];
  return $db_size_info;
}

/**
 * Implementation of hook_simpletest().
 */
function db_maintenance_simpletest() {

  // Scan through mymodule/tests directory for any .test files to tell SimpleTest module.
  $tests = file_scan_directory(drupal_get_path('module', 'db_maintenance') . '/tests', '\\.test');
  return array_keys($tests);
}

Functions

Name	Description
db_maintenance_admin_settings	Administration settings
db_maintenance_block	Implementation of hook_block().
db_maintenance_cron	Implementation of hook_cron().
db_maintenance_db_size_info	Turn bytes into something more readable like KB, MB, etc.
db_maintenance_help	Implementation of hook_help().
db_maintenance_menu	Implementation of hook_menu().
db_maintenance_optimize_tables	Perform the maintenance.
db_maintenance_optimize_tables_page	Callback page for manually optimizing tables.
db_maintenance_simpletest	Implementation of hook_simpletest().
theme_db_maintenance_block	MySQL size status by pcs305
_db_maintenance_list_mysql_tables	Get a list of all the tables in a database.