List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:March 28 2013 5:32pm
Subject:Re: Defragmentation of MySQL tables, how many times have I to do
it?
View as plain text  
optimizeall() is simply called from a cron script

the mysql-wrapper does not exist in public but you get the table
overhead with  "SHOW TABLE STATUS" as you can see below

Am 28.03.2013 16:59, schrieb Bheemsen Aitha:
> 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?
> 
> 
> On Mar 28, 2013 4:43 AM, "Reindl Harald" <h.reindl@stripped
> <mailto: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;
>      }


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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