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 <pa...@pmacct.net> 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 <pa...@pmacct.net> 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 +0000, 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 <e...@eaohana.com<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

Reply via email to