Wednesday, November 12, 2008

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.

Thursday, October 16, 2008

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.

Wednesday, October 15, 2008

What is Firebird?

I’d like to clarify some facts about Firebird and Firebird Foundation. I think right now is a correct time to remind how all these things are organized, because some messages about "crisis, firebird's death, low finances" and so on being spreaded around.
It seems that many people think that Firebird Foundations owns Firebird, makes development strategic/tactic decisions or something like this. This is not real, in fact the Firebird Foundation is an institution to collect donations and sponsors money and grant them to people, related with Firebird development. To understand exactly who is who we need to look at the current situation with Firebird.

First of all, Firebird does not exist as an entity. There is no such thing as Firebird company/organization.
We can say that Firebird as virtual entity contains 2 parts: 1) set of material assets and 2) people who perform different activities.

Three main assets of Firebird are: source code and installers stored in Sourceforge.net CVS, website firebirdsql.org and set of services for maintaining bugs and feature requests. Source code is controlled by Project admins awharrison, bellardo, dchri, dimitr, helebor, pcisar, seanleyne, skywalker (http://sourceforge.net/projects/firebird/)
You may notice that some of them are working at Sun :-). Probably this list was not updated for some time. Of course, there are contributors granted by admins who can submit code to CVS and perform other activities with files at sourceforge.net repository.
The second Firebird asset is web site firebirdsql.org. Domain is owned by Helen Borrie according to the DNS records. The site itself is located at the dedicated server (according to tracert) in Canada.
The third asset is Firebird tracker dedicated server, also somewhere in Canada. It would be interesting to see a photo of these computers :-)
Not so much? But it stands more than 5000 visits every day and log more than 1000 installations at Windows platform per day (!), according to the statistics of the installer’s 'landing page'. Also there are more than 1 mln downloads of all Firebird installers and other files per year from the sourceforge:
http://sourceforge.net/project/stats/?group_id=9028&ugn=firebird&type=&mode=year

Groups of people are involved into Firebird projects are much more interesting. There are 102 developers listed at Sourceforge home of Firebird.
As you can understand, not all of them are performing equally. Many people listed there did not submit anything for a long time ago, but there are always a small active group of people who perform 99% of R&D (research and development) and other work. Some members of these groups are migrating slowly: people come and leave, as their interests and life goals are changing.

How these people are compensated by its work? There are two sources of money: people work at “usual job” and dedicate part of time in fact paid by employer, to the Firebird development (the brightest example of this approach is IBPhoenix), and the second source is granting from Firebird Foundation or from other organizations.

How are decisions made in such structure? Firebird development decisions are made by projects admins and leading contributors (they know each other personally) - they discuss and issue roadmaps and perform tasks assignments (using tracker.firebirdsql.org), and perform actual coding and testing.

Web site is made by efforts of Helen Borrie and Pavel Cizar (may be someone else is involved too, not sure).

Finance decisions of Firebird – well, there are no finance decisions in Firebird itself, as it is a virtual entity.
Firebird Foundation considers developers' work and allocates grants according the amounts they have, IBPhoenix and other companies, who employ developers of Firebird, pay to them according to some internal reasons.

Since source code of Firebird is under IPL and IDPL licenses, it’s not possible to sell Firebird itself.

HR decisions – I mean who should make this or that or who takes this (virtual) position. In development area this decisions are made in democracy style: currently most active developers (now leaded by core developer Dmitry Yemanov) consider and discuss a person and its work/commitment. For other activities like web site and documentation there are a few volunteers who make everything, from decisions to implementation (there are always not enough skilled documentation writers).

This is the current situation. Of course, it has some advantages and disadvantages. The biggest advantage that Firebird as a distributed entity is virtually immortal in terms of close of sources. Many people are still afraid that someone will “own” Firebird, close its sources and make it proprietary. According to IPL and IDPL this scenario is not possible, and Firebird will live even with the single person who will perform basic activities.
The biggest disadvantage is the reflection of distributed structure of virtual Firebird entity - is not suitable for large investments of money and volunteers efforts.

