Subject Re: [firebird-support] Blob write to Embedded on Linux performance
Author Mike Ro

On 09/06/14 09:25, Olivier Mascia om@... [firebird-support] wrote:

I am seeing 45 seconds for a 13Mb file using C++ / IBPP and 48 seconds using PHP.

Exactly the same code, database structure and hardware on Windows inserts a 13Mb BLOB in just 2.4 seconds!

That just means the caching/flushing policy is different on both OSes.

I don't know the current state of implementation of sync-writes in linux versions of Firebird. Nor its relationship with OS filesystem cache. But reading about firebird.conf parameters FileSystemCacheThreshold, FileSystemCacheSize, MaxUnflushedWrites, MaxUnflushedWriteTimes, and synced / not-synced writes (database setting), will be a good starting point.

Olivier, thank you for your insightful reply here and on the IBPP-DISCUSS mailing list

This was indeed the reason for such a big difference, in fact Linux is now about twice the speed of Windows 8 (approximately 1.3 secs for 13Mb file), although this may not be like-for-like configuration parameters (the Windows system is still using the default firebird.conf).

Based on your comments and other information I did the following on Linux:

1. Uncomment the MaxUnflushedWrites and MaxUnflushedWriteTimes in firebird.conf (i.e. with values 100 and 5 respectively).

2. Make a backup of the database and  restore it with a larger page size:

 gbak -replace test.fbk test.fdb -page_size 16384 -v -user sysdba -password secret.

3.  Set the database mode to be asynchronous with:

 gfix -write async test.fdb -user sysdba -password secret

These tests were made using the embedded server and FlameRobin which I believe writes blobs in 32768 byte segments.

So the only mystery (for me!) remaining is why the UDF is so much faster when it is writing 4096 bytes at time with forced writes on?

Thanks again ... Mike.