[Bro] Connection records in a database?

[Randolph Reitz]

On Oct 6, 2008, at   10:04, Seth Hall wrote:

> I checked out your log importer too, but I noticed that you're doing
> individual inserts for each record.  In my testing, doing individual
> inserts doesn't scale for high data rates, the database can't insert
> data quickly enough.  I have been using the COPY [1] method for
> inserting data in batches and it turns out that even at high data
> rates the database can keep up just fine.

Yes, individual inserts don't work!

Here is the conn.log file on my BRO installation...

[brother at dtmb ~]$ s=$(wc -l spool/bro/conn.log | awk '{print $1}');  
while true; do sleep 10;s1=$(wc -l spool/bro/conn.log | awk '{print  
$1}');printf "%d\n" $((s1 - s));s=$s1;done
4750
4728
4565
4243
4926
4379
^C

Looks like conn.log is adding ~450 connections per second.  Here is  
what happens with a python script that tails conn.log and inserts each  
record into a Postgres DB...

[brother at dtmb ~]$ l=$(echo "select count(*) from bro_connections" |  
psql -h nimisrv nimi_dev | awk '/^ [0-9]/ { print $1}');while true;do  
sleep 10;n=$(echo "select count(*) from bro_connections" | psql -h  
nimisrv nimi_dev | awk '/^ [0-9]/ { print $1}');printf "%d\n" $((n- 
l));l=$n;done
1756
1625
1631
1667
1670
1838
^C

Maybe ~160 records per second.  Not even close.

It's always nice to know what not to do.

Randy