ACID and speed
As stated during our
recent benchmarks, the default SQlite3 write speed is quite slow,
when running on a normal hard drive. By default, the engine will pause after
issuing a OS-level write command. This guarantees that the data is written to
the disk, and features the
ACID properties of the database
ACID is an acronym for "Atomicity Consistency Isolation
Durability" properties, which guarantee that database transactions are
processed reliably: for instance, in case of a power loss or hardware failure,
the data will be saved on disk in a consistent way, with no potential loss of
You can overwrite this default behavior by setting the
TSQLDataBase.Synchronous property to
smOff instead of
smFull setting. When
Synchronous is set
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 same tests are performed with
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). We'll show below the detailed benchmark results.
So depending on your application requirements, you may switch Synchronous setting to off, to enhance server-side responsiveness.
Disabling the disk flush
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
instance, so will affect all tables handled by the
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,''); (Props.MainConnection as TSQLDBSQLite3Connection).Synchronous := smOff;
Now, some data, created with the latest version of our benchmark sample.
(ext file full)
(ext file off)
You can note that the writing speed has been increased to about 400 objects
per second with
Synchronous := smOff, and is now at the level of
Oracle, as stated by "SQLite3 (file off)" and "SQLite3 (ext file off)
Even within a transaction, write speed increased by a factor of two. Worth a try, isn't it?
Here are the corresponding read speed.
Unsurprisingly, changing the synchronization from
smOff does not affect the reading speed.
(ext file full)
(ext file off)
We did make some small code enhancements for speed, in the JSON serializer
for instance, since the previous framework release.
So all results, including
TObjectList, did even increase a
Conclusion: do your homework, i.e. do your backup
By default, the slow but truly
ACID setting will be used with
mORMot, just as usual.
We do not change this policy, since it will ensure best safety, in the expense of slow writing outside a transaction.
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. Consider
using an external and dedicated database (like Oracle or MS SQL) if
your security expectations are very high, and if the default
:= smFull 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.BackupGZ methods for a fast backup of a running
database. Adding a backup feature on the server side is as simple as
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.
Feedback is welcome on our forum.