[Asterisk-Users] OT: Best DB

[Chris Travers]

First, for an on-topic comment :-)

Which database you choose will largely have the most to do with what 
applications you need to integrate your Asterisk databases with.  If 
those applications are based on MySQL, you may need to use that.  Ditto 
with Oracle, MS SQL, etc.  My personal favorite is PostgreSQL, though.

CDR and conf storage are not demanding in and of themselves, so you will 
want to look into what else is available to do what you want.  For 
example, maybe you want it to connect to a billing app.

Now for the off-topic portion....

Giudice, Salvatore wrote:

>So, let me see if I am right. You run a support shop? You want your
>database to validate your data for you instead of leaving that logic to
>your application?
>
You validate your data in both places.  You validate it in the 
application in order to prevent certain types of problems, but...

If you don't validate it in the database, you have the possibility that 
a programming bug in your application could render your large database 
worthless because your data may not have a consistant meaning.

Here is the thing:  A good relational database manager will allow you to 
automate three things:  storage, mainenance/enforcement, and 
presentation of your data.  The first is handled using tables, the 
second using triggers, and the third using views.  Without these three, 
you do not have a real robust database manager.  MySQL is largely built 
for storage only.  I.e. it cannot handle the other two aspects 
reasonably at all.  If you are writing a content management system, this 
is OK, but if you are trying to build a complex data warehouse 
supporting multiple frontends, this breaks down very quickly.  Again, 
the more front-ends you have the more you have to worry about 
application bugs introducing bad data into your database.

Finally, on this point, you assume that the application and the database 
are extremely tightly coupled.  In larger deployments, these are usually 
maintained separately.  So the issue with numeric datatypes being 
truncated in MySQL is a *big deal* because the application cannot be 
expected to know what the database thinks the max size is for the 
field.  Same with strings but the effects are less severe because 
usually people are not doing mathematical operations on strings....  
This problem also becomes more severe when an application must support 
different database managers which have different limitations.  You see 
where I am going?  The RDBMS is the *only* place you can be *sure* to 
enforce your data constraints properly.

> Usually, a database is considered to be an asset worth
>protecting from unvalidated user input.Also, do you routinely try to
>  
>
>insert text strings into fields, which are not created large enough to
>accept these strings? This is somewhat disturbing. The lack of a warning
>is virtually unimportant, if you know your data before you insert it.
>
Ok, so consider the following scenario:

You have a database application that supports PostgreSQL and Firebird 
1.0.  Firebird 1.0 does NOT have a TEXT type, and you have a large 
comments field in one of your tables.  So the maintainer of the Firebird 
database schema sets the equivalent comments field to 2048 in length 
assuming that this will always be enough.

SO now the application has two possible limits: 1GB or 2048 characters.  
What happens if someone tries to insert 3000 characters?  Is it 
reasonable to just truncate the string?  Well, it may be or may not be 
depending on your application.  But I think that it is fair to say that 
the default should be to raise an error, and that this can be overridden 
if necessary by custom triggers.  If nothing else, this will require 
that the programming team be notified of the error rather than 
*silently* truncating your data.

Now, FWIW, PostgreSQL used to default to truncating strings.  They fixed 
this in the 7.x series.

> If
>you are running into those kinds of problems, you need better
>programmers or at the very least better DBA's to design better database
>schemas.
>
MySQL supports schemas now?  Oh wait---  a MySQL database is just what 
the rest of us call a schema.

Anyway, the issue has more to do with larger deployments of critical 
data (say bank transactions) rather than smaller, simpler apps.

>With regard to your performance examples, I can not agree nor disagree
>with your observations. The largest of my past applications involved a
>ridiculously high number of batch/blind inserts and periodic data
>condensation with replicated storage for high level report optimization.
>I ran this app using a Beowulf cluster for parsing and two 8-way cpu
>servers running MySQL with a 2-terrabyte ultra160 storage array. I
>realize this is not the typical user experience, but I can tell you that
>we were able to handle a peak of 700k inserts per hour.
>
Ok, so lots of inserts, no updates, few selects. Complex data processing 
on separate cluster.  MySQL should be pretty good at this.

However, if you need to handle complex reporting for multiple apps, you 
might want to simplify things using views.  However, I don't know 
exactly what you were doing so I don't know how possible this would have 
been on another database management system.

Best Wishes,
Chris Travers
Metatron Technology Consulting
-------------- next part --------------
A non-text attachment was scrubbed...
Name: chris.vcf
Type: text/x-vcard
Size: 127 bytes
Desc: not available
Url : http://lists.digium.com/pipermail/asterisk-users/attachments/20050317/5b2cae65/chris.vcf