Optimising DB automatically Contribution.

[♥yesudo]

Post any queries here:

[safoo]

What is the link to the contribution?

[♥yesudo]

http://www.oscommerce.com/community/contributions,2042

[defender39]

excellent...just what I was looking for...I will let you know if I have any problems

[mutter]

Hi, what things of the DB needs to be optimized?

 

This contrib checks the DB for errors, or just optimize the

db contents?

[♥yesudo]

Hi, what things of the DB needs to be optimized?

 

This contrib checks the DB for errors, or just optimize the

db contents?

This contribs goes through each table in your database and optimizes it.

 

Data can become fragmented over time which increases the time taken to find things in the DB.

 

This contrib defrags your DB/Tables.

Edited April 26, 2004 by yesudo

[safoo]

Thanks yesudo. Is there any risks to using this contribution? Can it cause errors in the database or is it 100% fool-proof?

 

It sounds like a fantastic contribution to speed up everyone's sites.

[♥yesudo]

Thanks yesudo. Is there any risks to using this contribution? Can it cause errors in the database or is it 100% fool-proof?

 

It sounds like a fantastic contribution to speed up everyone's sites.

Should be fine - cannot give any guarantees - apart from the fact that I Have it in place on my store now.

Edited April 26, 2004 by yesudo

[azer]

can someone tell if he could see great performance improvement ?

[♥yesudo]

can someone tell if he could see great performance improvement ?

Hi Azer,

 

Have you used the contrib - or asking out of interest ?

 

The first optimisation should happen the first time your store index.php file is called following installation. After that it will happen every seven days.

 

Results will be different for different stores - based on db size and how long ago the db was last optimized.

 

Any feedback would be appreciated though from anyone.

[mutter]

Hi, seems a very good contrib.

 

Everything that make a stie faster is welcome..

 

I will install it in my offline store, just have another question..

 

This contrib consume too much system resources when is optimizing the DB?

 

And the seven days time frame is the recommended for all stores?

 

Thanks...

[♥yesudo]

Hi, seems a very good contrib.

 

Everything that make a stie faster is welcome..

 

I will install it in my offline store, just have another question..

 

This contrib consume too much system resources when is optimizing the DB?

 

And the seven days time frame is the recommended for all stores?

 

Thanks...

Not sure what the recommended duration between optimizations is - i would guess 7 maybe to frequent.

 

You can change this in the optimize_table.php file:

 

if (($temp_day - $previous_day) > 6)

 

change 6 to what you want.

 

Although, regarding your other question, the more frequently you optimise then the less resources it takes.

[♥yesudo]

Important amendment made re end of year:

 

http://www.oscommerce.com/community/contributions,2042

Edited April 26, 2004 by yesudo

[♥yesudo]

New version - the store owner will be sent an Email when the optimization has taken place.

[gottaloveit]

This is a great contribution. one idea though, possible change. i installed on a heavily modified installation, with lots of traffic. there are 144 tables so far on this site, with feature plans probably growing to about 160.

 

because of this, and because we don't want any customer to lose performance, i decided to put the:

require(DIR_FS_CATALOG . 'optimize_table.php');

 

inside /admin/index.php as:

require('optimize_table.php');

 

and put the optimize_table.php file in the same folder as the /admin/index.php file. this way, the script loads whenever an admin logs in.

 

food for thought, but great contrib. thanks :)

[♥yesudo]

Hi Joseph,

 

Yep had thought about that - thanx I will update the contrib.

[dilirum]

yesudo: Thanks for a most excellently effective contrib!

gottaloveit: Thanks for your improvements to it! Gotta' Love It!

 

The only thing that I would like to add to kind of complete it is (probably in my [admin/backup.php] ... at the bottom of the table - something to the effect of

<?php echo "Database Last Auto-Optimized:" . optimize_check . " ?>

I know that I'm not scripting it right ... but something to print the last optimized date. Wouldn't serve as a function - except to display the last time the db was Auto Optimized. Would basically just serve as a visual that this contrib is running properly and that the db is continously optimized. Can anyone help me code that?

 

Thanks in advance to anyone that might be able to code that.

dilirum

[♥yesudo]

in the latest release you should get an email each time the db is auto optimized.

 

The coding you want is/try:

 

<?php 
$query = tep_db_query("select start_date from optimize_check");
$date = tep_db_fetch_array($query);


echo "Database Last Auto-Optimized:" . $date['start_date'];
?>

Edited May 5, 2004 by yesudo

[gottaloveit]

How about this:

 

On my backup.php file i inserted this text on line 411 after the

 

<tr>
               <td class="smallText" colspan="4"><?php echo TEXT_LAST_RESTORATION . ' ' . DB_LAST_RESTORE . ' <a href="' . tep_href_link(FILENAME_BACKUP, 'action=forget') . '">' . TEXT_FORGET . '</a>'; ?></td>
             </tr>
<?php
 }
