Firebird: Under the Hood

I have copied my old blog posts from Blogspot here, and hopefully I should get back blogging some day soon ;-)

Even if running in the Classic (isolated process) mode, Firebird needs some data to be available to all the running server processes. There are four kinds of information that must be shared:

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.

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.

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.

Before going into the rows batching details, let’s speak about some protocol details, in particular packets and buffers. As virtually any other kind of networking-enabled software, Firebird is packet oriented, i.e. it sends and receives logically bound chunks of bytes. So it caches the bytes to be sent in the buffer until the packet is completed or until the buffer is full, and only then it transmits the data.

Everybody knows that the performance of the data transfer in the Internet world mostly depends not on the network bandwidth but on its latency (response times). In other words, the chattier is the protocol the worse is the overall performance. Transferring the same data as a large single packet will be faster than splitting it across multiple smaller packets.

A somewhat unexpected behavior has been observed while investigating different aspects of the data type coercion. By coercion I mean an implicit casting to the “common” data type that happens when a few operands have different data types but the expression requires some predefined data type to be chosen for the resulting value. In Firebird, common data type determination is used in two cases: (a) describing the select list expressions of the union and (b) describing the resulting data type of the CASE/COALESCE functions.