Firebird Surgeon

FBScanner 3.6.1 is released

noreply@blogger.com (IBSurgeon) — Thu, 16 Jan 2014 18:30:00 +0000

New version of FBScanner (3.6.1) is released. FBScanner is an unique tool to trace and audit Firebird (1.0 - 2.5) and InterBase (4.0 - XE3) SQL traffic (connections, transactions, queries), it's very efficient tool for performance optimization and SQL bug tracking.

FBScanner works like a proxy, and enables transparent data capture and analysis:

FBScanner can be used for the following tasks:

Find queries with bad performance (i.e., slow queries with high prepare and execute times, queries with bad plans)
Track transactions usage
Real-time view of connections, users and transactions (connections can be killed)
Retrospective analysis of SQL traffic
and more - see presentation here.

FBScanner has 2 licensing options:

Per server - FBScanner can be used on the single server or computer to track Firebird traffic.
Unlimited ISV subscription - FBScanner can be installed on any number of computers during the 1 year for use with the specific software application from the single independent software vendor (ISV).

All users of FBScanner 3 already have FBScanner 3.6.1 in IBSurgeon Deploy Center.

User of FBScanner 2.x can upgrade to FBScanner 3.6.1 with 50% discount - please contact upgrade@ib-aid.com

Download now:

Demo version of FBScanner can be found here:
FBScanner Demo version

Please look at the FBScanner User Guide to get more information about its capabilities:
FBScanner User Guide

Firebird Tour 2013: detailed schedule for seminar “All About Transactions”

noreply@blogger.com (IBSurgeon) — Tue, 12 Nov 2013 19:57:00 +0000

Firebird Tour 2013: seminar “All About Transactions”

Siegburg, November 22, Kranz Park Hotel

Prague, November 25, Hotel Extol Inn

Overview

#	Topic	Speaker	Time
1	Overview of Firebird development	Vlad Khorsun	30
2	Overview of transactions concept and its parameters. Examples of transactions parameters and their usage.	Alexey Kovyazin/Pavel Cizar	45
3	Transaction in Firebird: peculiarities and special parameters. Transaction inventory page, versions and how transactions work in multi-generation database.	Dmitry Kuzmenko	60
	Coffee-Break		15
4	Inside Firebird transactions: what happen inside engine during transactions’ start, commit, rollback. What is retaining, autocommit, how transactions work with GTT. Memory consumption for transactions processing.	Vlad Khorsun	60
5	Transactions interaction and best practices for application development. Conflicts between transactions.	Dmitry Kuzmenko	60
	Lunch		60
6	Garbage collection mechanism and sweep in details	Vlad Khorsun	45
7	Transactions management in various drivers (Delphi, .NET, Java, etc): parameters and approaches.	Dmitry Kuzmenko/Jiri Cincura	45
	Coffee-Break		15
8	Diagnosing transaction management problems: most prevalent problems, how to catch and fix them.	Alexey Kovyazin	60
9	Round table		45

Detailed plan

1. Overview of Firebird development

a. New features and details of Firebird development,

b. Plan for next releases

2. Overview of transactions concept and its parameters. Examples of transactions parameters and their usage.

a. Historical overview of transactions: locking engines, dirty read, why Firebird has only snapshot и read_committed

b. Transaction as logical operation: bridge between business logic and database implementation

c. What is ACID

d. Isolation levels: standard levels and Firebird levels

e. read only\write

f. wait and nowait

g. Examples of transactions with various isolation levels, read-only/write and wait/nowait, from the client point of view

3. Transaction in Firebird: peculiarities and special parameters. Transaction inventory page, versions and how transactions work in multi-generation database.

a. Full syntax of SET TRANSACTION and TPB parameters

b. Some undocumented parameters

c. Who create transactions

i. Application

