Hello,
While trying to get Botnet plugin to work properly, I've encoutered some
problems with Events plugin.
First was problem with DBI:Mysql connection.
When starting nfsen, Events plugin is initialized and new connection is made to
the MySQL host.
After the first query (for example viewed once Plugins->Events tab and contents
of the events database are shown), somehow nfsen host sends out MySQL "request
quit" command. I traced it out with wireshark.
After that every Events plugin child, that contains a request to MySQL fails:
nfsen[18141]: comm child[18174] terminated Exit: 0, Signal: 11, Core: 0
In the Events plugin code is a row:
$dbh->{mysql_auto_reconnect} = 1
Somehow it has no good effect. I know, that since Mysql 5.0.16 there was a
major difference with handling persistent connections.
When I remove that line from the code, first query is successful, but after
that appear the following messages to the log:
"MySQL server has gone away".
My "hack" to get thing working, was to add to every major sub the following
line:
our $dbh = _db_connect();
So for example when a Botnet plugin calls for Events::process_event, a new
MySQL connection is made.
Where might be the problem? DBI?
Events plugin is version 0.1 from sf.net project and I'v installed the
following dependcies
perl-PHP-Serialization-0.27-1.el5.rf
perl-DBI-1.605-1.el5.rf
perl-DBD-mysql-4.007-1.el5.rf
mysql-5.0.45-7.el5
MySQL server version 5.0.27 is running on a different host.
After getting some data to the mysql database, came up a issue with MySQL
performance.
MySQL server is a old Pentium III with 1133Mhz cpu. some scsi raid and a
handful of ram.
when database grew up to the following size:
Table Rows Size
attributes 189,386 29.0 MB
events 8,217 10.4 MB
started the MySQL queries take too long, so the Botnet plugin didn't finish
within 5 minutes and after a few hours botnet couldn't start new child because
of child limit 20. MySQL servers load was above 5, cpu idle 0% and collapsing
under heavy cpu/io usage.
I turned on MySQL slow query log and found this:
# Query_time: 3 Lock_time: 0 Rows_sent: 1 Rows_examined: 0
SELECT ev.event_id FROM events ev right join (select event_id FROM attributes
WHERE (Name="Destination" AND Value ="1.2.3.4") OR (Name="Reporter" AND Value
="my-data-source") OR (Name="botnet_id" AND Value ="0") OR (Name="Source" AND
Value ="5.6.7.8") GROUP BY event_id HAVING count(name)=4) as qtrue on
(ev.event_id=qtrue.event_id) WHERE StopTime IS NULL AND Profile="./live" AND
Type="botnet";
These queries took under load about 5 to 12 seconds to complete. On a idle box
2 seconds. that is a bit too much...
Explain plan is quite sad:
id select_type table type possible_keys key
key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL
1
1 PRIMARY ev const PRIMARY,stoptime,profile,type PRIMARY
4 const 1
2 DERIVED attributes index value event_name 104
NULL 189061 Using where
My Botnet plugin is using one reporter which has over 800 entries and traffic
flow is 4-5 Gb/s, so within one cycle several hundreds of queries are made the
MySQL...
Adding indexes doesn't help much. increasing MySQL cache parameters also
doesn't have great help, due different query parameters.
Has anyone ran into this performance issue before?
I've thought about redesigning the Events mysql database. So that one Event
data in attributes table is stored on a single row, not on 7 rows. In that case
much more reasonable query could be used.
any ideas how to solve my problems?
best regards,
--
rauno tuul
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Nfsen-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/nfsen-discuss
