Firebird - 1 terabyte database

We made 1 terabyte database test with Firebird 2.1. Read more. Questions?

local protocol and multi-core processors

We found strange behavior of local protocol connection of the Firebird SuperServer 1.5 and Windows. Tests were made on AMD 2-core processor computer with command line backup like
gbak -b -g db.fdb db.fbk
When gbak is not "attached" to cores (uses all), or "attached" to another processor core where the Firebird SuperServer runs (for example, fbserver. exe at core 0, and gbak.exe at core 1), gbak nearly not loading used core, and fbserver.exe loads it's core only at 50%.
When we attach gbak.exe to the same core that uses fbserver.exe, backup speed raises nearly 2 times, and fbserver.exe loads core at 95%. Example results for gbak -b -g of 3.8 gb database:

• fbserver and gbak on the same core - 9 min 22 sec.
• fbserver and gbak on different cores - 15 min 41 sec.
  

This is an opposite to tcp protocol, when it doesn't matter on what core gbak.exe is run, and backup takes 4:10 minutes.

So, right now we do not suggest to use local protocol for Firebird 1.5, and use localhost instead. More tests on the way, stay tuned.

What is sort 2

Someone may make wrong conclusion from my previous post about sorting that "sorting ... mostly does writes". But, yes, I was speaking only about temporary sort files, and the sorting process itself. The whole picture of the query with PLAN SORT is the following

• server (Firebird, InterBase) reads portions of the data from the query, does sorting of this block and writes it to the temporary file. So here we have reads from the database and writes to the temporary file.
• after all data was read from the query (database) and sorted, server begin to send sorted data from the temporary file to user. Of course, this happens only if client application reads the resulting data, i.e. call "fetch". Here we have reads from the temporary file.

The number of writes and reads of the temporary file in this case is the same. But the number of database reads depends on the query itself and the amount of the processed records. I will speak about it later, because right now my computer is busy by some another interesting test.

What is sort?

Inspired by discussion about sorting (PLAN SORT), did some simple tests. Right now I do not have "an article" about this, but want to show you some discovered facts:

• sorting the temporary file (fb_sort_nnnn.tmp and ib_sort_nnnn.tmp) mostly does writes, not reads (excluding database reads and fetching data from temp file). Firebird 2.1 read/write ratio is 1:10. InterBase 2009 read/write ratio is different, and nearly 2/3.
• turning on Windows folder/files compression for TEMP lowers sorting temp file size up to 2-4 times (depends on sorting data, I've used repeating data, sorry)
  
• turning on Windows folder/files compression for TEMP increases processor load 2 times, and makes disk transfer ~4 times less.
• Firebird 2.1 show only small (invaluable) diffrerence when sorting at compressed and uncompressed TEMP (4 min 00 sec). Compressed TEMP produces more stable timings, when test is run several times.
  
• InterBase 2009 sorting speed is equal to Firebird 2.1 only on uncompressed TEMP. And it is slower at compressed TEMP (4 min 00 sec vs 5 min 00 sec). The cause is InterBase's higher read/write ratio (uncompression of blocks being read)
  
• on my computer sorting 31 million records (select varchar(20) from ...) produces temp file with the size of 4.28GB (uncompressed)
  
• InterBase 2009 uses bigger sorting blocks (chunks) than Firebird 2.1. By "sorting block" I mean set of records that are being sorted in memory and then written to sort file for future merge with other blocks
  
• Using uncompressed TEMP InterBase 2009 loads processor less than Firebird 2.1 (35% vs 40%), but writes to disk faster (30mb/sec vs 25mb/sec)

This was a "single-user" test. Running concurrent sorting queries may produce different result. What can be said now for sure, is that if you have lot of queries with PLAN SORT, you must (!) have TEMP pointing to the separate physical drive. And maybe RAID 0 will help.
So, questions? :-)

InterBase 2009 lost ODS 10.1 support

InterBase for years uses Y-valve, implemented by Jim Starkey, to support previous databases in new InterBase versions.
The native ODS (On-Disk Structure) for the particular server version is the only one - the ODS of database created with this server. For example, native ODS for InterBase 6.0 is 10.0. For Firebird 1.5 - 10.1, etc.
And, as a feature, InterBase and Firebird supports at least N-1 ODS number.
The decision what ODS support to remove from the server is completely on the server developers. Firebird 2.5 still can open databases from InterBase 5.6 only because Firebird developers still keep code to support it.
But, seems that InterBase developers decided to eliminate ODS 10.1 support from the InterBase 2009. InterBase 2007 can open ODS 10.1 databases without a problem. But, InterBase 2009

• initial release, 9.0.0.206 says "incompatible version of on-disk structure"
• IB 2009 Update 2 (9.0.2.369) simply crashes
  

Is it bad, or not? I don't know. Right now InterBase 2009 uses ODS 13 for new databases. The ODS difference a bit far (13-10=3) from the Firebird (11 - 10 = 1).

But, you need to know, that you may not now open old databases (less than ODS 11) with the InterBase 2009.

p.s. I have not found any databases with ODS 10.0 on my computer, but ODS 10.0 and 10.1 differs only by some additional indices on some system tables in ODS 10.1.

Broken Indices

IBAnalyst since version 2.0 may report about broken or inconsistent indices. The detection of this cases is being made by checking index key count and record cound. If key count is less than records + versions, than the index is broken.

How it can happen?
At first, of course, it can happen when the database is broken, and some keys are missing. But the more realistic case for this inconsistency is indexing data when the data is being modified.
It can be easily reproduced, because index creation or re-activation passes three steps:

1. server moves data from the table to the temp file
2. data in the temp file is being sorted
3. server moves sorted data to the database as an index

If there is no write lock on the table during all three steps, modified (or inserted) data will not exist in the created index. And will never be found by the index search.
So, find the big table, apply "create index" on it, then wait until temp file for the sorting will be completely created in the temp directory, and after that insert some record in the table and commit. Then try to find this record with where condition. Null. Scary?
Yes, but this is fixed in Firebird 2.0 (by write locks on table during indexing).

p.s. gfix also may detect this type of index inconsistency.

FBScanner 2.1 is released

We are proud to announce the release of FBScanner 2.1. This version introduces a very interesting new features, such as plans extraction for queries, improved logging and tags support; these features make development and administration of Firebird and InterBase much more productive and effective. Read feature matrix and download trial of FBScanner 2.1 to estimate its value and effectiveness.

Extraction of queries' plans is implemented both in the Viewer and in the log. Improved logging is a great advantage for administrators who wants some sort of an audit, or for developers who study queries behavior in sophisticated systems.

Using tags developers and administrators can easily track long-running or special queries, watch real-time flow of SQL queries from different applications.

With automatic priority setting for Firebird Classic administrators of heavily loaded Firebird-based systems can perform tuning and use their hardware resources better.