To be continued…

Tuesday, September 16, 2008

FBCon 08 - 25-26-27 september - Bergamo - Italy

You CAN'T miss FBCon 08 - 6th Firebird International Conference that will be held in Bergamo from 25 to 27 september. Conference will be from thursday september 25 till saturday Every day have a special theme with best italian and worldwide firebird-world expert!
Here some of our sessions, for a detail go to www.firebirdconference.net:

Italian sessions :
  • CTE e query ricorsive
  • Database per sysdba – avanzato
  • Database per sysdba – base
  • Database per sysdba – web
  • Delphi, DBExpress e Firebird
  • delphi4php e Firebird
  • GO: Gestionale Open
  • ISAPI
  • Php e Firebird
  • SQL and Firebird
  • SQL enhancements in FB 2.5
  • Sw-ing
  • vTiger

english sessions:
  • 10 ways to backup FB database
  • Common Table Expressions : tutorial
  • Delphi and Firebird
  • Developing Firebird apps for pocketpc/smartphone clients
  • Execute block e trigger (database)
  • Fb{Ref?}Doc - the Firebird Documentation project
  • Fire in The Lamp - FireTube an example for scaling Firebird on web2.0
  • Firebird 2.5 Architecture
  • Firebird Development in 2008/2009
  • Firebird in real time applications
  • Firebird on Linux - advanced topics
  • Firebird on Linux – basics
  • Firebird Stored Procedures in Depth
  • Firebird, tools and Linux distribution integration
  • Installing Firebird with your application under Windows
  • Introduction to development with Firebird and Python
  • Know what is happening in your database and server
  • Managing recursive data structures (trees) with Firebird
  • Migrating from MSSQL and mySQL to Firebird
  • Practical Guide to the Garbage Collection
  • Query optimization - how to speed up COUNT(*) function
  • Replication for a high availability infrastructure
  • Security in Firebird: 2.1, 2.5, 3.0
  • Separating web development from database logic
  • Service Oriented Architecture with Firebird
  • SQL: Migliorare e ottimizzare le prestazioni
  • Stored procedures
  • The Firebird System Tables
  • Towards a REST architecture with Delphi and Firebird
  • Transactions In Your Applications
  • Ultimate Vmware Appliance - Ubuntu and Firebird
  • Updating Database structures
  • Using embedded Firebird with .NET
  • Working with Firebird in Python

To REGISTER go to www.firebirdconference.net

Also note that 24-th September at the same place, before the Conference, there will be FREE IBSurgeon seminar:
  • Introduction
  • Firebird database recovery and protection against corruptions
  • How to find and solve performance problem in Firebird with IBSurgeon products
  • Replication with IBReplicator
  • Using dbFile
p.s. I will be there. My wife told me that I must taste native pizza, pasta, cappuchino, etc.

Sunday, September 14, 2008

Multi-file Database ?

Some last database repair cases we did were with multi-file databases. Interesting, that latest InterBase and Firebird versions was used, and also file system for the storage was NTFS, not FAT32 and FAT16. But, the databases was created and maintained using 1 gigabyte files.

If you still use InterBase 5 and less, stop reading this, please :-)

This is strange, because all InterBase and Firebird errors working with >4gb databases are gone, and also lot of people forgot about FAT32.
Some says that FAT32 is faster than NTFS, so they still use FAT32. Maybe this is true, but personally I don't see any reason to use old file system.
Moreover, once I had interesting case with FAT32: there was a 4gb logical drive with 3gb file. Maybe it was Windows 2000, but anyway, I couldn't copy this file to another logical drive with NTFS - operating system gave me an error when it tried to copy over 2 gigabytes of the file.

Well, maybe XP and Vista can copy 3gb files from FAT32 to NTFS, but multi-file databases have several disadvantages, that does not depend on file system:
  • some people think that multi-file database will have better performance than single-file database, on multi-processor/core computers and using InterBase SMP or Firebird Classic. This is wrong. Server does not do any "parallel" access to the secondary files. It treats multi-file database as one sequential file broken into parts. So, if table pages spreaded in several database files, table scan will scan these files one by one.
  • Multi-file databases have hard-links to the secondary files inside it, and it is impossible to move such a database from one logical disk to another (for example, from d: to e:). If you want to do this, you need to make backup and restore, or get hex-editor and hack this hardcoded drive letter and path in the header pages of the mult-file database. Single-file databases does not have this problem at all.
  • You need to watch over mult-file database's size, and from time to time, while database is growing, create additional secondary file with appropriate size. We've seen lot of databases with first files having some equal size, and the last file larger than other - DBA forgot to create additional secondary file at right time.
  • Secondary file size can be specified in 2 ways. Using STARTING AT, when you specify from what page number server will continue database in the next file, and using LENGTH, where you specify secondary file size in pages. The best way is to use LENGTH parameter for all files of the multi-tile database, including primary file and secondary files. But if you will start to mix STARTING AT and LENGTH you can loose understanding what size secondary files will have, and get multi-file database with different file sizes.
  • Backup and restore automated scripts must be updated from time to time to include additional secondary file, because there is no command-line parameters for gbak that could do automated restore of the database to create unknown number of secondary files. Each secondary file and it's size must be specified at the command line.
    This is really a headache for the DBA.
I think I gave you enough reasons to stop using multi-file databases, if you still do this. If you use InterBase 7, 2007, 2009, Firebird 1.0, 1.5, 2.x - at the nearest restore make your database single file, and forget about multi-file database completely.

upd: need to say that the well known (at least for us) database corruption for IB 5.x is when DBA forgets to add new secondary file, and the last secondary file comes to 4gb size.

Monday, September 01, 2008

What page size I should use in my database?

It's a common question with interesting background from developers . As I can suppose from IBSurgeon statistics, 99% of Firebird and InterBase developers use default page size: since the old ages it is 1024 bytes, and only for Firebird 2.0 it is changed to 4096 by default.
There are 4 main things related with page size: indices depth, database cache size, records per page quantity and disk cluster size.

The shortest answer to this question is to use 4k, 8k or 16k page size. That's it.
If you want to go a bit deeper, read the following:

Index depth
Indices depth is quite obvious thing - since the basic internal structure of the indices is a B-tree, index depth depends on page size, and too small page size (1024-2048) will produce 4-levels trees even at moderate amounts of data (several hundred thousands of integer-based
keys). It's recommended that index depth should be no more than 3. (you can check indices depth using IBAnalyst... and some other things too).
For tables with hundred millions records page size should be the largest. For example, if there is a 250Gb database contained billing information, so it must have page size = 16384.

Database cache
You probably know that Firebird and InterBase has database cache, it is counted as buffers * page size (in megabytes).
Rookie developers think that the more database cache is the better it work (another idea is "I want to use all my RAM").

The problem that mostly popular Windows versions of Firebird (and all InterBase) are 32-bit, so in theory there is only 4Gb of addressed memory available to use. Actually Windows restricts this size maximum to 2Gb per process by default (it can be changed to 3Gb in config.ini).
So, if you set 100000 buffers (using SuperServer, not Classic, of course) in the firebird.conf
DefaultDbCachePages = 100000
or in ibconfig
DATABASE_CACHE_PAGES 100000
and each page is 16k, the cache will consume 1.6Gb (at
first connection). And it will prevent to open the second database due to arithmetic fact 3Gb-1.6Gb = 1.4.
Of course, this applies mostly for SuperServer, due to the fact that Classic (Firebird only) allocates buffers for each connection (1.6Gb per connection is too much, really), not per database (regardless of connections).
And the most interesting thing that allocation of such a big cache does not bring a boost in performance - in some test it even decreases performance. Actually engine effectively "collaborates" with operation system's cache and allows OS to cache pieces of data being often read and write, and the main (but not only) purpose of cache is to store metadata records in RAM.
From this point of view large page size in combination with moderate cache size (<100000) decreases metadata page read-write operations.