ii. System transaction (tx #0)

iii. Garbage collector

iv. Autonomous transactions

v. Triggers ON CONNECT\ON DISCONNECT

d. Versioning basics

e. How versions are being created

f. States of transaction in Firebird (active, committed, rolled back, limbo)

g. Visibility of record versions in transactions

h. Key differences between Read Committed and Snapshot

i. Triggers for transactions start and stop

j. Context variables at transaction’s level

k. GTT and read-only transactions

4. Inside Firebird transactions: what happen inside engine during transactions’ start, commit, rollback.

a. What happens at transactions’ start

i. Global transaction markers (OAT, OST, OIT) and their meaning

ii. Autosweep

b. What happens at transaction commit

i. How DWF works

ii. DDL queries

iii. Dispatching events

iv. Flush of dirty pages

v. TIP processing

c. What happens at rollback

i. Standard rollback with cache flush

ii. Forces rollback without cache flush

iii. Rollback transactions with undo-log

iv. Rollback without undo-log

d. What is retaining

e. Auto-commit (does anybody know about it?)

f. Managing memory for transactions

i. Temporary BLOBs

ii. Undo-log

g. Local TIP and related issues

5. Transactions interaction and best practices for application development. Conflicts between transactions.

a. Transactions interactions types

b. Templates for transaction usage in application development

c. Scenarios breakdown: Readers and Readers, Readers and Writers, Writers and Writers

d. Best practices

i. Dictionaries

ii. Splitting read and write transactions

iii. Locking record with empty Update

iv. Locking with SELECT WITH LOCK

v. Reports

vi. Serialization: how to build effective queues

6. Garbage collection mechanism and sweep in details

a. Why we need sweep?

b. How sweep works

c. Types of the sweep: cooperative, background, background

d. Differences between sweep and garbage collection

e. Autosweep: pro and contras

f. Examples of application design mistakes which lead to excessive garbage

7. Transactions management in various drivers (Delphi, .NET, Java, etc): parameters and approaches.

a. What are direct and non-direct client drivers?

b. BDE, dbExpress, ODBC, .Net

c. IBX, FIBPlus, FireDAC

d. Transactions management in applications: examples

e. How to manage transactions lifetime in various drivers

f. How to implement edit dialogs in the effective and safe way

g. Commit retaining

8. Diagnosing transaction management problems: most prevalent problems, how to catch and fix them.

a. What we can see at gstat -h

b. OIT, OST, OAT, NEXT - their combinations and what they mean

c. When garbage collection does not work?

d. Transaction markers dynamics analysis with IBTM. Catching sweep moments.

e. How to determine quantity of garbage versions

f. How to find long transactions with MON$ tables

g. Transactions monitoring with MON$ and TraceAPI.

h. Legacy applications: workarounds for error in transactions management

9. Round table: questions and answers

Answers for 3 transactions questions

noreply@blogger.com (IBSurgeon) — Tue, 24 Sep 2013 14:58:00 +0000

So, it's time for the answers.

The question 1 is the easiest:

1. In some transaction there were 3 inserts made. The third INSERT raises exception. Is it possible to commit this transaction?

[ ] yes, [ ] no

Answer is Yes, of course. An ability to commit or rollback transaction does not depend on insert operations which were performed in the frame of transaction. Of course, usually application logic is built in the way to commit only successful set of operations, but it's not a rule.

The second question is a bit more tricky:

2. In some table there were 10 records. 3 simultaneous transactions were started and then in each transaction 1 record was inserted. In transaction 3 query SELECT COUNT(*) FROM Table was executed. The result will be:

[ ] 10, [ ] 11, [ ] 12, [ ] 13

In this question you need to take an account that transactions are not committed. As you know, all changes in the frames of non-committed transaction are visible only inside this transaction, so the answer will be 11.

The third question is devoted to the generators concept of Firebird engine.

3. Initially we have generator with name id, its value is 10. We have simultaneous transactions A and B. In A executed gen_id(id,1), in B gen_id(id,1). Then A was commited and B was rollbacked. What value is in id?

[ ] 10, [ ] 11, [ ]12

Generators (or sequences) work outside the frames of transactions. It means that commit or rollback of transactions does not affect the resulted value - i.e., generator ID will be increased both in A and B, and correct answer is 12.

Did you answer all these questions correctly? I guess so, since they are very easy.

There are more interesting topics related with transactions. For example, do you know that each database in Firebird has a limit of transactions which can be performed between backup/restore cycles? And, more interesting, that unused transactions occupy space in the database, which is released only after backup/restore, and there could be up to 700Mb of such space in the worst case?

If you are interested, consider to visit Firebird 2013 Tour seminar "All About Transactions" in Siegburg, Germany, November 22, 2013.

3 questions about transactions every Firebird developer should answer correctly

noreply@blogger.com (IBSurgeon) — Mon, 23 Sep 2013 13:17:00 +0000

Hi All,

As you probably know, Firebird Project will have a seminar, devoted to transactions topic, in Siegburg (Germany), November 22, 2013. More details: http://firebirdsql.org/en/firebird-conference-tour-2013/
Many developers think that transactions are straightforward and easy thing to understand and use, so probably there is no reason to devote 1 day seminar to discover transactions mechanism in Firebird in great details. Is it so?
Here I offer 3 simple questions about transactions which every Firebird developer should answer. Please take a look and choose the correct answer:

1. In some transaction there were 3 inserts made. The third INSERT raises exception. Is it possible to commit this transaction?

[ ] yes, [ ] no

[ ] 10, [ ] 11, [ ] 12, [ ] 13

[ ] 10, [ ] 11, [ ]12

So, take your time and choose correct answers. Tomorrow we'll consider the questions and their answers.

Update 1 for FBDataGuard 2.7

noreply@blogger.com (IBSurgeon) — Wed, 11 Apr 2012 10:11:00 +0000

Update 1 for FBDataGuard 2.7 is available for download (archive for Linux, User Guide). It replaces the previous version of FBDataGuard 2.7. This update contains important stability improvements (for environments with more than 200 concurrent users) and several bugfixes.

Please upgrade your FBDataGuard 2.7 installations as soon as possible. This is a minor upgrade, so instead of full reinstallation you can stop FBDataGuard 2.7 service, replace dataguard.jar (from new dataguard27.zip) and start it again.

Do not hesitate to ask any questions: support@ib-aid.com.

DBInfo - new IBSurgeon's free product

noreply@blogger.com (IBSurgeon) — Fri, 20 Jan 2012 12:18:00 +0000

IBSurgeon DBInfo is designed to visualize internal structure's statistics, so administrators or developers can view how much data, index, blob or other kind of information your Firebird or InterBase database stores.

Frequiently Asked Questions

1. What is the purpose of DBInfo?

This tool allows you to

view how much data, index, blob or other kind of information your InterBase or Firebird database store
detect some database corruptions
look at database page size, overall file size, database format
have some fun watching dynamically changing % indicators.

2. What InterBase and Firebird versions are supported?

Any from InterBase 4.0 to the latest InterBase and Firebird versions. DBInfo supports the following database formats:

8.x - InterBase 4.x
9.x - InterBase 5.x
10.0 - InterBase 6.x, Firebird 1.0
10.1 - Firebird 1.5.x
11.x - InterBase 7.0/7.1/7.5
11.x - Firebird 2.0/2.1/2.5
11.x - Firebird 2.x
12.x - InterBase 2007
13.x - InterBase 2009
15.x - InterBase XE

3. Do I need to have InterBase or Firebird server running?

No. DBInfo can check database file by itself, without server, and it does not need client library also.

4. Can I check for database contents on remote server?

A. Yes and no. Since DBInfo opens database file by itself, i.e. use direct access to database, you can check for database contents of database on remote server only if this database file is being shared on network.

5. May I use DBInfo if my database is on Linux (or other Unix)?

No, DBInfo is a Windows Win32 tool, so you may only check database files that can be accessed as files from Windows - locally or at shared folders.

6. Can DBInfo corrupt my database?

Never. DBInfo only reads the database file, and never writes to it. The open mode of the database file is read-only in any case.

7. May I check database contents while InterBase or Firebird is working with it?

Yes. DBInfo can open the database file even when InterBase or Firebird works with it. But, since reading the whole database file affects disk performance, be sure that at the moment your database is not at high load by applications.
Also, if you will check the database when the database is being modified by InterBase or Firebird, DBInfo may show warning that database is corrupted, which may be wrong.

8. What is the purpose of the option "Do not use Windows file cache"?

DBInfo can scan database file with two modes - using Windows cache or not using it. Usually when you copy file on Windows, it goes to the file cache. And, file cache is being allocated from physical memory. So, the bigger is file, the more amount of file cache is used by Windows. This can cause applications and other data to being moved to virtual memory, and slowdown computer performance.
For example, for database files larger than 500mb you could notice contiguous slowdown of DBInfo while scanning database file. Thus, we decided to turn off using cache in DBInfo by default. This allows to scan 1-10-100 gb databases without performance loss for DBInfo and operating system itself.

9. Why percentage of blob pages is marked by that color?

The colors is used to paint percentage bars does not have any meanings, except for bad pages found that is marked as red. This is the only case when we pays your attention to the damaged pages found in database.

10. What I can do if DBInfo found that my database is corrupted?

The better way is to immediately check the database by gfix and IBFirstAid Diagnostitian, and send full log to support@ib-aid.com. We will be able to help with Database Repair Services or you can buy gull version of FirstAID.

11. What if I want to see how much space is taken for each table and index in database?

In this case we suggest you to use our IBAnalyst tool, that not only can show you detailed information about your database, but also can give some notes and suggestions about performance optimizations

IBSurgeon Log Viewer: new free product

noreply@blogger.com (IBSurgeon) — Sun, 15 Jan 2012 20:40:00 +0000

IBSurgeon Log Viewer is designed to facilitate viewing and analyzing long Firebird and InterBase logs. Many Firebird and Interbase developers and administrators do not pay enough attention to the contents of firebird.log (interbase.log), though there is a lot of useful information, which can help to find and resolve various issues with Firebird/InterBase databases.

Originally Log Viewer was IBSurgeon's internal application, which helped to quickly navigate through very long Firebird and InterBase logs, and in version 2.0 it became mature enough to be released for the community.
Download free version

Install it and open server’s log (usually it's in C:\Program Files\Firebird\Firebird_n_n\ folder). There are also descriptions for log entries shipped with IBSurgeon Log Viewer:

Feel free to contact IBSurgeon Support (support@ib-aid.com) if you see suspicious messages in your logs. Don't forget to attach log to the message!

Firebird Conference 2011: Luxembourg, November 25-26

noreply@blogger.com (IBSurgeon) — Tue, 04 Oct 2011 08:48:00 +0000

The Firebird Project invites all Firebird users and developers to participate in the Firebird Conference 2011, hich will take place in Luxembourg, November 25-26, 2011.

IBSurgeon is a sponsor of Firebird Conference 2011, and we recommend all our customers to go there.

The Firebird Conference 2011 will be devoted to a variety of important topics: Firebird SQL language improvements, performance, optimization, database protection and recovery, and many others.

A number of presentations will be done by the Firebird Core team, which will ensure access to information at the best technical levels. The Firebird Conference 2011 will be a place to improve your Firebird development skills and give a chance to ask questions directly to Firebird Core team.

Firebird Core Team will be represented by Dmitry Yemanov, Vlad Khorsun, Alex Peshkoff, Philippe Makowski, etc. There are also will be speakers from IBSurgeon and other Firebird-related companies.

More information: www.firebirdsql.org/en/firebird-conference-2011/

Firebird 2.5.1 (sub-release) is available

noreply@blogger.com (IBSurgeon) — Fri, 30 Sep 2011 10:38:00 +0000

Firebird Project is happy to announce general availability of Firebird 2.5.1 - a minor release of the award-winning relational database management system. This sub-release introduces several bug fixes and many important improvements  - for example, performance improvements during a database restore, the ability to write to global temporary tables in read-only databases, etc.

For the full list of changes please refer to the Release Notes, Chapter 2 "New in Firebird 2.5".

Firebird 2.5.1 has 100% compatible on-disk structure with Firebird 2.5.0, so it is recommended to migrate to 2.5.1 as soon as possible. To download Firebird 2.5.1, please visit the download section at the official web-site: http://firebirdsql.org/en/firebird-2-5-1/

Tip for Sweep

noreply@blogger.com (IBSurgeon) — Thu, 25 Aug 2011 15:06:00 +0000

As you probably know, manual sweep (by invoking gfix -sweep) is the important part of Firebird database maintenance (especially for big databases). Unfortunately, there are few people who understand the internals of sweep process. In this post we will not explain the magic of sweep, because it requires long and detailed explanation, instead of this we will provide you with the simple method to check that sweep was completed successfully and fulfilled its task.
After running gfix -sweep you need to run any Firebird client (like isql.exe) and commit at least one transaction - this is necessary to move other transactions' markers after sweep.
Then run gstat -h and check its output: all transaction markers should be aligned (i.e., with minimal gap):

        Oldest transaction      16702

        Oldest active           16703

        Oldest snapshot         16703

        Next transaction        16704

If you see that gap is more than several transactions, it means that sweep did not remove all possible garbage (and in this case you can see gap of hundreds/thousands transactions). Unfortunately, sweep does not produce any errors or messages in firebird.log, so it's hard to determine the reason of failure.
Also, the big gap is an obvious alert to check database statistics (produced by gstat -r, visualized by IBAnalyst).
The most often reason why sweep does not clear all record versions is long-running writeable transaction (and this is the most often reason why automatic sweep does not work well), but there are other unpleasant options, like database corruption.
If you are sure that there were no connected users during sweep, or you saw that sweep finished unusually quickly for the big database (like several second for database 5+Gb in size), consider it as an alert, and run validation (gfix -v -full) as soon as possible.

Disadvantage of such approach (in terms of recognizing problem in system area of Firebird with failed sweep) is that sweep usually scheduled to run once per day, and it requires explicit attention of administrator, because only indirect signs appear. To monitor database health around the clock we are using our FBDataGuard tool - it check the same metadata that sweep touches during sweeping, and it sends alert immediately if something is wrong.

You DON'T need CPUAffinity, nBackup, shadow and multifile databases

noreply@blogger.com (IBSurgeon) — Tue, 23 Aug 2011 09:05:00 +0000

Right now at www.firebirdnew.org you can see the survey about Firebird features in production environment.

Some answers look like dangerous signals, especially for those who run big Firebird databases.

So, what's wrong with these answers?

1. CPUAffinity. CPUAffinity can be used to bind Firebird SuperServer to some particular CPU/core. At present we have plenty of cores even at desktop workstations, so there is no reason to use SuperServer and limit Firebird to the only CPU - use SuperClassic or Classic architectures to run Firebird at full throttle.

2. NBackup. In IBSurgeon we do not recommend our clients to use NBackup without external monitoring and as the only way of backup. NBackup makes the straight copy of database on page level - it's fast, but, unlike the gbak, it does not check pages' contents. If you use only NBackup and does not perform necessary database maintenance (at least regular sweep, combined with transaction markers monitoring), and someday your database became corrupted (due to RAM problem or abnormal shutdown, for example) , NBackup will continue to make (and overwrite) "backups". Another danger is "frozen: delta-file, when database are not unlocked correctly, and all changes are written to delta-file, and cannot be merged due to delta file problem. To use nbackup strengths and avoid pitfalls, we setup special backup scheme for our clients.

3. Shadow is completely useless in modern production environments. It provides protection from the only type of corruption - occasional critical crash of HDD (assuming that shadow is configured correctly, with 2 separate HDDs). Use RAID5 (or RAID10) instead - it will be much faster and more convenient for maintenance.

4. Multifile database. There is no actual reason to use multi-file database at present. With multi-file it will be impossible to do NBackup, database files will be tied to its locations, and it will give no advantage at all. Often multi-file databases are being implemented to perform copying of database to DVD, but you need to switch off Firebird during this operation.

Summary

Of course, the title of this post is a provocation, and sometimes administrator need to use CPUAffinity, nbackup and other features (not shadow!), in order to achieve some specific result, but it should be done correctly and with full understanding of steps to be done. NBackup is the most useful tool, which is very often understimated both in terms of strengths and possible problems with it.

More information:

"Firebird's Big Database" presentation at slideshare:

http://www.slideshare.net/ibsurgeon/firebirds-big-databases-in-english

Firebird: how to find SQL queries which use bad index?

noreply@blogger.com (IBSurgeon) — Sun, 17 Jul 2011 16:32:00 +0000

In the first part of our optimization story ("How to ruin Firebird performance...") we mentioned that indices should be analyzed before dropping, even if they appeared to be very bad in IBAnalyst in terms of garbage and number of unique values.

In essence, we need to log all queries used in applications, get their execution plans and check, how indices are used.

For this we need to log all SQL traffic between applications and Firebird database.

There are 2 ways to do it in Firebird 2.5 and later: FBScanner and Trace API, and the only way in early versions of Firebird: FBScanner.

FBScanner (Firebird Scanner) is a tool that can monitor and view all traffic between Firebird and InterBase servers and their client applications.

FBScanner can log all SQL traffic to text files and external Firebird database, it includes FBScanner LogAnalyzer module to analyze SQL performance.

Why not use Trace API?

For Firebird 2.5 users using TraceAPI could be good solution, but for high-load system TraceAPI can add too much load to Firebird. FBScanner add only 5-10% overhead to general operations and, moreover, it can be installed on separate [debug] server, so users and applications will not see any difference.
Also, as you can see below, FBScanner offers convenient user interface to analyze plans and queries.

FBScanner stores all information into the single table. It uses self-links to reduce the amount of stored information and it makes raw log hard to read and understand.

To facilitate log analysis we have created new module in FBScanner 3.0 - LogAnalyzer.

At first start LogAnalyzer will create necessary indices, it can take several minutes.

After that LogAnalyzer will show the last available day in the log at the “Server Load” tab:

“Server Load” tab shows how many SQL queries were run per minute, and how much time they took to execute. Effectively it shows server load, i.e., number of queries and their execution times.

Zoom in (button in the top left corner of the tab “Server load”), drag graph by holding right-button of the mouse and select the peak you are interested to investigate – click right-button to show popup-menu

It will show you tab “All statements”, where you can browse SQL queries

Select any query to see its text and, if plan logging feature is enabled, its plan.

To follow the execution flow, you can right-click on the query and look for connection and transactions for this query

LogAnalyzer marks bold queries in the same transaction:

You can sort queries and, for example, find query with the longest execution time:

To know more about this query - double-click on it and see more details

More information about FBScanner is available in "FBScanner User Guide".

After fixing plans we can delete bad indices. But good question - how to assure that new plans will act normally in production environment?
See answer for this question in the next article.

How to ruin Firebird performance with bad indices

noreply@blogger.com (IBSurgeon) — Sun, 10 Jul 2011 13:03:00 +0000

Abstract

This short article shows by example how bad index can affect Firebird database performance.

Test database

Imagine you have simple Firebird database:

CREATE DATABASE “E:\TESTFB25INDEX.FDB” USER “SYSDBA” PASSWORD “masterkey”;

CREATE TABLE TABLEIND1 (

I1 INTEGER NOT NULL PRIMARY KEY,

NAME VARCHAR(250),

MALEORFEMALE SMALLINT

);

CREATE GENERATOR G1;

SET TERM ^ ;

create or alter procedure INS1MLN

returns (

INSERTED_CNT integer)

BEGIN

inserted_cnt = 0;

WHILE (inserted_cnt <1000000) DO

BEGIN

Insert into tableind1(i1, name, maleorfemale) values(gen_id(g1,1), 'TEST name', (:inserted_cnt - (:inserted_cnt/2)*2));

inserted_cnt=inserted_cnt+1;

END

suspend;

END^

SET TERM ; ^

GRANT INSERT ON TABLEIND1 TO PROCEDURE INS1MLN;

GRANT EXECUTE ON PROCEDURE INS1MLN TO SYSDBA;

COMMIT;

For some reason you need:

· insert 1 million records

· update all these records

· delete all records

· run SELECT count(*) from this database.

To perform it, we can use the following script (to run it immediately after database creation script) :

set stat on; /*show statistics*/

select * from ins1mln;

update tableind1 SET MALEORFEMALE = 3;

delete from tableind1;

select count(*) from tableind1;

and store result for future analysis.

Then we will create database with the same structure - but add index for MALEORFEMAIL column

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MALEORFEMALE);

As you can see, we insert to this column only 0 or 1 integer values.

Then we repeat the script with this index and compare results.

Results are in the following table:

Without index for MALEORFEMAIL

With index for MALEORFEMALE

SQL> set stat on; /*show statistics*/

SQL> select * from ins1mln;

INSERTED_CNT

============

1000000

Current memory = 10487216

Delta memory = 80560

Max memory = 12569996

Elapsed time= 13.33 sec

Buffers = 2048

Reads = 0

Writes 18756

Fetches = 7833503

SQL> update tableind1 SET MALEORFEMALE = 3;

Current memory = 76551788

Delta memory = 66064572

Max memory = 111442520

Elapsed time= 15.04 sec

Buffers = 2048

Reads = 16166

Writes 15852

Fetches = 6032307

SQL> delete from tableind1;

Current memory = 76550240

Delta memory = -1548

Max memory = 111442520

Elapsed time= 3.27 sec

Buffers = 2048

Reads = 16147

Writes 16006

Fetches = 5032277

SQL> select count(*) from tableind1;

COUNT

============

Current memory = 76552064

Delta memory = 1824

Max memory = 111442520

Elapsed time= 1.35 sec

Buffers = 2048

Reads = 16021

Writes 1791

Fetches = 2032278

SQL> set stat on; /*show statistics*/

SQL> select * from ins1mln;

INSERTED_CNT

============

1000000

Current memory = 10484140

Delta memory = 75524

Max memory = 12569996

Elapsed time= 23.94 sec

Buffers = 2048

Reads = 1

Writes 23942

Fetches = 11459599

SQL> update tableind1 SET MALEORFEMALE = 3;

Current memory = 76548712

Delta memory = 66064572

Max memory = 111439444

Elapsed time= 29.30 sec

Buffers = 2048

Reads = 16167

Writes 19492

Fetches = 10035948

SQL> delete from tableind1;

Current memory = 76547164

Delta memory = -1548

Max memory = 111439444

Elapsed time= 3.41 sec

Buffers = 2048

Reads = 16147

Writes 15967

Fetches = 5032277

SQL> select count(*) from tableind1;

COUNT

============

Current memory = 76548988

Delta memory = 1824

Max memory = 111439444

Elapsed time= 0.69 sec

Buffers = 2048

Reads = 16021

Writes 1901

Fetches = 2032278

So, bad index decreases performance by approximately 2 times while inserting or updating. Also we can see that non-optimal index greatly increases number of writes and record fetches.

Let’s get statistics for this sample database (with bad index for MALEORFEMALE) and try to find some details.

To gather statistics, we run the following command:

gstat -r e:\testfb25index.fdb > e:\teststat.txt

TABLEIND1 table and indices statistics section looks intriguing, but what useful information it gives to us?

TABLEIND1 (128)

Primary pointer page: 166, Index root page: 167

Average record length: 0.00, total records: 1000000

Average version length: 27.00, total versions: 1000000, max versions: 1

Data pages: 16130, data page slots: 16130, average fill: 93%

Fill distribution:

0 - 19% = 1

20 - 39% = 0

40 - 59% = 0

60 - 79% = 0

80 - 99% = 16129

Index RDB$PRIMARY1 (0)

Depth: 3, leaf buckets: 1463, nodes: 1000000

Average data length: 1.00, total dup: 0, max dup: 0

Fill distribution:

0 - 19% = 0

20 - 39% = 0

40 - 59% = 0

60 - 79% = 1

80 - 99% = 1462

Index TABLEIND1_IDX1 (1)

Depth: 3, leaf buckets: 2873, nodes: 2000000

Average data length: 0.00, total dup: 1999997, max dup: 999999

Fill distribution:

0 - 19% = 0

20 - 39% = 1

40 - 59% = 1056

60 - 79% = 0

80 - 99% = 1816

To understand the meaning of shown numbers and percentage values, we can use IBAnalyst tool, which offers visual interpretation of database statistics:

By clicking Reports/View recommendations we can find the appropriate explanation for this index:

Bad indices count: 1.

By `bad` we name indices with many duplicate keys (90% of all keys)

and big groups of equal keys (30% of all keys). Big groups of equal

keys slowdown garbage collection - MaxEquals here is % of max groups

of keys having equal values. Index search for such an index is not

efficient. You can drop such indices (if they are not on FK

constraints).

Index ( Relation) Duplicates MaxEquals

TABLEIND1_IDX1 (TABLEIND1) : 100%, 50%

In production databases often we can see many bad indices, which can greatly affect database performance. In this example we can see table with 13 millions of records which have 7 bad indices, which are (most likely) useless and greatly decrease Firebird performance.

Usually the first move of developer is to drop these bad indices. On other hand, it’s possible that bad index was used in some particular SQL query, which required specific combination of indices (including bad one) in order to run fast enough.

Firebird SQL optimizer has complex rules, and it often happens that dropping of bad index can lead to significant performance degradation in some queries.

Certainly, these SQL queries should be rewritten to use more optimal execution plan without bad index, but there is a still a problem: how to find SQL queries which use bad index?
The answer is in the next episode of our Optimization series of articles.

Firebird Case Studies Catalog

noreply@blogger.com (IBSurgeon) — Tue, 30 Nov 2010 17:41:00 +0000

To support Firebird Project and MindTheBird!, we'll re-print their message here:

Hello All Firebirders!

Today we start very important activity - building Firebird Case Studies catalog.

WHAT IS CASE STUDY?

Case study is an example of real-world implementation of certain product. Case study should be very specific and describe
1) technical details (database size, # of users, transactions load),
2) type of business (ERP, hospital automations, CD catalogs, web-sites
examples, etc) and
3) business value – license costs economy, performance increasing, etc.

