Hello,
I'm having a big performance problem while trying to use sqlite as a
data output for my application. The application writes out a bunch of
packets relatively fast. There are about 10 different types of them
with all but one sizing under 100 bytes. Those small ones I write out
roughly once per second or less. The big one is the "real" data that
gets logged from ~10-1000x per second. It's size can be from 2-3kb to
32kb and it's size depends on other parameters that don't change very
often, generally not more than every few minutes. Raw data output rate
is at most ~8MiB/s and generally is around 3-5. At the time of writing
the files are never altered, just a bunch of data gets added to them
up to around 1GB is reached, then a new file is created.
When using my current flat-file output it doesn't cause almost any
measurable system load. When using the DB it pretty much halts the
application without causing significant CPU load. The data still gets
logged to DB but at a fraction of the speed it should be (~1MB per 15s
vs 5MB/s). I'm quite new to sqlite so I'm not really sure how to
benchmark it or how to know what is causing the slowdown. So far it
seems if I disable saving the big packets it works perfectly. The
overhead of using SQLite is needed to be absolutely minimal as the
application itself is doing a whole lot of raw computations already.
Flat file output has almost zero overhead but any modifications to
file format make things quite a bit complicated.
The big packet table is created like this:
char *errMsg = 0;
sqlite3_exec(db,
"CREATE TABLE Data ( \
NumRows INTEGER NOT NULL, \
NumColumns INTEGER NOT NULL, \
RowSize FLOAT NOT NULL, \
Begin FLOAT NOT NULL, \
End FLOAT NOT NULL, \
DataBegin FLOAT NOT NULL, \
DataEnd FLOAT NOT NULL, \
Timestamp DOUBLE NOT NULL, \
PacketID INTEGER NOT NULL, \
Data BLOB NOT NUlL, \
ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)",
0,
0,
&errMsg);
Data is inserted with this piece of code:
DataPing* ping = packet.dataPing;
const string queryStr = "INSERT INTO DataPing (NumRows,
NumColumns, Begin, End, DataBegin, DataEnd, Timestamp, PacketID,
Data)"
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
sqlite3_stmt* query(NULL);
long rc = SQLITE_OK;
if (query == 0) {
rc = sqlite3_prepare_v2(db, queryStr.c_str(), queryStr.size(),
&query, NULL);
if (rc != SQLITE_OK) {
TRACE_PRINT("error", ("SQL error: %s", sqlite3_errmsg(db)))
}
}
sqlite3_bind_int( query, 1, ping->numRows);
sqlite3_bind_double( query, 2, ping->numColumns);
sqlite3_bind_double( query, 3, ping->begin);
sqlite3_bind_double( query, 4, ping->end);
sqlite3_bind_int( query, 5, ping->dataBegin);
sqlite3_bind_double( query, 6, packet.dataEnd);
sqlite3_bind_int( query, 7, packet.timestamp);
sqlite3_bind_int( query, 8, packet.packetId);
sqlite3_bind_blob( query, 9, &ping->data[0],
ping->numRows*ping->numColumns*sizeof(ping->data[0]), 0);
if (rc != SQLITE_OK) {
TRACE_PRINT("error", ("SQL error: %s", sqlite3_errmsg(db)))
}
rc = sqlite3_step(query);
if (rc != SQLITE_OK && rc!=SQLITE_DONE) {
TRACE_PRINT("error", ("SQL error: %d: %s", rc, sqlite3_errmsg(db)))
}
rc = sqlite3_reset(query);
Basic data flow is program receives packet -> packet is parsed ->
packet is logged -> data from packet is used in a lot of computations
-> packet is destroyed (memory freed)
I have no idea how to fine-tune sqlite behavior, if I'm doing the
querys correctly or if I'm doing something else wrong. I tried to use
transactions by doing a pair of end/begin queries once per second but
it didn't seem to change much. Data got saved just fine but it was
still horribly slow. My best guess at the moment is that program hangs
because of excessive file synchronization. Anyone has any better
guesses what could be the problem and how to solve them?
--
Kalle Last
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
