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 <[email protected]> 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 <[email protected]> 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 <[email protected]<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
