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:pgb...@motorola.com]
> Sent: Thursday, March 28, 2013 8:59 AM
> To: Reindl Harald
> Cc: mysql@lists.mysql.com
> 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.rei...@thelounge.net>
> 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;
> > }
> >
> >
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
