Firebird vs Windows: file-system caching issue

This article is going to shed some light on the facts related to the issue registered as CORE-3791 in the project bug tracker. Let's start with some links.

The origin of the problem is described here:
http://support.microsoft.com/kb/2549369

A lot of additional useful information can be found here (especially in the comments):
http://blogs.msdn.com/b/ntdebugging/archive/2007/11/27/too-much-cache.aspx

In fact, this issue consists of two slightly different but interrelated parts.

The first one is that the file-system cache size is unlimited by default in Windows 64-bit, so its working set can consume all the available RAM, thus forcing almost everything else to swap and finally starting to swap itself. It's visually detected by a slow system response and lots of the HDD activity. The suggested solution is to limit the file-system cache size with the SetSystemFileCacheSize API function. And Firebird 2.5 supports this solution with the FileSystemCacheSize setting in firebird.conf.

Unfortunately, we have reports that the problem may still take place for some customers. I have performed a research and the findings are the following:

Records batching

As explained priorly, the fetch response includes multiple resulting records batched together. But how many records should be batched? Sending too few records is going to increase a number of round-trips. Sending too many records will cause the client to stall waiting until it can acknowledge the delivery. And it could also be possible that the client does not need so many records and it was going to close the cursor after receiving the first one. So it turns out that some compromise is required here. The minimal practical batch size depends on the used protocol buffer size, i.e. how many records could be cached before sending and then transmitted as a single packet. The buffer size for the TCP protocol is defined by the TcpRemoteBufferSize setting in firebird.conf. However, it often makes sense to send more records (i.e. a few protocol buffers) without waiting for an ACK, because the CPU power could allow to process more records while waiting for the network to transmit the next batch.

Firebird has its batching logic optimized to transfer between 8 and 16 packets at once. The client library sends this number to the server, waits for transmission of the requested number of records, starts caching the received records and returning them to the client application. The tricky thing here is that the batch size is expressed in records and this value is calculated using the batch size in packets and the expanded (unpacked) record length. A soon as the record gets packed or compressed in some way, the calculation becomes wrong and it results in sending less packets than expected. Also, the last packet could be sent incomplete. Currently, the only "compression" that's available is trimming of VARCHAR values. So the batching could be either effective or somewhat sub-optimal depending on how many VARCHAR values are fetched and how long the actual strings are as compared to their declared lengths.

Firebird roadmap has been updated

The project roadmap has been updated a bit. The change is to boost the v2.1.5 and v2.5.2 releases at the cost of slightly delaying the v3.0 Alpha release.

Firebird 2.1.4 was released exactly one year ago, so now it's a promised time for v2.1.5. It has 53 bugs fixed and no critical issues remaining unresolved. Firebird 2.5.1 was released more than 5 months ago and the expected release date for v2.5.2 is approaching the next month. It has 45 issues resolved up-to-date and a few more are in the pipeline. So it makes a lot of sense to release them sooner rather than later.

The v3.0 Alpha release will be going through the preparation stage while all three release candidates (v2.0.7, v2.1.5, v2.5.2) are being field tested, so it's likely to appear shortly after the aforementioned releases, in the second quarter.

Thanks for your understanding.