On Fri, 2005-07-15 at 02:40 +0000, Maurice Ling wrote:

> --- In firebird-support@yahoogroups.com, David Johnson
> <johnson_d@c...> wrote:
> >
> > aside: A mysql user suggesting that Firebird is not a "real" RDBMS?
> > mysql doesn't support two phase commits (performance versus
> > reliability), and only qualifies as relational if you take the minimum
> > possible requirements and twist them a bit to the left. Firebird is a
> > very different architecture, but it is almost (_almost_) a drop-in
> > replacement for DB2, and its commercial predecessor was originally
> > architected and marketed to compete directly with Oracle.
>
> I must say that I'm very interested in this issue. What do you meant
> by "only qualifies as relational if you take the minimum possible
> requirements and twist them a bit to the left."
>
> With system administrators still considering that mysql is far more
> superior than FB, what concrete proofs do we have?
>
> maurice
>

I suggest looking back in the archives on this and the Firebird-
Architecture lists for the many discussions surrounding mysql versus
firebird. This has been explored eloquently by people with far better
inside knowledge of both products than I have. Also, look at the
resources on IBPhoenix.com.

I'll quickly recap what I consider highlights of the prior discussions,
with the caveat that others on this list are far better qualified to
directly compare the two products.

1. On some benchmarks, mysql performs faster than firebird. However,
the measured performance difference is less than the improvement
measured between current generation firebird and the vulcan rewrite.

2. There are a number of mathematically defined characteristics that
must be met in order for a database to be considered relational. By the
most common interpretation of those definitions, mysql does not fully
meet those requirements. However, with very loose interpretation, it
barely meets the requirements. The database theorists amongst us can
fill in the gaps far better than I (or tell me I'm full of something
unpleasant - which I may be on this point).

3. Similarly, mysql is "transactional" only in the sense that a
statement is a transaction. This interpretation of "transactional" is
unique to the owners of mysql. Other databases that consider themselves
to be transactional (read the rest of the world) allow you to aggregate
a number of statements into a transaction, which is then considered
atomic. Either the entire set of statements that were executed are
committed, or the entire set rolls back. This has important
implications for reliability in the long term and under heavy loads, and
for resolution of concurrency in real-time systems.

Firebird is truly transactional, as are postgre, oracle, DB2, MS-SQL,
SYBASE, and others. These truly transactional DBMS's are able to fully
support the back end of a banking operation, meeting Sarbanes-Oxley
requirements, with out gyrations in the application layer.

mysql, in contrast, because it is not transactional in the sense that
the rest of the world uses, could not pass the Sarbanes Oxley
requirements to act as the back end of a banking or financials system on
its own. The product's shortcomings in this department would need to be
made up for in the application software.

Here is an experiment to try. In mysql and Firebird, both, create a
table - bankaccounts containing an ID and a balance.

In a transactional system, transfer $50000.00 from my account to your
account:

start transaction
try
update bankaccounts set acctbalance = acctbalance - 50000 where
ID=myaccount
randomly fail
update bankaccounts set acctbalance = acctbalance + 50000 where
ID=youraccount
commit
catch
rollback

In Firebird, if there is a power outage or "system glitch" before
completion of the commit, then after you get things running again,
NEITHER STATEMENT'S RESULTS WERE POSTED. The money never moves. I can
still pay you what I owe you.

With mysql, if the power outage occurs between the completions of the
two update statements, money is gone from my account but it never
appears in yours. I am out my money, and since I don't have that much
more just laying around to pay you, you lose that much money too. The
bank has an unexpected windfall of $50000.00 (until the auditors
discover the error and the bank's president or CIO goes to jail).

Worse, what if youraccount does not exist (clerk's typo)? In Firebird,
you can roll back the entire transaction. Under mysql, the money has
left my account but has nowhere to go, so we repeat the impacts to the
company, I am out my money, and you have no hope of collecting yours.

This is a single simple transaction (I pay you). Things get much worse
in highly concurrent real-time systems. Check out the threading
examples in Volume 2 of Core Java 2.

mysql resolved the performance/reliability in the favor of performance
at the expense of reliability. Firebird resolved it by being reliable,
accepting that reliability has its costs, and sacrificing a bare minimum
of performance to achieve its balance.






Anecdotally, one of my company's data vendors moved to mysql, and they
have suffered a number of outages due to that choice. The worst part of
the situation was that they had no warning, and the product appears to
fail silently. Their first warning of problems is generally when we
call them and ask if they are having problems.

Since their product is 24x7 data in response to our requests, to the
tune of 3/4 million requests per day, any unscheduled outage is a
serious problem for them and an aggravation for us.