Re: Defragmentation of MySQL tables, how many times have I to do it?

[Reindl Harald]

Am 28.03.2013 12:28, schrieb Antonio Fernández Pérez:
> So, is it not necessary (not recommended) to defragment tables if I have a
> lot of write operations (writing or deleting)?

it is recommended but not permanently and not blindly

i use a daily cronjob which runs optimize table on tables with >= 50 KB overhead
based on this methods of a internal mysql-wrapper-class


 public function optimizeall($action, $returntables, $flush, $min_overhead, 
$only_myisam=true)
 {
  $output = '';
  $dblist = $this->showdatabases();
  foreach($dblist as $akt)
  {
   if($akt != 'information_schema' && $akt != 'performance_schema')
   {
    if(function_exists('apache_reset_timeout'))
    {
     apache_reset_timeout();
    }
    $output .= $this->optimizetables($akt, $action, $returntables, array(), 
$min_overhead, $only_myisam);
    if($flush)
    {
     echo $output;
     @ob_end_flush();
     flush();
     $output = '';
    }
   }
  }
  return $output;
 }


 public function optimizetables($database, $action='optimize', $returntables=0, 
array $tablelist=array(),
$min_overhead=0, $only_myisam=true)
 {
  global $rh_php_sapi_name;
  $first  = false;
  $output = '';
  $sql    = '';
  if(empty($database))
  {
   $database = $this->parent->db;
  }
  if(empty($tablelist))
  {
   $tablelist = $this->showtables($database);
  }
  if(!empty($tablelist))
  {
   foreach($tablelist as $akt)
   {
    $ignore = false;
    if($only_myisam)
    {
     $this->parent->select_db($database);
     $type_result = $this->parent->query('SHOW TABLE STATUS LIKE \'' . $akt . 
'\'', 1, 0);
     $type_row = $this->parent->fetch_assoc($type_result);
     if(strtolower($type_row['Engine']) == 'innodb')
     {
      $ignore = true;
     }
    }
    if(!$ignore && ($min_overhead == 0 || $this->get_table_overhead($database, 
$akt) >= $min_overhead))
    {
     if($first)
     {
      $sql .= ', ';
     }
     else
     {
      $sql = $action . ' table ';
     }
     $sql .= '`' . $database . '`.`' . $akt . '`';
     $first = true;
     if($returntables)
     {
      $output .= $database . '.' . $akt;
      if($rh_php_sapi_name != 'cli')
      {
       $output .= '<br />';
      }
      $output .= MY_LE;
     }
    }
   }
   if($action != 'all')
   {
    if(!empty($sql))
    {
     $result = $this->parent->query($sql);
    }
   }
   else
   {
    if(!empty($sql))
    {
     $zsp = $sql;
     $result = $this->parent->query(str_replace('all', 'check', $zsp), 1, 0);
     $result = $this->parent->query(str_replace('all', 'repair', $zsp), 1, 0);
     $result = $this->parent->query(str_replace('all', 'optimize', $zsp), 1, 0);
    }
   }
  }
  return $output;
 }

signature.asc
Description: OpenPGP digital signature