Christmas discount - 20% off

Dear Friends!

IBSurgeon wishes you Merry Christmas and Happy New Year 2007 and announces traditional Christmas sales for all our products.

To get 20.07% discount you need to use discount coupon code:

xmas2007

This promotion is valid until 31 December 2007.

Save up to USD$340 (for IBSurgeon Pack Site License)!

Click "Buy Now" and don't forget to enter a coupon code!):

IBFirstAID Personal - save USD$60!
https://secure.shareit.com/shareit/product.html?productid=200879

IBAnalyst Personal - save US$39!
https://secure.shareit.com/shareit/product.html?productid=214084

IBSurgeon Pack Personal - save US$130!
(IBFirstAID+FBFirstAID+IBAnalyst+IBUndelete+IBBackupSurgeon)
https://secure.shareit.com/shareit/checkout.html?productid=300050548&quickbuy=1

IBSurgeon Pack Site/Technical Support license - save US$340!
https://secure.shareit.com/shareit/checkout.html?productid=300072100&quickbuy=1

Kind regards,
IBSurgeon Team

Firebird and FastReports seminar in Warsaw

Sorry for being late, and sorry for short seminar desctoption :-)
Series of world-wide Firebird seminars ended this year with seminar in Warsaw. This was a special seminar, because it was organized by www.SoftKey.pl. Great thanks to Masha Kostrzebska - everything went great, and also hotel (Lord) was very fine (better than in Prague, and definitely better than in Paris - those who were in Paris hotels will understand me).
Also there was a first experiment with translation - thanks to Yan).
~50 people visited the seminar. ~90% of them used both the FastReport and Firebird.
In common, everyone was satisfied by the seminar - speakers, organizers, and attendees, of course.

Photos from the seminar.

p.s. we and SoftKey.pl said that all seminar visitors will get 15% discount on all IBSurgeon products at SoftKey.pl. Right now we are sorry that not all of our produtcs are available at this electronic software shop. We will fix this soon, and discount will not be limited only for this year.

Firebird seminar in Prague

9 November, in Prague, in the series of Firebird events, there was a Firebird seminar. It was organized by IBPhoenix, IBSurgeon and FastReport with the great help of Pavel Cisar, IBPhoenix representative in Czech Republic. Sessions by main Firebird developer Dmitry Emanov, Dmitry Kuzmenko (IBSurgeon) and Mike Philippenko (FastReport), described Firebird roadmap, internals, security, optimization, database repair, and more.
There was about ~50 attendees, from different cities and companies. We had enjoyed interesting questions. It's nice to see that Firebird in Czech Republic and France (where seminar was at 30 October, in Paris) has strong and growing position and used by many professionals.

So, everything went fine, including coffe-breaks and lunch :-) Pavel Cizar found and arranged excellent place with suitable seats, nice personnel and good coffee and meal.

The next in the Firebird Events will be 2nd Firebird and InterBase Russian Conference, 23 november, and Firebird seminar in Warsaw, Poland, 7 december.

Firebird seminar in France

30 October 2007, in Paris, France, there will be free seminar, organized by IBSurgeon (www.ib-aid.com), IBPhoenix France (www.ibphoenix.fr), Fast Reports (www.fast-report.com) and Mandriva (www.mandriva.com).
Speakers at seminars will be Paul Beach, President of Firebird Foundation, with keynote and Firebird news, Vlad Khorsun, Firebird core developer, Dmitry Kuzmenko, IBSurgeon CEO and recovery and optimization expert, Mike Filippenko, Fast Reports CEO, and Philippe Makowski, vice-president of Firebird Foundation and chief consultant at IBPhoenix France.
Seminar is devoted to the Firebird project news and Firebird 2.1 release features, to new IBSurgeon and Fast Reports tools and technologies in database protection, reporting and business intelligence with Firebird, and more.
There will be round table with Firebird developers and experts in the end of seminar.