Records per page
Specifying "records per page" as a separate sub-topic seems to be repeating of cache explanation, but this is not true. During recovering of more than 2 thousands databases we have noticed that databases with larger pages size (4k, 8k, ...) had less corruption.

Cluster size
NTFS, for example, use 512 bytes clusters, when you format a logical disk. You can choose another size for clusters, but how it can be related to the page size? Let's compute with an example.

Page size = 4k
cluster size = 2k

Here operating system will read 2 clusters when server tells it to read one 4k page, and writes also 2 clusters when page is being written. There can be an unwanted overhead for the file system, that must store one page using the cluster chain. And, if 2 clusters can be placed by file system at different locations of the disk, there will be of course read slowdown.

Page size = 4k
Cluster size = 8k
Here 2 pages can be in the same cluster. For read operations it may be good, like "pre-loading" of pages, but you can't be sure that server will need second page from the cluster, when it reads first. Write operations will be doubled, because same cluster must be written when each page on it is changed and need to be written to disk.

So, it is better to have page size and cluster size equal.

Tuesday, August 19, 2008

Fatal lock manager error: semaphores are exhausted

If you get this message in firebird.log
Fatal lock manager error: semaphores are exhausted, errno: 1

It means that you sould try to increase semaphores count in firebird.conf.
Applications at Linux use semaphores to interchange data. More interesting question is why semaphores are exhausted? The most common answer is that some other software (probably, it was developed not well enough), that starts before Firebird, consumes significant amount of semaphores from OS.
Firebird itself is modest in terms of semaphores.

Thus, maybe you need not to increase semaphores count, but check order of programs being started at OS start. Also maybe you will need to increase semaphores count in OS configuration (and recompile the OS kernel, if needed).

Wednesday, May 28, 2008

Naming Generators

There is no rules to name generator, of course. Because generators does not have any relation to it's usage. When generator is being used in trigger or procedure, you can track dependencies in rdb$dependencies table.
But, during repair services for some databases, we found that generators may not have any understandable relation with table, trigger or procedure. Moreover, we couldn't compute any relation with generator and max of primary key value.

So, we decided that there must be some guides to name generators in InterBase/Firebird. For example, it can be gen_<table_name>_id. Otherwise, how else generator can be correlated with the table in this case?

Monday, April 14, 2008

Firebird RDBMS at russian conferences

In the second part of April there are 3 conferences in Moscow, Russia, where will be Firebird sessions:

Sessions will be presented by Dmitry Emanov, the leader of Firebird development team, and by Dmitry Kuzmenko, CEO of IBSurgeon.

Tuesday, January 15, 2008

Seminar in Munich, 24 January 2008

We'd like to invite German developers to our free seminar in Munich, Germany, Jan 24.

Agenda:
  • New in Firebird 2.1, Vladislav Khorsun, developer Firebird
  • Fast Reports Business Intelligence solutions for Developers, Michael Philippenko, CEO Fast Reports
  • Corruption fighting and Firebird database protection, Dmitry Kuzmenko, CEO IBSurgeon
  • Fast Reports new products and solutions, Michael Philippenko, CEO Fast Reports
  • Firebird optimization, Dmitry Kuzmenko, CEO IBSurgeon

Seats are limited, please register now:
http://fast-report.com/en/subscribe_seminar.html

Wednesday, January 09, 2008

195 defects in source code? Not ours.

InformationWeek mentions Firebird. As a "somewhat moribund project". Nice.
And, the real fact is that: "Nearly all of the 195 identified defects are in fact actually within an external piece of code we use for character sets and collation sequences ICU".