Category Archives: SQLite

Writing market data to SQLite database

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:

(more…)

Measuring SQLite insertion performance with C++ code

I did a quick Google search on “SQLite performance” and found the following:

Then to benchmark SQLite performance by myself I used the following C++ code that inserts 1000 batches of 1000 000 rows to a single table with an integer primary key:

(more…)

Compiling GDAL on Ubuntu Linux with SQLite and MySQL support

Fist install MySQL client libraries and check their location:

apt-get install libmysqlclient-dev
find / -name '*libmysqlclient*'

Then install SQLite:

sudo apt-get install sqlite3 libsqlite3-dev

Extract GDAL sources and configure supported modules (MySQL and SQLite are not compiled by default, so we need to specify them explicitly):

unzip gdal211.zip
cd gdal-2.1.1/
./configure --with-sqlite3 --with-mysql

this will output “MySQL support: yes” and “SQLite support: yes” along with other information.

(more…)