The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Cron to run every day to optimize mysql?

Discussion in 'Workarounds and Optimization' started by tiff2342, Aug 16, 2012.

  1. tiff2342

    tiff2342 Well-Known Member

    Joined:
    Apr 20, 2012
    Messages:
    140
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Is there a cron script i can run every day to flush the fragmented tables and improve mysql performance?
     
  2. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    Yes, create a db_clean.php and set it up as a cronjob....

    Code:
    <?
    include '../incl/config.php'; //// This should contain your DB connections
    	// Set font
    	echo "<font size=\"-1\" face=\"arial\">";
    	// Statement to select the databases
    	$db_select = 'SHOW DATABASES';
    	// Query mySQL for the results
    	$db_result = mysql_query($db_select);
    		// Loop through all the databases
    		while ($db_row = mysql_fetch_array($db_result)) {
    			// Select currently looped database and continue only if sucessful (have permission to)
    			If (mysql_select_db($db_row[0])) {
    				// Echo database name
    				echo "<br><b>";
    				echo $db_row[0];
    				echo "</b><br>";
    				// Statement to select the tables in the currently looped database
    				$tbl_status = 'SHOW TABLE STATUS FROM ' . $db_row[0];
    				// Query mySQL for the results
    				$tbl_result = mysql_query($tbl_status);
    					// Check to see if any tables exist within database
    					If(mysql_num_rows($tbl_result)) {
    						// Loop through all the tables
    						while ($tbl_row = mysql_fetch_array($tbl_result)) {
    							// Statement to optimize table
    							$opt_table = 'OPTIMIZE TABLE ' . $tbl_row[0];
    							// Query mySQL to optimize currently looped table
    						    $opt_result = mysql_query($opt_table);
    							// Echo table name
    							echo "&nbsp;&nbsp;<i>";
    							echo $tbl_row[0];
    							echo "</i><br>";
    						} // End table while loop 
    					} else {
    						// Alert that there are no tables within database
    							echo "&nbsp;&nbsp;<i>No Tables</i><br>";
    					} // End table existance if statement
    			} // End database if statement
    		} // End database while loop
    	// Alert them that operation was successful
    	echo "<br><br><b>Above tables successfully optimized!</b>";
    	// End font
    	echo "</font>";
    ?>
    
     
  3. tiff2342

    tiff2342 Well-Known Member

    Joined:
    Apr 20, 2012
    Messages:
    140
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    does this do all the databases on the shared server and will remove fragmented cache?
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    The script is very carefully commented for each line provided. Did you read the script provided to see what it is doing? If you read each comment above each command, you can see what it does specifically.
     
  5. dmacleo

    dmacleo Active Member

    Joined:
    Jan 28, 2012
    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    thats what made me also wonder if it did all.

    would a cron such as
    also do this?
    seems the script is much more involved.
     
    #5 dmacleo, Aug 18, 2012
    Last edited: Aug 18, 2012
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    The script will only check tables that can be optimized and exist. It's far more detailed and would likely take less processing power than running through them all without checking.

    It's up to you what you want to do, but it is definitely advised to always read the script comments to understand a script, since that's the purpose of having detailed comments.
     
  7. dmacleo

    dmacleo Active Member

    Joined:
    Jan 28, 2012
    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    for the record I wasn't denigrating the script, it looks powerful.
    just wanted to clarify if it did all also.
     
  8. syndicated

    syndicated Member

    Joined:
    Jun 1, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Looking over the script all it seems to do is optimize only

    // Statement to optimize table
    $opt_table = 'OPTIMIZE TABLE ' . $tbl_row[0];

    That seems to be the only command on SQL it actually does other then search for and loop through dbases
     
  9. howzit

    howzit Member

    Joined:
    Feb 20, 2012
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Howzit! Free Advertising Classifieds
    cPanel Access Level:
    Website Owner
    dmacleo, in short...
    Setting up this script as a cronjob to run at a specific time each day will optimize all db's on your server.
     
  10. syndicated

    syndicated Member

    Joined:
    Jun 1, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    The script will optimize, the command line will also repair and optimize.
    One is PHP the other is Shell
    One has customized reporting the other is default

    When I run the command line it skips all optimized tables and only does ones needing it, goes very fast but gives me a list of all the skipped ones + ones that are done.
     
  11. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    How would I set this config.php file and where to put it?

    Where is the script usually put on the server ... in root or elsewhere?
     
Loading...

Share This Page