Tutorial

Intro:
A php mysql database driven web site such as dolphin can and will get cluttered and cause overhead. Whether you, the admin of your site deletes, updates, or makes changes to various content and posts or your members do, it will cause overhead in the database. This of course is not Dolphin specific. It is just what happens with most any database when changes are made or applied.

In time you will see overhead in your database when someone makes changes. If you or other members make a lot of changes or delete a lot of things you could potentially have quite a bit of overhead in your database tables. Which, over time can slow your Dolphin site down if you have a lot or many tables with overhead.

Put in simple terms optimizing a database for overhead would be similar to defragmenting.

I wrote a couple tutorials a while back about how to manually optimize Dolphin database tables using phpmyadmin.


You can find them here:

Optimize Boonex Dolphin Database

Optimize Dolphin 7 Database Tables after deleting content


If you don't have much activity at your site or if you want to manually optimize your database tables refer to the tutorials above.


For those of you that don't want to mess with manually optimizing or don't want or have the time to do so I decided to post this script that makes database optimization happen automatically with a cron.

Disclaimer:
As with any 3rd party script, modification, module, etc. use this at your own risk. If you don't understand how to use this or how to add it, then don't.


Adding / Installing / Using:

You will need to know your Database Name, Database User, and Database Password which you can find in /inc/header.inc.php if you do not know. To find these open /inc/header.inc.php and find:

$db['host']                = 'localhost';
$db['sock']                = '';
$db['port']                = '';
$db['user']                = 'account_databaseuser';
$db['passwd']              = 'password';
$db['db']                  = 'account_databasename';

Note:
You might download a backup of /inc/header.inc.php first just incase. You are not going to edit header.inc.php at all. You are just getting the database details from the file so you can add them to optimize.php file below.


Download this .zip file:
Automatic MySQL Database Optimizer


Extract it to your local computer. Then open the file:
optimize.php

Find and replace the variables in optimize.php with the details you got from header.inc.php above:

//Database Connection variables :
$h = 'localhost';
$u = 'account_databaseuser';
$p = 'password';

$dummy_db = 'account_databasename';

Edit the variables to reflect your database user (account_databaseuser), database password, and database name (account_databasename) which you found above in header.inc.php.

Save the changes to optimize.php on your local computer.

You could put optimize.php in the Dolphin 7 periodic folder or directory, but for additional security consider putting it in your hosting account home, main, or root directory that is not publicly accessible to the entire internet world.

A common location with cpanel hosting would be: (a good choice for added security)
/home/your-cpanel-account/optimize.php

Publicly available location would be more like: (avoid this for added security)
/home/your-cpanel-account/public_html/dolphin-files-directories-here

Note:
The location you choose could be slightly different based on your hosts setup.


Add A Cron Job:

Info:
There is some debate over how often to optimize database tables. Some suggest doing so regularly if you update, delete, and change information contained in database tables often. Others lean towards optimizing occasional. It will depend on the site, how busy it is, how often things are updated, changed, and deleted. Which, can vary considerably from one site to another.

I don't think daily optimization is necessary for most web sites. Maybe once per week or even once per month depending on the site would be more reasonable.


Login to your hosts control panel and go to your cron jobs area.

Navigate to your hosts cron job area:

Add Dolphin 7 Cron Job



Add a new cron job with the following command for once per week:
0 0 * * 0  /usr/local/bin/php -q /home/accountname/optimize.php


Or if you want to run it only once per month, then set and use the following cron:
0 0 1 * *  /usr/local/bin/php -q /home/accountname/optimize.php


After it runs, you should get an email report that will look like:

Database Optimization Report

Found 2 databases
Database : information_schema

Database : your_database

sys_alerts needs optimization and is now optimized
sys_injections needs optimization and is now optimized
sys_menu_top needs optimization and is now optimized
sys_page_compose needs optimization and is now optimized
Process complete parsed in 0.079713 secs


In this example email report you can see 4 tables had overhead and were automatically optimized in 0.079713 secs.



If there was no overhead in your database the report would look like:

Database Optimization Report

Found 2 databases
Database : information_schema

Database : your_database

Process complete parsed in 0.034698 secs



If you do not want to receive this report by email you can add the following to your cron job:
>/dev/null 2>&1

