From: Mogens Melander
Date: March 12 1999 12:26am
Subject: MySQL, No problems !!
List-Archive: http://lists.mysql.com/mysql/68
Message-Id: <36E85F35.6B29D7A@fumlersoft.dk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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