Interbase's decision last month to release its upcoming InterBase 6.0 database under the open-source Mozilla Public License 1.1 will give this wallflower a chance to bloom. (InterBase is a division of Inprise Corp., which was bought by Corel Corp. just before press time.)
PC Week Labs' programming and benchmark tests of InterBase—the first time comparative test results of the package have been published—show the upgrade can stand up under the public scrutiny it's going to get when it's released around midyear. The source code will be released initially in Linux, Solaris and Windows versions.
Truly, all that glitters is not gold—sometimes, it's a database that comes out shining. In InterBase, we found a fast, full-featured, mature relational database, although its lack of a shared cache on Linux really slows it down under heavier workloads.
InterBase's move to open up its product to the world is significant because it means InterBase will be the first fully standards-compliant open-source database available. As a result, InterBase, which has been under development for more than 16 years, will be the first such database that IT managers can adopt without having to make tough decisions about which SQL database features they can live without. That makes it a very welcome addition to open-source software.
Standards compliance is a critical enterprise requirement, particularly when IT managers must design a corporate infrastructure that has to interoperate with current code and data sources—and be managed by several generations of IT staff.
In contrast, all five major commercial SQL databases running on minicomputers (databases from Computer Associates International Inc., IBM, Microsoft Corp., Oracle Corp. and Sybase Inc.) are SQL-92 entry-level-compliant, and have been for years.
PostgreSQL, the most sophisticated open-source database currently available, is not yet SQL-92 entry-level-compliant—although it is close—and lacks extensions such as declarative referential integrity and outer joins. In tests, PostgreSQL also had a number of other, smaller issues with SQL compatibility, such as with named columns in views and the numeric data type, that made porting our benchmark code to PostgreSQL slower and the final result less functional for us than the result our InterBase code produced.
Although having all the SQL-92 entry-level features—plus common extensions such as outer joins—in an open-source product is the most exciting part of this release, raw performance is important, too.
We moved to put the most recent versions of both databases available at the time of our tests (InterBase 5.6 and Postgre SQL 6.5.3) through a wide battery of tests to see exactly what the world was gaining with this new open-source offering. (For PC Week Labs' analysis of MySQL, another popular open-source database, see here.)
Our benchmark results show clear advantages to both products under different types of workloads. InterBase was significantly faster than PostgreSQL on both simple reads and complex joins on single-user tests, which is how database-enabled Web sites usually access databases. These results indicate an efficient query parser, optimizer and data page seek algorithm.
However, PostgreSQL's shared cache—something InterBase lacks in its Linux version—really paid off for it in tests where we had a high number of concurrent users, as is common in client/server environments. In this test, PostgreSQL's maximum throughput was almost three times faster than InterBase's.
The reason for this performance difference is InterBase's nonshared architecture, which is its single biggest weakness. On Linux, but not on other platforms such as Solaris and Windows, InterBase uses a design that maps each concurrent connection to a stand-alone database engine process. Because we had designed our configuration to go up to 100 concurrent users, we had to parcel out the server's 512MB of memory into 100 very small caches of 400KB each.
As a result, the server processes didn't have nearly enough cache to function well, let alone handle the extra computational work of continually selecting cached pages to discard. Although the independent-process approach provides some manageability benefits and intra-process crash protection, overall, it's a very inefficient design—each server must have its own execution plan cache, data cache and lock pool.
InterBase's Markus Kemper told us he hopes one of the first benefits of an open- source InterBase 6.0 will be a quick port by some smarter-than-your-average-bear programmer of the threaded engine to Linux.
InterBase is clearly a very well-tuned product when given enough memory. As a comparison point, we also tested InterBase using 65,536 cache pages of 4,000KB each in a single-user test; InterBase pushed through 139 tps (transactions per second) compared with PostgreSQL's peak result of 74 tps at the same cache size.
According to Jeff MacDonald at PostgreSQL Inc., the PostgreSQL support and services company, PostgreSQL 7.0 will gain both declarative referential integrity and outer joins (a beta is expected this month).
InterBase 6.0 will also introduce some critical improvements. Although the current release does come with basic administration tools (see screen), they do very little and include no graphical schema browsing or editing tools, let alone performance monitoring and tuning aids. Version 6.0 will have a completely new administration tool suite as well as new administration, backup and installation APIs that third-party developers can use to write their own administration tools.
InterBase Software has also contracted with Synectics Software Pty Ltd. (at www.synetics.co.za) to provide a commercial bidirectional replication add-on.
Two requests we'd pass on for 6.0 are a data import utility—we had to use a kludgy work around to import test data—and support for the SQL standard double-dash comment symbol.
We used a dual 450MHz Pentium III server with 512MB of RAM and a RAID array as our database testbed and, in both cases, tapped Red Hat Inc.'s Red Hat Linux 6.1 as the server operating system.
The benchmark is based on the ANSI SQL Standard Scalable and Portable Benchmark, or AS3AP, database benchmark—we extended the code to test a wide array of server usage patterns, from hard-core OLTP (online transaction processing) to production reporting to data warehousing. We wrote all the query code in C, using standard Open Database Connectivity APIs. To run the benchmark, we used Client/Server Solution Inc.'s Benchmark Factory 2.0 benchmarking tool.
Full implementation source code and scripts for the PC Week Labs database benchmark are posted online at ftp://ftp.zdnet.com/pcweek/labs/0207codefile.zip.
Senior Analyst Timothy Dyck can be reached at timothy_dyck@zd.com. Additional testing by Dan Vandebelt, researcher, Social Planning Council of Cambridge and North Dumfries, Ontario.
