Re: [pmacct-discussion] sql_dont_try_update causes key collisions?
Hi Ed, Yes, indeed sql_preprocess can affect that too, thanks for the note. Paolo On Mon, Mar 06, 2017 at 05:48:24PM -0600, Edward Henigin wrote: > Paolo, > > I assume also that the "aggregates to the backend" could be much smaller > than the "aggregates in memory" when there is a sql_preprocess in place to > filter (e.g. minb) inserts? > > And as far as updating docs, I might suggest adding to the > sql_dont_try_update key the fact that the sql_cache_entries needs to be > large enough to prevent multiple purges per update cycle :-) > > > Ed > > On Mon, Mar 6, 2017 at 4:53 PM, Paolo Lucente wrote: > > > > > Hi Ed, > > > > I should maybe add some documentation in this sense. QN reads as the > > Query Number, the amount of queries (read aggregates) to be processed. > > The two numbers you see are respectively: the amount of aggregates that > > actually made it to the backend and the total amount of aggregates in > > memory at this point. In the typical chase where sql_history matches > > sql_refresh_time these numbers should coincide; another case for these > > not coincide is the box running pmacct or the routers (or both) are not > > ntp'ed or not set to the same timezone or so (as Q18 of FAQS says, the > > recommendation in this sense is to run all as UTC); a simple counter- > > measure is to set 'nfacctd_time_new: true' so to assign flows to time- > > bins basing on the arrival time to pmacct rather than the start time of > > the flow (you loose a bit of accuracy in favor of simplicity, depending > > on the use-case this could be allright). ET is the Elapsed Time, the > > time (in seconds) it took to write all to the backend. PID, intuitively > > and only for completeness, is the Process ID of the writer process. > > > > Paolo > > > > On Sun, Mar 05, 2017 at 01:55:54PM -0600, Edward Henigin wrote: > > > I should have said that I know why Sqlite3 generates the error, I just > > > didn't know why nfacctd was performing duplicate inserts :-) > > > > > > Thank you Paolo, increasing sql_cache_entries makes a big difference. > > > Running at 524287 and the problem seems to be gone. > > > > > > Can you tell me (or point me to the documentation) regarding how to read > > > the 'purging' log line? > > > > > > e.g. > > > > > > Mar 5 13:47:04 server nfacctd[28824]: INFO ( ip_dst/sqlite3 ): *** > > Purging > > > cache - END (PID: 28824, QN: 577/284209, ET: 2) *** > > > > > > I'm curious what the QN: 577/284209 part means. > > > > > > > > > > > > On Fri, Mar 3, 2017 at 8:14 PM, Paolo Lucente wrote: > > > > > > > > > > > +1 on Tristan's feedback. Ed, you can check at this propo also: > > > > > > > > https://github.com/pmacct/pmacct/wiki/RDBMS:- > > Customising-the-SQL-database- > > > > indexes > > > > > > > > If commenting out sql_dont_try_update makes things work well then it > > > > means the setup is making use of UPDATE queries. Maybe you need a > > larger > > > > sql_cache_entries value if you reckon from the logs it is purging more > > > > often than once per minute? > > > > > > > > Paolo > > > > > > > > On Fri, Mar 03, 2017 at 11:55:07PM +, Tristan Bendall wrote: > > > > > Hi Edward > > > > > > > > > > I think what is happening here, in database speak, is that the > > primary > > > > key for the new record isn't unique, and that's breaking DB rules. > > > > > > > > > > Basically the DB is trying to add a new record that already exists, > > and > > > > with update turned off, it can't either update the matching record or > > add > > > > another non unique record. > > > > > > > > > > I think you'll need to add a unique field (such as an auto > > incrementing > > > > ID field) then include that in the primary key contstraint in the DB. > > > > > > > > > > > > > > > Tristan > > > > > > > > > > On 3 Mar 2017, at 17:49, Edward Henigin mailto:ed@ > > > > eaohana.com>> wrote: > > > > > > > > > > Hi Paolo, > > > > > > > > > > When enabling sql_dont_try_update: true, I get these errors fairly > > > > continuously: > > > > > > > > > > Mar 3 11:33:30 server nfacctd[10661]: ERROR ( ip_dst/sqlite3 ): > > columns > > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > Mar 3 11:33:33 server nfacctd[10662]: ERROR ( ip_dst/sqlite3 ): > > columns > > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > Mar 3 11:33:37 server nfacctd[10663]: ERROR ( ip_dst/sqlite3 ): > > columns > > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > Mar 3 11:33:44 server nfacctd[10667]: ERROR ( ip_dst/sqlite3 ): > > columns > > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > Mar 3 11:33:47 server nfacctd[10668]: ERROR ( ip_dst/sqlite3 ): > > columns > > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > > > > > > Any suggestions? Per the instructions, we do have these configured: > > > > > > > > > > sql_refresh_time: 60 > > > > > sql_history: 1m > > > > > sql_history_roundoff: m > > > > >
Re: [pmacct-discussion] sql_dont_try_update causes key collisions?
On Mon, 6 Mar 2017 17:48:24 -0600 Edward Henigin wrote: > And as far as updating docs, I might suggest adding to the > sql_dont_try_update key the fact that the sql_cache_entries needs to > be large enough to prevent multiple purges per update cycle :-) Last I looked the docs were on a wiki, so you could do it. :) Regards, Karl Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] sql_dont_try_update causes key collisions?
Paolo, I assume also that the "aggregates to the backend" could be much smaller than the "aggregates in memory" when there is a sql_preprocess in place to filter (e.g. minb) inserts? And as far as updating docs, I might suggest adding to the sql_dont_try_update key the fact that the sql_cache_entries needs to be large enough to prevent multiple purges per update cycle :-) Ed On Mon, Mar 6, 2017 at 4:53 PM, Paolo Lucente wrote: > > Hi Ed, > > I should maybe add some documentation in this sense. QN reads as the > Query Number, the amount of queries (read aggregates) to be processed. > The two numbers you see are respectively: the amount of aggregates that > actually made it to the backend and the total amount of aggregates in > memory at this point. In the typical chase where sql_history matches > sql_refresh_time these numbers should coincide; another case for these > not coincide is the box running pmacct or the routers (or both) are not > ntp'ed or not set to the same timezone or so (as Q18 of FAQS says, the > recommendation in this sense is to run all as UTC); a simple counter- > measure is to set 'nfacctd_time_new: true' so to assign flows to time- > bins basing on the arrival time to pmacct rather than the start time of > the flow (you loose a bit of accuracy in favor of simplicity, depending > on the use-case this could be allright). ET is the Elapsed Time, the > time (in seconds) it took to write all to the backend. PID, intuitively > and only for completeness, is the Process ID of the writer process. > > Paolo > > On Sun, Mar 05, 2017 at 01:55:54PM -0600, Edward Henigin wrote: > > I should have said that I know why Sqlite3 generates the error, I just > > didn't know why nfacctd was performing duplicate inserts :-) > > > > Thank you Paolo, increasing sql_cache_entries makes a big difference. > > Running at 524287 and the problem seems to be gone. > > > > Can you tell me (or point me to the documentation) regarding how to read > > the 'purging' log line? > > > > e.g. > > > > Mar 5 13:47:04 server nfacctd[28824]: INFO ( ip_dst/sqlite3 ): *** > Purging > > cache - END (PID: 28824, QN: 577/284209, ET: 2) *** > > > > I'm curious what the QN: 577/284209 part means. > > > > > > > > On Fri, Mar 3, 2017 at 8:14 PM, Paolo Lucente wrote: > > > > > > > > +1 on Tristan's feedback. Ed, you can check at this propo also: > > > > > > https://github.com/pmacct/pmacct/wiki/RDBMS:- > Customising-the-SQL-database- > > > indexes > > > > > > If commenting out sql_dont_try_update makes things work well then it > > > means the setup is making use of UPDATE queries. Maybe you need a > larger > > > sql_cache_entries value if you reckon from the logs it is purging more > > > often than once per minute? > > > > > > Paolo > > > > > > On Fri, Mar 03, 2017 at 11:55:07PM +, Tristan Bendall wrote: > > > > Hi Edward > > > > > > > > I think what is happening here, in database speak, is that the > primary > > > key for the new record isn't unique, and that's breaking DB rules. > > > > > > > > Basically the DB is trying to add a new record that already exists, > and > > > with update turned off, it can't either update the matching record or > add > > > another non unique record. > > > > > > > > I think you'll need to add a unique field (such as an auto > incrementing > > > ID field) then include that in the primary key contstraint in the DB. > > > > > > > > > > > > Tristan > > > > > > > > On 3 Mar 2017, at 17:49, Edward Henigin mailto:ed@ > > > eaohana.com>> wrote: > > > > > > > > Hi Paolo, > > > > > > > > When enabling sql_dont_try_update: true, I get these errors fairly > > > continuously: > > > > > > > > Mar 3 11:33:30 server nfacctd[10661]: ERROR ( ip_dst/sqlite3 ): > columns > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > Mar 3 11:33:33 server nfacctd[10662]: ERROR ( ip_dst/sqlite3 ): > columns > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > Mar 3 11:33:37 server nfacctd[10663]: ERROR ( ip_dst/sqlite3 ): > columns > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > Mar 3 11:33:44 server nfacctd[10667]: ERROR ( ip_dst/sqlite3 ): > columns > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > Mar 3 11:33:47 server nfacctd[10668]: ERROR ( ip_dst/sqlite3 ): > columns > > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > > > > Any suggestions? Per the instructions, we do have these configured: > > > > > > > > sql_refresh_time: 60 > > > > sql_history: 1m > > > > sql_history_roundoff: m > > > > nfacctd_time_new: true > > > > > > > > After commenting out sql_dont_try_update: true there are no errors > > > operationally. > > > > ___ > > > > pmacct-discussion mailing list > > > > http://www.pmacct.net/#mailinglists > > > > > > > ___ > > > > pmacct-discussion mailing list > > > > http://www.pmacct.ne
Re: [pmacct-discussion] sql_dont_try_update causes key collisions?
Hi Ed, I should maybe add some documentation in this sense. QN reads as the Query Number, the amount of queries (read aggregates) to be processed. The two numbers you see are respectively: the amount of aggregates that actually made it to the backend and the total amount of aggregates in memory at this point. In the typical chase where sql_history matches sql_refresh_time these numbers should coincide; another case for these not coincide is the box running pmacct or the routers (or both) are not ntp'ed or not set to the same timezone or so (as Q18 of FAQS says, the recommendation in this sense is to run all as UTC); a simple counter- measure is to set 'nfacctd_time_new: true' so to assign flows to time- bins basing on the arrival time to pmacct rather than the start time of the flow (you loose a bit of accuracy in favor of simplicity, depending on the use-case this could be allright). ET is the Elapsed Time, the time (in seconds) it took to write all to the backend. PID, intuitively and only for completeness, is the Process ID of the writer process. Paolo On Sun, Mar 05, 2017 at 01:55:54PM -0600, Edward Henigin wrote: > I should have said that I know why Sqlite3 generates the error, I just > didn't know why nfacctd was performing duplicate inserts :-) > > Thank you Paolo, increasing sql_cache_entries makes a big difference. > Running at 524287 and the problem seems to be gone. > > Can you tell me (or point me to the documentation) regarding how to read > the 'purging' log line? > > e.g. > > Mar 5 13:47:04 server nfacctd[28824]: INFO ( ip_dst/sqlite3 ): *** Purging > cache - END (PID: 28824, QN: 577/284209, ET: 2) *** > > I'm curious what the QN: 577/284209 part means. > > > > On Fri, Mar 3, 2017 at 8:14 PM, Paolo Lucente wrote: > > > > > +1 on Tristan's feedback. Ed, you can check at this propo also: > > > > https://github.com/pmacct/pmacct/wiki/RDBMS:-Customising-the-SQL-database- > > indexes > > > > If commenting out sql_dont_try_update makes things work well then it > > means the setup is making use of UPDATE queries. Maybe you need a larger > > sql_cache_entries value if you reckon from the logs it is purging more > > often than once per minute? > > > > Paolo > > > > On Fri, Mar 03, 2017 at 11:55:07PM +, Tristan Bendall wrote: > > > Hi Edward > > > > > > I think what is happening here, in database speak, is that the primary > > key for the new record isn't unique, and that's breaking DB rules. > > > > > > Basically the DB is trying to add a new record that already exists, and > > with update turned off, it can't either update the matching record or add > > another non unique record. > > > > > > I think you'll need to add a unique field (such as an auto incrementing > > ID field) then include that in the primary key contstraint in the DB. > > > > > > > > > Tristan > > > > > > On 3 Mar 2017, at 17:49, Edward Henigin mailto:ed@ > > eaohana.com>> wrote: > > > > > > Hi Paolo, > > > > > > When enabling sql_dont_try_update: true, I get these errors fairly > > continuously: > > > > > > Mar 3 11:33:30 server nfacctd[10661]: ERROR ( ip_dst/sqlite3 ): columns > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > Mar 3 11:33:33 server nfacctd[10662]: ERROR ( ip_dst/sqlite3 ): columns > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > Mar 3 11:33:37 server nfacctd[10663]: ERROR ( ip_dst/sqlite3 ): columns > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > Mar 3 11:33:44 server nfacctd[10667]: ERROR ( ip_dst/sqlite3 ): columns > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > Mar 3 11:33:47 server nfacctd[10668]: ERROR ( ip_dst/sqlite3 ): columns > > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > > > Any suggestions? Per the instructions, we do have these configured: > > > > > > sql_refresh_time: 60 > > > sql_history: 1m > > > sql_history_roundoff: m > > > nfacctd_time_new: true > > > > > > After commenting out sql_dont_try_update: true there are no errors > > operationally. > > > ___ > > > 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 > > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] sql_dont_try_update causes key collisions?
I should have said that I know why Sqlite3 generates the error, I just didn't know why nfacctd was performing duplicate inserts :-) Thank you Paolo, increasing sql_cache_entries makes a big difference. Running at 524287 and the problem seems to be gone. Can you tell me (or point me to the documentation) regarding how to read the 'purging' log line? e.g. Mar 5 13:47:04 server nfacctd[28824]: INFO ( ip_dst/sqlite3 ): *** Purging cache - END (PID: 28824, QN: 577/284209, ET: 2) *** I'm curious what the QN: 577/284209 part means. On Fri, Mar 3, 2017 at 8:14 PM, Paolo Lucente wrote: > > +1 on Tristan's feedback. Ed, you can check at this propo also: > > https://github.com/pmacct/pmacct/wiki/RDBMS:-Customising-the-SQL-database- > indexes > > If commenting out sql_dont_try_update makes things work well then it > means the setup is making use of UPDATE queries. Maybe you need a larger > sql_cache_entries value if you reckon from the logs it is purging more > often than once per minute? > > Paolo > > On Fri, Mar 03, 2017 at 11:55:07PM +, Tristan Bendall wrote: > > Hi Edward > > > > I think what is happening here, in database speak, is that the primary > key for the new record isn't unique, and that's breaking DB rules. > > > > Basically the DB is trying to add a new record that already exists, and > with update turned off, it can't either update the matching record or add > another non unique record. > > > > I think you'll need to add a unique field (such as an auto incrementing > ID field) then include that in the primary key contstraint in the DB. > > > > > > Tristan > > > > On 3 Mar 2017, at 17:49, Edward Henigin mailto:ed@ > eaohana.com>> wrote: > > > > Hi Paolo, > > > > When enabling sql_dont_try_update: true, I get these errors fairly > continuously: > > > > Mar 3 11:33:30 server nfacctd[10661]: ERROR ( ip_dst/sqlite3 ): columns > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > Mar 3 11:33:33 server nfacctd[10662]: ERROR ( ip_dst/sqlite3 ): columns > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > Mar 3 11:33:37 server nfacctd[10663]: ERROR ( ip_dst/sqlite3 ): columns > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > Mar 3 11:33:44 server nfacctd[10667]: ERROR ( ip_dst/sqlite3 ): columns > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > Mar 3 11:33:47 server nfacctd[10668]: ERROR ( ip_dst/sqlite3 ): columns > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > > > Any suggestions? Per the instructions, we do have these configured: > > > > sql_refresh_time: 60 > > sql_history: 1m > > sql_history_roundoff: m > > nfacctd_time_new: true > > > > After commenting out sql_dont_try_update: true there are no errors > operationally. > > ___ > > 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 > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] sql_dont_try_update causes key collisions?
+1 on Tristan's feedback. Ed, you can check at this propo also: https://github.com/pmacct/pmacct/wiki/RDBMS:-Customising-the-SQL-database-indexes If commenting out sql_dont_try_update makes things work well then it means the setup is making use of UPDATE queries. Maybe you need a larger sql_cache_entries value if you reckon from the logs it is purging more often than once per minute? Paolo On Fri, Mar 03, 2017 at 11:55:07PM +, Tristan Bendall wrote: > Hi Edward > > I think what is happening here, in database speak, is that the primary key > for the new record isn't unique, and that's breaking DB rules. > > Basically the DB is trying to add a new record that already exists, and with > update turned off, it can't either update the matching record or add another > non unique record. > > I think you'll need to add a unique field (such as an auto incrementing ID > field) then include that in the primary key contstraint in the DB. > > > Tristan > > On 3 Mar 2017, at 17:49, Edward Henigin > mailto:e...@eaohana.com>> wrote: > > Hi Paolo, > > When enabling sql_dont_try_update: true, I get these errors fairly > continuously: > > Mar 3 11:33:30 server nfacctd[10661]: ERROR ( ip_dst/sqlite3 ): columns > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > Mar 3 11:33:33 server nfacctd[10662]: ERROR ( ip_dst/sqlite3 ): columns > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > Mar 3 11:33:37 server nfacctd[10663]: ERROR ( ip_dst/sqlite3 ): columns > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > Mar 3 11:33:44 server nfacctd[10667]: ERROR ( ip_dst/sqlite3 ): columns > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > Mar 3 11:33:47 server nfacctd[10668]: ERROR ( ip_dst/sqlite3 ): columns > peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 > > Any suggestions? Per the instructions, we do have these configured: > > sql_refresh_time: 60 > sql_history: 1m > sql_history_roundoff: m > nfacctd_time_new: true > > After commenting out sql_dont_try_update: true there are no errors > operationally. > ___ > 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] sql_dont_try_update causes key collisions?
Hi Edward I think what is happening here, in database speak, is that the primary key for the new record isn't unique, and that's breaking DB rules. Basically the DB is trying to add a new record that already exists, and with update turned off, it can't either update the matching record or add another non unique record. I think you'll need to add a unique field (such as an auto incrementing ID field) then include that in the primary key contstraint in the DB. Tristan On 3 Mar 2017, at 17:49, Edward Henigin mailto:e...@eaohana.com>> wrote: Hi Paolo, When enabling sql_dont_try_update: true, I get these errors fairly continuously: Mar 3 11:33:30 server nfacctd[10661]: ERROR ( ip_dst/sqlite3 ): columns peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 Mar 3 11:33:33 server nfacctd[10662]: ERROR ( ip_dst/sqlite3 ): columns peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 Mar 3 11:33:37 server nfacctd[10663]: ERROR ( ip_dst/sqlite3 ): columns peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 Mar 3 11:33:44 server nfacctd[10667]: ERROR ( ip_dst/sqlite3 ): columns peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 Mar 3 11:33:47 server nfacctd[10668]: ERROR ( ip_dst/sqlite3 ): columns peer_ip_src, iface_in, ip_dst, stamp_inserted are not unique#012 Any suggestions? Per the instructions, we do have these configured: sql_refresh_time: 60 sql_history: 1m sql_history_roundoff: m nfacctd_time_new: true After commenting out sql_dont_try_update: true there are no errors operationally. ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists