Scott Yang's Playground

Firebird 2 Release Candidate 3 has just been released, and you can now download binaries for major platforms here. For Windows the FB 2 setup exe comes with both classic and super-server.

We actually have been deploying only super-servers, on beefy boxes with 2x dual core with 2x HT each (8x processing unit in total), because of our lack of experience in classic servers. Well, those server boxes do run other applications so you don’t get CPUs sitting there doing nothing, but I still wonder which one will be more suitable — one single super-server with shared page cache pool that can only utilise one CPU core, or multiple classic server instances that have their own page cache. However we do have up to 160 concurrent connections to the database on some of our boxes. Not sure how Win2k3 will perform with this kind of process count. Damn! Wish we can deploy them onto Linux boxes.

Well, we know that Firebird 3.0 will solve the super-server concurrency issue. When it that coming again?

Now onto Firebird 2 — it has not stopped to impress me with its improvement over FB 1.5. I was helping M resolving one slow query this afternoon. It looks something like this:

SELECT rows FROM table1 t1
 INNER JOIN table2 t2 ON t1.id=t2.id
 WHERE t1.id IN (v1, v2, v3, ..., vn)

Both table1 and table2 are large tables — both around 250,000 rows each. In Firebird 1.5, it will do a NATURAL (a sequential search basically) over the smaller of table1 or table2 and use the index on id column for join, even when n is small — like 20. So in order to return 20 rows of data, Firebird has to scan through at least 250,000 rows, which totally kills the performance. Actually it stalls the FB a couple of times this week on a production site to a point which we need to restart FB server to bring it back to sanity.

Whereas in Firebird 2, it will try to use the index for v1, v2, …, vn instead, if n is small (seems to be around 130 mark). It is much faster when the table size is large. Yeah! Problem solved by just upgrading the DB.

Except all our production servers (except one) still use FB 1.5, and probably will not switch until FB 2 final is out. Besides upgrading 40+ DB servers plus converting 200+ DB to 2.0 ODS will be painful. Also it won’t work all the time for us, as our n can be up to a few thousand. Problem of pulling data out from one SQL Server, and try to use it to perform queries on another DB…

Of course we can also build a temporarily tables and do a inner join. Now I just need to work out how to make thousands of temporary insertion fast…