[ADMIN] CPU Load Help

[Chris Jester]

A problem for you Postgres gurus:

I'm running Postgres via perl DBI::DBD interface and for some reason
whenever I run any SQL actions to the postgres daemon postgres takes up
ALL available CPU % while its running the process, then drops back down
to nothing when it quits.

I am starting postgres like the below line in my /etc/rc.d/init.d/postgressql
startup file:

su -l postgres -c '/usr/bin/postmaster -o -F -S -D/var/lib/pgsql'

It is version 6.4.2 on a PIII450 with all UWSCSI drives and 512Mb.Ram

My perl script is pretty simple........

Parses log files and totals some information up, then updates multiple
records in a postgres table based on accounting information that the
logs generated.....  forget the parsing the log files bit, thats not
a big process...... basically I end up with a list like this:


899200          10
383488          888
292999          123

First column being the account number and second being the total value
that was derived from the logs.....   I simply do this for each account:

GET current values of the account numbers total........
Add new value to original value......
UPDATE the account to show the new value (sum of current value + new value)
Thats it......

I do this for about 20 accounts right now but its killing the CPU load
average.

Anyone??

#!/usr/bin/perl


# Revisions:
# 11/22/99 - Updated to include RAW data stats in addition to the 
already present UNIQUE data.
# - CJ

$test   =       0;      # 1 if verbose mode needed.  For testing only.

require "system.conf";
require "toolcall.lib";
use DBI;

# Path to banner log files.
$log_root_name          =       "../admin/logs/banner";
$journal_root_name      =       "../admin/journals/impressions";


###################################################################### 
#####################
# Set up time variable. Freeze time for duration of cycle. And Mod a 
short version of it. #
###################################################################### 
#####################
$block                  =       0;      # Used to determine if this 
program has already processed the log.
$twilightzone   =       time; # Freeze time
@ticktock               =       localtime($twilightzone);
$small_time     =       $ticktock[0] + ($ticktock[1] * 60) + 
($ticktock[2] * 3600);

#########################################################
# Avoid need for log-rotation.... clip date to filename #
#########################################################
$journaldate    =       sprintf ("_%02d_%02d_%02d_%02d", 
$ticktock[4]+1,$ticktock[3],$ticktock[5],($ticktock[2]-1));
$control_name   =       $log_root_name . "/.banner" .  $journaldate;
$log_root_name .=       "/banner" . $journaldate;
open(LOG,"$log_root_name");

if (-e $control_name) {$block = 1;}

while(<LOG>) {
        ($idkey,$private,$ip,$modtime,$unique,$referer) = split(/\t/,$_);

        ($u,$r)=split(/:/,$tally{$idkey});

        if ($unique eq "U") {$u++; $r++;}
                else {$r++;}
        $tally{$idkey}="$u:$r";
        }
close(LOG);

print "IDKEY\tUNIQUE\tRAW\n" if ($test);

if (!$block) {  &connect;       }

foreach $key (keys(%tally)) {
        ($u,$r)=split(/:/,$tally{$key});

        if (!$block) {
                # Get SQL data: Current Stats Counters.
                $stmnt = "SELECT 
IMP_TODAY,IMP_TOTAL,IMP_TODAY_RAW,IMP_TOTAL_RAW FROM 
${customerTableName} WHERE ID_NUMBER='$key'";
                $sth = $dbh->prepare($stmnt);
                $rv = $sth->execute;
                @keyinfo = $sth->fetchrow;

                # Add new counts to SQL data.
                $keyinfo[0] += $u;      # Today Unique
                $keyinfo[1] += $u;      # Total Unique
                $keyinfo[2] += $r;      # Today Raw
                $keyinfo[3] += $r;      # Total Raw

                # Update customers record.
                $stmnt = "UPDATE ${customerTableName} SET ";
                $stmnt.= 
"IMP_TODAY='$keyinfo[0]',IMP_TOTAL='$keyinfo[1]',IMP_TODAY_RAW='$keyin 
fo[2]',IMP_TOTAL_RAW='$keyinfo[3]' WHERE ID_NUMBER='$key'";
                $dbh->do($stmnt);
                $dberr = $DBI::errstr;
                $dberr =~ s/\n//g;

                # Log impressions to journal.
                open(JOURNAL,">>${journal_root_name}/${key}");
                print JOURNAL "${small_time}\t${u}\t${r}\n";
                close(JOURNAL);
                }

        print "$key\t$u\t$r\n" if ($test);

}

if (!$block) {  &disconnect;    }

# Create control entry file to block any further logging and file 
manipulation should this program be run twice by mistake.
if (!$block) {
        open (CONTROL,">$control_name");
        close (CONTROL);
        }
exit;


Thank you.

Chris Jester
SplitInfinity


Email direct to my cell phone: [EMAIL PROTECTED]
(This is a beta-test and may not work, drop me a note to say HI!)


************