How To Capture network packets to MySQL

Question

I'm going to design a network Analyzer for WiFi (802.11) Currently I use tshark to capture and parse the WiFi frames and then pipe the output to a perl script to store the parsed information to Mysql database.

I just find out that I miss alot of frames in this process. I checked and the frames seem to be lost during the Pipe (when the output is delivered to perl to get srored in Mysql) Here is how it goes

(Tshark) -------frames are lost----> (Perl) --------> (MySQL) this is the how I pipe the output of tshark to script:

sudo tshark -i mon0 -t ad -T fields -e frame.time -e frame.len -e frame.cap_len -e radiotap.length | perl tshark-sql-capture.pl 

this is simple template of the perl script I use (tshark-sql-capture.pl)

# preparing the MySQL
my $dns = "DBI:mysql:capture;localhost";
my $dbh = DBI->connect($dns,user,pass);
my $db = "captured";

while (<STDIN>) {
    chomp($data = <STDIN>);
    ($time, $frame_len, $cap_len, $radiotap_len) = split "  ", $data;
    my $sth = $dbh-> prepare("INSERT INTO $db VALUES (str_to_date('$time','%M %d, %Y %H:%i:%s.%f'), '$frame_len', '$cap_len', '$radiotap_len'\n)" );
    $sth->execute;
}

#Terminate MySQL
$dbh->disconnect;

Any Idea which can help to make the performance better is appreciated.Or may be there is an Alternative mechanism which can do better. Right now my performance is 50% means I can store in mysql around half of the packets I'v captured.

Answer 1

Things written in a pipe don't get lost, what's probably really going on is that tshark tries to write to the pipe but perl+mysql is too slow to process the input so the pipeb is full, write would block so tshark just drops the packets.

Bottleneck could be either MySQL or Perl itself but probably the DB. Check CPU usage, measure insert rate. Then pick a faster DB or write to multiple DBs. You can also try batch inserts and increasing the size of the pipe buffer.

Update

while (<STDIN>)

this reads a line into $_, then you ignore it.