Synopse Open Source

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 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,'');
(Props.MainConnection as TSQLDBSQLite3Connection).Synchronous := smOff;

Now, some data, created with the latest version of our benchmark sample.

Insertion speed

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) columns.
Even within a transaction, write speed increased by a factor of two. Worth a try, isn't it?

Read speed

Here are the corresponding read speed.
Unsurprisingly, changing the synchronization from smFull to smOff does not affect the reading speed.

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 little bit.

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 Synchronous := 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.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.

Feedback is welcome on our forum.