List:General Discussion« Previous MessageNext Message »
From:Bheemsen Aitha Date:March 28 2013 3:59pm
Subject:Re: Defragmentation of MySQL tables, how many times have I to do it?
View as plain text  
Hi Reindl,

I would like to implement your strategy of optimizing tables. Can you
please share how are running these scripts? Where does the
mysql-wrapper-class exist? And what parameters need to be passed?

Thanks
Bheem Aitha
MySQL and Oracle DBA
On Mar 28, 2013 4:43 AM, "Reindl Harald" <h.reindl@stripped> wrote:

>
>
> 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;
>  }
>
>

Thread
Defragmentation of MySQL tables, how many times have I to do it?Antonio Fernández Pérez28 Mar
  • Re: Defragmentation of MySQL tables, how many times have I to doit?Reindl Harald28 Mar
    • Re: Defragmentation of MySQL tables, how many times have I to do it?Antonio Fernández Pérez28 Mar
      • Re: Defragmentation of MySQL tables, how many times have I to doit?Reindl Harald28 Mar
        • Re: Defragmentation of MySQL tables, how many times have I to do it?Antonio Fernández Pérez28 Mar
          • Re: Defragmentation of MySQL tables, how many times have I to do it?Manuel Arostegui28 Mar
            • Re: Defragmentation of MySQL tables, how many times have I to do it?Antonio Fernández Pérez28 Mar
        • Re: Defragmentation of MySQL tables, how many times have I to do it?Bheemsen Aitha28 Mar
          • Re: Defragmentation of MySQL tables, how many times have I to doit?Reindl Harald28 Mar
          • RE: Defragmentation of MySQL tables, how many times have I to do it?Rick James28 Mar
            • Re: Defragmentation of MySQL tables, how many times have I to doit?Reindl Harald28 Mar