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