Perfect Database Server
November 21, 2008 1 Comment
My current to-do favorite project is to come up with definitive solutions to the question: how to set up the perfect database server? I realize that there is not much information in the question to go on. What kind of hardware, operating system, DBMS, network environment, and application requirements are we talking about? Do you want open source or commercial closed-source solutions? What kind of data storage requirements are there? Do you want the ability to scale up (better hardware) or scale out (more hardware, like clustering)? What kind of budget do you have? See, just one question brings out so many questions (and I am not finished with this list yet). My future goal, therefore, is to answer such questions with authority for as many solutions as I (with or without help from other interested parties) can come up with.
The first thing I think, is to ask what does the application require? Does it require a basic data store or some fancy “enterprise” solution? Does it require read-heavy or write-heavy operations, or is it a mix of both? How many users of the application are anticipated?
Next step is to determine the environment in which this database will run. It includes people who will be running this server. What is their experience and expertise? How willing are they to venture out from their comfort zones? What kinds of servers are they already managing? Does the environment have solutions from a particular vendor or is it a hybrid? Do you want dedicated servers or are willing to go with virtualization? Can you find the performance hit of virtualization acceptable?
Now we look at the budget. How much can you spend on hardware? If your application requires a cluster, can you afford it? If your application can run on a single database server, can you purchase a better server than absolutely required? Can you afford Oracle, DB2, and/or MS SQL Server? Or do you want a different solution in PostgreSQL and MySQL? Do you want the features and support of Solaris, or are you comfortable with Linux?
After thinking a little, I see that not all solutions can be tested by just one person. There isn’t enough time and money to look over all possibilities. I can start with free and open source software. Once I have exhausted those to the best I can, I can move toward proprietary solutions. The trick, however, is to go back and revisit the solutions some time later to keep up with updates and new technology. For example, if I tested Debian with MySQL, then if a new version of either Debian or MySQL comes up, I should go back and see what needs to change in the solution I provided.
I think I will start with the following things (if and when I can):
FreeBSD, Debian, Red Hat Enterprise Linux, Solaris, OpenSolaris
Without virtualization and with virtualization (Xen, KVM, VMware, OpenVZ, Linux-VServer)
MySQL, PostgreSQL, Apache Derby, One$DB, Firebird, Ingres, DB2, Oracle
Standalone and cluster
Local and external data storage (iSCSI, ZFS, UFS, GFS, DRDB, XFS, JFS, ReiserFS, Ext3)