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!