Firebird Performance Tour

Firebird Performance Tour 2019 will take place in Moscow (November 29, 2018), Prague (December 4, 2018) and Bad Sassendorf, Germany (December 6, 2018).

Firebird Performance Tour will be devoted to the complex parts of SQL queries optimizations and their monitoring:

1. New features in Firebird 4 — Dmitry Yemanov, Firebird core developer
2. Explained SQL plans in Firebird 3 and 4 — Dmitry Kuzmenko, IBSurgeon
3. Understanding Firebird JOINs — Dmitry Yemanov, Firebird core developer
4. Monitoring of SQL queries in Firebird — Alexey Kovyazin, IBSurgeon
5. How to optimize heavy SQL queries with GROUP BY/ORDER BY — Dmitry Kuzmenko, IBSurgeon
6. Clustering: On the road from Master-Slave to Master-Master — Dmitry Yemanov, Alexey Kovyazin
7. Bonus: New Tips and Tricks To Speed Up Firebird database — Alexey Kovyazin, IBSurgeon

More details and registration

gbak -b -e. Compress or not to compress.

Normal backup (gbak -b) by default compresses data. What the compression is, I don't know, don't care, maybe will ask developers about it.
But, gbak have -e option, which turns compression off.

So, I decided to check how it will affect backup, and is there any reason to use it.

I took TPC-R database, 30 gigabytes size, Firebird 3.0.3, and made backup several times to another physical disk (to speedup backup).
Result - option -e makes backup 7.5-7.9% faster. For 18-minutes backup it means 1 minute less.
If backup time is around 10 hours, option -e will save  ~46 minutes. This is good, but also means that you can not pay attention to "use or not to use -e", if your backup time is near 2 hours.

Everything looks good until you will check backup size. Normal backup (with default comptession) of this database have 21 gigabytes size. But -e option produces 34 gigabytes backup file!
This is 30% bigger than normal backup, and 10% bigger than database itself.

So, small benefit in backup speed turns to huge growth of backup size.
Therefore, decide for youself, use -e, or not.

p.s. time difference may depend on your hardware, backup size may differ depending on your data.

Unexpected feature

I have a task to experiment with 2 instances of InterBase XE7 at the same computer.
Install went OK over existing instance gds_db. Don't forget to specify "Multi instance feature" = YES and new port name and number at the second dialog of installer.

Well, first of all, after running 2 instances of InterBase XE7, one at gds_db/3050, another at gds_db/3100, I decided to check do they ever work by connecting to the same database.

You know, SuperServer is one process, that opens database for itself, and none others. So, theoretically (and in previous versions) it locked database file using exclusive access, to avoid file corruption by modifying it with concurrent processes.

Ok, but both instances were able to connect to one DB at the same time! What the crap? Looking into monitoring tables (tmp$) I found that second instance (second connect) show only 1 record in tmp$attachments, while first show 2 records (one of them garbage collector).
Mmm... Then I came to an idea that second instance connected in read-only mode. Tried to update some data, and ...

The insert, update, delete, ddl or authorization statement cannot
 be executed because the transaction is inquiry only.
attempted update on read-only database.

Well, I got the idea but did not understand the purpose. Because the instance that connects to the database first will get read-write access, and second - read-only. But there are none options that can set "read-only mode for instance x". So, wins the first one?
For me it seems completely useless and even dangerous in production - unpredictable first connection from the wrong instance can lock DB (read-only) for the main instance. The better way, I think, was to lock DB in exclusive mode. At least, it can show at connect time that someone already opened DB by some another instance.

Incompatible Compatibility

As you know (maybe), InterBase XE7 have support of previous ODS (database format) of InterBase 2009 (ODS 13) and InterBase XE/XE3 (ODS 15).
This, I think, was supposed to help moving to XE7, starting to use XE7 with old databases, and "if something goes wrong" to have an ability to return to 2009 or XE/XE3.

But...

InterBase XE7, when opens database in ODS 13.1 format (InterBase 2009) upgrades it's minor version to 13.2. And after that InterBase 2009 can not open this database anymore. Because it supports 13.1, not 13.2. Oops. Incompatible.
So, support of ODS 13 by XE7 seems useless?

Mess of gbak options

