First of all: This is a great piece of software - just what I was looking for!
Second: This is my first post here and I'm wearing my newbie hat. Feel free to
ask me to go back to school if I'm way off mark.
I'm currently running Nfacctd with MySQL plugins (one for each direction) on a
fairly busy link with heavy log aggregation in order to keep the number of rows
down. At present I use a 5min sql_history which results in a total of around
1500 rows per 5min period. With the default sql_refresh_time, I end up with
multiple plugin and mysql instances, presumably because the cache flush
procedure doesn't always complete within the sql_refresh_time period. Changing
sql_refresh_time to the sql_history value reduced the number of UPDATE queries
and made it run without forking. Still it takes around 2:45 to complete a cache
flush to MySQL (even with a small database - it is currently around 200MB).
This would be acceptable with current traffic loads if it wasn't for
mysql_plugin locking the table during this time, making it almost impossible to
get any data out of the database with reasonable response time.
So, looking at INTERNALS, CONFIG_KEYS and mysql_plugin.* I'm wondering:
1: It seems like the current approach on cache flush is roughly (in
meta-language by creative unfolding of functions from MY_cache_purge):
LOCK TABLE
for all cache entries
do
// Maybe the row exists, try update
UPDATE TABLE
// No it didn't, insert the row instead
if update_failed then
INSERT TABLE
endif
done
UNLOCK TABLE
There is support for the sql_dont_try_update flag, which would be nice if it
was a way to synchronize with my sql_history periods as it would prevent as
manye UPDATE attempts as there are INSERT attempts. I can't see how that can be
done with NetFlow, however, as flow packets may arrive later with more
information for the same SQL history epoch. Are there ways to do this with
Netflow?
2: In INTERNALS Paolo writes: "Because we, at this moment, don't known if
INSERT queries
would create duplicates, an UPDATE query is launched first and only if no rows
are affected,
then an INSERT query is trapped."
This is a bit cryptic to me. What is meant by this? A plain INSERT will yield
the normal duplicate error if the row already exists (given default primary
key). A 4.1+ compatible INSERT... ON DUPLICATE KEY UPDATE... seems to me to do
all that's required and doing so in a single query, e.g:
insert into acct (ip_src, ip_dst, src_port,dst_port, ip_proto, packets,
bytes,stamp_inserted, stamp_updated) VALUES ("213.180.130.206", "81.93.160.46",
0, 0, "ip", 100, 1000,"2005-12-01 00:00", now()) ON DUPLICATE KEY UPDATE
packets=packets+100, bytes=bytes+1000, stamp_updated=now();
(Note: I've removed the MAC entries from my DBs and compile with HAVE_L2
undefined)
Are there doubts about the reliability of this approach that is behind the
comment in INTERNALS?
3: Ultimately performance enhancement is only of interest to me to ensure
proper logging of all Netflow data. Whether the CPU spends 5% or 50% of its
time doing this is irrelevant to me. What is not fine is that it locks the
table while doing it, thereby preventing concurrent analysis of Netflow
information.
This brings me to my last question: Why is the table locked? Is it only because
of the UPDATE/INSERT combination on multi-threading, where a failed UPDATE will
yield a failed INSERT if the row is inserted by another process in the mean
time? Could an INSERT... ON DUPLICATE KEY UPDATE... strategy enable us to
remove the dreaded locks or are there other issues?
All the best,
-- Inge
