OpenBSD as a Database Server

10/17/2000

An increasingly common tenant of the Internet world is the "online" database server, with functions ranging from dynamic content storage and generation to user information storage and webmail/message board services. The most common database server used online is MS SQL Server, particularly prominent in the corporate sector, in conjunction with ASP to process and format information between the web interface and the database back end. Recent security problems with MS SQL and IIS (with which ASP generally interoperates) has left plenty to be desired from the product and furthered the cause of alternatives, such as PHP and MySQL under Linux or BSD. In terms of security, price, performance, and scalability, PHP/MySQL performs very advantageously compared to commercial alternatives. Support for OpenBSD (within PHP in particular) has in the past been sketchy, but things appear to be improving now. PHP3 and MySQL are both in the ports tree distributed with OpenBSD 2.7, making installation and configuration a relatively simple process. For very high load sites, it is recommended to recompile Apache with PHP as a static object; the ports tree installation detailed below will build a DSO (Dynamic Server Object) that is still extremely fast for all but the highest load sites.

Assuming the user has extracted ports.tar.gz from /usr, to install MySQL from ports:

cd /usr/ports/databases/mysql/
make && make install

Once MySQL is installed, modify the php3 makefile to include MySQL support:

cd /usr/ports/www/php3/
vi Makefile

The CONFIGURE_ARGS+= section of the makefile should be modified to include a line:

--with-mysql \

Then complete, build and install PHP3:

make && make install

This will produce a MySQL compliant DSO of PHP3, which must then be read in by Apache. Edit /var/www/conf/httpd.conf within the LoadModule section to contain a line:

LoadModule php3_module /usr/lib/apache/modules/libphp3.so

Restart Apache, and the installation is complete. I won't go into the details of PHP or SQL programming, but a quick test.php file to run on your system might be:

<?php
echo "This is a test of PHP\n";
?>

Version 4 of PHP has recently been released and contains many advantages over PHP3. Unfortunately, there are still some rather nasty bugs with PHP4/OpenBSD that keep PHP3 as the standard ports tree install for OpenBSD 2.7. This is more indicative of PHP's development from a Linux-centric perspective than of OpenBSD quirks that prevent its full support; but with the capabilities of OpenBSD for this kind of application outlined here, hopefully, support is improving.

The other database engine commonly used in place of MySQL (in my personal opinion an inferior system, but best not to enter into such a debate) is PostgreSQL. The installation procedure is similar to MySQL.

Again assuming the user has extracted ports.tar.gz from /usr, the only prerequisite package for PostgreSQL is gmake (GNU make), which most users will already have, but it will automatically install from ports if not available.

To install PostgreSQL from ports:

cd /usr/ports/databases/postgresql/
make && make install

Some brief post-installation configuration specific to OpenBSD is required, documented in /usr/local/share/doc/pgsql/README.OpenBSD:

This complete, PHP should be compiled via the method described above for MySQL compatibility, with the exception of Makefile modification. This should instead read:

vi Makefile

The CONFIGURE_ARGS+= section of the makefile should be modified to include a line:

--with-postgresql \

This kind of setup enables a myriad of online database possibilities, as discussed above, but still provides some limitations. Although a powerful API for MySQL and PostgreSQL exists, it is, as yet, not feasibly implementable as an application database server. You cannot (easily) create Windows desktop applications that interact with a MySQL/PostgreSQL database or perform advanced database operations like replication or image information retrieval. It is for this kind of application that Oracle and MS SQL exceed the alternatives. Although a native port of any commercial database system does not exist for OpenBSD, OpenBSD's binary compatibility with SunOS/sparc and Linux/i386 comes in handy. Linux binary compatibility configuration is a rather arduous process, as is the installation of Oracle, so I will not discuss it in this overview of OpenBSD database operations.

Larger database operations, generally defined within the industry as "enterprise" databases, begin to impact system resources to the extent that special configuration or hardware selection may be necessary to cope. Although I in no way purport OpenBSD as an enterprise database server (yet!), capacity exists for this. The steps I've taken on my high-traffic database server to increase speed are:

Build PHP as a static object within Apache (including MySQL support).

Recompile both the kernel and the entire operating system from source on the local machine. This slightly improves speed of all operations, due to CPU optimization.

Ensure that plenty of RAM is available on the database server. We all know that once a machine runs out of memory and starts swapping, nightmare performance abounds, but be aware that even lightweight database servers such as MySQL and PostgreSQL can eat up some serious amounts of memory (and CPU time for that matter) when placed under sizable load.

Use some sort of disk access enhancing technique. Several options are available for this: CCD (similar to HPUX's LVM - Logical Volume Management), RAM disk usage, or kernel tuning. In this case I've opted to tune my kernel with the following option:

option BUFCACHEPERCENT=45

This means 45% of system memory is used as a filesystem buffer, enhancing access speeds to less commonly used data on a high memory system. This is really only viable with a significant amount of memory that is never put into commission by the system.

With options as advanced and diverse as these in terms of software selection, tuning, and performance, I see data warehousing and other database-related applications as being a strong future for OpenBSD, as these become increasingly sensitive to security problems. Windows NT and commercial flavors of Unix such as SunOS and HPUX continue to strongly dominate this market, but with "features" like the sa default account in MS SQL server and the outlandish cost of implementing a Sun or HP system, this may have a limited future.

David Jorm
has been involved with open source and security projects for several years, originally with OpenBSD and Debian GNU/Linux, now with the development team at wiretapped.net.