Hi all,
I those times of trouble reporting, i have to pitch in.
I still have to say "Go Monty, Go" :)
I do not have any problems at all, running ->
On the server side:
Linux (slackware 3.5)
Linux 2.0.34 #3 Mon Aug 29 23:31:01 CEST 1998 i586 unknown
Apache 1.3.3 (php3)
mysql Ver 9.20 Distrib 3.22.4-beta, for pc-linux-gnu (i586)
Linux (slackware 3.5)
Linux 2.0.35 #3 Mon Aug 31 23:42:33 CEST 1998 i586 unknown
Apache 1.3.3 (php3)
mysql Ver 9.20 Distrib 3.22.4-beta, for pc-linux-gnu (i586)
Linux (slackware 3.5)
Linux 2.0.36 #3 Mon Aug 28 22:30:10 CEST 1998 i586 unknown
Apache 1.3.4 (php3)
mysql Ver 9.20 Distrib 3.22.4-beta, for pc-linux-gnu (i586)
(and some RH5.x/slackware 3.x, Kernel 2.0.x, Apache 1.3.0 (php2), DB client)
On the client side:
Win 95/98: > 30 (unknown)
MyODBC Ver 2.50.17
Win NT 4.0 Server/WrkStation (SR 1->4) = 5
MyODBC Ver 2.50.17
Linux (slackware 3.5) > 10 (unknown)
Linux 2.0.33 #5 Mon Aug 31 23:00:48 MET DST 1998 i586
mysql Ver 9.20 Distrib 3.22.4-beta, for pc-linux-gnu (i586)
This setup is supplying detailed info on a point-to-point basis on any traffic
involving any front-line router/gateway (i'm involved in) for the last 3 months,
and consolidated for the last 2 years. I know this is basic stuff, but it's
still faster, and more "install and go" than the two heavy db (oracle and sybase)
test servers i have running. At some point i'll do a comparison on these date,
oracle/sybase against MySQL. Not that i have any cracks in my beliefs.
This MySQL environment is running "close to" uninterrupted 23 hours a day.
But at midnight, all routers and servers has to report (in a cron job), al
traffic occurred today (last 24 hours) on a source destination ip-addr basis,
consolidating, (if anybody feels like commenting, feel free) this is far
more than 1 million records a day, collected like this:
#!/bin/sh
cdir=`pwd`
cd /root/mysql
echo "Current directory are `pwd`"
DB_SERVER=xxxxx
ddir=/root/mysql/data #local pre data
odir=/root/mysql/data-ok #local post data
rdir=/root/mysql/data #remote data
MYSQL="/usr/local/bin/mysql -h xxx.xxx.xxx.x -u uuuuuuu -pppppppp"
d1=`date +"%H:%M:%S"`
DATO=`date +%y%m%d`
PID=`cat /var/run/nacctd.pid`
echo "Start at $d1"
echo
echo "Stop net-logging.. pid($PID)"
kill -TSTP $PID
echo "Moving /var/log/net-acct $ddir/net-acct.$DATO"
mv /var/log/net-acct $ddir/net-acct.$DATO
echo "Restart net-logging.. pid($PID)"
kill -CONT $PID
echo "Cleaning data..."
fsize=`filesize $ddir/net-acct.$DATO`
echo "Filesize before cleaning : $fsize"
# remove src/dst addr starting with zerro (isdn errors)
# remove all (src or dst) not local (isdn errors)
/root/mysql/cleandata
fsize=`filesize $ddir/net-acct.$DATO`
echo "Filesize after cleaning : $fsize"
echo "Generating ftp.cmd file..."
echo "open transfer" > ftp.cmd
echo "lcd /root/mysql/data" >> ftp.cmd
for A in `ls -1 $ddir`
do
echo "put $A" >> ftp.cmd
done
echo "quit" >> ftp.cmd
echo -e "Uploading transfer-data to $REMOTE_DB_SERVER ...\n\n"
ncftp < ftp.cmd
echo -e "\nStarting remote processing..\n\n"
for fname in `ls -A1 $ddir`
do
# What are we working on (try awk) ?
yymmdd=`tail -n1 $ddir/$fname|cut -f1 -d ' '`
yy=`echo $yymmdd | cut -f1 -d '/'`
mm=`echo $yymmdd | cut -f2 -d '/'`
dd=`echo $yymmdd | cut -f3 -d '/'`
echo "Loading $rdir/$fname $yy $mm $dd"
$MYSQL $DB_SERVER <<_EOF_
load data infile '$rdir/$fname' into table transfer;
_EOF_
echo -e "\n\tMoving $ddir/$fname to $odir...\n"
mv $ddir/$fname $odir/$fname
echo " Updating YEAR,MONTH,DAY fields...."
$MYSQL $DB_SERVER <<_EOF_
update transfer set yy=left(dato,4),
mm=substring(dato,6,2),
dd=substring(dato,9,2)
where yymmdd=0;
_EOF_
echo -e "\tUpdating perday table...\n"
$MYSQL $DB_SERVER <<_EOF_
insert into perday
select name,yy,mm,dd,sum(bytes)
from domain,transfer
where (source=addr or dest=addr) and yymmdd=0
group by name,yy,mm,dd;
_EOF_
echo -e "\tUpdating YYMMDD field....\n"
echo
$MYSQL $DB_SERVER <<_EOF_
update transfer
set yymmdd=(yy*10000)+(mm*100)+dd
where yymmdd=0;
_EOF_
tmp=`date +"%H:%M:%S"`
echo -e "Done processing $fname : $tmp\n\n"
done
echo "Emptying permonth table..."
echo
$MYSQL $DB_SERVER <<_EOF_
delete from permonth;
_EOF_
echo "Updating permonth table..."
echo
$MYSQL $DB_SERVER <<_EOF_
insert into permonth
select name,yy,mm,sum(bytes)
from perday
group by name,yy,mm;
_EOF_
OLD=`date -d "3 months ago" +"%Y%m00"`
echo "Deleting data older than 3 months ( $OLD )..."
$MYSQL $DB_SERVER <<_EOF_
delete from transfer where yymmdd < $OLD;
_EOF_
cd $cdir
echo "Current directory are `pwd`"
# Do the dirty deed
# Put some sanity logic in here
cp $MYSQL_DB_HOME/$DB_SERVER/* $BACKUP_DIR/$DB_SERVER
# Let somebody else worry about the backup :)
d2=`date +"%H:%M:%S"`
echo "Start : $d1"
echo "End : $d2"
--
Later
Mogens Melander mailto:mogens@stripped
http://www.fumlersoft.dk http://www.trader.dk
http://www.birkerod.dk http://tux.trader.dk
