[pmacct-discussion] configurafion
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: 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 '-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
Re: [pmacct-discussion] configurafion
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. 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: > > 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 '-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
Re: [pmacct-discussion] configurafion
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: 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 '-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
Re: [pmacct-discussion] configurafion
Hi Jaroslav, You move a good point: the feature you are looking for is called flow stitching, you can set nfacctd_stitching to true to enable it. As a result you will have a new timestamp_min and timestamp_max primitives populated. Read more in the CONFIG-KEYS doc: https://github.com/pmacct/pmacct/blob/master/CONFIG-KEYS The feature is tricky and a few considerations should be taken into account; like flows that span several time-bins (for which you may be wanting to do pro-rating [nfacctd_pro_rating]); also while doing tests with some operators we did hit a few timestamping bugs that, while in normal conditions are overall "OK-ish", they get really detrimental when this feature (or set of features if you consider also pro rating) is enabled. If you decide to give it a shot, please get in touch privately for some feedback, bugs, possible improvements, etc. Cheers, Paolo On Thu, May 26, 2016 at 10:02:56PM +0200, Jaroslav Jirásek wrote: > 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: > >> > >>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 '-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? > >> > >>