WHY FIREBIRD PROJECT NEEDS YOUR CASE STUDIES?

People are looking for templates in all areas of the life.
There are hundreds of “sitting ducks” Firebird deployments, which are very good in all terms – database sizes, # of users, high ROI for business, etc, etc.

We need to go for them and build the collection of case studies at www.firebirdsql.org web-site to clearly demonstrate to the world the actual value of Firebird deployments.

This is very important for Firebird Project future development and its recognition in IT world. Let's expose the power of Firebird to everybody, and for this we need participation of each Firebird developer!

WHO CAN PARTICIPATE IN FIREBIRD CASE STUDY CATALOG?

Yes, you can!
Firebird needs case studies and stories of all types - so regardless of what software you create - for bakery automation or to navigate space ships - create and submit your case study.
Every EXE-file or web-site which uses Firebird is important for Firebird project, if it does useful things. It will take only 1-2 hours and it will help Firebird a lot!

HOW TO CREATE FIREBIRD CASE STUDY?

Download Firebird Case Study Guide and Template www.mindthebird.com/docs/firebird_case_study_template.doc

You will go through several steps that are described in this template and then fill out the necessary text sections, attach appropriate graphics and write several quotes. We estimate time to create case
study in 1-1.5 hour.

Only case studies in English are accepted, but if you have Case Study in several languages, it will be good addition.

