Re: [pmacct-discussion] sql_dont_try_update causes key collisions?

[Paolo Lucente]

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  > > > 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
> > > > > 

Re: [pmacct-discussion] sql_dont_try_update causes key collisions?

[Karl O. Pinc]

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 Lucente]

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  > 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?

[Paolo Lucente]

+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 
> > 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?

[Tristan Bendall]

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 
> 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