I see lot of places where gbak options (Firebird, InterBase) specified randomly.
Examples:
gbak -c -r

what do you mean? -c or -r? Documentation specifies -c | -r. Symbol "|" is OR, not AND. Moreover, -r is not restore, it's REPLACE!
Thanks god, Firebird 1.5 and higher disabled -r option, because it allow to drop original database file without warning. Now you must specify -rep or -r o. Anyway, both of these is useless, because -c is enough. If you write script, rename original file before gbak -c. If something went wrong, you will have original DB file safe.

gbak -t ...
Come on, -t is default option for transportable backup. You think that someone will change it later to non-default? I doubt so.


gbak -c ... -page_size ...
don't incude this to regular script. Page size change usually made once, by administrative decision. If you specify this option in the script, and there is 4096 specified, after changing page size to 8192 and regular restore you will get 4096 again. Leave -page_size option to manual change.

gbak -b ... -ig ...
Ignoring checksum errors is useful only when database is corrupted. If you include this option to regular script, you may not find that database is broken.

p.s.

45 Ways To Speed Up Firebird Database

12 Common Mistakes while Backing Up Databases

HQbird: advanced Firebird SQL for Enterprises

Delimiter hell

You know, that each computer can have different regional settings. Moreover, some users change this settings to their preferences.
For example, if I like '.' as decimal separator, I can change it, if by default it is ','.

But, we are speaking here about Firebird and InterBase, right? Yes. There is a tool, GSTAT, used to get statistics from DB. It uses fixed decimal separator, '.'. So, if you run gstat -r employee.gdb, somewhere in the file there will be lines

COUNTRY (128)
    Primary pointer page: 407, Index root page: 408
    Average record length: 26.00, total records: 14, max record length: 31

You see "26.00" ? Right, here dot is used as delimiter, and it's being used forever, and system settings are ignored.
But, someone in Embarcadero, working on new InterBase version, decided to use system delimiter for Services API. So, if we will try to get statistics not by gstat, but by services API, we can get

    Average record length: 26.00, total records: 14, max record length: 31
or
    Average record length: 26,00, total records: 14, max record length: 31

I don't know why and when this happened. But I found this only few hours ago...

Don't miss Firebird Conference!

Only 15 days left before Firebird Conference, Luxembourg, 26-27 October.
Unique chance to handshake Firebird developer team, drink beer/wine during evening gathering, and, of course, to learn lot of interesting things about Firebird and application development!

http://firebirdsql.org/en/firebird-conference-2012/

Read the topics list, speaker names, and join us!

Operating system on RAID

We deal with the hardware by lot of different cases - when someone ask us what to buy for the new server, how to improve existing hadware, and, of course, when database becomes corrupted.

Many hadware vendors (as we see) sell servers with ready to use RAID (1, 5 or 10), with operating system installed on that raid. Also these hardware vendors think that it is cool that customer will place everything else on that RAID, and it will work perfectly (long and stable), so, no additional disks. That common mistake leads to real pain if something happens with the RAID.

Well, if you had your database on the RAID, and it fails, you need to find latest backup, and restore from it (after repairing RAID, of course).
But, if there were operating system on that RAID, it becomes a bit more complicated.

10 minutes ago we had a request to repair database from the RAID, broken by it's controller fail. The database is a real mess, so, sorry, there is nothing left to repair.
I don't know, was there also operating system, or not, but if it were:
- Your server failed, and it can't boot. You can't understand what happened with RAID.
- You need to attach additional HDD to server, install operating system on it, boot, then install RAID drivers and RAID software.

How much time it will take?
Well, let's put operating system on separate drive. RAID failed? Well, ok, but we can boot and look at RAID state.
You have doubts about that hard drive reliability? Make image backups. OS does not occupy much space - such an image can be stored even on flash-drive.

Ok, someone will say - yes, I will separate OS and my data. But I still want to keep OS on RAID 1, for reliability. Of course, it will protect OS from single disk failures, but, if RAID controller will fail, we will be at the starting point of that story.

Thus, my opinion is to have cheap (or not) and one (!) HDD for OS, that can be changed in a few minutes, and can be bought in any computer store nearby.
This solution, in spite of cheapness, can save lot of time in case of failure.
Think about it. Don't rely on "cool expencive RAID". Be a bit paranoid. :-)

