Strange performance issue with SQlite3 as external DB with FPC

ab · 2015-02-08 18:43:38

We have still a strange performance issue, on both FPC and CrossKylix, when using external SQLite3 tables.
There is even some issues on Windows, with FPC.
It is more than 10 times slower than the Delphi executable!

I tried to run the tests on SQLITE_MEMORY_DATABASE_NAME instead of files, and it is just as slow.
So it is not a problem of disk access.
There is a single SQLite3 connection on each file, and we ensured that Synchronous := smOff and LockingMode := lmExclusive.
The CPU is 100% during the tests.

Here are the numbers with FPC under Linux (internal statically linked Sqlite3 engine 3.8.8.2):
- External via REST: 170,354 assertions passed 1.47s
- External via virtual table: 170,354 assertions passed 15.20s
- External via REST with change tracking: 180,454 assertions passed 23.42s

And with CrossKylix (external libsqlite3.so.0 3.8.2):
- External via REST: 170,354 assertions passed 15.93s
- External via virtual table: 170,354 assertions passed 29.47s
- External via REST with change tracking: 180,454 assertions passed 38.08s

Where with Delphi (internal statically linked Sqlite3 3.8.8.2 engine) I got:
- External via REST: 170,354 assertions passed 758.77ms
- External via virtual table: 170,354 assertions passed 1.28s
- External via REST with change tracking: 180,454 assertions passed 2.10s

And With FPC under Windows (internal statically linked Sqlite3 3.8.8.2 engine):
- External via REST: 170,354 assertions passed 12.63s
- External via virtual table: 170,354 assertions passed 13.11s
- External via REST with change tracking: 180,454 assertions passed 13.98s

Direct SQLite3 access is just as fast on all targets, perhaps a little faster
Just the "external" SQlite3 access has issues.

I just do not understand those numbers.
If anyone has an idea....
I'm very open minded!

EgonHugeist · 2015-02-09 10:29:05

Hi Arnaud,

i can confirm your findings using Zeos only. Same full tests of Zeos comapring Delphi vs. FPC, FPC looses !one minute!

Personally i think the initial reason is the Memory-Management and some slow code generations(see FPC bugtracker, search for "slow code genreated").

Some times ago i was tracking the MemoryManager of FPC. From what i remember:
Florian did implement the FastMM4 way for AllocMem() and ReallocMem() see http://free-pascal-general.1045716.n5.n … 09801.html
But all other features are ignored. In addition i can't say how FPC-MemoryManger behaves for Multi-threading apps(scaling).. Sorry i can't find the mail of Florian Kämpfl where he wrote about ignoring all other FastMM4 features, but i definitely remember i read such one.

Note some FPC Compiler procs are written in optimized assembler code. Most of them for Win32... This might be a reason too.

ab · 2015-02-09 11:05:54

AFAIR FPC memory manager has a per-thread block pool.
(just our SynScaleMM, or SAPPMM - remember?)
I found it from the code itself, some years ago.

What is weird is that CrossKylix is even slower!
Using the FastMM4 memory manager.
So it is not a MM problem, I guess...

For internal SQLite3 - i.e. SynSQLite3.pas - not problem.
For external SQLite3 - i.e. SynDBSQLite3.pas which executes SynSQLite3.pas - performance issue.

warleyalex · 2015-02-09 19:03:27

I have read this http://serverfault.com/questions/486677 … -0-on-ext3 and I thought that filesystem could be an issue, I tried to improve hard drive write speed with write-back caching.
edit /etc/fstab and include this
UUID=2a44c2ba-67f9-4337-aa66-fbf658b4cdb2 / ext4 defaults,errors=remount-ro,noatime,barrier=0,data=writeback 0 1

and before reboot, to enable writeback cache
sudo tune2fs -o journal_data_writeback /dev/sdb6

but unfortunately it remains poor performance.

ab · 2015-02-09 20:49:18

I think I just identified the issue.

On Delphi, the following internal query:

  select Name,People,ID from TestJoinExternal where ID=1

is translated into:

  select FirstName,LastName,Data,YearOfBirth,YOD,Value,LastChange,CreatedAt,Key from PeopleExternal where Key=1

Whereas with FPC, the query is translated on the external table into:

  select FirstName,LastName,Data,YearOfBirth,YOD,Value,LastChange,CreatedAt,Key from PeopleExternal

In fact, there is no " where Key=1 " clause under FPC!
As a result, it performs a full scan on the table, which is much slower.

This happens within the "virtual table" SQLite3 system.
See https://sqlite.org/vtab.html
There is something wrong with the estimation cost of each potential request.
Sounds like if SynDBSQlite3 did not identify any index on "Key", so it performs a full table scan!

I've force the table primary key to be identified as an index...
and...
stay tuned...
nothing changed for FPC nor Kylix!

So we have to find why the Virtual Tables do not add the "where" clause as it should.

ab · 2015-02-11 11:10:17

I did a huge refactoring of the logging abilities of the whole framework.
See http://synopse.info/fossil/info/ff854a7d948c1
Now we have accurate timing for most SQL operations.

I managed to force use of the FPC internal memory manager.
See http://synopse.info/fossil/info/bfa76f4a11
The enhanced log states that the internal MM is used:

20150211 11422256  ! DB    			{"TSQLDatabase(B7266C20)":{"FileName":"test.db3","IsMemory":false,"UseCache":false,"TransactionActive":false,"BusyTimeout":0,"CacheSize":10000,"WALMode":true,"Synchronous":"Full","LockingMode":"Normal","MemoryMappedMB":0,"user_version":0,"OpenV2Flags":6,"BackupBackgroundInProcess":false,"SQLite3Library":{"TSQLite3LibraryStatic(B723A020)":{"Version":"3.8.8.2 with internal MM"}}}}

Now the results are pretty consistent with FPC:

2. mORMot

 2.1. External database:
  - TQuery: 2,003 assertions passed  10.92ms
  - SynDBRemote: 20,063 assertions passed  5.24s
  - External records: 2 assertions passed  541us
  - Auto adapt SQL: 543 assertions passed  149.32ms
  - Crypted database: 253,275 assertions passed  376.25ms
  - External via REST: 170,354 assertions passed  3.66s
  - External via virtual table: 170,354 assertions passed  5.33s
  - External via REST with change tracking: 180,454 assertions passed  6.54s
  Total failed: 0 / 797,048  - External database PASSED  21.32s


Generated with: Free Pascal 3.1.1 compiler
Tests performed at 02/11/2015 11:52:10 AM

Total assertions failed for all test suits:  0 / 797,048
! All tests passed successfully.

The "virtual table" problem we observed above is only when we use Kylix, and the external sqlite3.so.0 library.
But with FPC and our internal statically linked SQLite3 engine, no problem!
So we will just advice to not use external tables with Kylix - but it may not be a problem on our own side, since we will use only internal SQLite3, and MongoDB 3.0 for our own projects.
Then compile with FPC to have something usable on Linux, if you need external databases.

mORMot Open Source

#1 2015-02-08 18:43:38

Strange performance issue with SQlite3 as external DB with FPC

#2 2015-02-09 10:29:05

Re: Strange performance issue with SQlite3 as external DB with FPC

#3 2015-02-09 11:05:54

Re: Strange performance issue with SQlite3 as external DB with FPC

#4 2015-02-09 19:03:27

Re: Strange performance issue with SQlite3 as external DB with FPC

#5 2015-02-09 20:49:18

Re: Strange performance issue with SQlite3 as external DB with FPC

#6 2015-02-11 11:10:17

Re: Strange performance issue with SQlite3 as external DB with FPC

Board footer