Seminar will take place at 43 rue d’Aboukir 75002 Paris. Seminar is free for attendees.
Registration is here: http://www.ibphoenix.fr/spip.php?article79, it will be closed at early Monday, and only 3 seats remained now.

InterBase 2007 Service Pack 2

While the SP1 was the only small bugfix and Vista fix, SP2 can be named something like InterBase 2007.5. Because it have some new interesting functionality:

• Journal preallocation - you may specify initial journal size
• Database file preallocations - you may specify initial database size
• DESCRIPTOR parameters for UDFs - ability to pass different data types at runtime, while parameter type is not specified in UDF declaration
• Query optimizer improvements:
• shortcut boolean expression evaluation
• redundant index usage in query disjuncts
• outer join and sort/merge optimization
• invariant False restrictions in queries

Also to protect databases from OS and hardware failures database write mode for new databases by default now is SYNC.

You can find full description of these new features in IB2007UpdateGuide.pdf, which can be found after installing this update in Doc subdirectory. But, as always, we suggest you to save the whole Bin subdirectory of existing IB2007 installation before applying Service Pack 2.
At least it will be wery good to save ibserver.exe, gfix.exe and gbak.exe - if you will find some unusual or unwanted in this SP2, you will have an ability to return to SP1 as fast as possible simply moving these files back to the bin directory.

p.s. this is not approved, but some people have problems with journal archive after installing SP2. So if you use this functionality, save your journal archive also before installing SP2. We are investigating this issue, and soon provide the details - is this problem really exist or not.

Firebird Developer Pack from IBPhoenix

IBPhoenix announced a comprehensive set of tools for professional Firebird database application development - "IBPhoenix Firebird Developer Pack". This pack includes

• Firebird database engine
• Tools for database application development and management
• Tools for database monitoring and optimization
• Tools for database recovery and maintenance
• Replication tools
• Database connectivity drivers: .NET (with Visual Studio integration), Delphi/C++Builder, ODBC, JDBC, PHP, Ruby On Rails, Python, Perl, C++.
• Flexible reporting solution
• A comprehensive documentation set for Firebird
• Free 30-days installation support

This pack costs $799 for single developer, and there are also special discount for current customers of IBReplicator, IBSurgeon products or Database Workbench.

IBFirstAid 2.0

Today new version of IBFirstAid is released. In short, now there are 2 separate versions - for Firebird and Interbase, both have lot of improvements.
Read details at www.ib-aid.com.

Does InterBase run on Windows Vista?

