I have been using MySQL for a very intensive read-write web application (averaging 102 queries per second) for more than two years. I had ups and downs with it, like crazy MyISAM behavior that readers can block writers AND OTHER READERS. Basically, a table level lock is issued for read. I have 100+ records in a table, so it takes a while to find anything that is not indexed. In the meantime, users are pondering (102qps, remember) and load goes up so much because of web server processes queuing like crazy. Ok, I learned not to do that anymore. I now use binary logging, restore to a different server and query there. Maybe a switch to InnoDB would be a good idea, but in this case I’d rather use a serious MVCC database like Firebird. Why, you might ask… well, here’s one of many reasons, the one that prompted my to write this:

In Firebird, I can happily do this:

delete from atable a1 
where exists ( 
select 1 from atable a2 where a1.data = a2.data and a1.id <> a2.id );

It just does it, and fast, because index on primary key field ID is used. In MySQL, to quote the manual:

“Currently, you cannot delete from a table and select from the same table in a subquery.”

Come on, this is one of the most basic database operation. So, what am I now to do? Waste my time dumping the list of IDs to delete to some temporary location, and then iterating that list to delete. :(

Beside being free (both as beer and also open source), you don’t need 24x7 DBA and there are generally less headaches. Here’s a nice example explained by Norman Dumbar in a mailing-list post. Norman administers over 600 Oracle databases and about 40 Firebird ones:

Oracle uses log files for REDO and has ROLLBACK_SEGMENTS or UNDO Segments (depending on Oracle version) for UNDO. It never uses log files for UNDO - and UNDO is what provides Read Consistency/MVCC in an Oracle database.

Changes are written to the LOG_BUFFER (n memory) and periodically - on commit, every 3 seconds max, or when the buffer is 33% full - flushed to the REDO logs. These REDO logs might be archived to disc when they fill up. That Depends on the database archive log mode though.

These logs are used when a database is restored and rolled forward (using the RECOVER DATABASE command, for example).

In order to roll back changes and to ensure read consistency, UNDO is used. These do live on disc - as tablespace files - but remain in memory in the buffer cache alongside data blocks etc.

When a SELECT is started, the data returned are the data from the data blocks. Each row in a block has an indicator that tells when it was last updated. If a pending update is taking place (currently uncommitted) or if a commit has taken place since this SELECT started then the data read from that data block has changed - and is not consistent with the start time of this SELECT transaction.

When this is detected, Oracle “rolls back” the changes to the start time of the SELECT taking place by looking for the UNDO block(s) associated with the transaction that made the changes. If that results in the correct (consistent) data, that’s what you get.

If it turns out that there were other transactions that also changed the data, they too will be detected and undone.

In this way you only ever see data that was consistent at the start of your own transaction.

As long as the DBA correctly sizes the UNDO tablespace and correctly sets the UNDO_RETENTION parameter to a decent enough value, data changes are able to be rolled back happily all the time.

If the DBA failed miserably in his/her duties, the ORA-01555 Snapshot too old” errors are the result. And are most irritating. Long running SELECTS - batch reports for example - tend to show up this error mostly.

Of course, you would never see such problems with Firebird, because the old record versions are stored in database and not the log files. You don’t have to care if system crashes - after reboot it simply works.

You might think that engineers who build Firebird are smarter than Oracle’s but sometimes I think Oracle is deliberately made so complicated to require DBA and also offer them job security. And also makes sure nobody can complain it’s too easy to use.

I decided to automate conversion of MySQL database to Firebird. I got
tired of manual find&replace process, and created a small parser that
changes from MySQL to Firebird syntax. Currently it only supports
database structure dump (no data, only structure) from PHPMyAdmin. Not
all datatypes are supported, but feel free to “fix” it. It’s written
in PHP and it’s open source. It would be great if you would send back
any modifications you make. Download it here (source code included):

http://www.countdowngames.tv/mysql2firebird.tgz

Please note that this is quick&dirty converter, so make sure you test
the output before using it in production. It does support some tricky
stuff like auto increment columns and LAST_INSERT_ID. There is a
stored procedure for this at the end of the script, you can call it
like this:

select id from last_insert_id;

On Windows, you can run the program like this:

php.exe -f mysql2firebird.php mysql_dump.sql > firebird_dump.sql

Once you create the Firebird script, make sure you search for text
WARNING: which might indicate some incompatibilities. For example,
Firebird does not have tinyint, mediumint and unsigned datatypes.
Also, check for TEXT and LONGTEXT. I’m using UTF8, so I converted
those to varchar(8000), which might not be what you want. Maybe BLOB
SUB_TYPE 0 would be a better choice, but I don’t like PHP support for
it, so I’m using varchar instead. Maybe some command line options
could be added to the tool to support different behavior.

This program covers all the databases I had to convert so far. If you
are interested in improving it, please contact me.

I have a heavy-used website powered by LAMP stack (CentOS Linux,
Apache 2, MySQL and PHP). It started on a shared hosting so I had to
use MySQL. Year and a half later, I switched shared, virtual hosting
and not run it on a dedicated server. I decided to try Firebird to see
how it performs and also how it compares to MySQL in RAM usage, disk
usage, etc.

The software

The system is CentOS 5.5 64bit with default LAMP stack. I installed
Firebird 2.5. RC3 from the .rpm package on Firebird website.
Surprisingly, it does not require any additional rpm package :)

