LinuxPlanet - Print - Apples and Oranges: A Linux DBMS Comparison

Apples and Oranges: A Linux DBMS Comparison

By: Matthias Warkus
Wednesday, November 10, 1999 03:04:53 PM EST
URL: http://www.linuxplanet.com/linuxplanet/tutorials/1224/1/

Introducing the Series

When it was first suggested to me that I write a comparison of database-management systems for LinuxPlanet, I shook my head. I have never done such a comparison. But then, I thought: why not? Maybe a fresh, naīve approach isn't all that bad. So here you have it: a fresh-eyed look at the wild world of Linux DBMS systems, done in four parts.

The database managers compared here are free or cheap to license, and they run on all levels of Linux systems, from the lowest-end hardware to high-end multiple-CPU servers. And they are widely used in production systems all over the world. For this comparison, I put myself in the situation of someone running typical Linux boxes, powering an everyday DBMS.

I'll be looking at:

mSQL
MySQL
PostgreSQL

I'll begin with a discussion of the installation and configuration of these three database systems; then I'll proceed, in Part II, with the design of a small database in SQL, pointing out the difficulties and differences I encountered trying to make the DBMS swallow it. I'll also explain the coding of a program that will perform various real-life actions on the database, such as adding data and generating reports, while discussing the various differences of the databases in the process. This client will be implemented in C for all three DBMSes. To do this, I must learn not only the SQL APIs of all the systems, but SQL, too. If nothing else, this will surely be fun to watch.

Part III will contain some actual benchmarks, as a bonus. I have had the dubious pleasure of watching endless debates about database benchmarking on Linux newsgroups, so I will only time the performance of the client doing random stuff, both when done in one run under ideal conditions and when done via several concurrent processes. At that time I'll have already covered lots of differences between the DBMSes, so you should have a good idea of their strengths and weaknesses.

The final part will be what you'll all be waiting for: the conclusion, covering feature, implementation, API and performance differences between the DBMSes.

But now, grab your towel for a venture deep into the wonders of homebrew database management.

Acquiring and Installing PostgreSQL

At the software section of the PostgreSQL Web site there is a list of FTP and HTTP mirrors to download distributions from, as well as the option to order the DBMS on CD. Building and installing it is a multistep process.

First of all, if you want PostgreSQL's Perl bindings to be built, the Perl 5 executable on your system must be known under the name of perl5. Usually, that means you need to make a symbolic link.
Perhaps the most important thing to do is create a postgres user. On my system, this command worked:
```
useradd -c "PostgreSQL Administrator" -d /opt/postgresql -g users postgres
```
On other systems, the command might be different. Note that I assume postgres is in the users group. You might prefer to give the database administrator its own group.
Allother steps of the installation need to be done as user postgres if not otherwise mentioned.
Untar the PostgreSQL download and change into the source directory tree:
```
tar -xvzf postgresql-6.5.2.tar.gz
cd postgresql-6.5.2
```
As root, create the PostgreSQL directory and give it to postgres:
```
mkdir /opt/pgsql
chown postgres.users /opt/pgsql
```
Go to the source directory and configure the package. I used the following options:
```
./configure --prefix=/opt/pgsql --with-perl
```

Do the building and installation process:

cd ../doc
make install
cd ../src
make all
make install

Now it's time to perform some additional system configuration.

As root, add the line
```
/opt/pgsql/lib
```
to /etc/ld.so.conf and run ldconfig.
Install Perl bindings. When logged in as root, do:
```
cd interfaces/perl5
make install
```
Set the postgres account up. Add to the following to the .profile file:
```
PATH=$PATH:/opt/pgsql/bin
MANPATH=$MANPATH:/opt/pgsql/man
PGLIB=/opt/pgsql/lib
PGDATA=/opt/pgsql/data
export PATH MANPATH PGLIB PGDATA
LC_COLLATE=C
LC_CTYPE=C
export LC_COLLATE LC_CTYPE
```
The last three lines are necessary for the init process and maybe some tests. For actual users, they should of course be set to the appropriate locale, but everyone who uses PostgreSQL needs to have at least PGLIB and PGDATA set.
Source the .profile file:
```
source .profile
```
Run the post-installation:
```
initdb
```

Acquiring and Installing MySQL

The most recent version of MySQL can be obtained via the MySQL download page. There are numerous mirrors to choose from. You'll need to perform the following steps:

Untar it and change into the source directory tree:

tar -xvzf mysql-3.22.26a.tar.gz
cd mysql-3.22.26a

Configure the package; I used following options:
```
./configure --prefix=/opt/mysql
```
Build it:
```
make
```
Install it; as root:
```
make install
```

You'll then need to configure the installation:

As root, create the grant tables:
```
scripts/mysql_install_db
```
As root, start the server:
```
/opt/mysql/bin/safe_mysqld &
```

As root, create the mysql user:

useradd -c "MySQL Administrator" -d /opt/mysql -g users mysql

As root, give the installation tree to the mysql user:
```
chown -R mysql.users /opt/mysql
```

Acquiring and Installing mSQL

Downloading mSQL is not as uncomplicated as downloading PostgreSQL or MySQL, but then, the distribution is much smaller, too. Start out from Hughes Technologies Web page and look for further instructions. To download mSQL, one needs to first register with the site and go through a CGI-based downloading system which allows choice between a couple of mirrors if necessary. Building and installing mSQL is a matter of the following steps:

Untar it and change into the source directory tree:
```
tar -xvzf msql-2.0.11.tar.gz
cd msql-2.0.11
```
Generate the target definitions:
```
make target
```
Change into the target directory for your platform:
```
cd targets/Linux-2.2.12-i586
```
Configure the package:
```
./setup
```
Edit site.mm; I only changed the installation path into /opt/msql.
Build it:
```
make all
```
Install it as root:
```
make install
```

After building and installing mSQL, you'll need to configure it:

As root, create the msql user:

useradd -c "mSQL Administrator" -d /opt/msql -g users msql

As root, give the tree to the msql user:
```
chown -R msql.users /opt/msql
```

Next week: Designing the database.