If you have several good examples of Firebird usage, create several Case Studies for each.

The result will be submitted to Firebird Project Marketing and a professional designer will create the final case study document in PDF format.

PRIZES AND BENEFITS

Every submitted case study will be considered by MindTheBird Team and Firebird Project Marketing and, if it will be considered as a good and complete description of the real-world project or existing packaged
software, it will be included into Firebird Case Studies Catalog at firebirdsql.org, which will run in Q1 of 2011.
First 20 Case Studies will have prizes from the list www.mindthebird.com/benefits.html and, of course, they will be specially listed at minthebird.com and, later, at firebirdsql.org.

ANY QUESTIONS?

Send your questions to case(at)mindthebird.com

Vacations' corruptions

noreply@blogger.com (IBSurgeon) — Mon, 29 Nov 2010 06:20:00 +0000

Every year we have numerous corruption requests after vacations. Unattended computer systems can reach their implementation limits or encounter hidden problems, like lack of space, HDD bad blocks, automatic updates, etc.
Firebird is a software too, and it has "popular" reasons to stop working while administrator enjoys roasted turkey.
Before vacations and after them we recommend to check the free space on HDD with database and HDD on backups, check the transaction markers state (gstat -h) for the gaps and limits and, if possible, perform full backup/restore cycle (with gbak -b/gbak -r).
These simple actions can save your database from "vacations' corruptions".

