All websites require maintenance to a different degree and while most maintenance can be automated this cannot meet the needs of all websites. For example a website with 1,000 visitors a day will generate far less log data than one that get's 1,000,000 per hour. The maintenance strategy you choose is important and can effect the overall performance of your site. This goes for the hosting company you use, if they are diligent and keep logs cut, memory cleansed, disks defragged and optimised, your site will continue to perform consistently well.
The purpose of the web logs is to record the requests and response made by your site. These are usually processed by statistics programs such as AWStats which breakdown the information to human readable form and include graphs. As stats programs summarise information and store it in their own database web logs are not needed by them once the processing is complete.
If your statistics program updates daily there's no need to keep the logs any longer than 2 or 3 days and they can be removed. This will save you a significant amount of disk space. Some hosts will do this for you automatically but if you see your Disk Quota running out fast contact them and check.
A database can cause a significant performance issue if problems occur. MySQL is almost bullet proof and even the busiest sites handling many transactions per second will have few problems. However regular maintenance will not only keep your database performing at it's fastest but will also reduce the chances of corruption or other fault management.
The following tables can be cut or completely truncated for backup and host moves to reduce upload/download time.
vtp_bnr_tracking
vtp_slot_tracking
vtp_solo_tracking
vtp_text_tracking
vtp_tracking
vtp_track_tracking
The following table can be truncated (emptied) before a move or if it becomes too large:-
vtp_referral_tracking
If you host with Ventrino you do not need to worry as we take care of this for you automagically.
As the name suggests it is a process for removing data no longer needed. While MySQL is good at handling large tables reading and writing a table will slow down as it grows. We suggest you only keep 8 days of data as this is all that is needed for statistical purposes.
One way to do this is to create a script and place it in a cron. The following script will search all your databases for tracking tables to cut, just replace the host, username and password with your own:-
<?php /********************************************************************* * Hunts for tracking tables and removes unnecessary data *********************************************************************/ set_time_limit(300); error_reporting(E_ALL); $dbhost = 'localhost'; $dbuser = '~~~~~~ replace with your username ~~~~~~'; $dbpass = '~~~~~~ replace with your password ~~~~~~'; define('DAYS_TO_KEEP',8); $conn = @mysql_connect($dbhost, $dbuser, $dbpass) or die("Couldn't connect to database"); $databases = mysql_list_dbs($conn); echo '<ul>'; while($row = mysql_fetch_row($databases)) { $db = $row[0]; echo '<li>'.$db; $sql = "USE $db;"; echo '<br>'.$sql; $result = mysql_query($sql); if(!$result) {echo "<br><b><font color='red'>".mysql_error()."<br>SQL:=$sql</font></b>";} echo '</li>'; $tablelist=mysql_list_tables($db); echo "<UL>"; $table = 0; while ($table < mysql_num_rows($tablelist)) { $tablename=mysql_tablename($tablelist, $table); if (instring(strtolower($tablename),'tracking')) { echo '<li><em>'.$tablename.'</em>'; $sql = ''; switch ($tablename) { case 'vtp_referral_tracking': case 'vlt_tracking': case 'va_tracking': case 'vtp_track_tracking': // Don't want to do anything for the above tables break; default: $sql = 'DELETE FROM '.$tablename.' WHERE action_date < DATE_SUB(CURDATE(),INTERVAL '.DAYS_TO_KEEP.' DAY);'; echo '<br>'.$sql; $result = mysql_query($sql); if(!$result) echo "<br><b><font color='red'>".mysql_error()."<br>SQL:=$sql</font></b>"; $del_count = mysql_affected_rows(); $total_deleted += $del_count; printf("<br>Records deleted: %d\n", $del_count); } echo '</li>'; } $table++; } echo "</UL>"; } echo '</ul>'; echo "<br><b>Total records deleted: $total_deleted</b>"; mysql_free_result($databases); mysql_close($conn); function instring($text,$find){ /* Pass $text to search for the string in $find * $find can be a single string to look for or an * array of strings. If anything in $find is found * in $text then this function returns the position * it was found.*/ if (is_array($find)) { foreach($find as $f) { $retval = strpos($text,$f); if ($retval) break; } } else { $retval = strpos($text,$find); } if (!$retval) $retval = 0; return $retval; } ?>
To truncate a table is to empty it. All data is removed and an empty file is left behind. The referral tracking file can get to many GB in size. If you wish to backup your site this can cause a serious delay, maybe even cause the backup to fail. It is safe to empty it either through PHPMyAdmin or through the SQL viewer. Be careful using this command, type in the wrong table name and you could lose everything!
TRUNCATE vtp_referral_tracking;
If you are taking regular backups before making modifications to the configuration and the config.inc.php file then from time to time you may need to clear out the old backup files. For more information click here.