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