Hi, We have a C# application which logs data in real-time to sqlite files. The application is multi-threaded, and typically logs 200-500 data streams simultaneously. The logging rate per stream is 1Hz to 1000Hz, depending on the type of data coming in. Each data stream is logged to its own db3 file. This means that there are generally 200-500 connections active, and we run with connection pooling on. Each data stream typically buffers for 2-3 seconds then writes the buffer to the respective db3 file. All processing with the application is multi-threaded.
I am currently looking at updating the version of System.Data.SQLite we are running, but running into some issues. The application currently runs 1.0.80.0, and I am testing with 1.0.105.1, the code changes to our app were fairly minimal. The reason we were looking to upgrade is that performance profiling was showing a high degree of lock contention within the old driver code, primarily due to a static lock in the old driver. The newer driver uses locks based on the connection handle, which removes the lock contention we were seeing, which leads to a significant improvement in CPU usage. When logging with the old driver there are no issues, other than the lock contention. When logging with the new driver, the system sometimes locks up, or marks one or more of the db3 as malformed. After much testing, this seems to occur when we prepare statements on the sqlite command. If I disable the IDbCommand.Prepare() calls, the driver appears to be stable. Structurally our code is fairly standard, and makes extensive use of "using" blocks. We only hold the connection and transaction open for the minimal time possible, releasing the connection back to the pool. Using (connection) Open connection Using (begin transaction) Using (create command) Prepare insert command Transfer data Commit transaction One of the stability tests I run involves 2 processes logging to the same data sets (db3) files, because we do have multi-process access onto the db3 files. Connections are configured to use journal mode "persist". Should we not use prepare calls when running connection pooling, or do you have any other recommendations we should take note of. Kind Regards, Barry Roberts. b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
