Re: Defragmentation of MySQL tables, how many times have I to do it?
Am 28.03.2013 12:09, schrieb Antonio Fernández Pérez: Hi everybody, Once I have done the defragmentation of MySQL tables, mysql-tunner.pl suggests me do it again. Is this correct? I think that the idea is that in the result of the script there are not fragmented tables ... Any ideas? depends on how large your tables and how much fragmented tey are if you delete a record from a table it would be counted as fragmented - so what, if you permenantly run optimize table you produce more load as any frgamentation and additionally you trash the whole time the query-cache signature.asc Description: OpenPGP digital signature
Re: Defragmentation of MySQL tables, how many times have I to do it?
Hi Reindl, Thanks for your reply. So, is it not necessary (not recommended) to defragment tables if I have a lot of write operations (writing or deleting)? Best regards, Antonio.
Re: Defragmentation of MySQL tables, how many times have I to do it?
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;
}
signature.asc
Description: OpenPGP digital signature
Re: Defragmentation of MySQL tables, how many times have I to do it?
Ok. Thank you very much. Best regards, Antonio.
Re: Defragmentation of MySQL tables, how many times have I to do it?
Hello Manuel, Exactly. My objective doing performance MySQL it isn't to free disk space (it isn't the problem). I would like to get a good performance using cache and setting good values to configuration variables. I will defrag tables once per month. Best regards, Antonio.
Re: Defragmentation of MySQL tables, how many times have I to do it?
Hello Antonio, As Reindl said, it is recommended but it shouldnt be done just everyday if your tables are quite big and you use query cache. If you do have lot of writes and deletes (depending on what you consider a lot), your table will indeed have lot of 'blank rows' which eat disk space. Optimizing tables if good for both: saving disk space and reading faster from them. However, remember that optimize locks your table so it can affect your writes and create a big overhead in your server (IO). If I were your I would benchmark how fast my operations are after a...lets say monthly optimize and then decide. I am assuming you are not optimizing because of disk space issues. Manuel 2013/3/28, Antonio Fernández Pérez antoniofernan...@fabergames.com: Ok. Thank you very much. Best regards, Antonio. -- Manuel Aróstegui Systems Team tuenti.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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;
}
Re: Defragmentation of MySQL tables, how many times have I to do it?
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.rei...@thelounge.net
mailto: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;
}
signature.asc
Description: OpenPGP digital signature
RE: Defragmentation of MySQL tables, how many times have I to do it?
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
Re: Defragmentation of MySQL tables, how many times have I to do it?
Am 28.03.2013 19:43, schrieb Rick James: I have never traced a performance issue in InnoDB to the need for OPTIMIZE. but with files_per_table you can get back GIGABTYES of disk-space depending on your backups this may be relevant The Query Cache is irrelevant to this discussion it is NOT because look at the server-stats after a optimize table hint: cached results for any of the tables are thrown away signature.asc Description: OpenPGP digital signature
