A.P. Lawrence

Databases 101

Author: Dwight Evers

email: dmevers@bitstream.net

date: June 20, 2000

INTRODUCTION

Most, if not all, unix platforms support some form of storage mechanism. What you put on your storage device is up to you. How you store it, be it flat files that you parse with grep and sort, or a fully Relational Database Management System (i.e. Oracle, Sybase..etc) is up to you.

What we will look at here are 4 of the more "popular" and "inexpensive" databases for storing information.

QUALIFICATIONS

When I talk about a qualified database, I am using 3 criteria. 1. Client/Server accesible 2. Perl/Tcl add-on available 3. Accepts standard ANSI SQL commands (ASNI-SQL92 compliance not required)

Whis this in mind, I have selected 4 databases to review that work well on SCO as well as Linux. MiniSQL2, MySQL, Postgresql, and Interbase.

Although Interbase is going "opensource", the edition available at this time for OpenServer is still the 90 day eval version of 5.5. Linux was tested with 6.0.

Postgresql recently hit rev 7, which "officially" supports SCO, but only with its C API (this includes the PerlDBD/DBI interface) and with special compiler options.

MiniSQL2 (v2.0.11) is opensource, but not "free". See its license agreement for specifics. It does NOT have a MS-Windows ODBC driver available.

MySQL3 is opensource (just like MiniSQL2) but is "free". See its license agreement for specifics. An ODBC driver is available.

INSTALLATION

Of the database discussed, all but Interbase are available as both source and binary online. If you can, get a binary and install it, unless you have a compiler and developer tools available.

MySQL requires that FSUthreads be installed so that multithreading can take place on the server. This is available seperately at www.caldera.com/skunkware.

Disk Requirements: MiniSQL2 = 12MB MySQL = 15MB Postgresql7 = 40MB Interbaase = 65MB

Setup/Configuration: The databases listed all require a specific userid for the database to be run as. Generally speaking, it is best to have this setup and in its own group before proceding with the install. (i.e. MiniSQL2 runs as "msql")

To configure a database, all of them use an interactive sql tool, similar to oracle's sqlplus or sybases isql.

Creating a database is as simple as issueing the SQL command to do so.

DATABASE ACCESS

MiniSQL2 uses an access control list (ACL) stored in an external file to handle access control. This file contains database users, database names, and hosts/IP's allowable. Simple and effective. It does not infer group priveledges or multiple roles.

MySQL uses a dictionary table, like sybase and oracle, to determine access and priveledges. It understands users and groups, but, does not use roles, but does understand procedures.

Postgresql7 understands users, groups, roles, and privledges. It shares many of the same "high end" features of Informix, Oracle, and Sybase. Such as begin/commit and transaction processing. It does NOT allow you to perform joins across databases. This could SERIOUSLY limit it funtionality in a production environment.

Interbase is a true ANSI SQL92 compliant database. It supports SMP as well as supporting advanced groups/users and handles privledges in a more mature manner as well. Additionally, it can be accessed from both ODBC as well as JDBC. It has a C api and can communicate via TCP/IP as well as IPX. (although, IPX support is questionable for the future)

COMPARISON

Where these databases are useful is dependant on how you implement them. Here is a short list of Pro's/Con's for each.

Attribute       Interbase   MiniSQL2  MySQL3  Postgresql7  Oracle8i  Sybase11
SQL89           mostly      mostly    mostly  mostly       yes       yes
ANSI SQL92      yes         no        no      mostly       yes       yes
BLOBS           yes         no        no      yes          yes       yes
inner JOINS     yes         yes       yes     yes          yes       yes
outer JOINS     yes         no        no      yes          yes       yes
table JOINS     yes         yes       yes     yes          yes       yes
DB JOINS        yes         yes       yes     no           yes       yes
Caches          no          no        no      no           yes       yes
SMP Support     yes*        no        yes**   no           yes       yes
Procedures      yes         no        no      yes          yes       yes
transactions    yes         no        no      yes          yes       yes
ODBC            yes         no        no      yes          yes       yes
JDBC            yes         yes       yes     yes          yes       yes
hot backups     no          no        no      no           yes       yes
Cost/server     $750        $250      free    free         $1250     $750
Cost/client     $85         none      none    none         $50       $50
Cost/CPU        none        none      none    none         $1000     $1000

* Interbase runs a copy of itself each time you specify a processor/cpu
** MySQL3 requires the use of FPUthreads, but, this lib does not fully support
   SMP on SCO or on other some other operating systems.

NOTE: Oracle and Sybase11 charge a cost per "power point", which is dependant how fast your server is. So, Oracle on an E10000 from Sun would be more than Oracle on an E250. Pricing and availability subject to change.

USEFULNESS

Below, I generalize where things are better off getting implemented.

MiniSQL2: Web databases and tracking. Good speed and C-API. Perl and Tcl are supported. Flat files that are generated limit size of tables to 2GB, but, virtually unlimited number of tables per database allowed. Small size and simplicity makes it great for embeded applications.

MySQL3: Good SMP support, but, not consistent across platforms. Excellent speed and access. No support for transactions limit functionality. Great for Web databases as well as departmental tracking/access.

Postgresql7: No support for Multi database joins SEVERELY limits its production appeal. Excellent stability and API make it an (otherwise) high end solution. Lack of hot backups is limiting as well. Great for large databases and simple warehousing.

Interbase6: Great for Web databases and scales all the way up to several GB in size. Stability and SMP support are getting better, allowing for consistent performance. Memory issues resolved with v6. Lack of hot backups and replication make for questionable use in high volume traffic situations.

Oracle8i: The "All Mighty" of databases. Hot backups, parallel servers, replication, and logging make it the most widely distributed RDBMS in the World. The leader in Very Large DataBase (VLDB) and data warehousing.

Sybase11: Support for both datacache and table caches make it excellent for high performance tuning. Support for SMP and hot backups and roll back segments allow for full production use. Its replication server is not as mature, but is improving. Great for datawarehousing and transaction processing, but, does not lend itself to VLDB use.

SUMMARY

After installing and looking at these 4 databases, I have found that I prefer to use MiniSQL2, as it does not require the use of threads or limit me to just a C-API.

MySQL is always undergoing HEAVY developement, so some releases are more stable than others. Its support for SMP and speed makes it a great choice for more heavily used environments.

Postgresql7 has really taken off as far as support and features. With the addition of multi database joins and a hot backup solution, it could contend with the likes of Oracle and Sybase.

Interbase 6 (5.5 on SCO) has stepped up to the plate and delivered everything they have promised. Support for SCO is off-again/on-again, but, it has ANSI SQL92 support as well as transaction capabilites. Speed plagues it, still. Costs also inhibit its exceptance in our Opensource world, but, users/developers have been promised that Borland/Inprise will deliver.

At some later date, we shall revisit the latest in opensource database for both SCO and Linux.

Publish your articles, comments, book reviews or opinions here!

Enter your email address for automatic notification of new posts here
(be sure to whitelist 'feedburner.com' if you use spam filtering)

Unix and Linux Support

/Reviews/databases101.html copyright June 2000 Dwight Evers All Rights Reserved

Have you tried Searching this site?

Unix/Linux/Mac OS X support by phone, email or on-site: Support Rates

This is a Unix/Linux resource website. It contains technical articles about Unix, Linux and general computing related subjects, opinion, news, help files, how-to's, tutorials and more. We appreciate comments and article submissions.

Publishing your articles here