X-Cart forums - Database Auto-Optimizer

X-Cart forums (https://forum.x-cart.com/index.php)

- Dev Questions (https://forum.x-cart.com/forumdisplay.php?f=20)

- - Database Auto-Optimizer (https://forum.x-cart.com/showthread.php?t=8435)

Database Auto-Optimizer

This is a module to automatically optimize all of the tables in your database overnight through the use of a cronjob. Also performs repairs as needed.

First create a file in your Xcart root directory named: db_optimizer.php

Put the following inside that file:

Code:

<?php

##########################################################################

##                       ## Database Table Optimizer ##                 ##

##########################################################################

##                                                                      ##

## Optimize all database tables once every 24 hours through a cronjob.  ##

##########################################################################

## Version: 1.1.0 (6/8/2004)

## Last Updated: 7/13/2004



# Define Constants

define('ERROR_LOG', '/absolute-path-to-your-log-directory/db_optimization_errors.html');

define('DB_LINK', mysql_connect('server_address','sql_login','sql_password'));

define('DB_NAME', 'database_name');



# Get all tables in the database.

mysql_select_db(DB_NAME, DB_LINK);

$result = mysql_list_tables(DB_NAME);



# Begin table optimizations.

for ($i = 0; $i < mysql_num_rows($result); $i++) {

        $current_table = mysql_tablename($result, $i);

        $opt_table = mysql_query('OPTIMIZE TABLE '.$current_table,DB_LINK)

                                 or error_log("Couldn't optimize the '$current_table' table!

".

                                                           "MySQL said: ".mysql_error()."

\n",3,ERROR_LOG);

        

        if (!$opt_table && preg_match("/'(\S+)\.(MYI|MYD)/",mysql_error(), $damaged_table)) {

                # Repair broken indexes.

                mysql_query('REPAIR TABLE '.$damaged_table[1],DB_LINK)

                                 or error_log("Couldn't repair the '$current_table' table!

".

                                                           "MySQL said: ".mysql_error()."

\n",3,ERROR_LOG);

                $opt_table = null;

                # Try to optimize the table again.

                $opt_table = mysql_query('OPTIMIZE TABLE '.$current_table,DB_LINK)

                                 or error_log("Couldn't optimize the '$current_table' table after trying to repair it!

".

                                                           "MySQL said: ".mysql_error()."

\n",3,ERROR_LOG);

        }

}

?>

Next create a cronjob that runs sometime each day whenever your site has the least amount of traffic.
Use the cron command: php /absolute-path-to-this-file/db_optimizer.php

Substitute the data in the constants section of this file with your specific information. Also replace absolute-path-to's with the appropriate path.

That's all there is to it. If there are any problems, let me know! :wink:

Hi NuAlpha

Thank you for sharing this file. I've been looking for something like this for sometime. I ran the script from my browser without any problems.

Next I'll try to set up the cron file. Just need to learn a bit more about doing this.

Once again, thank you

Here is an update of the code. Noticed a potential security issue. This fix might disable the script on some people's servers so if it does and you have another solution, then just remove the condition. The condition if (empty($_SERVER['REMOTE_ADDR']) && empty($_SERVER['SERVER_ADDR'])) below is meant to prevent anyone but the cronjob from running the script.

I have also added the ability to optimize more than one database. This is useful if, for instance, you have your stie stats recorded to a seperate database.

Code:

<?php

# $Id: db_optimizer.php,v 1.2.3 2005/01/05 15:35:20 nualpha Exp $

##############################################################################

##                        ## Database Table Optimizer ##                    ##

##############################################################################

## Optimize all database tables once every 24 hours through a cronjob.      ##

## If a table is damaged, this script will attempt to repair it.            ##

##############################################################################

## Initially Created: 6/8/2004



# Ensure the script execution request is by the server.

if (empty($_SERVER['REMOTE_ADDR']) && empty($_SERVER['SERVER_ADDR'])) {

        

        # Define Constants

        define('ERROR_LOG', '/absolute-path-to-your-log-directory/db_optimization_errors.html');

        define('DB_LINK', mysql_connect('server_address','sql_login','sql_password'));

        

        # Define databases to be optimized.

        $db_name = array('list_of_comma_seperated','databases_here');

        

        foreach ($db_name as $db) {

                mysql_select_db($db, DB_LINK);

                

                # Get all tables in the database.

                $result = mysql_list_tables($db);

                

                $sim_tab = ''; // Simulate tab with HTML non-breaking spaces.

                

                # Begin table optimizations.

                for ($i=0; $i < mysql_num_rows($result); $i++) {

                        $current_table = mysql_tablename($result, $i);

                        $opt_table = mysql_query('OPTIMIZE TABLE '.$current_table, DB_LINK)

                                                 or error_log("Couldn't optimize the '$current_table' table!

".

                                                                          $sim_tab.'MySQL said: '.mysql_error()."

\n",3,ERROR_LOG);

                        

                        if (!$opt_table && preg_match("/'(\S+)\.(MYI|MYD)/",mysql_error(), $damaged_table)) {

                                # Repair broken indexes.

                                mysql_query('REPAIR TABLE '.$damaged_table[1], DB_LINK)

                                                 or error_log("Couldn't repair the '$current_table' table!

".

                                                                          $sim_tab.'MySQL said: '.mysql_error()."

\n",3,ERROR_LOG);

                                $opt_table = null;

                                # Try to optimize the table again.

                                $opt_table = mysql_query('OPTIMIZE TABLE '.$current_table, DB_LINK)

                                                 or error_log("Couldn't optimize the '$current_table' table after trying to repair it!

".

                                                                          $sim_tab.'MySQL said: '.mysql_error()."

\n",3,ERROR_LOG);

                        }

                }

        }

}

?>

Not being one that is all that familiar with mysql (thats what I pay others for), what happens to a database when it's being optimised?? i.e what does it actually do?

Quote:

Originally Posted by cotc2001

Not being one that is all that familiar with mysql (thats what I pay others for), what happens to a database when it's being optimised?? i.e what does it actually do?

From the MySQL manual...
"Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file."

Basically, optimizing the tables keeps record access as fast as possible. This module also detects and auto-repairs any damaged tables as well. :)

I guess I just run a bash script via cron that optimizes my tables and then backs them up.

Code:

#Change the 5 variables below to fit your computer/backup

COMPUTER=`/bin/hostname -s` # Name of this computer

CREDENTIALS="-u your_username -pyour_password"

BACKUPDIR=/backup # Where to store the backups

MYSQLDUMP="/usr/bin/mysqldump"

MYSQLCHECK="/usr/bin/mysqlcheck"

DATABASE="your_database"



#You should not have to change anything below here

PATH=/usr/local/bin:/usr/bin:/bin

DOW=`date +%a` # Day of the week e.g. Mon



# Optimizes the database

$MYSQLCHECK $CREDENTIALS -o -B $DATABASE



# Dumps the mysql db to a file in the backup dir

$MYSQLDUMP --opt $CREDENTIALS $DATABASE > $BACKUPDIR/$COMPUTER.$DOW.$DATABASE.sql

Quote:

Originally Posted by sstillwell@aerostich.com

I guess I just run a bash script via cron that optimizes my tables and then backs them up.

Looks good. Some people can't access bash in that manner because of security restrictions set in place by the company which provides their servers. Would work fine for a dedicated server though.

Great job NuAlpha 8)

And with regards to security mentioned further up in thread, if you can run cronjobs on your account then just put db_optimizer.php in a non web-accessible directory ie:
one directory up from your public_html directory

Quote:

Originally Posted by hooter

And with regards to security mentioned further up in thread, if you can run cronjobs on your account then just put db_optimizer.php in a non web-accessible directory ie:
one directory up from your public_html directory

HAH!! Good idea! And here I was rewriting the code to solve a simple problem. For some reason clever code always seems like the best solution in my mind even though there is probably some simpler way of taking care of the issue at hand. :lol:

When I run db_optimizer.php, I get the following mySQL error:

Code:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /usr/local/apache/htdocs/db_optimizer.php on line 21

I've tried the variations of db_optimizer.php given here, but they both result in the same error.

I would prefer to run the bash script rather than the php anyway, and I have access to bash, but I have no idea how set up and run bash scripts.

Any help on getting either method to work is much appreciated. Thanks!

===========
mySQL: 4.1.11
Apache/1.3.33 (Unix) PHP/4.3.11 mod_perl/1.29
xcart: 4.0.15