![]() If the SQLITE_OPEN_NOMUTEX flag is set, then the database connection opens in the multi-thread threading mode as long as the single-thread mode has not been set at compile-time or start-time. What page size, cache size, locking mode, and journal mode are the most performant for a single thread accessing a single SQLite database?įirst, some information about the parameters. Other operating system may produce other results.Goal: Find out how different parameters affect SQLite performance. Please note that these results are based on a Windows system. If there’s something (terribly) wrong with this analysis, please leave a comment below. If you do about the same amount of reading and writing, use multiple connections. If you do primarily writing, use a shared connection.… and you have more threads than CPUs (cores): use shared connection.… and the thread count is If you only have one thread, it doesn’t matter (obviously).Using read-only connections doesn’t affect the read performance. If memory is not an issue, shared caches shouldn’t be used as they may decrease read performance.Enabling WAL for a database gives a significant performance boost for all read and write operations.Conclusions ∞Īssuming, my code doesn’t contain any errors that are affecting the results in a significant way, the following conclusions can be drawn: Select-statements.csv (file containing data for charts in this section) Test: read-only ∞įirst test is about whether opening a database connection in read-only mode ( SQLITE_OPEN_READONLY) does result in any performance benefit.Īs you can see, in both cases using one connection per threads and using WAL provides the best performance. Each thread randomly reads a data row and then obtains all four values stored in it. Let’s start with the tests only reading data (i.e. WAL: Whether the connection(s) use a database in WAL (write-ahead logging) journal mode.įilled table: Whether the table to read from is empty or filled (not examined in this report due to missing data I should mention though that trying to read from an empty table is significant slower than reading from a filled table).Shared cache: Whether all connections share the same cache ( SQLITE_OPEN_SHAREDCACHE), or whether each connection has its own cache.Read-only: Whether the connection is opened in read-only or read-write mode ( SQLITE_OPEN_READONLY).Shared connections use SQLITE_OPEN_FULLMUTEX (serialized), multi connections use SQLITE_OPEN_NOMUTEX (multithread). multi connection: Whether all threads share the same database connection, or whether every thread has its own connection (to the same database though). Test Parameters ∞Įach test comprises of a certain combination of the following test parameters: Note: In all tests, the CPU was the limiting factor – not the hard drive. The first batch of tests simulated read access, the second batch simulated write access, and the third batch simulated both concurrently. Write access: Simulated by inserting random values into the table. Read access: Simulated by repeatedly selecting a random row from the table and reading all four values.There are two kinds of concurrent access: For SELECT tests this table was filled with 50,000 rows of random data. In each test scenario, the SQLite database contained only one table with four columns. Thus, the overall test duration was about 2 days. There are 40 tests in the suite testing various combinations of the available test parameters (see below).Įach test was executed for 1 to 20 threads to test concurrency.Įach test for a certain thread count (1, 2, 3, …) ran 30 seconds and was repeated 10 times. ![]() The tests ran on a Notebook featuring an Intel Core 2 Duo (2.53 GHz) and 8 GB memory. 5.2 Test: Shared or multiple connections.5.1 Assumption: WAL improves general performance. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |