Firebird 2.5 Benchmark: Firebird shows great results against MySQL and PostgreSQL

Tsutomu Hayashi performed series of tests with Firebird 2.5, 2.1 and MySQL/Postgresql.
Definitely Firebird need more evidences of its superior quality and advantages among other commercial and free open-source databases.

Firebird2.5 Benchmarks(English)20091031

View more presentations from tomneko.

Are you ready?

Tough day
Today I had a problem with video adapter at my computer.
Computer was shut down correctly, but won't start any more - shows black screen, HDD lamp is blinking.
Once I  saw these showings, I've opened computer case, plug out my Gigabyte GeForce, get VGA cable from the desk and plug it to the on-board video adapter. Now it works (though with pretty bad resolution).
Tomorrow I'll buy new video adapter (probably Asus with 9500GT) and situation will be resolved.

Outage of my work computer is less than a hour, and it will be back to the normal performance in less than 1 day.

This is quick because I had recovery plan.

Why I have recovery plan
Approximately 2 years ago I had the same problem, and it took almost 3 days to discover and resolve the problem. It was awful  - at first I thought that my soft RAID crushed, then - that motherboard is the reason, and also at the same time UPS died and gave more issues (I suppose, it was coincidence, but very bad coincidence). I've spent 2 days to fix hardware problem and 1 day to restore data and computer configuration because RAID was broken and I rebuilt it (pump data to the backup USB-drive and back).

Hold yourself in readiness

After that I decided to be prepared.
I knew that it is not possible to get 100% reliable hardware, but I wanted to minimize possible outage and reduce possibility of data loss as much as possible.
At new computer I've installed motherboard with integrated video-adapter to be able to quickly check problem with video adapter. Also I've bought new LCD  monitor, which have not only DVI, but VGA input too to be able to work with on-board VGA.
I've set another mirrored RAID - if one disk dies, I have another, and I've got 1000W UPS which is more than my 500W power supply.

...it happens
Today it happened, and I managed situation much quicker than 2 years ago. I knew that I was protected with my preparation and now this feeling is confirmed.

Why it is written in Firebird blog?
In our practice we often see very strange reasons of corruptions. People install Firebird at workstations and never bother to make backups. They never check firebird.log and system logs...
To be honest, work computer is only work computer - source code is in SVN, emails copies are at server mailbox and at notebooks, also there are backups.
But database is another story. It runs for business, and business run on it.... And free license of Firebird does not mean that stored data is also free. Or outage of business running on Firebird costs zero.

And  - do you have recovery plan for your Firebird database?

FBScanner 2.6

We are proud to announce the new version of FBScanner, our tool to solve all types of performance problems with Firebird.

Slowdowns, disconnects and networks errors, transactions' issues and ineffective SQLs - FBScanner can help developer or IT Pro to find the reasons and solve these problems.

New version of FBScanner introduces significant increasing of SQLs processing, new logging approach to database with one-click log creation, real time configuration (restart of not required).

Now it also has full support of Firebird 2.5 (including SuperClassic architecture).

For administrators FBScanner 2.6 offers additional security options:

• ability to restrict number of connections
• white and black lists of databases
• white and black lists of IPs.

Please download demo version of FBScanner 2.6 or read detailed description and see presentation of FBScanner features.

Here is short presentation regarding main FBScanner features and usage scenarios:

FBScanner: IBSurgeon's tool to solve all types of performance problems with Firebird

View more presentations from Nataly Polyanskaya.

Why Firebird is cool?

I've seen a lot of discussions between developers, students, administrators and other geeks regarding "what is cool and what is not". Definitely such talks are good with good beer in hand (like Czech's Velvet one :)), and they are very similar to any other "holy war" regarding software, cars, movies, etc.

Though such discussions are pretty common and may seem to be useless (especially without beer :), they actually represent the powerful marketing activity, the highest dream of every marketing manager - "word of mouth". Don’t omit the chance to tell someone about Firebird – it really helps, believe me.

So I decided to consider here 3 main talking points to help all of Firebird fans, developers and adopters to spread the word in the right and effective way:

1) Firebird is universal. By universal I mean not only the really wide range of supported range of platforms with (very important) 100% compatibility and easy one-step migration, but also the ability of Firebird to perform all types of database tasks: it can be used in ERP, CRM, billing, reporting/BI, web-site and other applications.

2) Firebird is flexible. Multi-generation architecture, suitable triggers and stored procedures allow developer to develop applications and achieve business goals in very fast way. Deployment is also very easy: small deployment packages without registration/activation can be supplied "as is" or built-in to enable silent installation, with choice of 4 architectures to bundle with your application: Embedded, SuperServer, Classic and SuperClassic (2.5).
Also flexibility means that you can use Firebird in almost all development environments and tools: .NET, Delphi, Java, PHP and dozens of less known languages.

3) And, the most important, Firebird is good enough. I like the comparison of Firebird with well-priced Japanese car: good-looking, comfortable, safe, with low fuel consumption and reliable. Definitely you cannot put 250 tons of rocks into its trunk, and you can't outrun flying Boeing... But do you really need it or ever wanted to do it? The same thing is about Firebird: it is enough for the majority of business tasks and fits 99% needs of enterprises in SMB segment (SMB states for Small and Medium Business, i.e., for people who count on real values and live in real world without personal jets).
Firebird is equipped very well to handle business, and it remains compact, fast and transparent for developers.

Of course, during the holy wars there are always objections and attempts to raise old dead myths from graves:

1) Firebird is for small databases and it cannot handle large database. This is really funny myth, especially after our test with 1Tb database and many examples of real-world multi-gigabyte databases. But there are one thing to be highlighted every time when you see this stupid statements: the density of stored data. It's not well-known fact that Firebird keeps data in compressed way, approximately 2 times more than MS SQL and Oracle. So 10Gb Firebird database actually will be ~18-20Gb database in MSSQL or Oracle (if someone would like to check it, I will be glad to publish the results here and everywhere).

2) Firebird is a slow database. The flexibility of Firebird has the other side: it forgives developers much more ugly things than other databases. Firebird keeps working when Oracle will say something regarding "rollback segments", it keeps working when lucky MS SQL administrators buy the second set of licenses and 30k hardware to deploy reports-only instance.
Definitely Firebird has limitations of self-tuning and out-of-box performance endurance. Guys, even the best Japanese cars can be broken, especially without right maintenance in heavy conditions and while driving by inexperienced driver.

3) Firebird is not reliable because it has no transactional log. This is as far from truth as statements that all Russians love vodka and all Americans love Kim Bassinger.
The concept of reliability cannot be based only at one technical feature. The right approach to provide stable work of overall IT system, reduce risks of outages and corruptions is to develop and prepare whole infrastructure to be ready for emergency situation and have in place realistic recovery plan (btw, that's we are doing in IBSurgeon :)).

When next time you'll hear this bollocks regarding absence of transaction log, just ask your opponent: "Do you have recovery plan for your full-blown 5k-per-CPU powerful database system? NO? So keep your fingers crossed, you'll need it sooner or later".

I think this is a bit more for three-beers-talk, but I hope it helps. :)

Differences between Firebird and InterBase, part 1: 32bit and 64bit

Unfortunately, many people still have wrong perception of Firebird as a “free InterBase”. InterBase and Firebird were very close in terms of features and low-level compatibility during versions 1.0 and 6.5, but these versions are gone long time ago (I think there are some people who use Firebird 1.0.x on legacy systems, but I doubt there are much people at InterBase 6.5). 

Another obvious reason why people still consider Firebird and InterBase as closely related to each other is that producers of developer’s tools and libraries traditionally support both servers, so it may look similar to create applications for Firebird and InterBase (or even try to create the single code work for both), though this is quite limited point of view, as we will see. We are at IBSurgeon also provide recover services and tools both for Firebird and InterBase, but, as you will see later, it does not mean that databases have identical internal structure or maintenance approach.

And, of course, lack of information regarding differences is also the reason of confusion. In this series of blog posts (I hope, afterwards we will put all pieces into the single article) we‘ll consider the most important differences between Firebird and InterBase.

We’ll start with 32-bit support and 64-bit support and implementation limits in Firebird and InterBase, then continue with architectural differences, devote some time for garbage collection implementations (including indices), compare incremental backups and logs approaches, look at maintenance differences (including recovery and anti-corruption techniques), then discover differences in transactions’ processing and finish with total cost of ownership. And probably there will be more topics between these milestones – the appetite comes with eating. So let’s go!

32bit and 64 bit support

We'll start with very basic differences: 32- and 64-bit support. As you know, Firebird fully supports 32-bit and 64-bit versions of Windows, Mac OS X and Linux, and InterBase works at 64-bit operation system only in compatibility mode.

Someone who knows that the main overhead in database management system lays in I/O operations can ask a very reasonable question: "What is the real benefit from 64 bit support? Is it a big deal?"

Actually it is big. The main advantage of 64-bit systems against 32-bit is memory usage limit. 64-bit systems allow server to use much more RAM.

Look at Microsoft knowledge base item with technical information regarding RAM limits:

http://msdn.microsoft.com/en-us/library/aa366778%28VS.85%29.aspx 

and good FAQ regarding 64-bit systems here here:

http://msdn.microsoft.com/en-us/isv/bb190528.aspx

Here is an excerpt:

	Maximum RAM*			Maximum CPUs*
Edition	32-bit	64-bit		32-bit	64-bit
Windows XP Professional	4 GB	128 GB		2	2
Windows Server 2003 Standard Edition	4 GB	32 GB		4	4
Windows Server 2003 Enterprise Edition	32/64 GB**	1 TB		8	8
Windows Server 2003 Database Edition	64/128 GB**	1 TB		32	64

(Many people do not like Microsoft but it's hard to deny their technical expertise and the fact that big % of Firebird installations are at Windows now.

I did not  find the same information regarding Linux/Mac OS X but I'll be glad to insert link & info here if someone point me to it).

RAM chips becomes cheaper and cheaper. I remember that in 1999 I saw computer with 64Mb of RAM and that was cool (and in 1994 4GB RAM was just amazing! etc, etc), and now 48Gb RAM is affordable not only for NASA or oil companies.

So if you can put more RAM at server there is no reason to do not insert new chips... except that your software will not use it.

As you probably know recently we did test with 1 Terabyte Firebird database (http://www.ibsurgeon.com/articles/item104) where referred to several real-world Firebird installations which will reach comparable sizes soon.

Since InterBase does not support 64-bit architecture it has no chance to support such big databases and remains database for small and medium size databases.

And it is in even more deep... trap than you can imagine - because since version 7.0 InterBase has the only architecture to serve multiple connections: it's SuperServer.

SuperServer is the single process which maintain multiple threads to serve connected users. 32bit architecture limits RAM size available for InterBase with 2Gb (there is a possibility to use 3Gb at Windows if application will be compiled with special option and if 4-gigabyte tuning (4GT) feature is on.

From practical point of view this approach limits the number of database page buffers which can used by InterBase. For example, if you would set database page size to 16384 and set page buffers to 100 000, InterBase process will occupy 1.5Gb of memory for buffers. The limit is 131072 page buffers with size 16384 bytes (actually gfix will not allow to set more than 131071 buffers to avoid erroneous situation).

So it seems that we have moved to the discussion of Limits. We'll devote next part to the limits of Firebird and InterBase and their differences.

To be continued...

Firebird Optimizer

One of the most mysterious things in Firebird is definitely its optimizer. There were several teams worked at InterBase Optimizer during Ashton-Tate, InterBase Corp and Borland InterBase stages of history, and Firebird team in the beginning of 200x faced the full set of optimizer techniques you can imagine: histograms, ad hoc hacks, different cost algorithms and so on.
Arno Brinkman was one of the the first optimizer warriors: he performed a lot of improvements in optimization approach and made Firebird 1.5 much faster and predictable than previous versions, and it helped Firebird 2.0 won against InterBase 7.5 in tpc-r like test (we have performed it during IBDeveloper times).
Since version 2.1 Dmitry Yemanov is in charge for optimizer development in Firebird. Below is his short presentation regarding basics of optimizer in Firebird.

How optimizer works, how it decides to use this or that index, why sometimes it fails and what you can do to improve performance?
Definitely this presentation has no answers for all these questions but it gives you a basic knowledge of Firebird optimizer internals.

This is not for everyone and requires some qualification, definitely, but it should be interesting for real Firebird geeks.
Enjoy:

Firebird: cost-based optimization and statistics, by Dmitry Yemanov (in English)

View more presentations from Nataly Polyanskaya.

IBDeveloper and amusing nostaligic InterBase magazine from 1997 and 1998

As you know, IBSurgeon was a publisher of "The InterBase and Firebird Developer Magazine" (or shortly IBDeveloper) in 2005 and 2006. There were 4 issues which attracted a lot of interest (up to 20000 of readers) but at that time there was no way to monetize it, so IBSurgeon decided to close the magazine as a redundant line of business.


Now we've put all issues of IBDeveloper to Scribd for easy reading and full text search indexing. You know, there are still "all times greatest hits" articles and materials (like one regarding locking in Firebird by Ann W. Harrison in Issue 2).

Moreover, we decided to put the full version of issue 4, which was not released to public before this time. So if you did not read full 4th issue or others, you have a chance now:

• Issue 1 featured article regarding SAVEPOINTS internals, by Vlad Khorsun, Firebird Core developer
  
• Issue 2 featured article regarding locking by Ann W. Harrison, A Legend
  
• Issue 3 a lot of cool materials and TPC-R based test InterBase vs Firebird (InterBase lost :))
  
• Issue 4, full version TPC-C based tests InterBase vs Firebird vs Yaffil
  

But the interesting fact that it was not the first magazine devoted to InterBase.
In 1997-1998 InterBase Software Corporation published several issues of InterCom magazine. 13 years ago! But still very cool, especially for those who remember those times:

InterCom v01n03, 1997    
InterCom v01n01 - old magazine devoted to InterBase, 1997








May be Firebird needs the third attempt to create the lucky magazine (since 3 is the lucky number) which will not gone in 2 years like InterCom and IBDeveloper did?
Go Firebird!

Friday Fun with Firebird: Curious Backup sizes

There are some tricky and unclear things in Firebird, which do not affect the performance or reliability, but they are curious. Some of them we are going to show, but not explain.

Let's start with restore sizes.
Grab any real-life Firebird database you have (but not an employee.fdb, it will not work with it).
Make a backup.

Then make restore with gbak using only param -c

gbak -c ...

after that make restore to the different database with -v[erbose] output param which make verbose output during restore:

gbak -c -v ...

then take a look at sizes of restored databases.

Are they identical? Usually you can see difference in sizes, equal to 2 database pages. Why turning on verbose output makes such difference? Well, nobody knows :)

Go Firebird! Have a good week-end!

Put papers on Scribd to share and embed

Scribd is a nice system for publishing and,  more important, share books and whitepapers for online reading. We decided to re-work all our whitepapers, manuals, docs and publish them on Scribd:
http://www.scribd.com/ibsurgeon?from_badge_profile_small=1

It gives exciting abilities to enhance our web-site and, moreover, allow to any reader embed  the fully readable version of any "scribded" doc to any site, blog or even newsgroups.

It will look like this:

IBSurgeon: Firebird DBA Tricks and Tips or how to use IBAnalyst to boost your database    

I love web 2.0 :)

People are carefree

This is true - people are carefree. Nobody has recovery plans for their databases, less than half people do regular backups and less than 25% do backups in right way.

Here is a short presentation about IBSurgeon offerings. It is intended for people in trouble, with corrupted database in hands, but can be helpful for people who knows that rainy days happen too.

How IBSurgeon can help you diagnose, fix and prevent corruptions of Firebird and InterBase databases

View more presentations from ibsurgeon.