Recent Posts
Sponsors
 |
Optimize Database Tables in MySQLDawn Rossi, 10-31-2007 |
MySQL tables and indexes have a tendency to develop fragmentation.
Fragmented blocks are sections inside your MySQL raw data and index files, that have been allocated but are not currently used.
Any MySQL table that gets hit with lots of INSERT and DELETE queries, is going to develop fragmentation. And fragmentation is bad because it eats up your disk space and slows down database performance.
Fortunately, MySQL has a built-in operation called OPTIMIZE TABLE designed to get rid of all fragmented blocks, free up unused space and make your database run faster.
I was recently able to take a machine from 88% hard disk usage, down to 32% (!) by running OPTIMIZE TABLE on all tables and databases.
MySQL doesn't allow you to run a single command that would optimize all tables across all databases, which is where the script below comes in handy.
Run it once a week. The script will lookup all tables with fragmented blocks and MySQL optimize them.
set_time_limit( 100 );
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;
//Connection variables :
$h = 'localhost';
$u = 'root';
$p = 'password';
$dummy_db = 'mysql';
$db_link = mysql_connect($h,$u,$p);
$res = mysql_db_query($dummy_db, 'SHOW DATABASES', $db_link) or die('Could not connect: ' . mysql_error());
echo 'Found '. mysql_num_rows( $res ) . ' databases' . "n";
$dbs = array();
while ( $rec = mysql_fetch_array($res) )
{
$dbs [] = $rec [0];
}
foreach ( $dbs as $db_name )
{
$res = mysql_db_query($dummy_db, "SHOW TABLE STATUS FROM `" . $db_name . "`", $db_link) or die('Query : ' . mysql_error());
$to_optimize = array();
while ( $rec = mysql_fetch_array($res) )
{
if ( $rec['Data_free'] > 0 )
{
$to_optimize [] = $rec['Name'];
echo $rec['Name'] . ' need to be optimized' . "n";
}
}
if ( count ( $to_optimize ) > 0 )
{
foreach ( $to_optimize as $tbl )
{
mysql_db_query($db_name, "OPTIMIZE TABLE `" . $tbl ."`", $db_link );
}
}
}
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 6);
echo 'Optimization completed. Total run time: ' . $total_time . ' secs' . "nn";
Fragmented blocks are sections inside your MySQL raw data and index files, that have been allocated but are not currently used.
Any MySQL table that gets hit with lots of INSERT and DELETE queries, is going to develop fragmentation. And fragmentation is bad because it eats up your disk space and slows down database performance.
Fortunately, MySQL has a built-in operation called OPTIMIZE TABLE designed to get rid of all fragmented blocks, free up unused space and make your database run faster.
I was recently able to take a machine from 88% hard disk usage, down to 32% (!) by running OPTIMIZE TABLE on all tables and databases.
MySQL doesn't allow you to run a single command that would optimize all tables across all databases, which is where the script below comes in handy.
Run it once a week. The script will lookup all tables with fragmented blocks and MySQL optimize them.
set_time_limit( 100 );
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;
//Connection variables :
$h = 'localhost';
$u = 'root';
$p = 'password';
$dummy_db = 'mysql';
$db_link = mysql_connect($h,$u,$p);
$res = mysql_db_query($dummy_db, 'SHOW DATABASES', $db_link) or die('Could not connect: ' . mysql_error());
echo 'Found '. mysql_num_rows( $res ) . ' databases' . "n";
$dbs = array();
while ( $rec = mysql_fetch_array($res) )
{
$dbs [] = $rec [0];
}
foreach ( $dbs as $db_name )
{
$res = mysql_db_query($dummy_db, "SHOW TABLE STATUS FROM `" . $db_name . "`", $db_link) or die('Query : ' . mysql_error());
$to_optimize = array();
while ( $rec = mysql_fetch_array($res) )
{
if ( $rec['Data_free'] > 0 )
{
$to_optimize [] = $rec['Name'];
echo $rec['Name'] . ' need to be optimized' . "n";
}
}
if ( count ( $to_optimize ) > 0 )
{
foreach ( $to_optimize as $tbl )
{
mysql_db_query($db_name, "OPTIMIZE TABLE `" . $tbl ."`", $db_link );
}
}
}
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 6);
echo 'Optimization completed. Total run time: ' . $total_time . ' secs' . "nn";
 |
Kevin, 10-31-2007 |
You can just add "WHERE Data_free > 0" to your SHOW TABLE STATUS query to avoid checking this condition in the foreach loop.
 |
Dawn Rossi, 10-31-2007 |
Thanks Kevin! I just tried it and it does work as you stated. Thanks for the tip
 |
Ravi Kumar. R, 02-06-2008 |
Hi, i'm getting the following error when i ran above script.
Script line: 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set_time_limit( 100 )' at line 1
Please help me out.
Thanks
Script line: 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set_time_limit( 100 )' at line 1
Please help me out.
Thanks
|
|
Dawn Rossi, 02-06-2008 |
Ravi -
Are you pasting this script into MySQL ?
This is a PHP Script
Are you pasting this script into MySQL ?
This is a PHP Script
|
|
Ravi Kumar. R, 02-06-2008 |
yes thanks Dawn. please help me to run the script in mysql.
Thanks
Thanks
|
|
Dawn Rossi, 02-06-2008 |
Ravi -
This is not a MySQL Script. It's a PHP script.
If you have PHP installed on your server, create a new PHP script and paste this code into the script. Update the username/password to match your database username/password and run the php script.
If you need any further help, please post a new project and we'll be happy to help. Can probably get it done for you in 1 hour or less.
This is not a MySQL Script. It's a PHP script.
If you have PHP installed on your server, create a new PHP script and paste this code into the script. Update the username/password to match your database username/password and run the php script.
If you need any further help, please post a new project and we'll be happy to help. Can probably get it done for you in 1 hour or less.
|
|
Ravi Kumar. R, 02-06-2008 |
Can you please help me as i am unable to generate the script in PHP as i am little week at PHP stuff.
|
|
Dawn Rossi, 02-06-2008 |
Ravi -
I'm sorry but if you need any personal hands-on assistance, please purchase an hour block from the link I pasted and we'll get right on it.
I'm sorry but if you need any personal hands-on assistance, please purchase an hour block from the link I pasted and we'll get right on it.
 |
Ron Jones, 03-12-2008 |
If you've got access to the server, just set up a cron job similar to the following (this is what I use to optimize the tables once every 24 hours).
2 6 * * * mysqlcheck -Aao --auto-repair -u root --password=<mysql-root-password> >> /dev/null 2>&1
HTH
2 6 * * * mysqlcheck -Aao --auto-repair -u root --password=<mysql-root-password> >> /dev/null 2>&1
HTH
|
|
Subscribe Now to receive new posts via Email as soon as they come out.
Comments
Post your comments
