-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello Rauno,
I read your mail earlier, but I wanted to take some time to look into
it. This caused me to keep postponing the answer, I'm sorry for that. In
return, you'll get a rather lengthy reply ;)
Rauno Tuul wrote:
| 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?
I don't know what the exact problem is. I am not able to replicate the
problem with mysql_auto_reconnect in my setup. What kind of problems do
you get when using that?
If we drop the mysql_auto_reconnect, it might be an idea to at least
keep the connection persistent during one plugin cycle. You noted that
you have several hundreds of queries per cycle, so without a persistent
connection, that would mean that it will set up several hundreds TCP,
which could have a performance impact.
|
| 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.
Well, you have a point there. The structure of the events database is
not very efficient. The reason for using this structure is that it is
common for nfsen plugins to require a database to correlate events from
different timeframes or possibly from different plugins. With the events
plugin I tried to provide a simple interface that provides access to a
database independent of the specific requirements of the plugin that
uses it.
If we would choose to use a separate database for each plugin, it would
require us to create a separate version of the event_mail plugin for
each database. So my preference is to try to improve the performance of
this setup. But I acknowledge that if the performance doesn't improve,
we are having a big show stopper.
I have been looking at the performance of the query in question. If
found that adding a key on the combination of the fields 'name' and
'value' reduces the number of rows returned form the attributes table
from 58912 to 7781 on my database. You noted that you have tried to add
indexes, but you didn't note which. Did you try this one?:
alter table attributes add key (`name`,`value`);
I also did some experiments with rewriting the query to optimize the
order of querying the different tables. By adding a key on the stoptime
field in the events table and first select the active events (with
stoptime is null) and from those the events with the right attributes,
the amount of queried rows is drastically reduced (55+3 in my case).
Could you try to add the key:
alter table events add key (`stoptime`);
and try the query:
select a.event_id, GROUP_CONCAT(Name) as names, GROUP_CONCAT(Value) as
qvalues FROM attributes a join events e on (e.event_id=a.event_id and
stoptime IS NULL) WHERE (Name="Destination" AND Value ="1.2.3.4") OR
(Name="Reporter" AND Value ="my-reporter") OR (Name="botnet_id" AND
Value ="0") OR (Name="Source" and Value="5.6.7.8") AND Profile="./live"
AND Type="botnet" GROUP BY event_id HAVING count(name)=4;
and look how that performs? If this yields acceptable query times for
you, I can change the event plugin to use this query in stead of the
current one.
Regards,
Werner Schram
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkkPI3IACgkQ3ULkMS4OADm5tACgl1EIR/AQYFJgnbv8hgkIAw1i
qnoAoN7aIKqvq8KdYxmlo0OZc9YZ87UG
=dxPM
-----END PGP SIGNATURE-----
-------------------------------------------------------------------------
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
Nfsen-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nfsen-discuss
