Subject Big Database Decision Pending - Wisdom Needed
Author dbdweeb
Our company has to make a significant database decision soon. We
currently host our application (ASP-like) with 1000+/- databases over
the internet. There are lots of enduser queries sometimes peaking at
about 200 queries per second for extended periods on some databases.
(We have peaked at 800 queries per second on a few occasions.) Our
developers want to standardize on one database for initial coding and
to minimize the number of other databases supported. We want a free
open source database engine as the primary database with code support
to run our apps on Oracle and SQLServer. Currently our primary
database is MySQL/MyISAM with a lot of extra coding effort expended
to work around its non-ANSI compliance, due mostly to the lack of
subselects and limited support (or non-production support) for ACID
compliant transactions.

Requirements: The database must be ACID compliant, with good ANSI-92
SQL support including support for subselects and unions. The database
must be scalable and fault tolerant with good hot backup and recovery
capabilities. It must be able to provide good 24X7 production support
with support for dynamic storage management to handle data growth.
Triggers, views, stored procedures, and RI are not so important at
the present.

We have looked at MySQL/Innodb but are concerned about 24X7
production support. We need to accommodate database growth without
having to bounce the database. Aside from ACID compliant
transactions, we deperately need production support for subselects
and are concerned as to when this will be supported by MySQL. If we
abandon our MySQL support we will have to migrate all the data to the
new database engine... no big deal.

Our current database engine candidates are: MySQL with Innodb;
PostgreSQL; Firebird/Interbase; and SAPDB.

What I'm looking for is a "decision matrix" comparing features among
the above free database engines. I'm also looking for some real world
examples or case studies on stretching these database engines to the
max as regards 24X7 OLTP support for large databases with high
numbers of concurrent users.

I believe this should be a good discussion topic for this list but I
will gladly receive any direct email. No spam please. :-)