?>

 

and before the

</table></td>
<?php
 $heading = array();

 

<?php
// beginning of optimizing date add on 
?>
             <tr>
               <td class="smallText" colspan="4">
               <?php
               $optimize_date_query = tep_db_query("select date_format(start_date,\"%m-%d-%Y) as start_date from optimize_check where counter = '1'");
               $optimize_date = tep_db_fetch_array($optimize_date_query);
               echo 'Database Last Auto-Optimized: <b>'.$optimize_date['start_date'].'</b>';
               ?>
               </td>
             </tr>
<?php
// end of optimizing date add on
?>

 

Hope that helps

[blueline]

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it's not likely that you need to do this more than once a week or month and only on certain tables.

 

OPTIMIZE TABLE works as follows:

 

If the table has deleted or split rows, repair the table.

If the index pages are not sorted, sort them.

If the statistics are not up to date (and the repair couldn't be done by sorting the index), update them.

Note that MySQL locks the table during the time OPTIMIZE TABLE is running.

 

The only problem with this contribution is that it is really not needed for most databases, and even when useful, it will only make optimal changes to certain tables (not all tables will benefit).

 

Also, while this optimization will not take long, notice that it does temporarily freeze the tables that are being optimized. On a large scale...this could be problematic.

 

In order to make this contribution even better for those that wish to use it, I would recommend setting this page up to a cron job and call it weekly or biweekly at some point in time in the middle of the night (i.e., every other Tuesday at 3:00 am). This will just provide further data integrity and reduce risk of data corruption.

 

HTH,

-Chris

Edited May 5, 2004 by blueline

[dimports]

Will this cause any changes to your product or category ids. I give out some links like http://www......com/catalog/index.php?cPath=68 will take you to my lighting collection. After optimizing my tables, will it rearrange the ids and move lighting 'up the latter' and change it to an empty spot of cPath=47, for example? I want to maintain all my links, even tho they are supposed to be dynamic. Thanks in advance.

[dilirum]

Most excellent. Using the script from gottaloveit yields a mysql error for me ... but using your technique but with yesudo's scripting I get the desired results. Excellent job folks. I dig Chris's idea here - about making it run on a cron job. Thanks all!

 

Dili

[♥yesudo]

Will this cause any changes to your product or category ids. I give out some links like http://www......com/catalog/index.php?cPath=68 will take you to my lighting collection. After optimizing my tables, will it rearrange the ids and move lighting 'up the latter' and change it to an empty spot of cPath=47, for example? I want to maintain all my links, even tho they are supposed to be dynamic. Thanks in advance.

 

Optimizing your tables/DB won't change any of your product/category/path details.

 

If you have access to CRON jobs then that is the ONLY way to go. IF you don't have access to cron jobs then this is AN alternative.

Edited May 6, 2004 by yesudo

[Guest]

Will this cause any changes to your product or category ids. I give out some links like http://www......com/catalog/index.php?cPath=68 will take you to my lighting collection. After optimizing my tables, will it rearrange the ids and move lighting 'up the latter' and change it to an empty spot of cPath=47, for example? I want to maintain all my links, even tho they are supposed to be dynamic. Thanks in advance.

 

Optimizing your tables/DB won't change any of your product/category/path details.

 

If you have access to CRON jobs then that is the ONLY way to go. IF you don't have access to cron jobs then this is AN alternative.

How would I go about setting this contrib using CRON?

[Jack_mcs]

I wanted to see the result of the optimization so I replaced the code between

 $tbl_result = mysql_query($tbl_status);

and

$db_optimized_subject = 'Store database has been optimized.';

with the following:

  $dbsize_before = 0;
   $dbsize_after = 0;
 
   // 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)) 
     {
    $dbsize_before += $tbl_row['Data_length'] + $tbl_row['Index_length']; 
       // Statement to optimize table
       $opt_table = 'OPTIMIZE TABLE ' . $tbl_row[0];

       // Query mySQL to optimize currently looped table
       $opt_result = mysql_query($opt_table);
    $dbsize_after += $tbl_row['Data_length'] + $tbl_row['Index_length']; 
     } // End table while loop 
   } 
 } // End database if statement

 $db_optimized_email = '<b>Store database has been optimized.</b>' . 'Size before: '.$dbsize_before . 'Size After: '. $dbsize_after;

When you receive the email, it says something like

Store database has been optimized. Size before: 1354291 Size After: 1358291

Thought I would post it in case anyone else would like to see the results of their optimization.

 

Jack