Firebird Performance Testing - presentation is available

noreply@blogger.com (IBSurgeon) — Tue, 19 Oct 2010 14:17:00 +0000

Please take a look at presentation from Firebird Performance Testing webinar.
If you have any questions regarding tests, please feel free to ask them via email

Firebird.performance.testing

View more presentations from Mind TheFirebird.

Firebird Day'2010 in France

noreply@blogger.com (IBSurgeon) — Fri, 08 Oct 2010 07:31:00 +0000

October 1, 2010 - Firebird Day took place in Paris, France.
31 attendees and speakers:

Philippe Makowski, Firebird Foundation
Thierry Laborde (CodeGear) : Delphi and Firebird.
Henri Gourvest (progdigy.com) :
Unicode, UIB, FB2.5 specifities in UIB.
Frédéric Guillien (GrizzlyDev) :
Maintenance best practice.
Alexey Kovyazin
Anti-corruption approach for Firebird - ppt is here are http://www.slideshare.net/ibsurgeon/firebird-anticorruption-approach.
Paul Reeves (IBPhoenix) :
PHP Framework and Firebird.
Pierre Yagger :
Firebird and Ruby/Rails.

Conference is ready to start

Philippe Makowski (is launching Firebird 2.5, so photo is too light :))

Henri Gourvest (progdigy.com) :
Unicode, UIB, FB2.5 specifities in UIB

