[ Site Index] [ Linux Index] [ Feedback ]
Storing data is one of the commonest tasks computers are called upon to do. Sales of products like dBase propelled PC sales in the late 1970's and early 1980's; sales of heavyweight relational databases like IBM's DB2 and Oracle pushed the server and mainframe market.
With Linux increasingly visible to the industry, it's no surprise that the usual suspects are all available in Linux editions; you can buy Oracle, DB2, CA OpenIngres, Adabas D, Informix, and others that are familiar from Windows NT and the corporate UNIX world.
However, there's a new phenomenon in the database world. A number of open source database projects exist; either as outgrowths of earlier academic research (PostgreSQL), developments of lightweight storage tools for websites (MySQL), or open-sourced commercial products (InterBase). The successes of these products have been intimately entwined with the Linux platform, and as Linux has spread, so the open source developer community has grown. All of these databases now have commercial support and development offerings to rival the professional products: unless you plan to run a multi-terabyte data warehousing operation they'll do just about everything you need.
In this feature we're going to look at the open source database field -- specifically at MySQL, PostgreSQL, and InterBase -- and see what they can do for you, and what support options are available.
MySQL
MySQL is probably the most popular open source relational database: it seems to pop up everywhere. Back in the mists of time, an Australian academic called David Hughes wrote a very lightweight database engine called mSQL (short for mini SQL) -- it didn't implement a number of features required for full ANSI SQL certification, but it was very small and very fast. However, mSQL was distributed as shareware. (You can find out more about it at Hughes Technologies). Also (as noted) it was incapable of doing a number of essential things -- like joins, for example. Consequently Swedish programmer Monty Widenius started out trying to improve it, using some fast ISAM routines developed for his employers, TcX: but this didn't work well, so he shifted focus and began working on an entirely new product. Over the next few years MySQL rapidly grew until, although it's still a fast, light database, it's also a pretty powerful one.
MySQL was previously licensed commercially, but is now an open source product, released subject to the Free Software Foundation's General Public License (GPL). You can still buy a commercial license if you want to use MySQL in a manner incompatible with the terms of the GPL, or if you want support from MySQL AB, the company spun off from TCX to cope with demand for the product. (Support is also available from other people, most notably NuSphere, who also contribute to the code base -- more about this later.)
MySQL is written in C++, compiles using GNU gcc, and is pretty portable -- it has been ported to OS/2, Windows 95 and NT, as well as a variety of flavours of UNIX. (Note that there are extensive notes in the manual on how best to compile it -- for example, a statically linked msqld compiled using the experimental optimizing pgcc compiler will run up to 30% faster on Intel hardware than a vanilla dynamically linked mysqld compiled using Gcc 2.7.2.)
Don't expect a bunch of cute fluffy icons; MySQL gives you a bunch of command line tools to manage, including the mysql query monitor (a shell you can interactively type SQL commands at). The core application is a multithreaded database server (called mysqld) that speaks an extensive subset of ANSI SQL92. There are one or two variations from the language standard; in particular, sub-SELECTs don't work (e.g. SELECT * FROM table_foo WHERE id IN (SELECT id FROM table_bar)), and MySQL doesn't support SELECT INTO TABLE. If you find you really need a mouse-driven application, graphical shells for MySQL are available -- notably KMySQL for KDE 1.1, or KMySQLAdmin (for KDE 2).
MySQL is relatively unusual in that instead of supporting transactions, its paradigm for preserving data integrity is the atomic operation. Atomic updates are carried out in such a way that while each specific update is running, no other user can interfere with it and there will never be an automatic rollback. Atomic operations are in general far faster than transactions based on COMMIT, and the MySQL approach to avoiding race conditions is to include simple checks before updates and to run scripts that check databases for inconsistence and repair or warn in event of problems. MySQL allows table level locking and it's possible to lock a table for consistency checking while using INSERT DELAYED to queue updates until it's safe to apply them. If you really and truly need transactions, MySQL provides a table type that supports COMMIT and ROLLBACK operations, subject to a performance penalty.
MySQL supports foreign keys only grudgingly. They aren't used in table joins, but for checking referential integrity; therefore the FOREIGN KEY syntax exists only for SQL compatability.
Stored procedures are chunks of SQL commands that are compiled and stored in the database server, so that clients can invoke a stored procedure by name instead of re-compiling the whole thing every time they need it. A trigger is a stored procedure which is triggered whenever a particular event occurs -- for example, you can have a trigger that goes off whenever a record is deleted from one table (in order to tidy up corresponding data in other tables). MySQL doesn't support triggers, but stored procedure support is planned for a near-future release.
MySQL is a server designed to run in a network environment; you can talk to it via TCP/IP (as well as UNIX domain sockets). Consequently it has an extensive access control mechanism to prevent unauthorised data diddling. Users are granted privileges which are stored in the mysql database (which contains all the server configuration information). Users can be granted privileges to select, insert, update, delete, alter, index, create or drop tables, on a per-table basis; other administrative privileges exist (including the ability to grant privileges to other people). Users are themselves defined in the mysql.user table, which stores an encrypted password for each user (and lists the hosts from which they're allowed to connect to the server). When you connect to a MySQL server it decides whether to accept or reject your connection based on your identity and whether or not you can authenticate yourself. Given the network environment in which MySQL is typically deployed, it's important to read and understand chapter 6 of the manual (which describes how to set up a secure MySQL installation) carefully.
MySQL doesn't really support fully distributed operations, but it's moving in that direction. Since about release 3.20, MySQL has supported logging; with logging switched on, all transactions, or transactions which modify tables, are logged to a text file somewhere in the filesystem that MySQL runs on. This means that you can take a backup of a database, and a logfile, and by applying the logfile to the backup you can bring it up to date -- this is especially handy if some process is used to copy the logfile to a remote system.
Replication support is provided by way of a binary logfile and a mechanism that declares a MySQL server to be one partner in a master/slave relationship with another. The slave periodically polls the master, sucks up the binary logfile, and plays it back against its own database until it's in step. You can use this to build fail-over capabilities (the master falls over, but your client software is smart enough to try to establish a fallback connection to the slave server), or for clustering -- but this works best if you have one master and multiple slaves. (All operations that update the database should be directed to the master server, while the battery of slaves are available as a pool to handle queries.) While it's possible to define a bunch of master/slave relationships that run in a ring, there's some latency in the time it takes updates to propagate between servers -- so it's a bit risky to assume that the answer you're getting from a query to one server is the same as the answer that all the others will give. (Promised at a future date is the ability to log the machine an update came in from, so that true clustering is possible -- that is, updates will go to those hosts that need them.)
If you're going to build a database using MySQL, you really need to read the friendly manual. It's quite exhaustive (650 pages, printed!), and in addition to a complete language reference it has extensive notes on performance tuning, a lengthy tutorial, notes on performance-critical issues such as table types (it provides ISAM, MERGE ISAM, HEAP, and Berkeley DB tables), and how to administer a server. MySQL might be described as small and lightweight, but small and lightweight are relative terms -- it's comparable to the larger heavyweight products five years ago. Among the tools that come in the box are a bewildering variety of APIs (for C, C++, Eiffel, Java, Perl, PHP, and Python -- among other things!), maintenance tools, benchmark tests, documentation, and other helpful widgets. You can wallow in these tools for days before finding limits to what you can do with MySQL -- while you wouldn't want to base a major corporate enterprise server on it, it's fine for most medium to heavy duty tasks for which speed is more important than a rich set of object types or less-frequently-used features such as triggers. MySQL powers some of the most heavily whacked-upon sites on the world wide web (such as slashdot), and if you're planning a web-based development project it's the database of choice unless you really, really need some unusual features.
MySQL is supported commercially by at least two companies: MySQL AB in Sweden and NuSphere LLC, in the United States. MySQL AB provide support in a range of formats, from basic email support, extensive email support (including debugging simple customer problems, rather than just pointing to the manual or helping with any bugs in MySQL itself), login support (an engineer will log into your server and try to diagnose/fix problems in situ), and extended login support (an engineer will try to optimize your system to your satisfaction).
NuSphere's approach is somewhat different. They sell a packaged MySQL distribution, including printed manual, O'Reilly pocket guides (for some of the other software supplied), CDROM with binary images of the software optimized for different configurations, and a browser-based administration tool that lets you administer a MySQL server via the web. NuSphere aren't simply making money off of MySQL AB's work, however; they're actively contributing to the MySQL code base, for example by adding row-level locking facilities.
PostgreSQL
PostgreSQL, now at version 7.0.2, is an advanced open source database server; its primary development emphasis has been on functionality rather than speed (although the current version performs favourably when compared to MySQL). Most current relational databases can trace their ancestry to a few common projects. PostgreSQL is descended from the original Ingres research database, which was commercialised by Ingres Corporation (later purchased by CA, who still sell CA OpenIngres). The Ingres research project was taken over at UCB by Michael Stonebraker, who led a team to develop an object-relational database server called Postgres. This in turn spun off the commercial Illustra database (which has since become part of Informix Universal Server).
The PostgreSQL project got under way in 1996, with a bunch of open source developers who were acutely aware of the need for a first-rank relational database system in the open source world. Originally they focussed on bugfixing -- the original research project was rather brittle -- but more recently they've begun work on adding new features and improving system performance.
PostgreSQL is a client/server system, like MySQL and InterBase. A client application that wants to use the database uses a communications API to connect (via TCP/IP) to the Postmaster master server; this in turn spawns a Postgres server to handle the current session.
PostgreSQL is not a simple beast. In addition to supporting almost all of SQL92, its object-relational heritage shows through in numerous ways. A table (also referred to as a class) has numerous rows (instances), and you can operate on it using SQL commands (interactively, via the pgsql query monitor, or via a language API). In addition, tables exhibit inheritance -- you can define a child table that inherits all the attributes (column names) of a parent class (table) and adds extras of its own. SELECTs can be applied either to a specific table, or to a table and all its children. In addition to providing the normal range of types (INT, VARCHAR, FLOAT and so on), PostgreSQL provides a rich range of data types: in particular it provides non-atomic values -- you can create columns which contain arrays of items, for example multi-dimensional arrays of integers, and then access arbitrary slices of an array (subarrays) by specifying both lower and upper bounds for each subscript. There are a whole range of other categories, too: geometric types (such as line segment, point, and polygon coordinates), network (such as ethernet MAC address and IP network address), and temporal data types (date/time/interval). (BLOB support goes without saying, but MySQL provides BLOBs, too.) You can convert between datatypes using CAST, and a range of built-in functions are provided for manipulating each type.
PostgreSQL provides a bunch of features in its data manipulation language that exceed those of MySQL. You can provide aliases for table names, permitting self-join operations (where a join is performed on two instances of a table). Sub-selects are fully supported. SELECT statements can be chained together using UNION, EXCEPT and INTERSECT clauses; this is handy because the one chunk of SQL92 that PostgreSQL doesn't support is outer joins, but this can be simulated using subqueries.
From an architectural point of view, the big difference between PostgreSQL and MySQL is that, while MySQL emphasizes speed, PostgreSQL is intended to provide ACIDity -- Atomicity, Consistency, Isolation, and Durability. These buzz-words basically mean that the database is required never to lose data -- no matter if someone trips over the server's power cable, or a disk fills up in the middle of a transaction. Anything that gets committed to the database must be retrievable, and if this entails a performance hit relative to a less risk-averse system, so be it. PostgreSQL provides extensive transaction support, including serializable isolation (i.e., a multi-statement transaction in process won't see any updates to the database happening concurrently due to other processes). Locking is provided at table and row level. Unlike Oracle, PostgreSQL doesn't have raw access to a disk partition -- so when processing transactions it calls sync() after each update; this may slow things down considerably, depending on your system, but it can be switched off (if you're willing to assume your filesystem is robust enough to save your bacon in event of trouble). In any event, it's probably sensible to run a mission- critical PostgreSQL database on top of a journaling filesystem.
Security is pretty much what you'd expect, with a complex domain-based system of authorisations. One nice twist is that it's possible to configure PostgreSQL to handle TCP/IP connections encrypted using SSH (secure shell) -- this is essential if you're moving confidential material over a network that may be snooped on by strangers. Another nice feature is support for Kerberos authentication.
PostgreSQL provides support for stored procedures (referred to as server-side functions) and triggers. Stored procedures can be written in several languages, including C, PL/TCL, PL/Perl, and PL/PGSQL, rather than just SQL. A trigger calls a stored procedure for each modified row in its target table. A different type of trigger called a rule alows SQL queries to be executed when a table is accessed.
As with MySQL, the commonest way of interacting with PostgreSQL is via a SQL query monitor (pgsql) or programmatically, from an application written using a database access library. However, there's a nice graphical interface supplied with the PostgreSQL distribution called pgaccess.
In addition to having its own newsgroup for peer-to-peer support, you can buy commercial support contracts for PostgreSQL. Just as MySQL AB and NuSphere sell support (and a packaged distribution) for MySQL, PostgreSQL has been taken up by GreatBridge. They'll sell you a packaged, QA-tested distribution of PostgreSQL, complete with printed documentation; they also sell technical support packages and professional consulting services. They're clearly targeting the high end, with a premium support package that provides 24x7 coverage, unlimited phone and email incident supports, and a team of (named) support analysts assigned to each customer. This is probably sensible. Unless you really, truly, need a distributed database that runs on multiple servers and provides this level of features, PostgreSQL will fit the niche that Oracle currently occupies -- and you can buy an awful lot of support for the price of a multi-processor Oracle license, so aiming at the low-end Oracle market is the right thing for GreatBridge to do.
Just in case this introduction to PostgreSQL looks like a feature by feature comparison with MySQL -- that's the idea. MySQL has at least one feature that PostgreSQL doesn't, namely rudimentary support for distributed operations. You could probably emulate MySQL's logging using a trigger, and broadcast INSERTs to a cluster of servers by having the trigger invoke some C code that throws the transaction at a syslog server, but that would fundamentally be a kluge: PostgreSQL is a very powerful transaction-based system designed to run on a single server and to provide assurances that your data integrity is being preserved. MySQL, in contrast, is a less feature-rich server optimized for raw speed over reliability. They're different beasts, for different purposes.
PostgreSQL has been saddled for years with a reputation for slowness, which is probably an unfair hangover from its past as an academic research project -- and backed up by tests on versions up to 6.5.3. But PostgreSQL 7.0 is a different animal from version 6.x, and it shows. GreatBridge paid for a bunch of comparative benchmarking in August 2000, which demonstrated that PostgreSQL performed creditably under heavy load. The general consensus on the report (after it was chewed over on Slashdot and the tests re-run with input from third parties on how to tune the competition) is that PostgreSQL performed better than expected. When used to run AS3AP and TPC-C benchmarks on identical hardware, using ODBC drivers to communicate with the clients, PostgreSQL outperformed "the proprietary leader" (Oracle's license expressly forbids you from quoting their name in benchmark figures), and, by a narrower margin, MySQL and InterBase. (Note that there's some questions over the InterBase benchmark as the InterBase ODBC driver they used is notoriously slow.) On the second run, a highly-tuned MySQL system managed to process roughly 30% more transactions per second than PostgreSQL, but degraded as additional users were added to the test system. While you should always read benchmark figures from a software vendor with the proverbial kilogram of salt, the figures from GreatBridge give the lie to PostgreSQL's reputation for being an order of magnitude slower than other database servers.
InterBase
InterBase is open source. This happened back in mid-2000; Borland (who acquired InterBase Corporation and marketed their database as their own high-end offering) announced that they were going to stop supporting it: but instead of killing the product, they would release it as open source. The source code appeared through 2000, and is now available.
InterBase is a SQL92-compliant relational database; it runs on Windows (95/98, and NT) as well as most flavours of UNIX (Linux, Solaris, and HP-UX specifically supported). It's available under a variant of the MPL (Mozilla Public License) used by Netscape. There are two versions, the Superserver and Classic server; on UNIX the server generally communicates with clients via TCP/IP (and can consequently cope with remote logins). InterBase also uses NetBEUI (under Windows) and the Netware version of the server uses IPX/SPX.
InterBase provides most of the features you'd expect of a database that focusses on transaction processing. To prevent multiple clients updating each other's data, it uses a multigenerational architecture: it can store and create multiple versions of each data record, so that clients can see a consistent view of a record even if another client is in the process of changing it. Locking is provided at table and row levels, and InterBase can apply a lock onto to those rows that are affected by an update request, rather than having to lock an entire table for the duration of a transaction. (Earlier versions of Postgres experimented with a multigenerational architecture, including one that retained a full history for every record: this has some specialised uses, but the performance impact was severe.) In addition to SQL92- compatibility (but a thinner set of supported data types than PostgreSQL), InterBase provides the expected extras: rules, triggers, and stored procedures are all part of the package.
One point to note is that InterBase acquired the ability to handle multiple users around release 4, some years ago. At that time, a back door was unintentionally incorporated into the server (as part of the mechanism for updating user accounts); this came to light recently, affecting all versions up to InterBase 6, and a security patch was hastily distributed in January 2001.
The InterBase kit consists of a bunch of command-line tools. These include ibmgr, the manager application (starts up and shuts down a server), gsec (security configuration tool), gstat (display database statistics), gbak (backup and restore tool), gfix (database maintenance and tuning tool), and isql (interactive query monitor). Unfortunately the documentation -- which is as comprehensive as you'd expect -- tends to assume that you're using the IBConsole graphical utility to manage your server, and IBConsole is only available for Windows.
InterBase provides lots of options for tuning the performance of the On Disk Storage (ODS) system. Generally, an InterBase database resides in a single file, although additional files can be used for external tables (with some loss of accessibility). One point to note is that you can't put an InterBase database on an NFS filesystem. If you try to, InterBase will detect the NFS mount and try to contact a remote InterBase server on the machine that exports the NFS filesystem -- if none is available, any attempt to connect to the database will fail. (There are good reasons for this behaviour, as anyone who has had the misfortune of working with NFS will recognize.)
Where InterBase really shines compared to PostgreSQL if you want to talk disaster recovery. You can set up a shadow database that contains an identical physical copy of a database you're working on: if the primary database goes offline for some reason (like a disk crash) the shadow can be made available automatically or manually. As with PostgreSQL, you can configure the server to force a write through to disk every time the database performs an internal update, or allow it to buffer writes -- it all depends on how much you trust your system (as forced writes will slow the server down, but enhance recoverability in event of a hardware problem).
The other critical advantage InterBase has over PostgreSQL is IBReplicator. IBReplicator provides data replication between servers -- changes to data in one InterBase database can be propagated to a bunch of other servers. It's fast, lightweight, and supports a number of highly desirable features such as N-way replication, replication of data between databases with divergent structures, and different ways of triggering a replication event. This goes way beyond the limited replication facility available to MySQL. And now for the bit that caused your reviewer to grind his teeth: the Replication Manager (used to control replication) only runs on Windows, and the Replication Server itself, although available for Linux, is commercial software: it's sold via Synectics Software Ltd, and has definitely not gone open source along with the rest of InterBase.
In terms of support, the picture with InterBase is mixed. On the one hand, InterBase (a division of Borland) no longer sells or supports the product. On the other hand, a vibrant open source community has emerged, because there are a lot of InterBase users already out there. Companies like Synectics Software (who have a long history of supporting InterBase) haven't given up, just because the core product has gone open source. So you may have to hunt for support a little harder than with MySQL or PostgreSQL, but it's still available. If InterBase takes off in the open source community (as seems possible), expect to see a company like GreatBridge and NuSphere begin selling support contracts.
In the meantime, InterBase is broadly comparable to PostgreSQL. It has a smaller range of data types, and doesn't integrate as well with various open source languages and development tools (no stored procedures in Perl or Python!), but has a wider range of graphical development tools (on Windows) and if you're willing to pay it can provide world- class replication service.
NOTE: Since writing this feature, SAP have released SAP DB, their relational database (derived from ADABAS-D) under an open source license. You can find it here.
I haven't had time to review it, but on the basis of the write-ups it may be superior to all the other RDBMSs in this review.
Non-relational databases
Relational database systems that organise data in tables and are accessible via SQL (or a similar query language) aren't the only options for UNIX. For certain types of job, a non-relational database system may be a sensible alternative.
Firstly, designing and normalising a relational database schema is a specialist job. SQL has grown into a complex and daunting language, and the features and performance-tuning facilities offered by many relational databases are hairy enough that to get the most out of one you need an expert database administrator. For example, just knowing when to use index tables and what underlying data store to use can result in multiple order of magnitude performance improvements for some databases. Regardless of how much the software costs (or doesn't, if it's open source), by far the greatest expense in running an RDBMS is the wages bill for the DBA's.
Secondly, relational databases make a fundamental trade off between performance and flexibility. They're designed so that you can alter table structures on the fly, construct views, and generally apply arbitrary queries to them. This flexibility comes at the price of performance and complexity. If you want to store a large mass of very simple data -- say, username and password combinations for a million users logging into an ISP -- you don't need that flexibility; what you need is raw speed and simplicity.
UNIX (and Linux) provides a number of standard database libraries that you can link to programs written in C, C++, Perl, Python, or just about any other language. The granddaddy of these is GNU dbm, the GNU replacement for the original UNIX dbm and ndbm libraries. GNU dbm is a library of routines that manages data files that contain key/data pairs. A program that has opened a dbm file can store, retrieve, and delete chunks of data by key, and carry out an unsorted traversal of all keys in the database file. Programs are allowed to use multiple data files at the same time. A process that opens a gdbm file is designated as a "reader" or a "writer". Only one writer may open a gdbm file, but many readers may open the file.
You'd typically use gdbm (or the older dbm or ndbm libraries) where you have a bespoke program that needs to maintain a large collection of records with a single key field -- and where you know the structure of the data it will be maintaining well in advance.
There are more sophisticated database libraries: the current favourite appears to be Berkeley DB, developed as a replacement for ndbm. The Berkeley DB library is a toolkit that provides embedded database support for both traditional and client/server applications. Berkeley DB includes b+tree, queue, extended linear hashing, fixed, and variable-length record access methods, transactions, locking, logging, shared memory caching and database recovery. DB supports C, C++, Java, and Perl APIs. Berkeley DB supports multiple simultaneous readers and writers and guarantees that all changes are recoverable, even in the case of a catastrophic hardware failure during a database update. The Berkeley DB library doesn't provide end-user interfaces, data entry GUI's, SQL or ODBC support, or other standard database interfaces. What it gives you are the programmatic building blocks that allow you to easily embed database-style functionality and support into other objects or interfaces.
Finally, in case you're an inveterate dinosaur, there's at least one open source COBOL implementation for UNIX and Linux -- TinyCOBOL. This implements a subset of COBOL 85.
[ Site Index] [ Linux Index] [ Feedback ]