no_garbage_collect

You do not know what is that? Well, InterBase and Firebird allow specifying connection parameter that will disable garbage collection by statements in this connection.
This is the same as the option -g for gbak. And, of course, all other connections without this parameter will continue to try collecting garbage in the database, if any.

This connection option mostly is not known, because standard and "closed source" drivers usually does not produce access to server-specific features. Thus, you may use this option only for IBX (IBExpress components in Delphi and C++Builder), FIBPlus, and some other "direct access" components. ODBC, dbExpress? No, of course.

But, why you may ever need this option?
Sometimes you may want to run huge report that will scan lot of data. And, at the moment this report will run, there may be lot of garbage record versions in the database. So, your report connection will collect garbage (especially if you use Firebird Classic), and report will run much slower than it could be.
In this case, before running the report, you may set no_garbage_collect option, and report will be fast as a rocket.

I also knew some small applications with small databases (up to 1gb), that used no_garbage_collect option for all connections. This way garbage is not collected at all, and during the day size of database could grow 2 or 3 times. But, each day database was scheduled to backup and restore, so, why to care about garbage?

Since I do not see needs to do backup and restore each day, I do not recommend disabling garbage collection this way. Use it only for specific connection, if you ever need to do this at all.

Are you ready? Yes, I am.

Alexey posted interesting info about sudden computer failure. Since I'm working with computers from 1987, I wan to share my experience too.

3 years ago my computer won't turn on. Possible causes could be - processor, motherboard or power unit. After some thinking I choosed to buy new power unit, and I was right - motherboard and processor were alive. I was lucky not buying new motherboard, righ?
During last 3 months I hadn't any problems, but
- videocard on my wife's computer died
- motherboard of my daughter's computer died

In first case it took 2 days to diagnoze what happened. The second took 1 day. But, anyway, daugter's computer still don't work, because I hadn't bought new MB and processor.

Alexey's case also shows that it is very important to have hardware nearby to change. So, it means that you computer (server) may stop working because something is dead

1. processor
2. motherboard
3. videocard
4. power unit
5. hard drive

While first 4 parts you may change without affecting your system, last part, hard drive, is a core of your system. If you don't have backup, you will not be able to return server to it's working state. Yes, you can spend some time installing OS at your new hard drive, but, have you ever thought about how much time it will take?

Alexey and I speaking about desktops. Alexey had a lot of computers at home, and a netbook, so, the failure isn't so hard. Also, my daughter is "safe", because all the music and photo's at her hard-drive still undamaged.
But, really, what if the server will stop working? How much time it will take to restore it? Think about software as a hardware. Database failure can cause same damage, as broken hard drive.

p.s. right now we are at contact with the customer, who has broken hdd. Instead of 3 databases restore service produced 9 databases. Now customer need to understand what databases are the latest ones, and which one we need to repair. Also, databases are heavily damaged, so, only copies of that databases may help to restore data.

Tips'nTricks using FBScanner

Yes, sometimes I use FBScanner too. :-)
My system is complex, because I have huge number of Firebird, InterBase and Yaffil versions. While Yaffil does not interfere with Firebird and InterBase, I need to run periodically Firebird 1.0, 1.5, 2.0, 2.1, 2.5 and InterBase 6.x, 7.0, 7.1, 7.5, 2007 and 2009. I do this by removing services records with "instsvc remove" after installation, because I don't need FB or IB as a service and run them ony as application like

fbserver -a
or
ibserver -a

To simplify this task I've created several cmd files that looks like
fb2.cmd:
call remove_all.cmd
d:\firebird2\bin\instreg install
d:\firebird2\bin\fbserver -a

and remove_all.cmd is:
d:\ib71\bin\instreg remove
d:\ib75\bin\instreg remove
d:\ib2007\bin\instreg remove gds_db
d:\ib2009\bin\instreg remove gds_db
d:\ya\bin\instreg remove
d:\intrbase\bin\instreg remove
d:\firebird\bin\instreg remove
d:\firebird2\bin\instreg remove
d:\firebird25\bin\instreg remove
...

So, if I need to run Firebird 1.5, I simply call fb15.cmd and less than in a second I have Firebird 1.5 running. If I need to run InterBase 2007, I just stop Firebird 1.5 application (shutdown) and run ib8.cmd.