Paul Reeves (IBPhoenix), Philippe Makowski and unknown person, preparing for the next session (Firebird and PHP)

Some moment of the conference

Almost all people were until the end of conference - probably, presentations were good!

3d Russian Firebird Conference: Photos

noreply@blogger.com (IBSurgeon) — Tue, 05 Oct 2010 18:24:00 +0000

September 29, 2010 - 3d Russian Firebird Conference. 114 people attended. Here are some photos

Philippe Makowski and Dmitry Yemanov - coffee before sessions.

Sessions will start soon.

Philippe and Dmitry.

Kirill Sinitsin from Ansoft, Phillipe Makowski and Dmitry Yemanov

Opening of conference

Phillipe's talk

Alexey Kovyazin, Alex Peshkoff, Vlad Khorsun and Phillipe Makowski

Dmitry Yemanov started his presentation

Alexey Kovyazin's presentation

Room is full

Embarcadero session about Delphi

Alex Peshkoff's presentation

Another Embarcadero presentation

Unique photo: Firebird and InterBase at the sofa: Dmitry Yemanov and Christine Normile

Presentation about www.lensart.ru

Round table in the end of conference - from left to the right - Dmitry Yemanov, Alexey Kovyazin, Christine Normile, Vlad Khorsun, Philippe Makowski.

