List:General Discussion« Previous MessageNext Message »
From:Rick James Date:March 28 2013 6:43pm
Subject:RE: Defragmentation of MySQL tables, how many times have I to do it?
View as plain text  
How often to OPTIMIZE?

The Short answer:  Never.

The Long answer:  A _few_ tables _may_ need OPTIMIZE _sometimes_.

One test:  Is the "free" space (according to SHOW TABLE STATUS or equivalent
information_schema stuff) is > 10%, then OPTIMIZE.  Maybe.  However...  That math
works OK for MyISAM, but is unreliable for InnoDB because (1) things are freed in big
chunks, thereby showing large free space, and (2) hiding small chunks that don't yet
showing in the free space.  That is, the number may be too high or too low.

BTrees are inherently 'stable'.  InnoDB merges adjacent BTree blocks.  These comments lead
to the conclusion that there is rarely anything to gain by OPTIMIZEing an InnoDB table or
MyISAM indexes.

MyISAM data, after lots of DELETEs/UPDATEs/INSERTs of _variable_ length rows ('Dynamic')
can cause fragmentation of individual rows.  Normally a row is contiguous in the .MYD
file; but it could be in multiple pieces if there were small free spots when it was
inserted.  So..., if there is a lot of churn, it may be useful to OPTIMIZE.  However, I
would suggest only once a month.  This is perhaps the only case I have found for
OPTIMIZEing MyISAM for performance.

With PARTITIONing, do not attempt to OPTIMIZE a single PARTITION; it will reOPTIMIZE the
entire table (at least in the InnoDB case).  Instead, use ALTER TABLE..REORGANIZE.. on
one partition into itself.

I have never traced a performance issue in InnoDB to the need for OPTIMIZE.

The Query Cache is irrelevant to this discussion.



> -----Original Message-----
> From: Bheemsen Aitha [mailto:pgbn73@stripped]
> Sent: Thursday, March 28, 2013 8:59 AM
> To: Reindl Harald
> Cc: mysql@stripped
> Subject: Re: Defragmentation of MySQL tables, how many times have I to
> do it?
> 
> 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