So the entire cron would look like for weekly with no email report:
0 0 * * 0  /usr/local/bin/php -q /home/accountname/optimize.php >/dev/null 2>&1

Monthly with no email report would look like:
0 0 1 * *  /usr/local/bin/php -q /home/accountname/optimize.php >/dev/null 2>&1


If you would like to make sure it is working first login to your hosts control panel and open phpmyadmin.

Select your Dolphin database in phpmyadmin.

Scroll down the page and look for tables that contain overhead. If you haven't optimized the tables manually lately you should have at least some with overhead.

Take note of some of the overhead.

Set the cron temporarily to run every minute like so:
* * * * *  /usr/local/bin/php -q /home/accountname/optimize.php

Wait a couple of minutes and check your email associated with the cron job.

The email will come to the address associated with the cron job setup area. Here in this example you can see the "Current Email" is: thiiagopiimenta@hotmail.com (which is just made up for this example). This is where the optimization report will be sent to.

Cron Jobe Email



You should receive an email report with the above mentioned optimization details.

Now check your Dolphin database in phpmyadmin again and you shouldn't have anymore overhead.


Don't forget:
Now finally be sure to set the cron back so it is not running every minute with either the weekly or monthly suggestions above.



Additionally:

This isn't Dolphin specific. It can be used with any mysql database driven site. Just update the optimize.php variables with the proper database, user, and password details.

So it would work with Dolphin 6.0x, 6.1x, 7.x, wordpress, etc., etc.


Example phpmyadmin Dolphin 7 database tables before script has run with overhead:

With Overhead



Example phpmyadmin Dolphin 7 database tables after script has run by cron - No Overhead:

No Overhead



Example setting cron job in cpanel weekly:

Weekly Cron Job



Example setting cron job in cpanel monthly:

Monthly Cron Job



Other sample cron commands:
0 0 * * 0 /usr/local/bin/php -q /home/accountname/optimize.php
0 0 * * 0 /usr/bin/php -q /home/accountname/optimize.php
0 0 * * 0 /usr/local/bin/php4 -q /home/accountname/optimize.php
0 0 * * 0 /usr/local/bin/php5 -q /home/accountname/optimize.php

To find and verify your account path (/home/accountname/) open /inc/header.inc.php and look for:
$dir['root']               = "/home/accountname/public_html/";

Just leave off the public_html part and anything after it if you followed these instructions and uploaded optimize.php above the public_html directory. This is a common path for cpanel hosting. If your host uses another control panel or type this may look slightly different.



Problems:
Double check the variables for your Database Name, Database User, and Database Password in optimize.php.

Double check and make sure the cron command path is correct for the location to optimize.php and you have entered the path to php correctly as well.

  • You can find the path to php in /inc/header.inc.php if you are unsure. Open /inc/header.inc.php and look for:


$video_ext                 = 'avi';
$MOGRIFY                   = "/usr/local/bin/mogrify";
$CONVERT                   = "/usr/local/bin/convert";
$COMPOSITE                 = "/usr/local/bin/composite";
$PHPBIN                    = "/usr/local/bin/php";

The $PHPBIN will tell you the path to php. In this case, /usr/local/bin/php

This info along with the location we uploaded optimize.php to gives us the following cron command. The 0 0 * * 0 is once per week (12:00 a.m. midnight every Sunday).

0 0 * * 0  /usr/local/bin/php -q /home/accountname/optimize.php

Other than that a file permission of 644 should be sufficient for most setups. If you have problems you could try setting optimize.php to another permission level.



Help Us Out by Donating:

If you find this Dolphin Tutorial, or any of our other tips, suggestions, and information here on this site helpful please consider donating. It helps keep this site online, which will allow us to continue posting free tutorials and other helpful information for everyone.


 
Written By
Tutorial by: Jeremy LeSarge (AKA Ray)

I am the owner and administrator of DialMe.com. I write Tutorials for Boonex Dolphin as well as tips and resources surrounding website programming and development. I enjoy working with WordPress, SEO, and Web Hosting / Servers. I also maintain a WordPress Blog here on this site where you will find a variety of technology and webmaster resources.

Actions
Sponsored Links
Recommend