Alex Peshkoff, Vlad Khorsun, Philippe Makowski, Alexey Kovyazin

Firebird 2.5 Introduces New Audit Features and Improved Scalability

noreply@blogger.com (IBSurgeon) — Mon, 04 Oct 2010 11:34:00 +0000

October 4, 2010. The Firebird Project today announces Firebird 2.5, the fifth and newest major release of its open source relational database management system. Enhancements in this new release are pitched at better serving the needs of businesses of any scale, from embedded device to enterprise resource planning solutions.

Philippe Makowski, President of the non-profit Firebird Foundation observes that the Firebird 2.5 release is a very important step in Firebird's 10th anniversary year, not just for the Firebird Project but for the whole open-source world, too.

"Today businesses all around the world are looking for mature, cost-effective solutions and Firebird 2.5 offers them a solution that is truly powerful and truly free", he noted.

The mix of high performance, small footprint, supreme scalability, silent and simple installation and royalty-free deployment make Firebird a highly attractive choice for all types of software developers and vendors. Firebird deployments are well established around the globe, serving data to hundreds of thousands of business systems with hundreds of users and databases exceeding 300GB.

Known Firebird installations number more than 2,000 each day, according to download statistics.

"Many of our customers have Firebird databases with sizes from 200GB to 400GB and they keep growing", said Stewart Spink, CTO of Watermark Software. "The performance improvements in Firebird 2.5 will ensure that their future demands will be satisfied completely."

Alexander Shaposhnikov, CIO of medical distributor Profitmed predicts that Firebird 2.5 will support the growth of Profitmed's business. "Currently we have a 65Gb database and 250 active users working 24x7. We intend to double both the size of the database and the number of users in two years and we have chosen Firebird 2.5 for this business-critical task."

Firebird binaries are distributed for a variety of system and hardware platforms: Windows, Linux, MacOS, Solaris, HP-UX and more. Firebird runs on on x86, x64, PowerPC, Sparc and other hardware platforms, providing an easy migration mechanism between these diverse platforms.

The V.2.5 release binaries ship for 32-bit and 64-bit Windows and Linux, with MacOSX x86 to follow. Other POSIX platform builds are likely to appear before the year is out, in response to demand.
New features of Firebird 2.5

* New SuperClassic Architecture Firebird 2.5 introduces a new architecture, tagged "SuperClassic", to gain better leverage from multi-core and multi-CPU hardware environments and improve resource usage for systems with large numbers of users and huge databases.

* Audit System audit tracing and user trace sessions via the Services API enable nearly real-time monitoring and analysis of everything going on in a database

* Cross-database queries Firebird 2.5 opens the way for Firebird database instances to query one another and to exchange information

* Enhanced user management User management becomes accessible and flexible through SQL requests submitted from user databases

* Other features include autonomous transactions within PSQL modules (stored procedures, triggers, SQL blocks), support for regular expressions as arguments in SQL using the SIMILAR TO predicate, asynchronous cancellation of connections, enhancements of monitoring capabilities and much more.

Paul Beach, President and CEO of IBPhoenix, an organisation that offers professional services and contributes to Firebird development, considers the new security features and the impressive scaling capability of Firebird 2.5 will be welcomed by many existing Firebird users.

"In particular, it will help those in the finance and health industries to meet the growing requirements of their businesses", he notes, "especially in pushing the limits of performance."

David Wilder, CTO of Bas-X, welcomed the Firebird 2.5 release for its improved useability and flexibility. "It will enable our customers to turn over more transactions and run more concurrent users on the same hardware," he said.

Pricing and Availability

Firebird 2.5 is an open source database system that is available free of charge for any kind of usage, be it commercial, educational, non-profit or simply for private use. Both binary packages and the complete source code can be downloaded at no cost through the Firebird Project website for immediate installation. No registration or activation is required.

About the Firebird Foundation