Converting the database

As far as I can tell, there are no tools to do this automatically. I
created Firebird database and tables by hand, slightly editing the
schema dump from phpMyAdmin. This was easy. Loading the data seemed a
problem because default mysqldump places multiple VALUES clasuses in
INSERT statements. I used a Postgres tool mysql2pgsql to convert the
file to a more usable form:

http://pgfoundry.org/projects/mysql2pgsql/

I had to alter it a little bit, to avoid prefixing strings with E
character. I commented out this line:

#s/'((?:.*?(?:\\')?.*?)*)'([),])/E'$1'$2/g;

# for the E” see
http://www.postgresql.org/docs/8.2/interactive/release-8-1.html

Next problem was that ” and ’ are escaped with backslash .

With Firebird ” does not need escaping and ’ is escaped with another
’, becomes ”. A simple sed command to fix this:

cat postgres.sql | sed s1\\\\\"11g | sed s1\\\\\'1\'\'1g > firebird.sql

A few more manual edits were needed to remove the CREATE TABLE and
similar stuff, because I only needed data. After that I added
“commit;” to the end of the script and ran it via isql:

/opt/firebird/bin/isql /var/db/firebird/s.fdb -user sysdba -pass 
******** -i firebird.sql

this took some time. Here is the result:

# du -h -s /var/lib/mysql/slagalica/
1.9G /var/lib/mysql/slagalica/
# du -h -s /var/db/firebird/slagalica.fdb 
2.1G /var/db/firebird/slagalica.fdb

This is before I created indexed on tables in Firebird database.
Afterwards we get:

2.3G /var/db/firebird/slagalica.fdb

So, Firebird database is slightly bigger.

Now, it’s time to convert the DB access layer in PHP application, and
compare the perfomance. Stay tuned…

There’s a bug introduced between PHP 5.2.1 and 5.2.2 that affects
handling of Firebird blobs on 64bit machines. Having moved some of my
servers to 64bit Slackware (Slamd64 to be more precise) I run into
this problem.

Unless you want to recompile the whole PHP to a newer version with the
fix (5.3 as I understand, although did not test), you can patch the
sources: Just open ibase_blobs.c file and modify this function:

_php_ibase_quad_to_string

char *result = (char *) emalloc(BLOB_ID_LEN+1);
/* shortcut for most common case */ 
if (sizeof(ISC_QUAD) == sizeof(ISC_UINT64)) { 
  sprintf(result, "0x%0*" LL_MASK "x", 16, *(ISC_UINT64*)(void *) &qd);
} else {
  ISC_UINT64 res = ((ISC_UINT64) qd.gds_quad_high;
  sprintf(result, "0x%0*" LL_MASK "x", 16, res);
}
result[BLOB_ID_LEN] = '\0';
return result;

Rebuild the interbase.so, copy to extension directory, restart Apache and you’re done.

10 Years of Firebird DBMS

I have been using it since 2002. It’s rock-solid, small-footprint,
lean and mean database. And it’s completely free. No dual-licensing
crap like MySQL, no crippleware like Microsoft or Oracle Express
editions.

Not mention 2MB embedded engine with x-copy deployment (just copy a
few DLLs alongside your EXE and you’re good to go). And, without any
changes to the applications, it scales to 10 GB databases (largest I
tried myself, reportedly, there are much larger ones out there).

Imagine a world where DBMS download is a few megabytes and install is
a few seconds…

Well, do not dream, try Firebird and see it for yourself. Once you get
used to that, other DBMS will look like bloatware.

Jaybird

I took this photo during my vacation on Corfu last summer. Of course,
it reminded me of Firebird JDBC driver, which is named Jaybird. Maybe
Roman (the main developer) was on the yacht, but I didn’t take the
time to check it out. Just kidding.

Who says you can’t make money off open source ;)