[Asterisk-Users] OT: Best DB

[Giudice, Salvatore]

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? 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. 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. As for the incorrect table being created, this sounds like
either a bug or human error. Either way, how is MySQL supposed to flag
that as an error? Can it read your mind and tell you wanted an InnoDB?
After you have checked your programming and isolated it to a potential
malfunction of the database, you could call MySQL technical support
directly and get immediate assistance (assuming that you have a support
contract).

I agree that postgreSQL and MySQL have different feature sets. Your
application design may drive your selection based on feature
requirements.

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. MySQL gave us
very few problems and probably had a cumulative downtime of
approximately 4 days per year until the project was decommissioned. When
you are pushing the limits of a database with this type of application,
the entire dynamic is changes. Other factors beyond selects become
important, such as the speed at which you can create indexes.

Either way, pick whichever DB you feel comfortable with regard to your
circumstances.


-----Original Message-----
From: Chris Travers [mailto:chris at verkiel.metatrontech.com] 
Sent: Tuesday, March 15, 2005 5:57 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] OT: Best DB

Giudice, Salvatore wrote:

>MySQL: Speed, Power and Precision
>  
>

Thanks, I will file this in my MySQL Appointment Book under Feb 31.....
Oh, you mean that is not a valid date? MySQL had no problem with it...

Seriously though, precision and accuracy are not strongpoints of MySQL.

MySQL really has been designed to work extremely well for content 
management systems.  And it does this quite well.  However, for 
applications where the precision of your data manners, MySQL is not 
worth trusting under any circumstances, IMO.  For example:

Feb 31 is a valid date, as is Feb 29, 2005.
0000-00-00 is also a valid date.

If you create a numeric(4,2) field, and insert into it a value like 
1000000 it will truncate your number without even raising an error.

Text fields are truncated without so much as a warning.

Under certain circumstances, a MyISAM talbe can be created where an 
Innodb table was specified, thereby running inserts/updates/deletes on 
that table outside transactional control.  Again, an error is not
reported.

All of the above behaviors are violations of the ANSI SQL standards 
which under certain circumstances can endanger the integrity of your
data.

Look, I am not saying "Don't use MySQL."   I am saying that "I don't use

MySQL because I have no use for it."

Also, MySQL does perform faster on simple selects with low concurrency 
than PostgreSQL does (1-2 clients).  But if you get up to 32 concurrent 
users, some reports indicate that MySQL will actually take more time to 
run the queries concurrently than that serially, but YMMV.  PostgreSQL 
does scale better for high concurrency usage under every single 
benchmark I have seen.  With modern versions, it doesn't even require 
tuning unless you want to use that system solely as your database
manager.

Also, MySQL does not have many features I use for my more advanced 
work.  It support for subselects is somewhat immature, and it has no 
support for views, stored procedures, triggers, schemas, complex data 
types, and the like.  Iirc, it has no group or role permissions either, 
meaning that if you have a large number of users, managing the security 
can be a bear (yes, I have added emulation to some MySQL databases of 
this feature, but it is easier to add in other database managers).

If you want to use MySQL, go ahead.  That is fine.  My business will 
even support you if you do.  We just think that there are deficiencies 
in the database manager, so we run all our operations on PostgreSQL and 
only support MySQL for some customer applications.

>If you are so keen on paying for something, try buying support - MySQL
>AB. With PostgreSQL, you could get support from a mom and pop shop...
>However, either way you will save tons of money over Oracle.
>
>  
>
You could get PostgreSQL support from a mom-and-pop shop.  Same with 
MySQL...  Of course that is not the only place you can get support.

Fujitsu is now selling a version of PostgreSQL which they support, for 
example.  PostgreSQL, Inc. also sells support and pays at least some 
members of the core development team.  Then there are businesses such as

Metatron Technology Consulting (shameless plug, we will support both, 
BTW), SRA, and Command Prompt, each of which offer high quality support.

Best Wishes,
Chris Travers