Thanks to Fredrik Haglund for his post about this question.
There are some issues with License Management, Local protocol (same as for Firebird, but IPCName can't be changed in ibconfig, as I know), and IBConsole.

Does Firebird run on Windows Vista ?

Yes, it does!

Of course, you want to know the details. Here they are.
I used Vista Business that I've got some days ago from Microsoft. First I tried to install Firebird 2.01 RC1 distributive, and it went fine. Sorry, I have not tested Classic yet, but at least there are no problems with SuperServer.
Since lot of public tests show that Windows Vista is slower than Windows XP at least for games, I thought that for the first time "backup test" will be enough to understand is there any performance loss.

My desktop system is:
AMD 64 3500+ (socket 939)
1GB RAM
EPOX-9NPA3 Ultra (Nforce 4 Ultra chipset)
NVidia GF 7600GT

and 3 hard disks
system: HDS728080PLAT20, IDE, IBM/Hitachi, 80gb
second: ST3200827AS, SATAII, Seagate, 200gb
third: HDS728080PLA380, SATA, IBM/Hitachi, 80gb

Vista index of this computer is 4.2 (hdd and graphics are good - ~5.5, but processor and memory not - ~4.3).

I used 2gb database and run gbak -b -g using local protocol, tcp (localhost) and services api. Right now I don't want to go into test details, because it will be special article, not a blog record. So, here are just common results:

• local protocol is not working on Vista, if Firebird runs as service. This is a classic error "unavailable database", which happens when program and Firebird works as service, sometimes when program runs as application, and in some other unknown conditions.
  Really this is not painful, because I think that Firebird (or InterBase) must not be run as service on developer's computer. If you run Firebird as fbserver -a, local protocol will work on Vista, but ~20% slower than on Windows XP. So, get into the way to use tcp (localhost) connection.
• Backup using services and tcp show the same speed as on Windows XP.
• Using -v (console output) gbak option makes backup process ~2-3% slower than on Windows XP. This is because console output on Windows Vista is made via 3D graphics (new Vista GUI interface).

So, you can move to Windows Vista and continue to use Firebird without any performance loss (excluding local protocol). Of course, you should not use Vista as "peer-to-peer" server, because it will be waste of new graphics user interface. If you have some dedicated, not powerful computer as Firebird server - use Windows NT, Windows 2000, and maybe Windows XP, but not Windows Vista.

New nbackup (or online dump) myth

Today I've made some simple tests of Firebird's nbackup and InterBase 2007 online dump features.
They have some differences, but one main point. Let's describe what nbackup or online dump is:

You know that if you try to copy database when connections are active, you'll get broken copy. Because copying works sequentially, while database is a random access file. The idea of nbackup and online dump is to copy pages (by server) from database using timestamp marks. When such copying starts, server does not write changes to database file, but into special temporary file. When copy is done, server writes those modified pages back to database (and maybe to online dump). So, original database can be read and written during this process, and resulting copy is consistent.

This is fine, and according to nbackup documentation you can organize incremental backups, using "backup levels". When you make nbackup level 0, full copy of database is made. When you make nbackup level 1, only changes from previous full nbackup is written to difference file. And so on.
Example of "practical nbackup application" says that you can use full backup monthly, level 1 backup daily, and level 2 backup hourly. This is fine, but "hourly backups" can become impossible.
Why these "hourly backups" looks suspicious to me?

Because each time you run nbackup (any level) in Firebird or online dump in InterBase 2007, server reads every page in original database.
I took 13gb database from tpc-c test to check what time is needed to read the whole database.
At first I tried to copy database from one SATA drive to another. This took 6 minutes.
Then I created backup level 0 with FB and online dump with IB 2007. Both reads database and writes it's full copy:

• Firebird - 6 minutes
• InterBase 2007 - 8 minutes

Not a big difference, but let's run test again (nbackup level 1, and online dump):

• Firebird - 6 minutes
• InterBase 2007 - 6 minutes

This time both servers only reads original database, and writes nothing, because database was not changed.

So, there is no difference what backup level you use - database will be read 100% each time. And, you see that reading 13gb database took 6 minutes when no users were connected. I think that when there will be some activity, "backup" time will be definitely longer. Since reading each page of the database is not an easy operation for HDD, this will of course lower server performance.

Here, I think that running nbackup or online dump each hour for 10gb database is not a good idea. And I'm sure that if you have ~50gb database, you will not be able to do incremental backups each hour, when users work with database.
If you don't believe this - make this simple test, and send me results.

BTW: At least you must be sure that you have fast and well configured RAID controller, and disks that you use for database and its "copy" have the same performance.

p.s. Don't try to place incremental backups on the same hard drive, even on different logical disks - if your hard drive fails, you will loose both - database and backup. And, nbackup/online dump time will be 2-3 times slower.

Backgound garbage collection don't work?

InterBase 6.0 introduced background garbage collection, but in active multi-user environments this caused to increase accumulation of garbage in database.
In InterBase 7.1 Borland added 2 parameters, enabling to do something with garbage collection thread, allowing at least to experiment some with this issue. These parameters are

• SWEEP_QUANTUM - how much server time garbage collector thread will take. Or, as written in documentation, how much records garbage collector will check in one pass
  
• SWEEP_YELD_TIME - pause between each garbage collection attempts
  

Playing with this can help, but not much.

We made special test to understand how server will collect garbage if there are lot of updates of the same data (not the reads). The test was simple - table with 10k rows, and application that update and commit (pressing one button) these 10k rows by one update statement. For each server version we made 15 updates/commits in one row, and then checked statistics with IBAnalyst 2.0 (it has "refresh statistics button" and allow to see data and versions in database).
Also this test allows to check how new garbage collection in Firebird 2.0 works.

The results are:

• All InterBase and Firebird versions with Superserver architecture and background garbage collection (GCPolicy=background for FB 2.0) left garbage in database, even after long time of waiting. So, we can say that background garbage collection really don't work with default configuration parameters. Of course, not all garbage record versions was left in database, but 70% of garbage left is a huge number.
• Setting SWEEP_YELD_TIME to 1000 did the best for InterBase 7.5 and 2007 - right after all 15 updates only 10k versions was left.
• Setting SWEEP_YELD_TIME to 10000 causes IB 7.x/2007 to collect all the garbage after ~1 minute, but only if you do not touch server
• GCPolicy = combined in Firebird 2.0 works like SWEEP_YIELD_TIME=1000. But this is not the same because IB 7.x/2007 does not have non-background garbage collection at all.
• GCPolicy = cooperative in Firebird 2.0 doesn't leave any garbage, but makes updates to run slower.

We asked Vlad Horsun, one of the main developers of Firebird, to comment this. And he found interesting thing in Firebird sources (also this test allows us to think that the same thing is true for all InterBase versions from 6.0 to 2007) - when garbage collector thread checks some data for garbage, it fails if there are updates is made, and leaves data not garbage collected.
Vlad also made experimental Firebird 2.0 build with special fix, and our tests went successfully - even with GCPolicy option all of the garbage was collected right after 1-2 seconds of last update/commit.

So, the conclusion is: if you see with IBAnalyst that garbage in your database grows constantly, try to play with configuration parameters - GCPolicy in Firebird 2 and SWEEP_YIELD_TIME in InterBase 7.5/2007. At the same time with Firebird 1/1.5 and InterBase 6.x/7.1 you do not have chances to fix that, and the only solution is to run gfix -sweep on your database from time to time.

p.s. Soon there will be full article with description of sweep, garbage collection, and practical examples.

Do you like null?

"Firebird Null Guide" - nearly complete but boring descrption about nulls. Some things are missing:

• client-side specific of null processing
• bugs description does not refer to Firebird bug tracker
• no examples or links to "by descriptor" UDFs

Anyway, I have not seen anything close to this document.

Statistics autoupdate

Several days ago I saw poll about auto-updating index statistics feature for Firebird at firebirdnews.org. Here is the results link.

Well, 28% of voters think that this will be good. Interesting, that I can't say percent of systems where this feature will make performance worse. And can't say also, what percent of those 28% this will affect.

Right now this is not a hard question to make auto-update of index statistics by yourself. You can use gidx tool from gtools, AT or cron + isql running your handmade script, and so on.
But, the main question is when and how often this auto-update feature must start by itself.

IBAnalyst 2.0 have some thoughts about it in its help file, in Additional Q&A section (10-th Q/A). Here is the summary:
Depending on your applications activity record count in some tables may vary more than 50%. So, statistics update must be made at the moment when data in such tables is very close to mostly useful value. Example: if you reload some table with data several times per day, you need to refresh index statistics for that table only when table is populated with data, not empty. Because, I'm sure, you want performance when you run queries on non-empty table.
But, only you know when your application deletes data from table and fills it with new data. And there will be the best moment to apply SET STATISTICS command.

p.s. Note that index statistics is being built by index. And index keys does not contain transaction numbers, so SET STATISTICS can't understand are those keys belong to deleted data or not.
p.p.s. running SET STATISTICS on all indices in 2gb database takes about 35-45 seconds on AMD 64 3500 and SATA HDD.