Hello!
My troubles continue with INSERTs (i posted a message about INSERT
DELAYED
crushing a server before). Now, I have this:
osiris:/root-> mysqladmin processlist
+----+------+-----------+-----------+---------+------+--------+-------
----------------------------------------------------------------------
-------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+-----------+-----------+---------+------+--------+-------
----------------------------------------------------------------------
-------------------------+
| 3 | root | localhost | iptraffic | Query | 484 | update | INSERT
INTO test (a1,a2,a3,a4) VALUES (0,0,108,165)
|
| 22 | root | localhost | iptraffic | Query | 240 | update |
REPLACE INTO traffic
(interface,dt,sip1,sip2,sip3,sip4,sport,dip1,dip2,dip3,dip4,dport,prot
o,dbytes, |
| 53 | root | localhost | | Query | 0 | | show
processlist
|
+----+------+-----------+-----------+---------+------+--------+-------
----------------------------------------------------------------------
-------------------------+
As you see the queries hang for 484 and 240 seconds. Both are executed
on
iptraffic database, but they work with different tables. Both are very
simple
queries.
The database defined as:
create table test (
a1 tinyint unsigned not null,
a2 tinyint unsigned not null,
a3 tinyint unsigned not null,
a4 tinyint unsigned not null,
primary key (a1,a2,a3,a4)
);
create table traffic (
interface CHAR(4) NOT NULL,
dt DATETIME NOT NULL,
sip1 TINYINT UNSIGNED NOT NULL,
sip2 TINYINT UNSIGNED NOT NULL,
sip3 TINYINT UNSIGNED NOT NULL,
sip4 TINYINT UNSIGNED NOT NULL,
sport TINYINT UNSIGNED NOT NULL,
dip1 TINYINT UNSIGNED NOT NULL,
dip2 TINYINT UNSIGNED NOT NULL,
dip3 TINYINT UNSIGNED NOT NULL,
dip4 TINYINT UNSIGNED NOT NULL,
dport TINYINT UNSIGNED NOT NULL,
proto CHAR(6) NOT NULL,
dbytes INT UNSIGNED NOT NULL,
pbytes INT UNSIGNED NOT NULL,
INDEX index_a (interface, dt, sip1, sip2, sip3, sip4),
INDEX index_b (interface, dt, dip1, dip2, dip3, dip4),
UNIQUE index_u (interface, dt, sip1, sip2, sip3, sip4, sport,
dip1, dip2, dip3, dip4, dport, proto)
);
The script which queries the TEST table is:
#!/usr/bin/perl
use DBI;
$dbh=DBI->connect('DBI:mysql:iptraffic','','');
$sql="INSERT INTO test (a1,a2,a3,a4) VALUES (?,?,?,?)";
$sth=$dbh->prepare($sql);
$a1=0;$a2=0;$a3=0;$a4=0;
for ($i=0;$i<1000000;$i++){
$sth->execute($a4,$a3,$a2,$a1);
$a1++;
if ($a1>255){
$a1=0;
$a2++;
if ($a2>255){
$a1=0;$a2=0;
$a3++;
if ($a3>255){
$a1=0;$a2=0;$a3=0;
$a4++;
}
}
}
unless ($i%100){print "$i $a4 $a3 $a2 $a1\n";}
}
The script which updates the traffic table is trickier.
@interfaces=('ed0','ed1');
$DBH=DBI->connect('DBI:mysql:iptraffic','','','');
unless ($DBH){
die "Cannot connect to database\n";
}
$sql="REPLACE INTO traffic
(interface,dt,sip1,sip2,sip3,sip4,sport,dip1,dip2,dip3,dip4,dport,prot
o,dbytes,pbytes)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
$sth=$DBH->prepare($sql) || die "Cannot prepare";
foreach $interface (@interfaces){
open (A, "/usr/local/bin/traflog -n -o intrastat -i $interface
|") || die "Cannot pipe data";
while (<A>){
@data=split(/\t/,$_);
@sip=split(/\./,$data[1]);
@dip=split(/\./,$data[3]);
$sth->execute(
$interface.'',
$data[0].'',
$sip[0]+0,$sip[1]+0,$sip[2]+0,$sip[3]+0,
$data[2]+0,
$dip[0]+0,$dip[1]+0,$dip[2]+0,$dip[3]+0,
$data[4]+0,
$data[5].'',
$data[6]+0,
$data[7]+0
) || die "Cannot execute query";
}
close A;
}
$DBH->disconnect();
What would cause the lockup problem?
MySQL: 3.23.33 compiled from tar ball with compiler option suggested
to make it stable for large loads on FreeBSD (they are listed in the
port's makefile).
OS: FreeBSD 4.2-STABLE
Regards,
Artem Koutchine
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
