Need to daily Optimize

rontgoff

I have written a code to optimize your entire Mysql db. no programing needed. You just put the code in, and it reads your databases and tables then flushes and optimizes them. If their is interest just reply here and place the code. I just don't want to put it up if no one cares.

ultraslacker

Is it php or a cronjob that uses myisamchk?

rontgoff

it is php code run under cronjob. it works great

ultraslacker

Please post it - I'm curious as to what it looks like.

rontgoff

$link = mysql_connect ("localhost", "", "")
				        		  or die ("Could not connect");


$db_list = mysql_list_dbs($link);


while ($row = mysql_fetch_object($db_list)) {
       $db = $row->Database;
    print "<b>$db</b><br>";

$tables = mysql_list_tables($db); 

while (list($table_name) = mysql_fetch_array($tables)) { 


   print "<b>Table:</b> $table_name<br>";
   print "Optimizing Table...";
   $sql = mysql_query("OPTIMIZE TABLE $table_name"); 
   print "$table_name has been Optimized<br>";
    print "Flushing Table...";
   $sql2 = mysql_query("FLUSH TABLE '$table_name"); 
   print "$table_name has been Flushed<br><br>";	

}
print "<br>";
print "<br>";

Let me know what you think.

ultraslacker

Great - helpful for people who do not have access to the command line. IIRC, this is analgous to myisamchk -o and mysqladmin flush-tables

rontgoff

I know it has speed things up a bit for me. And with the optimize feature it clears up my overhead making my system running at optimum speed. Thanks for checking it out.

ainars

I didn't use this script yet.

I have many large tables, one of them with 160 000 records (103 M😎. When I use optimize command just for 1 table MySQL is not available at all for some 1-2 minutes. Connection fails. Is this normal? Is it safe to run this script if I have many large tables?

scrupul0us

just optimize when traffic is lowest... e.g 3am or w/e suits your traffic pattern