SQLite3 performance benchmark
Here we insert 5,000 rows of data, with diverse scenarios:
- 'Direct' stands for a individual
Client.Add()
insertion; - 'Batch' mode regrouping data rows;
- 'Trans' indicates that all insertion is nested within a transaction - which makes a great difference, e.g. with a SQlite3 database.
Benchmark was run on a Core i7 notebook, with standard SSD.
So it was a development environment, very similar to low-cost production site,
not dedicated to give best performance.
During the process, CPU was noticeable used only for SQLite3 in-memory
and TObjectList - most of the time, the bottleneck is not the CPU, but
the storage or network.
As a result, rates and timing may vary depending on network and server load,
but you get results similar to what could be expected on customer side, with an
average hardware configuration.
Direct | Batch | Trans | Batch Trans | |
SQLite3 (file full) | 503 | 399 | 96391 | 123064 |
SQLite3 (file off) | 853 | 930 | 99534 | 130907 |
SQLite3 (file off exc) | 31829 | 35798 | 101874 | 132752 |
SQLite3 (mem) | 85803 | 109641 | 103976 | 135332 |
TObjectList (static) | 321089 | 548365 | 312031 | 547105 |
TObjectList (virtual) | 314366 | 513136 | 316676 | 571232 |
SQLite3 (ext full) | 451 | 511 | 12092 | 137249 |
SQLite3 (ext off) | 971 | 909 | 108133 | 144475 |
SQLite3 (ext off exc) | 42805 | 51256 | 113155 | 150829 |
SQLite3 (ext mem) | 97344 | 121400 | 113229 | 153256 |
Due to its ACID implementation, SQLite3 process on file waits for the hard-disk to have finished flushing its data, therefore it is the reason why it is slower than other engines at individual row insertion (less than 10 objects per second with a mechanical hardrive instead of a SDD) outside the scope of a transaction.
So if you want to reach the best writing performance in your application
with the default engine, you should better use transactions and regroup all
writing into services or a BATCH process.
Another possibility could be to execute DB.Synchronous := smOff
and/or DB.LockingMode := lmExclusive
at SQLite3 engine
level before process: in case of power loss at wrong time it may corrupt the
database file, but it will increase the rate by a factor of 50 (with hard
drive), as stated by the "off" and off exc" rows of the table - see below.
Now the same data is retrieved via the ORM layer:
- 'By one' states that one object is read per call (ORM generates a
SELECT * FROM table WHERE ID=?
forClient.Retrieve()
method); - 'All *' is when all 5000 objects are read in a single call (i.e. running
SELECT * FROM table
from aFillPrepare()
method call), either forced to use the virtual table layer, or with direct static call.
Here are some reading speed values, in objects/second:
By one | All Virtual | All Direct | |
SQLite3 (file full) | 26936 | 514456 | 531858 |
SQLite3 (file off) | 27116 | 538735 | 428302 |
SQLite3 (file off exc) | 122417 | 541125 | 541653 |
SQLite3 (mem) | 119314 | 539781 | 545494 |
TObjectList (static) | 303398 | 529661 | 799232 |
TObjectList (virtual) | 308109 | 403323 | 871080 |
SQLite3 (ext full) | 137525 | 264690 | 546806 |
SQLite3 (ext off) | 134807 | 262123 | 531011 |
SQLite3 (ext off exc) | 133936 | 261574 | 536941 |
SQLite3 (ext mem) | 136915 | 258732 | 544069 |
The SQLite3 layer gives amazing reading results, which makes it a
perfect fit for most typical ORM use. When running with DB.LockingMode :=
lmExclusive
defined (i.e. "off exc" rows), reading speed is very high,
and benefits from exclusive access to the database file - see below.
External database access is only required when data is expected to be shared
with other processes.
For both writing and reading, TObjectList
/
TSQLRestServerStaticInMemory
engine gives impressive results, but
has the weakness of being in-memory, so it is not ACID by design, and the data
has to fit in memory. Note that indexes are available for IDs and stored
AS_UNIQUE
properties.
Synchronous writing
You can overwrite the first default ACID behavior by setting the
TSQLDataBase.Synchronous
property to smOff
instead of
the default smFull
setting.
When Synchronous
is set to smOff
, SQLite
continues without syncing as soon as it has handed data off to the operating
system. If the application running SQLite crashes, the data will be
safe, but the database might become corrupted if the operating system crashes
or the computer loses power before that data has been written to the disk
surface. On the other hand, some operations are as much as 50 or more times
faster with this setting.
When the tests performed during benchmarking use Synchronous :=
smOff
, "Write one" speed is enhanced from 8-9 rows per second into about
400 rows per second, on a physical hard drive (SSD or NAS drives may not suffer
from this delay).
So depending on your application requirements, you may switch Synchronous setting to off.
To change the main SQLite3 engine synchronous parameter, you may code for instance:
Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,'');
Client.Server.DB.Synchronous := smOff;
Note that this setting is common to a whole TSQLDatabase
instance, so will affect all tables handled by the
TSQLRestServerDB
instance.
But if you defined some SQLite3 external tables, you can define the setting for a particular external connection, for instance:
Props := TSQLDBSQLite3ConnectionProperties.Create(DBFileName,'''','');
VirtualTableExternalRegister(Model,TSQLRecordSample,Props,'SampleRecord');
Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,'');
TSQLDBSQLite3Connection(Props.MainConnection).Synchronous := smOff;
File locking
You can overwrite the second default ACID behavior by setting the
TSQLDataBase.LockingMode
property to LockingMode
instead of the default lmNormal
setting.
When LockingMode
is set to lmExclusive
,
SQLite will lock the database file for exclusive use during the whole
session. It will prevent other processes (e.g. database viewer tools) to access
the file at the same time, but small write transactions will be much faster, by
a factor usually greater than 40. Bigger transactions involving several
hundredths/thousands of INSERT won't be accelerated - but individual insertions
will have a major speed up.
To change the main SQLite3 engine locking mode parameter, you may code for instance:
Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,'');
Client.Server.DB.LockingMode := lmExclusive;
Note that this setting is common to a whole TSQLDatabase
instance, so will affect all tables handled by the
TSQLRestServerDB
instance.
But if you defined some SQLite3 external tables, you can define the setting for a particular external connection, for instance:
Props := TSQLDBSQLite3ConnectionProperties.Create(DBFileName,'''','');
VirtualTableExternalRegister(Model,TSQLRecordSample,Props,'SampleRecord');
Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,'');
TSQLDBSQLite3Connection(Props.MainConnection).LockingMode := lmExclusive;
In fact, exclusive file locking improves the reading speed by a factor of 4
(in case of individual row retrieval).
As such, defining LockingMode := lmExclusive
without
Synchronous := smOff
could be of great benefit for a server which
purpose is mainly to serve ORM content to clients.
Performance tuning
By default, the slow but truly ACID
setting will be used with
mORMot, just as with SQlite3.
We do not change this policy (as FireDAC library does, for instance),
since it will ensure best safety, in the expense of slow writing outside a
transaction.
The best performance will achieved by combining the two previous options, as such:
Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,''); Client.Server.DB.LockingMode := lmExclusive; Client.Server.DB.Synchronous := smOff;
Or, for external tables:
Props := TSQLDBSQLite3ConnectionProperties.Create(DBFileName,'''',''); VirtualTableExternalRegister(Model,TSQLRecordSample,Props,'SampleRecord'); Client := TSQLRestClientDB.Create(Model,nil,MainDBFileName,TSQLRestServerDB,false,''); TSQLDBSQLite3Connection(Props.MainConnection).Synchronous := smOff; TSQLDBSQLite3Connection(Props.MainConnection).LockingMode := lmExclusive;
If you can afford loosing some data in very rare border case, or if you are
sure your hardware configuration is safe (e.g. if the server is connected to a
power inverter and has RAID disks) and that you have backups at hand, setting
Synchronous := smOff
would help your application scale for
writing. Setting LockingMode := lmExclusive
will benefit of both
writing and reading speed.
Consider using an external and dedicated database (like Oracle or MS
SQL) if your security expectations are very high, and if the default safe but
slow setting is not enough for you.
In all cases, do not forget to perform backups as often as possible (at
least several times a day).
You may use TSQLRestServerDB.Backup
or
TSQLRestServerDB.BackupGZ
methods for a fast backup of a running
database. Adding a backup feature on the server side is as simple as
running:
Client.Server.BackupGZ(MainDBFileName+'.gz');
Server will stop working during this phase, so a lower-level backup mechanism could be used instead, if you need 100% of service availability. Using an external database would perhaps keep you main mORMot database small in size, so that its backup time will remain unnoticeable on the client side.
Note that with the current implementation, low-level backup is not working as expected on the Win64 platform. The error seems to be at the SQlite3 64 bit library level, since it is not able to release all internal instance statements before backup. We were not able to fix this issue yet.
Feedback is welcome on our forum, as usual.