script causing high cpu usage

[Don Jones]

Hi again

I have another question based on some problems I have with some important, old
code from my organisation, that I am the lucky maintenance programmer for.
This code is from one of 3 scripts that gets run from cron once a month to do
billing for internet usage, it interfaces with a mysql database to get its
data and outsput's  various csv's html and pdf's. 

The problem is as soon as the script runs mysqld quickly gobbles up all the
cpu usage on this box (which happens to perform a lot of other important
functions). The code that seemsto be generating the excessive usage is
included below. the initial $Team places approximately 1700 ip addresses in
@Addresses. It then does a foreach $address (sort byIpAddress @Addresses)
block, there are numerous db accesses in this block (I havent included the
whole block, theres a bunch more code).

The problem as i see it is that there are _NO_

$sth->finish();
$dbh->disconnect();

statements in this whole script (and there are 2 more large subs doing similar
things to this one).

I think the excessive cpu usage by mysqld is being caused by many db
connectionsbeing made to the bd and then not being closed properly.

Obvious sloution is close the connections

QUESTIONS:

1. Do you agree with my assessment or is there something else fundamental Im
missing?

2. If i am correct how is it best to use $sth->finish(); and
$dbh->disconnect();. Is it best to use a finish after each query (after the
fetchrow_array )and a disconnect at the very end of the script (my guess)? or
some other configuration of finish and disconnect?

As usual any advice/help appreciated. The scary thing is this code has been
running "successfully" each month for the last few years.

Thanks

Don Jones

Offending Code Follows:
------------------------------------------------------------
sub createTeamOutput {
        ($CurrentOrganisation,$Team,$StartDate,$EndDate) = @_;
        my %CurrentTeamValues;

        # Need a list of all the ipaddresses in this team (but only this
organisation).
        print STDERR "select address from addressinfo,personinfo,subnetinfo
where team = '$Team' and addressinfo.person = personinfo.person and
organisation = '$CurrentOrganisation' and subnetinfo.subnet =
addressinfo.subnet\n";
        $sth = $dbh->prepare("select address from
addressinfo,personinfo,subnetinfo where team = '$Team' and addressinfo.person
= personinfo.person and organisation = '$CurrentOrganisation' and
subnetinfo.subnet = addressinfo.subnet");
        $sth->execute;
        my @Addresses;
        while(@currentAddress = $sth->fetchrow_array) {
                print STDERR "push \@Addresses, $currentAddress[0]\n";
                push @Addresses, $currentAddress[0];
        }
        foreach $address (sort byIpAddress @Addresses) {
                my %CurrentAddressValues;
                # Now here we need to get the total traffic for the address,
so we can check
                # that it is non-zero, like we do for the subnet
                print STDERR "\nCreating output for address $address...\n";
                print STDERR "select (sum(tooctets+fromoctets)/(1024*1024))
from NeTraMet_Daily1 where sourcepeeraddress = '$address' and (date >=
'$StartDate' and date <= '$EndDate') and not (type = 6 or type = 17)\n";
                $sth = $dbh->prepare("select
(sum(tooctets+fromoctets)/(1024*1024)) from NeTraMet_Daily1 where
sourcepeeraddress = '$address' and (date >= '$StartDate' and date <=
'$EndDate') and not (type = 6 or type = 17)");
                $sth->execute;
                @UnknownTotal = $sth->fetchrow_array;
                $CurrentAddressValues{'Unknown'} = $UnknownTotal[0];
                print STDERR "Address Unknown at
$CurrentAddressValues{'Unknown'}\t";
                $CurrentTeamValues{'Unknown'} += $UnknownTotal[0];
                print STEDRR "Team Unknown at $CurrentTeamValues{'Unknown'}\n";
                print STDERR "select (sum(tooctets+fromoctets)/(1024*1024))
from NeTraMet_Daily1 where sourcepeeraddress = '$address' and (date >=
'$StartDate' and date <= '$EndDate') and (type = 6 or type = 17)\n";
                $sth = $dbh->prepare("select
(sum(tooctets+fromoctets)/(1024*1024)) from NeTraMet_Daily1 where
sourcepeeraddress = '$address' and (date >= '$StartDate' and date <=
'$EndDate') and (type = 6 or type = 17)");
                $sth->execute;
                @IPTotal = $sth->fetchrow_array;
                $CurrentAddressValues{'IP'} = $IPTotal[0];
                $CurrentAddressValues{'Total'} = $CurrentAddressValues{'IP'} +
$CurrentAddressValues{'Unknown'};
                $CurrentTeamValues{'IP'} += $IPTotal[0];
                $CurrentTeamValues{'Total'} += $CurrentAddressValues{'IP'} +
$CurrentAddressValues{'Unknown'};
                print LOG "Address IP at $CurrentAddressValues{'IP'}\tAddress
Total at $CurrentAddressValues{'Total'}\tTeam IP at
$CurrentTeamValues{'IP'}\tTeam Total at $CurrentTeamValues{'Total'}\n";
                # Here we ignore the address if the value is zero,
                # we also want to ignore 'team 00' values of less than 5 MB
                next unless ($CurrentAddressValues{'Total'});