I guess the big question is, is SQLite3 really appropriate to use in a
"light weight" but general purpose way?

Is it fast enough for real-world use? Are there bugs or limitations that
prevent it from really working well?

My particular issues with it, given this configuration:

DROP TABLE host_srcif;
CREATE TABLE host_srcif (
        peer_ip_src CHAR(15) NOT NULL DEFAULT '',
        ip_dst CHAR(15) NOT NULL DEFAULT '',
        packets INT UNSIGNED NOT NULL,
        stamp_inserted DATETIME NOT NULL,
        stamp_updated DATETIME,
        PRIMARY KEY (peer_ip_src, iface_in, ip_dst, stamp_inserted)

! nfacctd configuration file
plugins: sqlite3
aggregate: peer_src_ip,in_iface,dst_host
sql_optimize_clauses: true
sql_db: /var/opt/pmacct/g2.db
sql_table: host_srcif
sql_refresh_time: 120
sql_history: 2m
sql_history_roundoff: m
sql_dont_try_update: true
nfacctd_time_new: true

Produced "columns are not unique" errors at a rate of 31 per second:

ERROR ( default/sqlite3 ): columns peer_ip_src, iface_in, ip_dst,
stamp_inserted are not unique#012

combined with "database is locked" errors at around 16 per second:

ERROR ( default/sqlite3 ): database is locked#012

so I commented out "sql_dont_try_update: true", and the "columns are not
unique" errors are gone, but the "database is locked" errors continue.

BUT, even despite those errors, there are lots of records in the database:

sqlite> select stamp_inserted, count(*) from host_srcif group by
2017-02-09 13:42:00|143277
2017-02-09 13:50:00|135071
2017-02-09 15:50:00|122825
2017-02-09 15:52:00|118091

The 13:42:00 and 13:50:00 periods were when nfacctd produced the "columns
are not unique" error, and the 15:50:00 and 15:52:00 periods were when
nfacctd produced the "database is locked" error. So despite the errors,
records are certainly making it in.

That said, the IO load on the server is massively higher in the 2nd

So what do I do? Apparently data can go in despite errors, but data must be
getting lost. How do I get all the data to go in without losing any?

And finally my last question. I have little experience with SQLite, I
understand it's a file-based database. I noticed in the 2nd period
(15:50:00+, above) that if I went into the SQLite CLI to run queries (just
SELECTs, just read-only), that I often could not perform my selects because
it said the database was locked. Is it true that an SQLite database cannot
be read while a write is in progress? That would seem to limit the
usefulness of the system. Is there a config parameter in pmacct/nfacctd
that might make it "better" somehow?


pmacct-discussion mailing list

Reply via email to