Dne 2016-05-25 09:32, Paolo Lucente napsal:
Hi Jaroslav,
To increase precision beyond historical accounting, ie.
stamp_inserted
and stamp_updated, you can use timestamp_start and timestamp_end keys
in
your aggregation method. But, as you will see, while you will
increase
precision, you will increase the amount of data - whether this is a
better
trade-off, it depends on your project.
Yes, i do it this way now. I store timestamp_start and timestamp_end.
Maybe if stamp_inserted could be starttime of first flow, which belongs
to this time-bin and stamp_updated endtime of last flow of this
time-bin ?
I'm looking for ways to reduce my database size (I store 26GB
per day now - with indexes). Is there way to not store timestamp_end
but only flow time (timestamp_end - timestamp_start)?
Thank you, Jaroslav
The current 120 secs 'lag' makes sense to me: it takes the router 60
secs
to export to pmacct, then it takes pmacct another 60 secs to write it
to
disk (sql_refresh_time: 60).
Finally, sql_history_roundoff it's just a way to nicely align time
bins;
you basically want that always on. If you disable it, you configure 5
mins
time bins and start pmacct at 9:32am then time-bins will be falling
at
9:32, 9:37, 9:42, etc. with the feature on, it will nicely align to
the
hour and give you time bins like 9:30, 9:35, etc.
Paolo
On Wed, May 18, 2016 at 10:50:48PM +0200, Jaroslav Jirásek wrote:
Hi, I am trying to store flows to mysql, I have this nfacctd config:
nfacctd_time_new: true
aggregate:
src_host,dst_host,post_nat_src_host,post_nat_dst_host,proto,src_port,dst_port
plugins: mysql
sql_db: pmacct
sql_user: pmacct
sql_passwd: xxxx
sql_optimize_clauses: true
sql_table: acct_%Y%m%d_%H
sql_table_schema: /etc/pmacct/acct.schema
sql_refresh_time: 60
sql_history: 1m
sql_history_roundoff: m
sql_dont_try_update: true
sql_multi_values: 1048576
sql_startup_delay: 60
file acct.schema:
CREATE TABLE acct_%Y%m%d_%H (
ip_src CHAR(15) NOT NULL,
ip_dst CHAR(15) NOT NULL,
post_nat_ip_src CHAR(15) NOT NULL,
post_nat_ip_dst CHAR(15) NOT NULL,
src_port SMALLINT UNSIGNED NOT NULL DEFAULT 0,
dst_port SMALLINT UNSIGNED NOT NULL DEFAULT 0,
ip_proto SMALLINT UNSIGNED NOT NULL DEFAULT 0,
packets INT UNSIGNED NOT NULL,
bytes BIGINT UNSIGNED NOT NULL,
stamp_inserted DATETIME NOT NULL DEFAULT '0000-00-00
00:00:00',
stamp_updated DATETIME,
KEY (ip_src, stamp_inserted),
KEY (ip_dst, stamp_inserted)
);
I have flow timeout 60 secs on flow exporter (mikrotik).
I want one table per hour.
I want best possible sql performance, so directive
sql_dont_try_update
is important for me (I thing). But I also need best possible time
accurancy
of flow start and end in database.
Now every flow exported in database takes two minutes:
(stamp_inserted: 2016-05-18 20:00:00 stamp_updated: 2016-05-18
20:02:01)
although I make short data transfer (several seconds long)
Is there any way to improve time accurancy?
Last records in database has start time for example 2016-05-18
19:59:00 and
end 2016-05-18 20:01:01, but last one minute belongs to next mysql
table.
Is there any way to have one hour table with flows which belongs in
this hour?
subtraction stamp_updated-stamp_inserted is always 121 seconds, i
thing I dont need
stamp_updated in database? Btw why is difference 121 secs ? It may
be 120 secs...
I try scenario without sql_history, with 'nfacctd_time_new: false'
and
with 'sql_dont_try_update: false', but in this setting I got table
name
in mysql acct_19700101_01, why right date is not used?
i don't understand directive sql_history_roundoff, can you explain
it to me?
Thank you for help and suggestions
Jaroslav
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
