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.

Is Firebird Poor?

This is a continuation of previous post about Firebird. Now I’d like to consider some facts about money flows around Firebird.
First of all, a good question is how much money is being consumed by Firebird.
The only public information is a set of reports from Firebird Foundation. The last public report is from 2007 year: http://firebirdsql.org/ffoundation/plenary/page_finance_agm2007.pdf
Full set of FF documents is here: http://firebirdsql.org/index.php?op=ffoundation&sub=docs

Well, you can look at numbers in these reports yourself and make conclusions too. Probably many readers of this blogs have annual income more than whole FF has.
I don’t know the numbers from private companies who pay staffers for work at Firebird (part of full time), but I can suppose that their spending is in the same range.
Comparing this amount with money spent by different ventures or IT companies into other open-source databases and their forks we can say it’s low. I don’t speak here about MySQL investments and impressive final sale, but there some examples for PostgreSQL with several million investments (check Google for “PostgreSQL investments”).
There can be a lot of speculations about nature and intentions of venture capitalists and so on, but it seems quite obvious that our “bird” does not eat enough to grow fast enough…
But let’s get back to the initial question - Is Firebird Poor?
In terms of cash flow I think we need to say - yes, Firebird as virtual entity is poor.
But we need also to estimate the assets that Firebird has (since it’s a virtual entity I need to say “can leverage” or “is possible to increase money flow for people involved”, but I hope that “Firebird has” is clear enough). If someone has valuable assets – sure he is not poor .

Let’s start with web-site as it’s a well known asset for everyone.
Since firebirdsql.org has 5000+ visitors highly concentrated on Firebird and related areas, it can be a good source of money.
Let’s imagine a shop at this site, something like shop.firebirdsql.org with featured products at the main page of firebirdsql.org. General shareware rule gives us an empiric ratio of visit to sales as 100 to 1. Consider we have 50 sales of a software everyday (actually there can be more sales since there will be more products for different areas of Firebird-related things), 20 from featured products at main page and 30 from general shop, with average price USD$200. Getting 50% average commission from featured products and 30% from general shop will produce USD$200*20*%50 + USD$200*30*%25 = USD$ 3500. It’s per day, and ~1.2mln USD per year. Looks impossible? Ok, even if you imagine the hardest crisis for the next year, you can divide it by 10, so it can be 120k USD, the same as Foundation gathers from members, sponsorship and donations.
Many users of Firebird are also Delphi/C++ Builder users. Let’s ask them to buy from shop.firebirdsql.org to support Firebird and get sales commission from Embarcadero – here is another hundred of USD$k.
Advertising is also a good point to sell. Imagine the advertising of Oracle at some page? Looks abnormal? Really? Probably you richer than IBM – look here: http://www-03.ibm.com/systems/x/solutions/infrastructure/database/oracle.html
(This way firebirdsql.org also can sell InterBase, by example. No? Still feel you are richer than Mr. Gates?). And this is only the first point. Having community in several hundreds people it’s a good first step to build some nice social network sponsored by advertising (don’t forget that Google is an advertising agency and… they feel good :-) )
There were some opinions that firebirdsql.org is a technical site for project news and stuff only and it should keep spirit of open-source development, and if someone wants to make a shop he need to build it at some other place and so on…

Well, personally I don’t see anything bad in going in a quick way to get money for Firebird and use valuable asset in the most efficient way. I am pretty sure Firebird users will be happy with all-in-one site and they will understand and support the reasons of implementing Firebird-related shop near the downloads and news pages of Firebird.
But, of course, it’s possible that I am just not rich enough and I think too much about money… may be true open-source believers should work in basements and hope for … raise of machines? Come into a fortune from the rich uncle? Donation from mr. Abramovich?

From the organization point of view, to keep intact the independence and distributed nature of Firebird, shop activity (site redesign, orders processing, money flow management) can be outsourced to some company-operator.
The company-operator should be chosen at the open tender to correspond to required conditions and should sign service-level agreement to implement and reflect all important aspects of this shop’s operation.
Who need to perform this tender? What are the conditions? Who will decide how to spend money and there to invest it? (now there is no such question, because all amount is completely consumed by research and developments, but with $1M per year… hmm, Firebird can afford some marketing activities too)
Obviously the main source of decisions should come from current administrators of Firebird project. This will require some courage and will of power, but I am sure they have it. Some people can say that Firebird Foundation can play this role, but, sorry, it is a non-commercial organization. I am the member of Firebird Foundation, but my opinion is that Firebird entity need to set (and achieve) very specific business-oriented goals, such as growth of user base and increasing money flows for R&D and marketing, aggressive competition and so on.
Open source is good when it’s quickly developing and spreading over the world (like IBM does :-) ), but it seems that currently Firebird does not look like a very successful open-source project. I would say the numbers of users and installations are much lower than it can be.
I don’t intend to offence any Firebird Foundation members or Committee, they do a great job, but I truly believe that Firebird need to have more business-oriented management which will rule their assets more efficiently.

And let’s back to the assets :-) In the next part I’ll share some ideas how to use Firebird brand-name (which is currently owned by FF), source code and millions of installations to get several millions $ more for Firebird growth.