Well, returning back to the FBScanner. By default it tries to find Firebird or InterBase service installed and intercept it's configuration to work on different than 3050 port. Unfortunately for the FBScanner I have only InterBase 4.1 service installed. Anyway, I leave FBScanner configuration as is, to intercept 3050 port and to redirect it to port 3052.
Then, I'm editing firebird.conf for example for the Firebird 2.1, uncommenting and changing parameter RemoteServicePort:

RemoteServicePort = 3052

So, when I start fb2.cmd my Firebird 2.1 runs and listens to port 3052, not to 3050.

So, if I will connect from any application to the Firebird, FBScanner will intercept traffic to the 3050 and will log everything is happening between Firebird server and client.

But, sometimes I don't want to intercept or watch some specific connections, or to watch connections only for specified databases. That's simple!
You need to know, that if fbclient.dll finds in the path one level above the file firebird.conf, it will use port number specified in it.

For example, if I will connect to some database with IBExpert, specifying client libriary as ...firebird2\bin\fbclient.dll, it will use port 3052 from the firebird.conf and traffic will not be intercepted by FBScanner.
Instead, if I want traffic to be intercepted by FBScanner, I need to write server name not as localhost, as usual, but as localhost/3050. This time traffic will go through FBScanner, and every statement and transactions will be monitored.

I hope this example will help you to configure Firebird and FBScanner if you want to check what your application is doing with the server.

Nostalgia

Remember our IBDeveloper Magazine, no? It was (and is) at the website www.ibdeveloper.com, but some time ago it was hacked, so, your browser may tell you that you should not open this link.

Anyway, we started to place interesting presentations about Firebird and InterBase on Scribd, and now decided to put there our IBDeveloper Magazine, all 4 issues. And, we found old lovely InterCom magazine issues (one of us a bit thrifty, or provident, if you wish) and placed there too.

If you spent years with InterBase, don't be shy to drop a tear on a keyboard while re-reading InterCom issues from the past century.

64 bit Delphi. Who needs it?

I'm watching not only the InterBase and Firebird newsgrops and forums, but the Delphi also.
And I know that at least lot of russian Delphi programmers complaining about still non-existing support of 64 bit Windows in Delphi.

Today at DelphiFeeds.com I saw the post "64 bit tommorow – Wh/if you’ll have more than 4GB “today”?", and want to share my opinion on this. Also I wish you to vote at that post, as I did.
That post has a lot of technical replies, but I want to look at "business" point of supporting 64 bit Windows.

Yes, 64-bit operating systems are used now, but mostly for servers. But Delphi programmers write programs mostly not for the servers, but for the usual customers, working on desktop computers.

Let us look at very good Steam report:
http://store.steampowered.com/hwsurvey/

Right now ~18% of gaming computers uses 64-bit OS. But, gamers are not enterprise customers. 32-bit programs still works well at 64-bit operating systems, but 64-bit programs can't run on 32-bit OS.

Moreover, I'm sure that most of Delphi developers who wants 64-bit support really wants only to use things they have, without the details how it can be done. Maybe I will look a bit rude for someone, but I think the Joker quote can be used here:
"You know what I am? I'm a dog chasing cars. I wouldn't know what to do with one if I caught it."

Of course, some Delphi developers really needs 64-bit Delphi. But for what tasks?

• middleware application servers
• scientific software
• compatibility/dll software

And that's it. 95% of software written in Delphi, or even more, designed for the end-users, who doesn't care about 32 or 64bits, and by the specific of this applications 64bit support will give nothing to them. Currenly, the more fun stuff is with multi-core processors. What stock or accounting software can utilise more than 1 core of processor? And what for? And the main question - do you know how hard to upgrade operating system for the enterprise, where lot of compatibility things need to be in count?

Interesting, that using GPU for computation gave much more capabilities and performance for the scientific applications than 64-bit systems. You may, if any, have not more than 20-30% increase of the application performance if it goes from 32 to 64bits, and only if it is optimised for that, but using GPU allows to speedup computations up to 100 times.

But, don't consider me as an orthodox person, I'm just a bit sceptic, and trying to look at things realistic. I believe in mult-cores - games can utilize up to 3 cores now! -, and I believe in 64-bit.

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.