The Firebird Foundation is a non-profit organization with the goal to support the development and growth of the Firebird relational database system. The Foundation was incorporated in 2002 and is currently supported by more than 300 active members, several of whom are also cash sponsors. It also accepts and manages donations of cash and resources from persons and organisations that are not regular members. The Foundation redistributes these funds as grants to some of the key workers in the Firebird Project. The Firebird Project has no other source of income.

Oracle Worst Practices

noreply@blogger.com (IBSurgeon) — Tue, 07 Sep 2010 17:03:00 +0000

It seems that database design problems are very common and do not depend on used database platform - every new generation of developers each 4-5 years repeats the same mistakes...
The presentation below is recommended for every database developer, regardless of the database being used.
My favourite slide is about "How many tables do you really need? - Four at most!" :-))

Oracle Database Worst Practices

The need for backups

noreply@blogger.com (IBSurgeon) — Mon, 06 Sep 2010 10:45:00 +0000

I've read the post at http://successfulsoftware.net about 10 things which non-technical users do not understand. During our work with Firebird database recoveries we interact mostly with people considered as IT professionals - developers and system administrators, but we can confirm the absolute truth of the following words regarding backups:

8. The need for backups

Every few days I get an email from someone who has lost all their  data because they had a major hardware problem and no backups on a  separate device. Sometimes this is because they don’t even realize the  data is stored on their computer. You can mention the  need for back-ups in your documentation and/or in the software, but  it is unlikely to make much difference. History shows that this is a  lesson most people have to learn the hard way (techies included).  Mentioning it doesn’t hurt though and it might help to defuse an angry  users if you point it out to them after the event.

Consider this post as a small reminder that your Firebird database should have backups, and they should be done in the right way. 

How to increase Firebird backup and restore speed (up to 30%)

noreply@blogger.com (IBSurgeon) — Wed, 01 Sep 2010 05:24:00 +0000

It seems that many Firebird developers and administrators are not aware about switch -se[rvice] for gbak.exe
The origin of this switch is in the fact that code for backup and restore can be invoked in Firebird twice - first as explicit external application (i.e., through gbak.exe) and, second, as a Services API call.

Backup/restore through Services is faster than usual approach - due to the bypassing of interprocess communication and, probably, due to some IO optimizations.

To use Services instead of regular gbak.exe you need to add switch -se to the gbak command:

gbak -se service_mgr -b -g ...

Try to run backup with your database with and without switch -se[rvice] and compare results. In our experience switch -se can increase backup and restore speed up to 30%.
Btw, our FBdataGuard uses Services API to run backup and restore, and many users find it faster than general gbak.exe. Now you know that it's not the magic - just good knowledge of Firebird and its internals.

InterBase 2009 installer - say hello to Windows 3.1 again

noreply@blogger.com (IBSurgeon) — Mon, 30 Aug 2010 06:44:00 +0000

It seems that InterBase development suffers from the lack of resources. Recently I downloaded InterBase 2009 Developer Edition in order to install it at new workstation, with intention to look at corrupted database from customer, started to install it and saw this dialog:

Someone new it IT could not not even recognize where is this dialog from... it's from Windows 3.1. Installation is the first experience that new users have from InterBase and what they see? Old-styled dialogs indicate the level of attention from company to its product.
On other hand, localized and fast (and much smaller) Firebird installer at Windows looks much better and give very positive user experience, because opens source developers create products for own use too, not for the abstract customers.
Also, CodeGear in the installation location - IB2009 was published in August 2008, when Embarcadero already bought virtual CodeGear division from Borland, and there were at least 3 service packs and 1 hotfix after that.
It seems that there is nobody in Embarcadero who is responsible for the renovation of InterBase installer.

Update
After this blog-post Sriram Balasubramanian from Embarcadero contacted us and commented on branding issue:

Thank you for the report. InterBase 2009 branding and name specifications were set before the Embarcadero purchase timeline for CodeGear. Thus the name still reflects C:/CodeGear/interbase/

Since the installer does not get branding/naming changes in Service Pack releases, it still (unfortunately) reflects no change.

We have already made the branding changes in an upcoming release of InterBase, http://channel-e.embarcadero.com/index.php?option=com_jvideodirect&v=2BIc9syaWrknH&x=1

If there are other installer specific input that you'd like to suggest, please post your comments to http://forums.embarcadero.com and/or log a case in Quality Central for InterBase (at http://qc.embarcadero.com).

Thanks!

Best wishes,
Sriram Balasubramanian
InterBase,
Embarcadero Technologies

3d Russian Firebird Conference

noreply@blogger.com (IBSurgeon) — Sun, 08 Aug 2010 12:01:00 +0000

3d Russian Firebird Conference will take place in Moscow, September
29, 2010. The conference will be devoted to the Firebird new versions
and to best practices in maintenance and development, with special
focus on big Firebird databases.

Special speakers at conference: Phillipe Makowski, President of
Firebird Foundation, Firebird developers team: Dmitry Yemanov, Vlad
Khorsun and Alex Peshkoff.
Registration (in Russian): http://ibase.ru/conf2010/

Free FBDataGuard for Firebird Developer Day participants

noreply@blogger.com (IBSurgeon) — Sun, 25 Jul 2010 13:35:00 +0000

At FDD'2010 Dmitry Kuzmenko has presented 2 presentations - the is one about approach to maintain big databases (50-400Gb) in the right way and the second is about restore speed tests.
Among other thing Dmitry said that any participant who will send email request to dataguard at ib-aid.com till 20-JUL-2010, will get this tool's 1 year license for free.
We just finished to process 108 requests and sent them all (check antispam folder if you did not get it).

So, it is interesting to see that approximately 20% of attendees can think about right database maintenance and how to avoid corruption. Other 80% are welcome to our recovery service.