SQLite utilizes up to 90% of the my SSD disk when SHIB coin riches its local peaks with more than 10.000 – 18.000 trades/min that is 166 – 300 trades/second. My C++ app inserts 64-bit values to tables like this:
CREATE TABLE trades (time INT PRIMARY KEY NOT NULL, price REAL, volume REAL) WITHOUT ROWID;
so it is only 3984 – 7200 bytes/second. Totally across all markets there can be about 30.000 trades/min that is 500 trades/second and only 12.000 bytes/second and it is enough to slow down my SQLite database.
I use WAL with 4GB cache:
PRAGMA journal_mode = WAL;
PRAGMA cache_size = -4 * 1024 * 1024;
and do all the inserts in a transaction once a 5 seconds into 500+ tables (a separate table for each market) with a prepared statement.
See the screenshots of my app below:
Links:
In WAL mode, a writer and readers do not block each other, but there is still only one writer allowed, see https://dba.stackexchange.com/a/45400/198776
https://stackoverflow.com/questions/35717263/how-threadsafe-is-sqlite3
Sqlite is not a particularly good match for any problem that has multithreaded (or multi-process) access to a database, and the “database is locked” message is, in my experience, pretty much unavoidable in such a scenario. You can get better performance by accessing the database from just a single thread, and combining inserts into a single statement (i.e. use a multivalued insert). If that is not enough, hammering a sqlite database with multiple threads will not help performance at all. If you have a use case where such an approach seems necessary, you should consider installing a database built for that purpose, like PostgreSQL.
https://stackoverflow.com/questions/1680249/how-to-use-sqlite-in-a-multi-threaded-application
Make sure sqlite is compiled with the multi threaded flag.
You must call open on your sqlite file to create a connection on each thread, don’t share connections between threads.
SQLite has a very conservative threading model, when you do a write operation, which includes opening transactions that are about to do an INSERT/UPDATE/DELETE, other threads will be blocked until this operation completes.
If you don’t use a transaction, then transactions are implicit, so if you start a INSERT/DELETE/UPDATE, sqlite will try to acquire an exclusive lock, and complete the operation before releasing it.
https://stackoverflow.com/questions/39785185/best-way-to-write-data-to-a-sqlite-from-multiple-threads
SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.
https://stackoverflow.com/questions/14234007/sqlite-wal-mode-with-multiple-transactions-in-multiple-threads
SQLite, in its current versions anyway, does not support concurrent writes. There can be multiple reader processes simultaneously, but at most one writer. (See FAQ entry #5
With Write-Ahead Logging enabled, that fact does not change. WAL enables more concurrency:
WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
But not write concurrency:
Writers merely append new content to the end of the WAL file. Because writers do nothing that would interfere with the actions of readers, writers and readers can run at the same time. However, since there is only one WAL file, there can only be one writer at a time.
(Above excerpts from the the documentation on WAL.)
https://www.sqlite.org/threadsafe.html
Run-time selection of threading mode
If single-thread mode has not been selected at compile-time or start-time, then individual database connections can be created as either multi-thread or serialized. It is not possible to downgrade an individual database connection to single-thread mode. Nor is it possible to escalate an individual database connection if the compile-time or start-time mode is single-thread.
The threading mode for an individual database connection is determined by flags given as the third argument to sqlite3_open_v2(). The SQLITE_OPEN_NOMUTEX flag causes the database connection to be in the multi-thread mode and the SQLITE_OPEN_FULLMUTEX flag causes the connection to be in serialized mode. If neither flag is specified or if sqlite3_open() or sqlite3_open16() are used instead of sqlite3_open_v2(), then the default mode determined by the compile-time and start-time settings is used.
Transactions in SQLite are SERIALIZABLE. Changes made in one database connection are invisible to all other database connections prior to commit. A query sees all changes that are completed on the same database connection prior to the start of the query, regardless of whether or not those changes have been committed.
Can’t see SQLite database changes on a database open by multiple processes
https://stackoverflow.com/questions/43949228/cant-see-sqlite-database-changes-on-a-database-open-by-multiple-processes
So for changes to be visible, the writing connection must end its transaction, and the reading connection must not have started its own transaction before that. (When using automatic transactions, ensure that statements are reset or finalized.)
https://www.sqlite.org/c3ref/c_open_autoproxy.html
SQLITE_OPEN_NOMUTEX
https://stackoverflow.com/questions/26600948/sqlite-temp-view-table-in-background-thread
Temporary tables/views are local to the database connection.
Is there AUTO INCREMENT in SQLite?
https://stackoverflow.com/questions/7905859/is-there-auto-increment-in-sqlite
You get one for free, called ROWID. This is in every SQLite table whether you ask for it or not.
If you include a column of type INTEGER PRIMARY KEY, that column points at (is an alias for) the automatic ROWID column.
ROWID (by whatever name you call it) is assigned a value whenever you INSERT a row, as you would expect. If you explicitly assign a non-NULL value on INSERT, it will get that specified value instead of the auto-increment. If you explicitly assign a value of NULL on INSERT, it will get the next auto-increment value.
Valid range of SQLite rowid?
https://stackoverflow.com/questions/8966667/valid-range-of-sqlite-rowid
Row IDs are 64-bit signed integers, so the maximum is 0x7FFFFFFFFFFFFFFFLL. But unless a negative or zero row ID has been entered explicitly, auto-generated row IDs are always greater than zero. If you can be certain that row IDs will always be generated automatically then zero or -1 would be safe values to for error status returns.
Thinking further, I realise that the sqlite3_last_insert_rowid API call returns zero if nothing has ever been inserted into the table, thus making zero a de-facto “invalid” row ID.
In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.
https://www.sqlite.org/autoinc.html#:~:text=In%20SQLite%2C%20a%20column%20with,a%2064%2Dbit%20signed%20integer.
On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.