Discussion:
[Asterisk-Users] OT: Best DB
l***@ipmotel.net
2005-03-09 19:50:47 UTC
Permalink
I know this is a bit off topic but we are using Asterisk :)

Since this list is full of tech gurus w/ all different sorts of backgrounds,
I thought I would get the best opinions here.

We have several different switches and other telecom equipment at our
facilities which all have their own proprietary cdr platforms, which are
rather limited. The company I work for is looking to develop their own
in-house billing system that would combine cdr from all platforms and bring
it into one big db, so we can do whatever we like w/ the data...billing,
invoices, reports, asr...etc...

So my question is this....

What's the most stable, fastest & reliable database for this project? Call
volume is about 8 to 10 million minutes per month, and we want to have 12
months of cdr available at any given time, anything older can be archived on
tape.
So what's the best db...oracle, ms sql, informix, mysql or something else?

What server specs would be ideal for this type of setup?

TIA,
Jon

---
[This E-mail scanned for viruses by Declude Virus]
Steven Critchfield
2005-03-09 20:08:27 UTC
Permalink
Post by l***@ipmotel.net
I know this is a bit off topic but we are using Asterisk :)
Since this list is full of tech gurus w/ all different sorts of backgrounds,
I thought I would get the best opinions here.
We have several different switches and other telecom equipment at our
facilities which all have their own proprietary cdr platforms, which are
rather limited. The company I work for is looking to develop their own
in-house billing system that would combine cdr from all platforms and bring
it into one big db, so we can do whatever we like w/ the data...billing,
invoices, reports, asr...etc...
So my question is this....
What's the most stable, fastest & reliable database for this project? Call
volume is about 8 to 10 million minutes per month, and we want to have 12
months of cdr available at any given time, anything older can be archived on
tape.
So what's the best db...oracle, ms sql, informix, mysql or something else?
Best is subjective. It really depends on what needs you have to satisfy
and what you are willing to compromise on.

You can drop all the commercial DBs if your group is willing to
develop/work a bit more.

If you need replication, you need to look into what supports it. I
understand there are tools for Mysql and Postgres to do replication, but
postgres may not be as well polished there yet.

My experience has been that postgres is better at scaling than mysql
without having to jump through the hoops to make it scale. Of course my
experience was about as much writing(inserts, updates) as reads.

For stability, I don't think there is any problems with mysql or
postgres. I only had problems with mysql under heavy load.
--
Steven Critchfield <***@basesys.com>
Tzafrir Cohen
2005-03-09 20:18:09 UTC
Permalink
Post by l***@ipmotel.net
I know this is a bit off topic but we are using Asterisk :)
Since this list is full of tech gurus w/ all different sorts of
backgrounds, I thought I would get the best opinions here.
We have several different switches and other telecom equipment at our
facilities which all have their own proprietary cdr platforms, which are
rather limited. The company I work for is looking to develop their own
in-house billing system that would combine cdr from all platforms and bring
it into one big db, so we can do whatever we like w/ the data...billing,
invoices, reports, asr...etc...
So my question is this....
What's the most stable, fastest & reliable database for this project? Call
volume is about 8 to 10 million minutes per month, and we want to have 12
months of cdr available at any given time, anything older can be archived
on tape.
So what's the best db...oracle, ms sql, informix, mysql or something else?
I'm not saying that it is the best or anything, I'm just wondering why
it is ommited:

PostgreSQL

Comes well-integrated with your linux distro of choice, just like MySQL.
Well-supoprted by Asterisk.

I don't know exactly what type of application you'll have but generally
it performs better than MySQL with more complex logic.

Both MySQL and PostgreSQL have the obvious atvantage of being free of
licensing headaches. E.g.: you'll never loose a week of development
because the server licenses are delayed and you'll never have to change
your design because client licenses are expensive.
--
Tzafrir Cohen | New signature for new address and | VIM is
http://tzafrir.org.il | new homepage | a Mutt's
***@cohens.org.il | | best
ICQ# 16849755 | Space reserved for other protocols | friend
l***@ipmotel.net
2005-03-09 20:43:46 UTC
Permalink
For some reason I didn't think PostgreSQL was for mission critical apps. I
don't think I have any reasoning behind it, just didn't think it was
"hardcore"...sounds like i might be wrong...i'll have to look into it more.

Open source advantages are obvious, but aside from licensing and cost
factors, I believe speed, security, and stability are going to be the key
factors for us, whether open source or not.

Thanks,

Jon


----- Original Message -----
From: "Tzafrir Cohen" <***@cohens.org.il>
To: <asterisk-***@lists.digium.com>
Sent: Wednesday, March 09, 2005 3:18 PM
Subject: Re: [Asterisk-Users] OT: Best DB
Post by Tzafrir Cohen
Post by l***@ipmotel.net
I know this is a bit off topic but we are using Asterisk :)
Since this list is full of tech gurus w/ all different sorts of
backgrounds, I thought I would get the best opinions here.
We have several different switches and other telecom equipment at our
facilities which all have their own proprietary cdr platforms, which are
rather limited. The company I work for is looking to develop their own
in-house billing system that would combine cdr from all platforms and bring
it into one big db, so we can do whatever we like w/ the data...billing,
invoices, reports, asr...etc...
So my question is this....
What's the most stable, fastest & reliable database for this project? Call
volume is about 8 to 10 million minutes per month, and we want to have 12
months of cdr available at any given time, anything older can be archived
on tape.
So what's the best db...oracle, ms sql, informix, mysql or something else?
I'm not saying that it is the best or anything, I'm just wondering why
PostgreSQL
Comes well-integrated with your linux distro of choice, just like MySQL.
Well-supoprted by Asterisk.
I don't know exactly what type of application you'll have but generally
it performs better than MySQL with more complex logic.
Both MySQL and PostgreSQL have the obvious atvantage of being free of
licensing headaches. E.g.: you'll never loose a week of development
because the server licenses are delayed and you'll never have to change
your design because client licenses are expensive.
--
Tzafrir Cohen | New signature for new address and | VIM is
http://tzafrir.org.il | new homepage | a Mutt's
ICQ# 16849755 | Space reserved for other protocols | friend
_______________________________________________
Asterisk-Users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users
http://lists.digium.com/mailman/listinfo/asterisk-users
---
[This E-mail scanned for viruses by Declude Virus]
---
[This E-mail scanned for viruses by Declude Virus]
Linterra
2005-03-09 20:58:47 UTC
Permalink
Post by l***@ipmotel.net
For some reason I didn't think PostgreSQL was for mission critical apps. I
don't think I have any reasoning behind it, just didn't think it was
"hardcore"...sounds like i might be wrong...i'll have to look into it more.
Open source advantages are obvious, but aside from licensing and cost
factors, I believe speed, security, and stability are going to be the key
factors for us, whether open source or not.
Thanks,
Jon
----- Original Message -----
Sent: Wednesday, March 09, 2005 3:18 PM
Subject: Re: [Asterisk-Users] OT: Best DB
Post by Tzafrir Cohen
Post by l***@ipmotel.net
I know this is a bit off topic but we are using Asterisk :)
Since this list is full of tech gurus w/ all different sorts of
backgrounds, I thought I would get the best opinions here.
We have several different switches and other telecom equipment at our
facilities which all have their own proprietary cdr platforms, which are
rather limited. The company I work for is looking to develop their own
in-house billing system that would combine cdr from all platforms and bring
it into one big db, so we can do whatever we like w/ the data...billing,
invoices, reports, asr...etc...
So my question is this....
What's the most stable, fastest & reliable database for this project? Call
volume is about 8 to 10 million minutes per month, and we want to have 12
months of cdr available at any given time, anything older can be archived
on tape.
So what's the best db...oracle, ms sql, informix, mysql or something else?
I'm not saying that it is the best or anything, I'm just wondering why
PostgreSQL
Comes well-integrated with your linux distro of choice, just like MySQL.
Well-supoprted by Asterisk.
I don't know exactly what type of application you'll have but generally
it performs better than MySQL with more complex logic.
Both MySQL and PostgreSQL have the obvious atvantage of being free of
licensing headaches. E.g.: you'll never loose a week of development
because the server licenses are delayed and you'll never have to change
your design because client licenses are expensive.
--
Tzafrir Cohen | New signature for new address and | VIM is
http://tzafrir.org.il | new homepage | a Mutt's
ICQ# 16849755 | Space reserved for other protocols | friend
_______________________________________________
Asterisk-Users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users
http://lists.digium.com/mailman/listinfo/asterisk-users
---
[This E-mail scanned for viruses by Declude Virus]
---
[This E-mail scanned for viruses by Declude Virus]
_______________________________________________
Asterisk-Users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users
http://lists.digium.com/mailman/listinfo/asterisk-users
PostgreSQL 8.0 would be my recommendation. The new features that were
put into production with this latest release are impressive.
Replication, point-in-time recovery, transactions and many other
features make this database outshine mySQL (even with innoDB)

I have several database applications which each average over two
million queries a day. mySQL works just fine except it degrades to
full-tablespace scans too quickly, whereas postgreSQL does a much
better job of optimizing more complicated queries. Even when
postgreSQL resorts to a full table scan, it still seems to return the
results quite a bit faster than mySQL. Everything else is the same
(same OS, computer, processor, RAM, hard-drive, etc).

Anything new that my team produces will definately be running on postgreSQL.

Just my $.02.
Steven Critchfield
2005-03-09 21:02:03 UTC
Permalink
Post by l***@ipmotel.net
For some reason I didn't think PostgreSQL was for mission critical apps. I
don't think I have any reasoning behind it, just didn't think it was
"hardcore"...sounds like i might be wrong...i'll have to look into it more.
Open source advantages are obvious, but aside from licensing and cost
factors, I believe speed, security, and stability are going to be the key
factors for us, whether open source or not.
Postgres is probably more developed than mysql. Mysql gets a lot of
press though as being an easy to install and config database. As for
stability/scalability, the .org registry is on postgres.
--
Steven Critchfield <***@basesys.com>
Walt Reed
2005-03-09 22:13:33 UTC
Permalink
Post by Steven Critchfield
Post by l***@ipmotel.net
For some reason I didn't think PostgreSQL was for mission critical apps. I
don't think I have any reasoning behind it, just didn't think it was
"hardcore"...sounds like i might be wrong...i'll have to look into it more.
Open source advantages are obvious, but aside from licensing and cost
factors, I believe speed, security, and stability are going to be the key
factors for us, whether open source or not.
Postgres is probably more developed than mysql. Mysql gets a lot of
press though as being an easy to install and config database. As for
stability/scalability, the .org registry is on postgres.
We use both MySQL and Postgres inhouse for production applications.
MySQL has advanced significantly in the past year, and functionality is
catching up with Postgres. Postgres is improving performance and is
catching up to MySQL. Both are rock solid. Both have features the other
lacks. I'd probably go with Postgres however for a new application. I
see no point in giving MS or Oracle any more money for something that is
a freely available commodity at this point.
Adam Goryachev
2005-03-10 01:40:10 UTC
Permalink
Post by Steven Critchfield
Post by l***@ipmotel.net
For some reason I didn't think PostgreSQL was for mission critical apps. I
don't think I have any reasoning behind it, just didn't think it was
"hardcore"...sounds like i might be wrong...i'll have to look into it more.
Open source advantages are obvious, but aside from licensing and cost
factors, I believe speed, security, and stability are going to be the key
factors for us, whether open source or not.
Postgres is probably more developed than mysql. Mysql gets a lot of
press though as being an easy to install and config database. As for
stability/scalability, the .org registry is on postgres.
Yesterday, for the first time, a customer pointed out this database:
http://firebird.sourceforge.net/

Has anyone had any experience with it, and could compare it to either
mysql/postgres?

Thanks,
Adam
Forrest W. Christian
2005-03-10 08:04:35 UTC
Permalink
Post by l***@ipmotel.net
For some reason I didn't think PostgreSQL was for mission critical apps. I
don't think I have any reasoning behind it, just didn't think it was
"hardcore"...sounds like i might be wrong...i'll have to look into it more.
For your app, probably either MySQL or PostreSQL will work.

I'm a happy MySQL user ... others are just as happy with PostgreSQL.

I think it's almost what you're familiar with at this point. The
differences between the two are getting smaller.

MySQL traditionally was considered a very high speed database server
lacking some advanced features such as transactions and triggers and some
query types. Postgres was considered a slower, feature complete SQL
implementation.

Today, MySQL has more features that it lacked earlier - i.e. it's got
transactions and additional queries, and so on.

I understand that PostgreSql has also gotten faster than it used to be.

So, at this point it's almost devolved into a holy war as opposed to there
being any real difference.

Personally I use MySQL because I find it easier to admin and configure on
my FreeBSD systems than PostgreSQL, which I tend to have ongoing problems
with in the spots I have to run it. I don't miss the couple of PostgreSQL
features that mysql still doesn't have (but will in the near future).

I'd really recommend that you look at developing the app so it is database
independent - at least between MySQL and PostgreSQL. That way, you can
swap from one to the other if you decide you don't like the one you pick
initially.

-forrest
Tom Ivar Helbekkmo
2005-03-10 09:33:52 UTC
Permalink
Post by Forrest W. Christian
Today, MySQL has more features that it lacked earlier - i.e. it's got
transactions and additional queries, and so on.
I understand that PostgreSql has also gotten faster than it used to be.
So, at this point it's almost devolved into a holy war as opposed to there
being any real difference.
Emphasis on "almost", though. MySQL still has a long way to go, and
the bits that are missing or inferior will take an awful lot of work
to catch up. It *is* getting better with every release, though. It's
just still (as it has been throughout) trailing quite a bit behind
PostgreSQL as a "real" RDBMS. Its huge popularity was a matter of
timing and luck, and once it reached critical mass, well... :-)

That said, lots and lots of people are quite happy with their MySQL
installations. The reason why I recommend not choosing MySQL for a
new project is that you don't know when you'll suddenly need some
capability that it doesn't have -- and finding out after you've
invested a lot of time and effort isn't any fun. Better to use what
is known to have everything you need *plus* most of the stuff you
might conceivably find yourself needing in the future.

-tih
--
Don't ascribe to stupidity what can be adequately explained by ignorance.
Walt Reed
2005-03-10 13:44:56 UTC
Permalink
Post by Forrest W. Christian
I understand that PostgreSql has also gotten faster than it used to be.
It's interesting. Just yesterday I was saying that we use both MySQL and
Postgres here, and that we were probably going to move everything to
postgres just to consolidate.

Now one of our lead engineers has done some performance testing last
night for our
app and found MySQL to be 8 to 100 times faster for all but one of our
operations (combination of ~80% reads, 20% writes on the InnoDB table
type.) His testing basically increased the load until performance was
unacceptable.

This is with lots of optimizations on Postgres (the current DB for the
app) and none on MySQL. Needless to say, we now need to re-evaluate our
decision to move everything to Postgres.

In the end, it all comes down to knowing exactly what features you need
for your app, how your specific app performs on each DB, what you need
for support, etc. As Forrest mentioned, write DB independant code and
then you can easily choose the DB that is best for your app. 2 years for
now, you may find a need to switch DB's.
David Filion
2005-03-10 13:55:53 UTC
Permalink
Post by Walt Reed
Post by Forrest W. Christian
I understand that PostgreSql has also gotten faster than it used to be.
It's interesting. Just yesterday I was saying that we use both MySQL and
Postgres here, and that we were probably going to move everything to
postgres just to consolidate.
Now one of our lead engineers has done some performance testing last
night for our
app and found MySQL to be 8 to 100 times faster for all but one of our
operations (combination of ~80% reads, 20% writes on the InnoDB table
type.) His testing basically increased the load until performance was
unacceptable.
This is with lots of optimizations on Postgres (the current DB for the
app) and none on MySQL. Needless to say, we now need to re-evaluate our
decision to move everything to Postgres.
In the end, it all comes down to knowing exactly what features you need
for your app, how your specific app performs on each DB, what you need
for support, etc. As Forrest mentioned, write DB independant code and
then you can easily choose the DB that is best for your app. 2 years for
now, you may find a need to switch DB's.
Out of curiosity, what version of PostgreSQL was used? 7.x, 8.x? Also,
was the test run on the same system? I'm not looking to bash. I'm just
curious as we are in the same MySQL/PostgreSQL boat.

David
Walt Reed
2005-03-10 15:09:00 UTC
Permalink
Post by David Filion
Post by Walt Reed
Now one of our lead engineers has done some performance testing last
night for our
app and found MySQL to be 8 to 100 times faster for all but one of our
operations (combination of ~80% reads, 20% writes on the InnoDB table
type.) His testing basically increased the load until performance was
unacceptable.
This is with lots of optimizations on Postgres (the current DB for the
app) and none on MySQL. Needless to say, we now need to re-evaluate our
decision to move everything to Postgres.
Out of curiosity, what version of PostgreSQL was used? 7.x, 8.x? Also,
was the test run on the same system? I'm not looking to bash. I'm just
curious as we are in the same MySQL/PostgreSQL boat.
We are useing 7.4.6. Considering 8.0 just came out in January, and
considering how many major changes went into it, we were leary of
upgrading until it had time to get tested by the masses. I would expect
8.x to be faster that 7.x, but I didn't see anything in the release
notes that would indicate a 1 to 2 orders of magnitude performance increase.

The tests were run on the same server (RHEL3 on a maxed out DL380-g4).
We had been tuning the table design / query design, postgres config,
etc. for quite some time, trying to get better performance. the mysql
install was just the standard binaries available on the mysql site, with
the default config.
Tom Ivar Helbekkmo
2005-03-10 15:39:25 UTC
Permalink
I would expect 8.x to be faster that 7.x, but I didn't see anything
in the release notes that would indicate a 1 to 2 orders of
magnitude performance increase.
A few points concerning PostgreSQL and performance:

- Each of the latest releases has improved performance quite a bit.

- Out of the box, it is tuned for minimal resource use, and dismal
performance. It really needs to be tuned. Check out Josh Berkus's
web site, <http://www.powerpostgresql.com/>, for hints and tips.

- Nothing helps much if your schema and your queries are suboptimal.
Think about how your data is used, consider what indexes you need,
rewrite slow queries to be smarter (use EXPLAIN).

- Did I mention you need to tune the database system to your needs?

-tih
--
Don't ascribe to stupidity what can be adequately explained by ignorance.
Walt Reed
2005-03-10 15:54:32 UTC
Permalink
Post by Tom Ivar Helbekkmo
I would expect 8.x to be faster that 7.x, but I didn't see anything
in the release notes that would indicate a 1 to 2 orders of
magnitude performance increase.
- Each of the latest releases has improved performance quite a bit.
- Out of the box, it is tuned for minimal resource use, and dismal
performance. It really needs to be tuned. Check out Josh Berkus's
web site, <http://www.powerpostgresql.com/>, for hints and tips.
- Nothing helps much if your schema and your queries are suboptimal.
Think about how your data is used, consider what indexes you need,
rewrite slow queries to be smarter (use EXPLAIN).
- Did I mention you need to tune the database system to your needs?
You snipped out my paragraph where I mentioned tuning the DB itself,
queries, and schema. I have no doubt that 8.x is faster than 7.x, but I
did not find any reports from people claiming a 10X performance boost. I
didn't look hard, but I did look. I'll look into installing 8.x and see
if we can rerun the tests.
Tom Ivar Helbekkmo
2005-03-10 16:41:14 UTC
Permalink
Post by Walt Reed
You snipped out my paragraph where I mentioned tuning the DB itself,
queries, and schema.
Yeah, I see now that it looked as if I were implying that you hadn't.
My little list wasn't directed at you as such, I just used your
posting as a hook for pointing out to people the importance of tuning.
Sorry about the misunderstanding!

-tih
--
Don't ascribe to stupidity what can be adequately explained by ignorance.
Andrew Kohlsmith
2005-03-10 14:17:11 UTC
Permalink
Post by Walt Reed
Now one of our lead engineers has done some performance testing last
night for our
app and found MySQL to be 8 to 100 times faster for all but one of our
operations (combination of ~80% reads, 20% writes on the InnoDB table
type.) His testing basically increased the load until performance was
unacceptable.
I'd *love* to see the particulars of that test. It's been shown time and time
again that postgres' speed CLOBBERS mysql for anything but the simplest
selects, and that it can handle far more concurrent connections without
slowing down.

Have you asked the folks on freenode #postgresql as well?
Post by Walt Reed
This is with lots of optimizations on Postgres (the current DB for the
app) and none on MySQL. Needless to say, we now need to re-evaluate our
decision to move everything to Postgres.
It's quite possible your optimizations are buggering things up too. I ran
into that. :-)

-A.
Steven Critchfield
2005-03-10 17:01:07 UTC
Permalink
Post by Andrew Kohlsmith
Post by Walt Reed
Now one of our lead engineers has done some performance testing last
night for our
app and found MySQL to be 8 to 100 times faster for all but one of our
operations (combination of ~80% reads, 20% writes on the InnoDB table
type.) His testing basically increased the load until performance was
unacceptable.
I'd *love* to see the particulars of that test. It's been shown time and time
again that postgres' speed CLOBBERS mysql for anything but the simplest
selects, and that it can handle far more concurrent connections without
slowing down.
This brings back the question of testing methodology. If the tester that
posted here only tested sequential queries, I could see MySQL showing
faster. A test that would probably show less of a gap is running
whatever testing app multiple time simultaneously as it will start
showing the ability to handle concurrent users.
--
Steven Critchfield <***@basesys.com>
Jay Milk
2005-03-10 06:37:37 UTC
Permalink
Firebird started at Interbase. Around version 6.0.1 Interbase "went
open-source" and published all available source-code for Interbase,
quite possibly in an attempt to enter formerly closed markets. Since
then, IB was further developed (closed-source) into version 7.0 and now
7.5, and Firebird recently matured to 1.5. Interbase reached market
recognition in the PC World as the database of choice for Borland's
Delphi and C-Builder products. Borland, during the brief period they
called themselves Inprise, acquired Interbase, so now it's Borland
Interbase.

So much for the history. As for usability, IB is your typical (almost)
ANSI SQL-92 compliant database engine. It supports RI, triggers, stored
procs, just like we all like'em. Its engine is touted for the
"superserver architecture" but in years of working with it
professionally, I still couldn't quite tell you what that is. I saw a
few benchmarks floating around in the mid-90s were Interbase just rocked
the heck out of Oracle, MSSQL and Sybase. I'm not sure if it's still in
the lead on performance, but IB has supported SMP since version 7 --
firebird trails a bit on this. In practice, I've seen performance
issues in databases of a certain size.

Administration is where IB/Firebird really shine -- A database exists in
a single file. No special partitions, folders, devices, access rights
needed, just the one file. This simplicity carries security
ramifications; you can simple copy a database file from one server to
another, and obtain full access to its contents, because users and
privileges are stored outside of the database file. Yep, that's bad.

Programming IB or FB is the same as other engines out there. ODBC,
JDBC, ADO DB and native drivers are available. As someone who's dealt
quite a bit with tweaking IB for best performance, I recommend you take
a good long look at IB's locking mechanisms due to its
multi-generational architecture. When you know how this works, and you
program accordingly, you can really make IB sing. If you access it
without regard for its uniqueness, you've got yourself a ball-and-chain.

I've had some extensive IB experience, and I never once considered IB/FB
for my web-based apps -- probably because mySQL "feels" leaner and/or
was available at the time and fully integrated in PHP. At this stage, I
would look toward mySQL or Postgres for anything that runs on Linux.
For PC applications on the other hand, Firebird is an amazingly scalable
and affordable (duh!) solution. Write a stand-alone app and deploy your
DB in a single file, running on the FB embedded (single-DLL) engine.
Scale it up and use that same DB file with the full-blown FB server.
Open source, it's a beautiful thing.
-----Original Message-----
Sent: Wednesday, March 09, 2005 7:40 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] OT: Best DB
Post by Steven Critchfield
Post by l***@ipmotel.net
For some reason I didn't think PostgreSQL was for mission
critical
Post by Steven Critchfield
Post by l***@ipmotel.net
apps. I
don't think I have any reasoning behind it, just didn't
think it was
Post by Steven Critchfield
Post by l***@ipmotel.net
"hardcore"...sounds like i might be wrong...i'll have to
look into it more.
Post by Steven Critchfield
Post by l***@ipmotel.net
Open source advantages are obvious, but aside from licensing and
cost
factors, I believe speed, security, and stability are
going to be the key
Post by Steven Critchfield
Post by l***@ipmotel.net
factors for us, whether open source or not.
Postgres is probably more developed than mysql. Mysql gets a lot of
press though as being an easy to install and config
database. As for
Post by Steven Critchfield
stability/scalability, the .org registry is on postgres.
Yesterday, for the first time, a customer pointed out this
database: http://firebird.sourceforge.net/
Has anyone had any experience with it, and could compare it
to either mysql/postgres?
Thanks,
Adam
Steven Critchfield
2005-03-10 07:00:01 UTC
Permalink
Post by Jay Milk
So much for the history. As for usability, IB is your typical (almost)
ANSI SQL-92 compliant database engine. It supports RI, triggers, stored
procs, just like we all like'em. Its engine is touted for the
"superserver architecture" but in years of working with it
professionally, I still couldn't quite tell you what that is. I saw a
few benchmarks floating around in the mid-90s were Interbase just rocked
the heck out of Oracle, MSSQL and Sybase. I'm not sure if it's still in
the lead on performance, but IB has supported SMP since version 7 --
firebird trails a bit on this. In practice, I've seen performance
issues in databases of a certain size.
If it stores the entire DB in 1 file, it can not scale as well as other
DBs. Postgres 8 supports splitting a single DB up so you can put
portions of it on different media if needed. If you have to tune for
absolute speed, you can purchase one of the solid state drives for the
tables that need that kind of speed while using much less expensive
harddrives for the rest of the DB. While I do not remember mysql
supporting it this directly, I think I remember the file structure being
not to difficult to figure out and split and symlink back together if
need be.
--
Steven Critchfield <***@basesys.com>
Richard Cook
2005-03-10 07:03:20 UTC
Permalink
Post by l***@ipmotel.net
I know this is a bit off topic but we are using Asterisk :)
Since this list is full of tech gurus w/ all different sorts
of backgrounds, I thought I would get the best opinions here.
We have several different switches and other telecom
equipment at our facilities which all have their own
proprietary cdr platforms, which are rather limited. The
company I work for is looking to develop their own in-house
billing system that would combine cdr from all platforms and
bring it into one big db, so we can do whatever we like w/
the data...billing, invoices, reports, asr...etc...
So my question is this....
What's the most stable, fastest & reliable database for this
project? Call volume is about 8 to 10 million minutes per
month, and we want to have 12 months of cdr available at any
given time, anything older can be archived on tape.
So what's the best db...oracle, ms sql, informix, mysql or
something else?
We use PostgreSQL in house. It performs wonderfully and cross-platform
drivers (ODBC, .NET) are way further along than MySQL. We switched from
MySQL a couple of months ago and have never been happier.

--
Richard Cook
***@voctel.com
T: 705-223-2000 x2010
Apollon Koutlides
2005-03-10 07:41:24 UTC
Permalink
Post by Richard Cook
We use PostgreSQL in house. It performs wonderfully and cross-platform
drivers (ODBC, .NET) are way further along than MySQL. We switched from
MySQL a couple of months ago and have never been happier.
We use Postgres exclusively too (12 databses, several of them with
several millions of records, both OLAP and OLTP roles). We switched from
informix 4 years ago and we also subscribe to the "never been happier"
point of view.
Jay Milk
2005-03-10 14:57:42 UTC
Permalink
IB/FB stores the DB in one file, but the file can span multiple drives
if needed. However, you can't select which table goes into which file.
Personally, I don't think that's very feasible, nor is it required -- if
a table is accessed often enough to be mission critical, large parts of
it will reside in memory due to caching anyway.
-----Original Message-----
Sent: Thursday, March 10, 2005 1:00 AM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: RE: [Asterisk-Users] OT: Best DB
If it stores the entire DB in 1 file, it can not scale as
well as other DBs. Postgres 8 supports splitting a single DB
up so you can put portions of it on different media if
needed. If you have to tune for absolute speed, you can
purchase one of the solid state drives for the tables that
need that kind of speed while using much less expensive
harddrives for the rest of the DB. While I do not remember
mysql supporting it this directly, I think I remember the
file structure being not to difficult to figure out and split
and symlink back together if need be.
Steven Critchfield
2005-03-10 16:45:01 UTC
Permalink
Post by Jay Milk
IB/FB stores the DB in one file, but the file can span multiple drives
if needed. However, you can't select which table goes into which file.
Personally, I don't think that's very feasible, nor is it required -- if
a table is accessed often enough to be mission critical, large parts of
it will reside in memory due to caching anyway.
Maybe I work in an odd environment where writes(updates and inserts) are
probably equal to or more than the reads. Caching isn't real helpful at
making the data get to disk faster. Caching helps for reads only.

I'll admit I haven't had to use this feature yet, but I see where some
people could really need it.
Post by Jay Milk
-----Original Message-----
Sent: Thursday, March 10, 2005 1:00 AM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: RE: [Asterisk-Users] OT: Best DB
If it stores the entire DB in 1 file, it can not scale as
well as other DBs. Postgres 8 supports splitting a single DB
up so you can put portions of it on different media if
needed. If you have to tune for absolute speed, you can
purchase one of the solid state drives for the tables that
need that kind of speed while using much less expensive
harddrives for the rest of the DB. While I do not remember
mysql supporting it this directly, I think I remember the
file structure being not to difficult to figure out and split
and symlink back together if need be.
--
Steven Critchfield <***@basesys.com>
m***@americatel.com.sv
2005-03-10 15:09:09 UTC
Permalink
Post by Andrew Kohlsmith
I'd *love* to see the particulars of that test. It's been
shown time and time
again that postgres' speed CLOBBERS mysql for anything but
the simplest
selects, and that it can handle far more concurrent
connections without
slowing down.
I strongly agree with this, i have a prepaid voip solution with asterisk,
freeradius and postgresql , the hole thing relies in stored procedures and
triggers (i mean the billing, traffic monitoring, admin system, etc). It had
scaled from thousands of minutes per month to two millions in these days
without an issue, we export the cdr to mysql for the IT/Customer Service
guys, because they have php/mysql programmers (We do Java/Postgres), but we
keep the original data in our postgres DB, for simple select like the sum
of minutes per month or per customer in a period of time, yeah, postgres is
about 30% slower than mysql, but if they want to know the total
minutes/calls per destination country, customers and peak hours, we run a
single stored procedure with temporal tables and cursors, wait some seconds
and... oh yeah, the beautiful report suddently appears in our screen, then
we smile while our workmates run 3 or 4 querys (stored procedures in mysql?,
not yet, triggers?, not yet, cursors?, not yet, what else ??.... not
yet,etc) and wait minutes for their results.

Hardware
HP DL380, 2x 2.8 Ghz, 3GB RAM
Software
Asterisk, Freeradius, Postgresql 7.4, Mysql 4.1


Just my $0.02

---
Miguel
Walt Reed
2005-03-10 18:41:31 UTC
Permalink
Post by m***@americatel.com.sv
Post by Andrew Kohlsmith
I'd *love* to see the particulars of that test. It's been
shown time and time
again that postgres' speed CLOBBERS mysql for anything but
the simplest
selects, and that it can handle far more concurrent
connections without
slowing down.
I strongly agree with this, i have a prepaid voip solution with asterisk,
freeradius and postgresql , the hole thing relies in stored procedures and
triggers (i mean the billing, traffic monitoring, admin system, etc). It had
scaled from thousands of minutes per month to two millions in these days
without an issue, we export the cdr to mysql for the IT/Customer Service
OK, as some of you suspected, I found out that the test was serial. I'm
having the programmer re-do the testing to be more representative of
real-life - many concurent connections doing many different kinds of
queries / inserts / updates at the same time.

I too prefer postgres, but it's damn hard to state your case when someone
hands you test results that show mysql beating the pants off it.

I expect that we will see very different results under the new test.
Scott Bussinger
2005-03-10 16:08:37 UTC
Permalink
We're setting up some Sipura 841 phones and they're working pretty well, but
the microphone volume on the headset (not the handset) is too loud with our
Plantronics headsets. Is there some way to turn down the amplification on
the headset mic?

The microphones are picking up the sound of someone walking on the floor
across the room and every little movement or shuffle of the user. I found
places to control the output volume of handset, headset, speaker, and ring
but I haven't found anything for input control.

Can anyone suggest anything? Thanks!
Eric Wieling
2005-03-10 21:03:56 UTC
Permalink
Post by Scott Bussinger
We're setting up some Sipura 841 phones and they're working pretty well, but
the microphone volume on the headset (not the handset) is too loud with our
Plantronics headsets. Is there some way to turn down the amplification on
the headset mic?
The microphones are picking up the sound of someone walking on the floor
across the room and every little movement or shuffle of the user. I found
places to control the output volume of handset, headset, speaker, and ring
but I haven't found anything for input control.
Can anyone suggest anything? Thanks!
On the Plantronics M175 there is a little three position switch where
the plug is to adjust the microphone sensitivity. I usually keep mine
set to the middle position, except when I'm calling my grandmother who
is hard of hearing.
--
Always do right. This will gratify some people and astonish the rest.
Mark Twain
m***@americatel.com.sv
2005-03-10 16:25:42 UTC
Permalink
Post by Walt Reed
You snipped out my paragraph where I mentioned tuning the DB itself,
queries, and schema. I have no doubt that 8.x is faster than
7.x, but I
did not find any reports from people claiming a 10X
performance boost. I
didn't look hard, but I did look. I'll look into installing
8.x and see
if we can rerun the tests.
a fast query is not always enough, if this is your case, then go for mysql.
is you look for full featured db, strong performance (may be you can see a
mysql query is faster some times, but with postgres you get acceptable
response time on every query, no matter how complex it is), great support
forums, true open source (free), and you have talented SQL programmers (you
need to use more than simple sum,left join, case statements) you must go
with postgres, they feel more comfortable with their great PLPG/SQL
language, not the limited functions of mysql (read my previus post on this
thread for details).

---
Miguel
James Pooton
2005-03-10 20:00:44 UTC
Permalink
I think you're in luck :) Sipura has a new firmware out for the 841 now,
version 3.1.1a. (Quite the jump from 0.9.5) It includes -6db, 0db, +6db
options for input gain on the handset, headset and speakerphone now. Release
notes and downloads are available on their site.

-James

-----Original Message-----
From: asterisk-users-***@lists.digium.com
[mailto:asterisk-users-***@lists.digium.com] On Behalf Of Scott
Bussinger
Sent: Thursday, March 10, 2005 9:09 AM
To: 'Asterisk Users Mailing List - Non-Commercial Discussion'
Subject: [Asterisk-Users] Sipura 841 Headset microphone volume?

We're setting up some Sipura 841 phones and they're working pretty well, but
the microphone volume on the headset (not the handset) is too loud with our
Plantronics headsets. Is there some way to turn down the amplification on
the headset mic?

The microphones are picking up the sound of someone walking on the floor
across the room and every little movement or shuffle of the user. I found
places to control the output volume of handset, headset, speaker, and ring
but I haven't found anything for input control.

Can anyone suggest anything? Thanks!


_______________________________________________
Asterisk-Users mailing list
Asterisk-***@lists.digium.com
http://lists.digium.com/mailman/listinfo/asterisk-users
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
Eric Wieling
2005-03-10 21:05:42 UTC
Permalink
Post by James Pooton
I think you're in luck :) Sipura has a new firmware out for the 841 now,
version 3.1.1a. (Quite the jump from 0.9.5) It includes -6db, 0db, +6db
options for input gain on the handset, headset and speakerphone now. Release
notes and downloads are available on their site.
The handset and speakerphone gain settings missing from 0.9.5 is what
kept us from buying more of the 841's.
--
Always do right. This will gratify some people and astonish the rest.
Mark Twain
Jay Milk
2005-03-10 23:06:28 UTC
Permalink
It's not that odd an environment, but I've found that with all the
various apps we're running on DBs, updates are usually the least common
transaction, inserts the next, and reads the most common by a margin --
and this is where caching shines. Updates also aren't that costly,
since IB/FB, as most DB engines, supports a write-through cache and
commits changes to disk when idle. I've seen small tables with a high
ratio of updates vs. reads (1:3, 1:4) occasionally, but this was a
result of the programmers choice to put temporary operating data into
the database, rather than into memory.
-----Original Message-----
Sent: Thursday, March 10, 2005 10:45 AM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: RE: [Asterisk-Users] OT: Best DB
Post by Jay Milk
IB/FB stores the DB in one file, but the file can span
multiple drives
Post by Jay Milk
if needed. However, you can't select which table goes into which
file. Personally, I don't think that's very feasible, nor is it
required -- if a table is accessed often enough to be mission
critical, large parts of it will reside in memory due to caching
anyway.
Maybe I work in an odd environment where writes(updates and
inserts) are probably equal to or more than the reads.
Caching isn't real helpful at making the data get to disk
faster. Caching helps for reads only.
Giudice, Salvatore
2005-03-11 00:14:36 UTC
Permalink
I vote for MySQL. PostgreSQL is fine, but MySQL handles much better
under extreme load. MySQL is also usually touted as being generally
faster for the average application. However on the flip side, PostgreSQL
supports more features than MySQL, such as subqueries, more
functionality in stored procedures, cursors, and views. In terms of
support, you can get support from MySQL directly, while PostgreSQL means
you have to turn to mailing lists. It's really your preference depending
on the size of your organization and how skilled your staff is in
supporting open source in house. Lastly, be aware that MySQL is
distributed under the GNU license with a commercial rider for derivative
works and PostgreSQl is a BSD license.

Also if you are looking at SER as part of your infrastructure, I would
recommend you stick with MySQL.

Cheers... SG

-----Original Message-----
From: Apollon Koutlides [mailto:***@planewalk.net]
Sent: Thursday, March 10, 2005 2:41 AM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] OT: Best DB
Post by Richard Cook
We use PostgreSQL in house. It performs wonderfully and cross-platform
drivers (ODBC, .NET) are way further along than MySQL. We switched
from
Post by Richard Cook
MySQL a couple of months ago and have never been happier.
We use Postgres exclusively too (12 databses, several of them with
several millions of records, both OLAP and OLTP roles). We switched from

informix 4 years ago and we also subscribe to the "never been happier"
point of view.
Mohit Muthanna
2005-03-11 01:05:58 UTC
Permalink
On Thu, 10 Mar 2005 19:14:36 -0500, Giudice, Salvatore
Post by Giudice, Salvatore
I vote for MySQL. PostgreSQL is fine, but MySQL handles much better
under extreme load. MySQL is also usually touted as being generally
I'd have to (respectfully) disagree with that... MySQL just cannot
handle high load or large datasets... it's inherent design does not
allow it to scale too well...

I lost countless hours trying to optimize disk / filesystem
distribution, SQL queries, kernel parameters etc. etc. to get MySQL to
_not crawl_. After many failed attempts, I switched to Postgres and
haven't looked back.

I personally believe there is a "right tool for the right job". MySQL
works great for small datasets and (relatively) lighter load. Infact,
it shines there. But don't expect it to perform as your database grows
in orders of magnitude.

Postgres is certainly a database that is "recommended" (IMHO) for
production environments. If you're a VoIP provider, and are trying to
provide a near "carrier-grade" service, postgres shines.

Moht.
--
Mohit Muthanna [mohit (at) muthanna (uhuh) com]
"There are 10 types of people. Those who understand binary, and those
who don't."
Matthew Boehm
2005-03-11 14:51:33 UTC
Permalink
Post by Mohit Muthanna
If you're a VoIP provider, and are trying to
provide a near "carrier-grade" service, postgres shines.
I'm not disagreeing with you, but we are a CLEC and we do provide
'carrier-grade' service and we use MySQL everywhere.

IMHO, MySQL is just so much more easy to use, install and maintain.
phpMyAdmin makes it even easier.

-Matthew
Post by Mohit Muthanna
On Thu, 10 Mar 2005 19:14:36 -0500, Giudice, Salvatore
Post by Giudice, Salvatore
I vote for MySQL. PostgreSQL is fine, but MySQL handles much better
under extreme load. MySQL is also usually touted as being generally
I'd have to (respectfully) disagree with that... MySQL just cannot
handle high load or large datasets... it's inherent design does not
allow it to scale too well...
I lost countless hours trying to optimize disk / filesystem
distribution, SQL queries, kernel parameters etc. etc. to get MySQL to
_not crawl_. After many failed attempts, I switched to Postgres and
haven't looked back.
I personally believe there is a "right tool for the right job". MySQL
works great for small datasets and (relatively) lighter load. Infact,
it shines there. But don't expect it to perform as your database grows
in orders of magnitude.
Postgres is certainly a database that is "recommended" (IMHO) for
production environments. If you're a VoIP provider, and are trying to
provide a near "carrier-grade" service, postgres shines.
Moht.
Steven Critchfield
2005-03-11 16:19:07 UTC
Permalink
Post by Matthew Boehm
Post by Mohit Muthanna
If you're a VoIP provider, and are trying to
provide a near "carrier-grade" service, postgres shines.
I'm not disagreeing with you, but we are a CLEC and we do provide
'carrier-grade' service and we use MySQL everywhere.
IMHO, MySQL is just so much more easy to use, install and maintain.
phpMyAdmin makes it even easier.
If that is a deciding reason, you should check out phppgadmin sometime.
Very similar interface but for postgres.
--
Steven Critchfield <***@basesys.com>
Tom Ivar Helbekkmo
2005-03-11 03:49:20 UTC
Permalink
PostgreSQL is fine, but MySQL handles much better under extreme
load.
Every actual test I've seen says the opposite, as does most of the
anecdotal evidence. Theoretically, as well, PostgreSQL should have
the edge under heavy load.
PostgreSQL supports more features than MySQL, such as subqueries,
more functionality in stored procedures, cursors, and views.
...where "more functionality" means they actually exist. :-)
In terms of support, you can get support from MySQL directly, while
PostgreSQL means you have to turn to mailing lists.
Not true. There's plenty professional support available, including
from some of the implementors of PostgreSQL.

-tih
--
Don't ascribe to stupidity what can be adequately explained by ignorance.
Andrew Kohlsmith
2005-03-12 20:29:55 UTC
Permalink
Post by Giudice, Salvatore
I vote for MySQL. PostgreSQL is fine, but MySQL handles much better
under extreme load. MySQL is also usually touted as being generally
You *gotta* be kidding me. MySQL can't hold a candle to PostgreSQL for high
load, high volume or complex queries. MySQL is great for simple selects and
light duty use but you will have to introduce clustering and failover much
sooner for MySQL than you ever will for Postgres.

As far as speed goes, MySQL's speed falls down *very* quickly once you start
using anything more than simple SELECTs. Throw in some joins, some ordering
and complex clauses and it grinds to a crawl.
Post by Giudice, Salvatore
functionality in stored procedures, cursors, and views. In terms of
support, you can get support from MySQL directly, while PostgreSQL means
you have to turn to mailing lists. It's really your preference depending
There are plenty of companies to help you with PostgreSQL,
http://www.commandprompt.com being the most obvious choice (they will sell
you PostgreSQL with a support license.)
Post by Giudice, Salvatore
supporting open source in house. Lastly, be aware that MySQL is
distributed under the GNU license with a commercial rider for derivative
works and PostgreSQl is a BSD license.
MySQL's constant licensing issues are the biggest reason why it's not natively
supported in Asterisk! Please, please, please get your facts straight.

-A.
joachim
2005-03-15 20:08:23 UTC
Permalink
Giudice, Salvatore
2005-03-11 18:56:47 UTC
Permalink
I have had MySQL databases running in excess of 2 terrabytes handling up
to 700,000 inserts/hour on an 8 cpu machine. Try doing that with
PostgreSQL.

If you are just running SER or Asterisk, etc - you simply do not need
the increased feature set or the need to optimize postgreSQL.

As for the "production recommendation" you refer to, I would
respectufully disagree. If you are an enterprise comapny looking to
deploy an open-source DB, you will pick the one that has an established
support company to contract with. So, 'NO': postgreSQL is not
recommended for production environments. MYSQL AB provides enterprise
class support. PostgreSQL support consists of contracting with mom and
pop support shops, mailing lists, and irc. That simply will not be
acceptable for the enterprise user.

In the end, pick whichever one works for you with the least problems.
Maybe postgreSQL is easier for your people to support. Green pill or
blue pill, it's your choice...

-----Original Message-----
From: Mohit Muthanna [mailto:***@gmail.com]
Sent: Thursday, March 10, 2005 8:06 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] OT: Best DB

On Thu, 10 Mar 2005 19:14:36 -0500, Giudice, Salvatore
Post by Giudice, Salvatore
I vote for MySQL. PostgreSQL is fine, but MySQL handles much better
under extreme load. MySQL is also usually touted as being generally
I'd have to (respectfully) disagree with that... MySQL just cannot
handle high load or large datasets... it's inherent design does not
allow it to scale too well...

I lost countless hours trying to optimize disk / filesystem
distribution, SQL queries, kernel parameters etc. etc. to get MySQL to
_not crawl_. After many failed attempts, I switched to Postgres and
haven't looked back.

I personally believe there is a "right tool for the right job". MySQL
works great for small datasets and (relatively) lighter load. Infact,
it shines there. But don't expect it to perform as your database grows
in orders of magnitude.

Postgres is certainly a database that is "recommended" (IMHO) for
production environments. If you're a VoIP provider, and are trying to
provide a near "carrier-grade" service, postgres shines.

Moht.
--
Mohit Muthanna [mohit (at) muthanna (uhuh) com]
"There are 10 types of people. Those who understand binary, and those
who don't."
_______________________________________________
Asterisk-Users mailing list
Asterisk-***@lists.digium.com
http://lists.digium.com/mailman/listinfo/asterisk-users
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
Matthew Boehm
2005-03-11 20:10:58 UTC
Permalink
My god. WTF is doing 700,000 inserts/hour for 2TB of data?

-Matthew
Post by Giudice, Salvatore
I have had MySQL databases running in excess of 2 terrabytes handling
up to 700,000 inserts/hour on an 8 cpu machine. Try doing that with
PostgreSQL.
David Filion
2005-03-11 21:25:59 UTC
Permalink
Post by Giudice, Salvatore
I have had MySQL databases running in excess of 2 terrabytes handling up
to 700,000 inserts/hour on an 8 cpu machine. Try doing that with
PostgreSQL.
If you are just running SER or Asterisk, etc - you simply do not need
the increased feature set or the need to optimize postgreSQL.
As for the "production recommendation" you refer to, I would
respectufully disagree. If you are an enterprise comapny looking to
deploy an open-source DB, you will pick the one that has an established
support company to contract with. So, 'NO': postgreSQL is not
recommended for production environments. MYSQL AB provides enterprise
class support. PostgreSQL support consists of contracting with mom and
pop support shops, mailing lists, and irc. That simply will not be
acceptable for the enterprise user.
In the end, pick whichever one works for you with the least problems.
Maybe postgreSQL is easier for your people to support. Green pill or
blue pill, it's your choice...
-----Original Message-----
Sent: Thursday, March 10, 2005 8:06 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] OT: Best DB
On Thu, 10 Mar 2005 19:14:36 -0500, Giudice, Salvatore
Post by Giudice, Salvatore
I vote for MySQL. PostgreSQL is fine, but MySQL handles much better
under extreme load. MySQL is also usually touted as being generally
I'd have to (respectfully) disagree with that... MySQL just cannot
handle high load or large datasets... it's inherent design does not
allow it to scale too well...
I lost countless hours trying to optimize disk / filesystem
distribution, SQL queries, kernel parameters etc. etc. to get MySQL to
_not crawl_. After many failed attempts, I switched to Postgres and
haven't looked back.
I personally believe there is a "right tool for the right job". MySQL
works great for small datasets and (relatively) lighter load. Infact,
it shines there. But don't expect it to perform as your database grows
in orders of magnitude.
Postgres is certainly a database that is "recommended" (IMHO) for
production environments. If you're a VoIP provider, and are trying to
provide a near "carrier-grade" service, postgres shines.
Moht.
Maybe I miss read, but doesn't the licensing of the newer releases of
MySQL require companies to purchase a license? This would mean that
while it is open source, it is not "free as in beer". This does not
mean it is not a good DB, just that there may be more that just the
costs of a support contract involved. This is why most distros still
ship the last version before the license change. As for support, check
out http://techdocs.postgresql.org/companies.php.

For terrabytes, you can ask on the PostgreSQL mailing lists and get
numerous responses from people running DBs in the terrabyte range. I'm
sure the MySQL lists have simular stories.


<rant>
The DB mailing lists are full of "to-from" and "success-failure"
stories. Both camps post stories saying "we're #1!".

Personally, I go with what meets (and ideally exceeds) a projects
needs. If after evaluating a projects needs there is not a clear
winner, try the possibilities and determine for you self what works.

Asking "what database is best" is like asking "which Linux distro is the
best". There is no clear answer and generally the only result is a
massive thread with no clear answer.
</rant>

Just my $0.02CDN.

David Filion









David
Tzafrir Cohen
2005-03-12 23:04:20 UTC
Permalink
Post by David Filion
Maybe I miss read, but doesn't the licensing of the newer releases of
MySQL require companies to purchase a license?
No. The license is GPL. Originally it was LGPL for the client libraries
but this got changed recently.

So you have a number of options:

1. Use the GPL libraries, and use the code internally only. As long as
you don't distribute your code the GPL imposes no restrictions on your
code.

2. Use the GPL libraries and allow your internal code to be exposed

3. Pay MySQL AB for a license.
Post by David Filion
This would mean that
while it is open source, it is not "free as in beer". This does not
mean it is not a good DB, just that there may be more that just the
costs of a support contract involved. This is why most distros still
ship the last version before the license change. As for support, check
out http://techdocs.postgresql.org/companies.php.
Debian keeps both 4.0 and 4.1 . Fedora's rawhide now has 4.1, Latest
Mandrake has 4.1.
--
Tzafrir Cohen | New signature for new address and | VIM is
http://tzafrir.org.il | new homepage | a Mutt's
***@cohens.org.il | | best
ICQ# 16849755 | Space reserved for other protocols | friend
Tzafrir Cohen
2005-03-12 22:46:45 UTC
Permalink
Post by Giudice, Salvatore
As for the "production recommendation" you refer to, I would
respectufully disagree. If you are an enterprise comapny looking to
deploy an open-source DB, you will pick the one that has an established
support company to contract with. So, 'NO': postgreSQL is not
recommended for production environments. MYSQL AB provides enterprise
class support. PostgreSQL support consists of contracting with mom and
pop support shops, mailing lists, and irc. That simply will not be
acceptable for the enterprise user.
You know, you probably shouldn't rely on Linux (the kernel) as there is
no company behind it. I also wonder what is your source for support for
Apache. What about PHP? is Zend your sole source of support there?

BTW: the fact that the MySQL stuff is in add-ons is also because
Asterisk is about as strict as MySQL regarding the license. But you may
also be interested in reading
http://lists.alioth.debian.org/pipermail/pkg-voip-maintainers/2005-February/001301.html
--
Tzafrir Cohen | New signature for new address and | VIM is
http://tzafrir.org.il | new homepage | a Mutt's
***@cohens.org.il | | best
ICQ# 16849755 | Space reserved for other protocols | friend
Giudice, Salvatore
2005-03-11 20:46:09 UTC
Permalink
Security events generated from IDS.

-----Original Message-----
From: Matthew Boehm [mailto:***@cytelcom.com]
Sent: Friday, March 11, 2005 3:11 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] OT: Best DB

My god. WTF is doing 700,000 inserts/hour for 2TB of data?

-Matthew
Post by Giudice, Salvatore
I have had MySQL databases running in excess of 2 terrabytes handling
up to 700,000 inserts/hour on an 8 cpu machine. Try doing that with
PostgreSQL.
_______________________________________________
Asterisk-Users mailing list
Asterisk-***@lists.digium.com
http://lists.digium.com/mailman/listinfo/asterisk-users
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
Robert Hajime Lanning
2005-03-11 21:02:10 UTC
Permalink
<quote who="Giudice, Salvatore">
Post by Giudice, Salvatore
Security events generated from IDS.
That is called logging noise.
That must have been a experiment in statistic anomalies and trends.
--
END OF LINE
-MCP
Mohit Muthanna
2005-03-11 23:04:33 UTC
Permalink
Not sure what kind of IDS you used, but you'd better switch to another one.


On Fri, 11 Mar 2005 15:46:09 -0500, Giudice, Salvatore
Post by Giudice, Salvatore
Security events generated from IDS.
-----Original Message-----
Sent: Friday, March 11, 2005 3:11 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] OT: Best DB
My god. WTF is doing 700,000 inserts/hour for 2TB of data?
-Matthew
Post by Giudice, Salvatore
I have had MySQL databases running in excess of 2 terrabytes handling
up to 700,000 inserts/hour on an 8 cpu machine. Try doing that with
PostgreSQL.
_______________________________________________
Asterisk-Users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users
http://lists.digium.com/mailman/listinfo/asterisk-users
_______________________________________________
Asterisk-Users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users
http://lists.digium.com/mailman/listinfo/asterisk-users
--
Mohit Muthanna [mohit (at) muthanna (uhuh) com]
"There are 10 types of people. Those who understand binary, and those
who don't."
tim panton
2005-03-12 11:10:50 UTC
Permalink
Post by l***@ipmotel.net
I know this is a bit off topic but we are using Asterisk :)
Since this list is full of tech gurus w/ all different sorts of
backgrounds, I thought I would get the best opinions here.
We have several different switches and other telecom equipment at our
facilities which all have their own proprietary cdr platforms, which
are rather limited. The company I work for is looking to develop their
own in-house billing system that would combine cdr from all platforms
and bring it into one big db, so we can do whatever we like w/ the
data...billing, invoices, reports, asr...etc...
So my question is this....
What's the most stable, fastest & reliable database for this project?
Call volume is about 8 to 10 million minutes per month, and we want to
have 12 months of cdr available at any given time, anything older can
be archived on tape.
So what's the best db...oracle, ms sql, informix, mysql or something else?
At the risk of sounding like a closed source fan (I'm not) I do think
you should
at least consider Oracle for this job.
I built a system a few years ago which takes a constant stream of
entries from a number (<100)
of remote systems analizes them and generates reports
(see http://www.westpoint.ltd.uk/example-reports/reports/index.htm)

We use Oracle for it, and it has been great. Also they have improved the
weakest points:
1) pricing - It is now _much_ cheaper than it was
2) Install - I had a couple of oracle newbies install it in a couple
of hours, that was never possible
in the old days.

Once you have it there are _stacks_ of neat features and a really solid
performance.
I am especialy fond of the ability to put java into triggers (we send
SNMP traps to
ops console when specific error conditions occur on inserts) and the
whole oracle
Text and XML integration has saved me _months_ of development time on
various project.

My view is that if you are going to spend significant development time/money
on a big database project, you shouldn't rule oracle out 'cos of a 2k fee.

Tim.
Post by l***@ipmotel.net
What server specs would be ideal for this type of setup?
TIA,
Jon
---
[This E-mail scanned for viruses by Declude Virus]
_______________________________________________
Asterisk-Users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users
http://lists.digium.com/mailman/listinfo/asterisk-users
Chris Travers
2005-03-13 19:27:18 UTC
Permalink
Post by tim panton
At the risk of sounding like a closed source fan (I'm not) I do think
you should
at least consider Oracle for this job.
I built a system a few years ago which takes a constant stream of
entries from a number (<100)
of remote systems analizes them and generates reports
(see http://www.westpoint.ltd.uk/example-reports/reports/index.htm)
We use Oracle for it, and it has been great. Also they have improved
1) pricing - It is now _much_ cheaper than it was
2) Install - I had a couple of oracle newbies install it in a
couple of hours, that was never possible
in the old days.
On the other hand, PostgreSQL is Free and can be installed for light use
in under an hour from source by a first time user, or far less time than
that if you use RPM's etc. Tuning it for heavy load can take a little
more time, but that is life with a read DB (Oracle is not so different
in this regard.
Post by tim panton
Once you have it there are _stacks_ of neat features and a really
solid performance.
I am especialy fond of the ability to put java into triggers (we send
SNMP traps to
ops console when specific error conditions occur on inserts) and the
whole oracle
Text and XML integration has saved me _months_ of development time on
various project.
Ditto with PostgreSQL. Except that usually triggers are written in
PLPGSQL, Perl, or C. I don't know whether PHP, Python, or Java support
triggers yet but I am sure that they will soon :-)

Anyway..... People's main gripes regarding PostgreSQL tend to come down to:

1) Slow performance under heavy load. Historically (prior to 7.4),
PostgreSQL installed with very conservative memory settings, allowing it
to run on pretty much any system made after the 1970's (ok, that is an
overstatement but you get the idea). It required tuning to get good
performance under heavy concurrent use. Nowadays, it does a better job
of autodetecting settings, but if you need a *lot* of concurrent
connections, you will still want to do some tuning.

2) Historically, the alter table command was a bear. Nowadays it is
better.

3) PostgreSQL doesn't ship with a GUI interface, so go get phppgadmin,
Pgaccess, or Pgadmin III.

The selling points are:

1) Extremely good performance under load (+/- 10% compared to
commercial RDBMS's when properly tuned)
2) Extensible data types
3) User defined functions in a wide variety of languages including
Java, TCL, Perl, Bash(!), Python, PHP, and more.
4) Extreme care taken on making sure your data is consistant and
meaningful. Compared to MySQL which does not take so many precautions,
PostgreSQL is the way to go. As an aside, PostgreSQL and Oracle differ
in whether they consider empty strings to be synonymous with NULL
values. Oracle says Yes, while PostgreSQL (and iirc the ANSI standard)
says No, but this is a minor point which is usually academic.

But whatever you do, don't trust your accounting information to MySQL.

Best Wishes,
Chris Travers
Metatron Technology Consulting
Giudice, Salvatore
2005-03-15 18:00:54 UTC
Permalink
MySQL: Speed, Power and Precision
_________________________________

MySQL is free. It can be installed in less than 59 minutes from source
for light use by a first time user AND there is no need for extravagant
tuning. Out of the box, it comes with 4 database engines: Isam, MyIsam,
Heap, InnoDB, and BDB - and if you are particularly keen on undertaking
elaborate tuning projects to squeeze every last drop of life from a
database, you can even write your own database engine for MySQL.

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.

For benchmark information comparing MySQl with several DB's on various
OS's (yes Oracle and PostgreSQL are included) see the following link:

http://ftp.iranscience.net/pub/databases/mysql/information/benchmarks.ht
ml





-----Original Message-----
From: Chris Travers [mailto:***@verkiel.metatrontech.com]
Sent: Sunday, March 13, 2005 2:27 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] OT: Best DB
Post by tim panton
At the risk of sounding like a closed source fan (I'm not) I do think
you should
at least consider Oracle for this job.
I built a system a few years ago which takes a constant stream of
entries from a number (<100)
of remote systems analizes them and generates reports
(see http://www.westpoint.ltd.uk/example-reports/reports/index.htm)
We use Oracle for it, and it has been great. Also they have improved
1) pricing - It is now _much_ cheaper than it was
2) Install - I had a couple of oracle newbies install it in a
couple of hours, that was never possible
in the old days.
On the other hand, PostgreSQL is Free and can be installed for light use

in under an hour from source by a first time user, or far less time than

that if you use RPM's etc. Tuning it for heavy load can take a little
more time, but that is life with a read DB (Oracle is not so different
in this regard.
Post by tim panton
Once you have it there are _stacks_ of neat features and a really
solid performance.
I am especialy fond of the ability to put java into triggers (we send
SNMP traps to
ops console when specific error conditions occur on inserts) and the
whole oracle
Text and XML integration has saved me _months_ of development time on
various project.
Ditto with PostgreSQL. Except that usually triggers are written in
PLPGSQL, Perl, or C. I don't know whether PHP, Python, or Java support

triggers yet but I am sure that they will soon :-)

Anyway..... People's main gripes regarding PostgreSQL tend to come down
to:

1) Slow performance under heavy load. Historically (prior to 7.4),
PostgreSQL installed with very conservative memory settings, allowing it

to run on pretty much any system made after the 1970's (ok, that is an
overstatement but you get the idea). It required tuning to get good
performance under heavy concurrent use. Nowadays, it does a better job
of autodetecting settings, but if you need a *lot* of concurrent
connections, you will still want to do some tuning.

2) Historically, the alter table command was a bear. Nowadays it is
better.

3) PostgreSQL doesn't ship with a GUI interface, so go get phppgadmin,
Pgaccess, or Pgadmin III.

The selling points are:

1) Extremely good performance under load (+/- 10% compared to
commercial RDBMS's when properly tuned)
2) Extensible data types
3) User defined functions in a wide variety of languages including
Java, TCL, Perl, Bash(!), Python, PHP, and more.
4) Extreme care taken on making sure your data is consistant and
meaningful. Compared to MySQL which does not take so many precautions,
PostgreSQL is the way to go. As an aside, PostgreSQL and Oracle differ
in whether they consider empty strings to be synonymous with NULL
values. Oracle says Yes, while PostgreSQL (and iirc the ANSI standard)
says No, but this is a minor point which is usually academic.

But whatever you do, don't trust your accounting information to MySQL.

Best Wishes,
Chris Travers
Metatron Technology Consulting
Andrew Kohlsmith
2005-03-15 18:40:05 UTC
Permalink
Post by Giudice, Salvatore
MySQL: Speed, Power and Precision
Now *that* is funny. Thank you for bringing some humour to the list. Now
take the rest of this email and file it under FUD and exaggeration on MySQL's
capabilities, especially the benchmarks.

-A.
Chris Travers
2005-03-15 22:57:29 UTC
Permalink
Post by Giudice, Salvatore
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.
Post by Giudice, Salvatore
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
Zanzamar Majere
2005-03-15 23:05:48 UTC
Permalink
Is anyone else having issues pulling up voip-info.org?
Bruno Hertz
2005-03-15 23:21:47 UTC
Permalink
Post by Zanzamar Majere
Is anyone else having issues pulling up voip-info.org?
There's been a 'wiki down' thread running all day on this list. So it's
been noticed, yes.

Regards, Bruno.
Jason Stewart
2005-03-16 13:37:33 UTC
Permalink
Post by Giudice, Salvatore
MySQL: Speed, Power and Precision
_________________________________
Speed, yes. Anyone can write an SQL layer over a flat file and make it
fast. If you want real speed (faster than MySQL with the same level of
reliability choose SQLite.

Power - I agree here too. There are lots of great tools for MySQL due to
it's ubiquity.

Precision - No Way! see-
http://sql-info.de/mysql/gotchas.html
Post by Giudice, Salvatore
MySQL is free. It can be installed in less than 59 minutes from source
for light use by a first time user AND there is no need for extravagant
tuning.
and if you are particularly keen on undertaking
elaborate tuning projects to squeeze every last drop of life from a
database, you can even write your own database engine for MySQL.
So a beginner user can install MySQL in less than an hour from source
with no need for tuning, but if they feel the need to tune their
database other than what's out of the box a newbie can write their own
database engine? I'd much rather mess with a few config options that
write a database engine.

For the record PgSQL can be installed in the same amount of time as
MySQL. For the extreme noob who knows nothing about databases and is
still learning then tuning will not be a factor. For anyone else the
first thing that they'll do is look at the manual for the tuning
section. It's not rocket science.
Post by Giudice, Salvatore
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 also get enterprise level support through Pervasive, a company
much larger and older than MySQL AB.

http://crn.com/sections/breakingnews/breakingnews.jhtml?articleId=57700307
Post by Giudice, Salvatore
For benchmark information comparing MySQl with several DB's on various
http://ftp.iranscience.net/pub/databases/mysql/information/benchmarks.ht
ml
Hmm... More benchmarks, eh? I've see benchmarks swing both ways with
MySQL being faster and others with PGSQL being faster. In my experience
Postgres has handled our multi-gigabyte database much more smoothly than
MySQL. Larger, complex queries seem to return much more quickly with
Postgres.

My mantra is "pick the right tool for the job". For smaller webapps I
use MySQL. For huge enterprise databases I use PostgreSQL.


Regards,
--
Jason Stewart | Tel: 616-532-2300
Systems Administrator/ | Fax: 616-532-3461
Programmer | Email: ***@rtl.org
Right to Life of Michigan | Web: http://www.rtl.org
Giudice, Salvatore
2005-03-15 19:21:21 UTC
Permalink
Sticks and stone still break my bones, but PostgreSQL is still a dog.




Market share:


According to CD Times magazine dated July 1, 2004

Top Deployed Databases poll shows following databases in use:

SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL - 8%.












-----Original Message-----
From: Andrew Kohlsmith [mailto:akohlsmith-***@benshaw.com]
Sent: Tuesday, March 15, 2005 1:40 PM
To: asterisk-***@lists.digium.com
Subject: Re: [Asterisk-Users] OT: Best DB
Post by Giudice, Salvatore
MySQL: Speed, Power and Precision
Now *that* is funny. Thank you for bringing some humour to the list.
Now

take the rest of this email and file it under FUD and exaggeration on
MySQL's

capabilities, especially the benchmarks.



-A.

_______________________________________________

Asterisk-Users mailing list

Asterisk-***@lists.digium.com

http://lists.digium.com/mailman/listinfo/asterisk-users

To UNSUBSCRIBE or update options visit:

http://lists.digium.com/mailman/listinfo/asterisk-users
Chris Wade
2005-03-15 19:43:48 UTC
Permalink
Post by Giudice, Salvatore
Sticks and stone still break my bones, but PostgreSQL is still a dog.
Enough, take it off list, PLEASE!
Andrew Kohlsmith
2005-03-15 19:47:26 UTC
Permalink
Post by Giudice, Salvatore
Sticks and stone still break my bones, but PostgreSQL is still a dog.
Until you actually show some benchmarks where the tests are clearly documented
and Postgres is properly tuned, you're spreading FUD. Your testing should
also demonstrate real world performance (hundreds of connections, complex
queries, etc.) or it's just marketing fluff, which is exactly what your
links, including this one on market share are. It's been stated time and
time again that Postgres' default values are *very* conservative.

There's a reason that most people who actually try Postgres after years of
using MySQL continue using Postgres, and it isn't because Postgres is a dog,
as you state.

-A.
Robert Goodyear
2005-03-15 19:49:22 UTC
Permalink
Post by Giudice, Salvatore
Sticks and stone still break my bones, but PostgreSQL is still a dog.
 
According to CD Times magazine dated July 1, 2004
SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL - 8%.
Devil's advocate here: what does deployment quantity have to do with
stability, performance or otherwise?

I could start a pretty big flame war if I tried to compare Windows 95
with MacOS X by deployment stats instead of stability.
Steven Critchfield
2005-03-15 20:47:12 UTC
Permalink
Post by Giudice, Salvatore
Sticks and stone still break my bones, but PostgreSQL is still a dog.
According to CD Times magazine dated July 1, 2004
SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL - 8%.
I see they created this with Mysql,
78 + 55 + 44 + 8 = 185%
I'm sure if you add in the others we would get to something around 300%
deployment.
--
Steven Critchfield <***@basesys.com>
David Brodbeck
2005-03-15 20:10:30 UTC
Permalink
I could start a pretty big flame war if I tried to compare Windows 95 with
MacOS X by deployment stats instead of stability.
[David Brodbeck] I've seen Mac OS X locked up solid just by putting in a
damaged CD-R disc. It's a nice OS, mind you, but it's not as stable as some
people would lead you to believe.
David Brodbeck
2005-03-15 20:51:37 UTC
Permalink
-----Original Message-----
Post by Giudice, Salvatore
SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL - 8%.
I see they created this with Mysql,
78 + 55 + 44 + 8 = 185%
I'm sure if you add in the others we would get to something
around 300%
deployment.
Presumably some sites had more than one type of database in use.
Steve Wolfe
2005-03-15 22:47:49 UTC
Permalink
Comment:
"Best" sometimes gets fuzzy.

MySQL's "mind-share" is frightening.

Because of the mind-share/marketing I see MySQL being deployed where
perhaps PostgreSQL should be and Oracle is considered too expensive.
(avoiding MS SQL server. :) )

Also probably due to the 'mind-share' documenation is more accessable
for MySQL instead of PostgreSQL. I find this sad, but it has not really
changed much over the years. Indeed, it might be getting worse.

And, I have found it easier to compile PostgreSQL than MySQL .. but
easier to find performance tweeks for MySQL. In the scheme of things,
the performance tweeks are probably more imporant.

=Steve
Giudice, Salvatore
2005-03-15 23:04:22 UTC
Permalink
Maybe, it has absolutely nothing to do with performance or stability.
Maybe, it has something with 'ease of implementation', 'ease of use',
'availability of commercial support', and which database vendors
ultimately decide to support in their products. Obviously, Microsoft has
a lot of vendors pushing SQL server integration with their products.
Oracle has pretty good penetration with vendors also. Now if you were a
vendor and you going to integrate with open source database - Would you
choose MySQL, which is available under GPL with the possibility
commercial licensing AND has a real enterprise class support structure
behind it, or are you going to run with PostgreSQL (bow wow) distributed
under a BSD license with some mom and pop support shops and some mailing
lists? Well, I would say that vendors and enterprise customers are
speaking loud and clear when they are choosing MySQL 4 to 1 over
PostgreSQL.



Hey, it's your choice. Do you want to eat American Grade A American beef
or that strange meat flavored tofu? As long as it meets your needs,
choose whatever you have the ability to handle.





_____

From: Robert Goodyear [mailto:***@jrob.net]
Sent: Tuesday, March 15, 2005 2:49 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] OT: Best DB





On Mar 15, 2005, at 11:21 AM, Giudice, Salvatore wrote:



Sticks and stone still break my bones, but PostgreSQL is still a dog.







Market share:



According to CD Times magazine dated July 1, 2004



Top Deployed Databases poll shows following databases in use:



SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL - 8%.



Devil's advocate here: what does deployment quantity have to do with
stability, performance or otherwise?



I could start a pretty big flame war if I tried to compare Windows 95
with MacOS X by deployment stats instead of stability.
Paul
2005-03-16 00:01:38 UTC
Permalink
Let's get back to the highly valid point that Robert made. He is
absolutely right. Good parents teach their children to do the right
thing regardless of what the majority of children are doing these days.

Consider 2 approaches to dealing with a compromised PC. I think the
majority of the so-called "experts" will run a few security-related
programs and really beleive that it is safe to use the computer again.
My approach is always to write zeroes to the MBR and directory areas and
then do a new OS install. I always do that and any patches/updates from
behind a hardware firewall. That puts me in a small minority but that
minority is right and the majority is wrong.

If postgreSQL is a dog, it will be winning first place at all the dog
shows. MySQL(or should I say YourSQL?) is a rodent.
Post by Giudice, Salvatore
Maybe, it has absolutely nothing to do with performance or stability.
Maybe, it has something with ‘ease of implementation’, ‘ease of use’,
‘availability of commercial support’, and which database vendors
ultimately decide to support in their products. Obviously, Microsoft
has a lot of vendors pushing SQL server integration with their
products. Oracle has pretty good penetration with vendors also. Now if
you were a vendor and you going to integrate with open source database
– Would you choose MySQL, which is available under GPL with the
possibility commercial licensing AND has a real enterprise class
support structure behind it, or are you going to run with PostgreSQL
(bow wow) distributed under a BSD license with some mom and pop
support shops and some mailing lists? Well, I would say that vendors
and enterprise customers are speaking loud and clear when they are
choosing MySQL 4 to 1 over PostgreSQL.
Hey, it’s your choice. Do you want to eat American Grade A American
beef or that strange meat flavored tofu? As long as it meets your
needs, choose whatever you have the ability to handle.
------------------------------------------------------------------------
*Sent:* Tuesday, March 15, 2005 2:49 PM
*To:* Asterisk Users Mailing List - Non-Commercial Discussion
*Subject:* Re: [Asterisk-Users] OT: Best DB
Sticks and stone still break my bones, but PostgreSQL is still a dog.
*Market share:*
According to CD Times magazine dated July 1, 2004
SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL - 8%.
Devil's advocate here: what does deployment quantity have to do with
stability, performance or otherwise?
I could start a pretty big flame war if I tried to compare Windows 95
with MacOS X by deployment stats instead of stability.
------------------------------------------------------------------------
Andrew Kohlsmith
2005-03-16 11:43:31 UTC
Permalink
Post by Giudice, Salvatore
commercial licensing AND has a real enterprise class support structure
behind it, or are you going to run with PostgreSQL (bow wow) distributed
under a BSD license with some mom and pop support shops and some mailing
It's time to put up or shut up.

Can you please give supporting evidence that MySQL AG has no more "oomph" in
commercial support than companies like Command Prompt, Fujitsu, Red Hat, or
even PostgreSQL, Inc.? Every single one of those organizations has
commercial support available for PostgreSQL. I'm genuinely curious if you
consider MySQL AG "more of a company" than Red Hat or Fujitsu.

Seriously. You're frothing at the mouth and tripping over yourself trying to
make your point, and you're so far off base to begin with that you couldn't
possibly be more wrong.

As far as your benchmark points go, until you can show me properly organized
and open benchmarks, your point is totally invalid. In my cursory check
(hint: try locating the open database bake-off from a couple years ago,
phpbuilder's evaluation a few years back, http://benchw.sourceforge.net, or
locate anything done by independent testing groups) it appears that under
real-world load, Postgres trounces MySQL handily and can handle FAR more
concurrent connections than even a tuned-out MySQL server can handle. Yes,
Postgres needs some tuning out of the box, this has been hashed over
repeatedly and nobody's denying it. Yes, MySQL is fast for the simplest
queries and inserts. And my personal favourite, Yes, MySQL will take
artistic license with your data. These are all facts that everyone (MySQL AG
included) but you seems to be able to agree upon. The only benchmarks you'll
speak of are those found with mysql-bench, but those results are generally
held as a practical joke with zero relevance in real-world applications.

Your comment on licensing is also interesting. I wonder, do you also have
problems with Apache because it too is released under a BSD license? How
about the BSD Unixes themselves? How is BSD less good than GPL? Honestly
I'd love to know!
Post by Giudice, Salvatore
Hey, it's your choice. Do you want to eat American Grade A American beef
or that strange meat flavored tofu? As long as it meets your needs,
choose whatever you have the ability to handle.
Exactly my point. This is *exactly* why I run PostgreSQL over MySQL.

At any rate I've participated in this offtopic thread enough. Unless you post
some practical examples to back up your points I will let you have the last
word. The list archives will no doubt commemorate this particular
thread. :-)

-A.
Giudice, Salvatore
2005-03-15 23:52:00 UTC
Permalink
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:***@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
Post by Giudice, Salvatore
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.
Post by Giudice, Salvatore
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
Robert Hajime Lanning
2005-03-16 00:34:06 UTC
Permalink
<quote who="Giudice, Salvatore">
Post by 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.
Data validation should be done at all levels. Period.
--
END OF LINE
-MCP
Jon Gabrielson
2005-03-16 01:04:28 UTC
Permalink
Post by Robert Hajime Lanning
<quote who="Giudice, Salvatore">
Post by 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.
Data validation should be done at all levels. Period.
Validating the SAME data at each level greatly decreases your speed.
It is much simpler and easier to just validate it first.


Jon.
Steven Critchfield
2005-03-16 02:31:06 UTC
Permalink
Post by Jon Gabrielson
Post by Robert Hajime Lanning
<quote who="Giudice, Salvatore">
Post by 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.
Data validation should be done at all levels. Period.
Validating the SAME data at each level greatly decreases your speed.
It is much simpler and easier to just validate it first.
Of course most of us want to follow DRY (Don't repeat yourself). In
doing so, you try and let one place be an authoritative source. The DB
should be authoritative as to what is correct.

You shouldn't have to babysit the DB to make sure it is doing the
correct thing. If you have to babysit it, it isn't worth it.
--
Steven Critchfield <***@basesys.com>
Mohit Muthanna
2005-03-16 13:23:52 UTC
Permalink
Post by Jon Gabrielson
Post by Robert Hajime Lanning
Data validation should be done at all levels. Period.
Validating the SAME data at each level greatly decreases your speed.
True, but at the expense of data reliability and security. If one
validation layer is compromised (buffer overflow, packet injection, or
even a bad link between client and server), the other will catch it.
See my previous post.

Infact, many coding standards and certifications call for strict
validation at all levels.

Never _ever_ sacrifice security for performance. Big mistake.
Post by Jon Gabrielson
It is much simpler and easier to just validate it first.
Disagree. If you were to validate it only in one layer, it would have
to be last (i.e., closest to the server). Think of a website doing
javascript validation of credit card information. One can easily
override the validation my simply modifying the HTTP requests (or
maybe even disabling javascript).

Anyhow, this is getting way off topic. A thousand apologies.
--
Mohit Muthanna [mohit (at) muthanna (uhuh) com]
"There are 10 types of people. Those who understand binary, and those
who don't."
Mohit Muthanna
2005-03-16 00:34:18 UTC
Permalink
On Tue, 15 Mar 2005 18:52:00 -0500, Giudice, Salvatore
Post by 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
That is the silliest thing I've heard yet. I'm hardly a database
designer and even I know that there are many reasons for "server-side"
validation.

Say you have different client interfaces (command-line, web, RPC etc.)
for your app; in this case, why implement and maintain validation
logic in the clients. Sure, you can use a three-tier architecture and
have the middleware do the validation; but this is many-times not
practical.

What if your client app (or middleware) has bugs? A simple
buffer-overflow attack on a client or middleware piece of code can
potentially render it's validation moot. Server-side validation can
atleast add another layer of security to catch errors in higher
layers. One could then decide (wieghing the tradeoffs), how much (or
how little) client side validation to add.

What about data / referential integrety? You expect the application to
take care of all that?

It's probably not a good idea to lecture someone on database design
practices when you have a few lessons to learn yourself.
Post by Giudice, Salvatore
I agree that postgreSQL and MySQL have different feature sets. Your
application design may drive your selection based on feature
requirements.
And the general consensus here is that MySQL is _not_ the ideal
solution for heavily loaded applications or large datasets.
Post by Giudice, Salvatore
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
Right... 700k/hr inserts of "security events" from your IDS. Parsing
on a Beowolf cluster? 8-way cpu servers. Sounds like fun.

Mohit.
--
Mohit Muthanna [mohit (at) muthanna (uhuh) com]
"There are 10 types of people. Those who understand binary, and those
who don't."
tim panton
2005-03-16 08:55:22 UTC
Permalink
Post by Giudice, Salvatore
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
y
That's more than 1% downtime, not even two nines .
What's your downtime worth per day?


http://www.westhawk.co.uk/
Chris Travers
2005-03-17 19:30:28 UTC
Permalink
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....
Post by 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?
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.
Post by Giudice, Salvatore
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.
Post by Giudice, Salvatore
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.
Post by Giudice, Salvatore
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
David Brodbeck
2005-03-16 14:56:20 UTC
Permalink
This Postgres vs. MySQL business is ultimately just a religious debate, like
PC vs. Mac, Ford vs. Chevy, or Kirk vs. Picard. They both work; they both
have their plusses and minuses; and debates about which are better never
convince anyone to change their preconceived ideas. It's also about as
on-topic for this list as any of the other subjects I just mentioned.
Chris Travers
2005-03-17 19:44:06 UTC
Permalink
Post by David Brodbeck
This Postgres vs. MySQL business is ultimately just a religious debate, like
PC vs. Mac, Ford vs. Chevy, or Kirk vs. Picard.
With all due respect I disagree. It is much more like a public policy
debate. There are those of us in any of the Oracle, DB2, or PostgreSQL
camps who feel like the RDBMS should be responsible for maintaining data
integrity, because we value our data. The fact that I don't have a use
for MySQL does not withstand everything else I say here.

FWIW, I don't consider MySQL to be an RDBMS. I consider it to be a
RDBSS (Relational Database Storage System). It does not adequately
provide features to Manage (i.e. the M in RDBMS) or Present your data to
make it worthwhile where one needs an RDBMS. That being said, it works
well for certain types of applications which don't need these features
nor worry as much about data integrity as an accountant might.

This is why, in the end, what you want to do with your data will
determine your choice. However, of the Free DB's, PostgreSQL will give
you the most growth potential, while MySQL may give you the most
ready-to-use-apps. This last comment is entirely on-topic because it
asks back:
What are your ultimate plans for the data? Only then can you make a
reasonable choice.

Best Wishes,
Chris Travers
Giudice, Salvatore
2005-03-16 16:29:30 UTC
Permalink
Use whichever you want. Go get your own benchmarks. I'm sure you will
find benchmarks all over the web based on different conditions. The fact
remains that enterprises are deploying MySQL 4:1 over postergreSQL. I
believe the driving factors for this are the ability to commercially
license Mysql for product integration over PostgreSQL's BSD license, and
the availability of support from MySQL directly. With regard to Redhat,
Fujitsu, etc - MySQL database support is not their main line of
business. If you believe different, then let's hear it.

As for your 'artist license with your data' comment, put it into some
context. I would blame a programmer for trying to insert a string of 255
characters into a field only 100 character wide. Maybe you could blame
the dba for not building a schema to support the application.
Regardless, I would not call the database deficient because it truncates
your data to 100 characters and doesn't warn you with an error. Get
real. It is not as if this behavior is unexpected or some sort of a
surprise.

Run whichever DB you want. It's your choice, as always. You are
certainly free to sit in your office frothing all over yourself in your
own twisted PostgreSQL fantasy.


-----Original Message-----
From: Andrew Kohlsmith [mailto:akohlsmith-***@benshaw.com]
Sent: Wednesday, March 16, 2005 6:44 AM
To: asterisk-***@lists.digium.com
Subject: Re: [Asterisk-Users] OT: Best DB
Post by Giudice, Salvatore
commercial licensing AND has a real enterprise class support structure
behind it, or are you going to run with PostgreSQL (bow wow)
distributed
Post by Giudice, Salvatore
under a BSD license with some mom and pop support shops and some
mailing

It's time to put up or shut up.

Can you please give supporting evidence that MySQL AG has no more
"oomph" in
commercial support than companies like Command Prompt, Fujitsu, Red Hat,
or
even PostgreSQL, Inc.? Every single one of those organizations has
commercial support available for PostgreSQL. I'm genuinely curious if
you
consider MySQL AG "more of a company" than Red Hat or Fujitsu.

Seriously. You're frothing at the mouth and tripping over yourself
trying to
make your point, and you're so far off base to begin with that you
couldn't
possibly be more wrong.

As far as your benchmark points go, until you can show me properly
organized
and open benchmarks, your point is totally invalid. In my cursory check

(hint: try locating the open database bake-off from a couple years ago,
phpbuilder's evaluation a few years back, http://benchw.sourceforge.net,
or
locate anything done by independent testing groups) it appears that
under
real-world load, Postgres trounces MySQL handily and can handle FAR more

concurrent connections than even a tuned-out MySQL server can handle.
Yes,
Postgres needs some tuning out of the box, this has been hashed over
repeatedly and nobody's denying it. Yes, MySQL is fast for the simplest

queries and inserts. And my personal favourite, Yes, MySQL will take
artistic license with your data. These are all facts that everyone
(MySQL AG
included) but you seems to be able to agree upon. The only benchmarks
you'll
speak of are those found with mysql-bench, but those results are
generally
held as a practical joke with zero relevance in real-world applications.

Your comment on licensing is also interesting. I wonder, do you also
have
problems with Apache because it too is released under a BSD license?
How
about the BSD Unixes themselves? How is BSD less good than GPL?
Honestly
I'd love to know!
Post by Giudice, Salvatore
Hey, it's your choice. Do you want to eat American Grade A American
beef
Post by Giudice, Salvatore
or that strange meat flavored tofu? As long as it meets your needs,
choose whatever you have the ability to handle.
Exactly my point. This is *exactly* why I run PostgreSQL over MySQL.

At any rate I've participated in this offtopic thread enough. Unless
you post
some practical examples to back up your points I will let you have the
last
word. The list archives will no doubt commemorate this particular
thread. :-)

-A.
_______________________________________________
Asterisk-Users mailing list
Asterisk-***@lists.digium.com
http://lists.digium.com/mailman/listinfo/asterisk-users
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
Tom Ivar Helbekkmo
2005-03-16 20:50:52 UTC
Permalink
Post by Giudice, Salvatore
Regardless, I would not call the database deficient because it
truncates your data to 100 characters and doesn't warn you with an
error. Get real. It is not as if this behavior is unexpected or some
sort of a surprise.
Quoting the SQL/92 standard:

If a store assignment would result in the loss of non-<space>
characters due to truncation, then an exception condition is raised.
Post by Giudice, Salvatore
Run whichever DB you want. It's your choice, as always. You are
certainly free to sit in your office frothing all over yourself in
your own twisted PostgreSQL fantasy.
I don't see anyone but you frothing at the mouth in this discussion. :-)

-tih
--
Don't ascribe to stupidity what can be adequately explained by ignorance.
Linterra
2005-03-16 23:11:38 UTC
Permalink
My apologies to the rest of the readers for the flame, but Mr
Salvatore, you are sadly misinformed.

I like MySQL as well as PostgreSQL and they both have their merits,
but it's annoying to see someone give a recommendation of one over the
other based on ignorance instead of relevant facts.

If you're developing an application which you would eventually like to
redistribute and you want to be able to redistribute the database
without any licensing fees, the BSD license will allow you to do so,
but the MySQL license does not. Anyone prefering the GPL license over
the BSD, MPL or Apache licenses is either ignorant, stupid,
financially independent or a GPL zealot.

As far as technical support goes, several large reliable companies
provide paid support for PostgreSQL if you feel the need to pay for
support. For example, Pervasive (absolutely not a "mom and pop shop")
provides paid technical support for PostgreSQL. I've used their
products and services for many years and they are extremely reliable,
responsive and not too expensive. But, you can get just as good
support directly from the developers making paid support irrelevant
until you hit upon a question which cannot be solved by the developers
(unlikely but possible). Command Prompt, Inc. also provides 24/7/365
support and there are many others listed on the PostgreSQL web site.

MySQL and PostgreSQL will both probably get the job done. MySQL is
slightly faster under simpler conditions. PostgreSQL will outperform
MySQL on larger more complicated queries.

For example, on the same computer running both databases, the
following query took over six minutes to complete on MySQL and less
than three seconds on PostgreSQL. Both databases had the same indexes
defined and contained exactly the same data.

select *
from Listing, InHouseListing, Trans, TransactionDetail, DepositItem,
DepositDetail, chart
where Listing.listingId = InHouseListing.listingId
and InHouseListing.listingTransId = Trans.transId
and InHouseListing.closed = 1
and TransactionDetail.transId = Trans.transId
and TransactionDetail.transactionItemType = 6
and TransactionDetail.transactionCategoryId = 1
and DepositItem.listingId = Listing.listingId
and DepositItem.depositItemId = DepositDetail.depositItemId
and DepositDetail.debit = TransactionDetail.actualAmount
and chart.id = DepositDetail.chartId

Also, the following query on MySQL (not my example but another one
that I researched)

SELECT DISTINCT d.ID, sqt.policy_reference, sr.*
FROM d, dsr, sr, sqt
WHERE (d.ID = dsr.dbramm_id) AND (dsr.scs_risk_list_id = sr.ID) AND
(sr.scs_base_risk_question_number = sqt.qindex)
AND (d.ID IN(SELECT DISTINCT d2.ID FROM DBRAMM AS d2 WHERE
d2.real_dbramm_id = 216)))
----------------------------------------------------------------------
[requires minutes]

compared to the identical query without subselect:

----------------------------------------------------------------------
SELECT DISTINCT d.ID, sqt.policy_reference, sr.*
FROM d, dsr, sr, sqt
WHERE (d.ID = dsr.dbramm_id) AND (dsr.scs_risk_list_id = sr.ID) AND
(sr.scs_base_risk_question_number = sqt.qindex)
AND (d.ID IN(1,34,15,36,324,...some more))
----------------------------------------------------------------------
[requires 8 seconds]

Whereas the same data on PostgreSQL took < 6 seconds in both cases.

Also, if you need triggers written in more than one scripting
language, custom data types, custom indexes, references to external
data and many many more features, you won't find them in MySQL.

Simple is better until you need complex, so my bottom line
recommendation is this: Write your code in a database independent way
and use MySQL until you outgrow it (which may never happen), then
switch to PostgreSQL.
David Brodbeck
2005-03-16 16:45:19 UTC
Permalink
-----Original Message-----
As for your 'artist license with your data' comment, put it into some
context. I would blame a programmer for trying to insert a
string of 255
characters into a field only 100 character wide. Maybe you could blame
the dba for not building a schema to support the application.
Regardless, I would not call the database deficient because
it truncates
your data to 100 characters and doesn't warn you with an error.
And the sad fact is, if the software isn't doing any data verification, it's
probably not doing error checking either. So if the DB throws an error,
your database will be protected, but the application will probably crash or
do something undefined. Which of those situations (truncated data, or a
crashed app) is better depends on the application. It's not clear cut.
Joe Greco
2005-03-16 16:47:27 UTC
Permalink
I believe the driving factors for this are the ability to commercially
license Mysql for product integration over PostgreSQL's BSD license,
This is a ridiculous FUD statement. Are you actually trying to suggest that
one cannot commercially license PostgreSQL?

That's simply FALSE.

The primary difference is that you are likely to have to *pay* for a
commercial MySQL license, and you don't need to *pay* for one for
PostgreSQL.

So let's not be completely stupid. You can pay for your database if you
prefer. Some of us prefer free software.

... JG
--
Joe Greco - sol.net Network Services - Milwaukee, WI - http://www.sol.net
"We call it the 'one bite at the apple' rule. Give me one chance [and] then I
won't contact you again." - Direct Marketing Ass'n position on e-mail spam(CNN)
With 24 million small businesses in the US alone, that's way too many apples.
Jon Gabrielson
2005-03-16 16:58:47 UTC
Permalink
Ok, we all get it, some people prefer mysql, some people prefer postgres.

Now can we all just get on with our life or at least create a mailing list:

asterisk-***@lists.digium.com

so that those people who think that mustard tastes better than ketchup have
somewhere more appropriate to argue.



Thanks,


Jon.
Giudice, Salvatore
2005-03-16 21:48:14 UTC
Permalink
Then go post your findings to the mysql bug lists as a noncompliance
issue or contribute a patch so you can get your error message. The fact
remains that you do not have to have your data truncated nor ever see an
error message, if your fields are large enough to contain the data you
will insert into them. So, while you are waiting for your fix, put some
validation in your app or create a bigger field in your db -> then go
froth yourself.

-----Original Message-----
From: Tom Ivar Helbekkmo [mailto:***@eunetnorge.no]
Sent: Wednesday, March 16, 2005 3:51 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: [Asterisk-Users] Re: OT: Best DB
Post by Giudice, Salvatore
Regardless, I would not call the database deficient because it
truncates your data to 100 characters and doesn't warn you with an
error. Get real. It is not as if this behavior is unexpected or some
sort of a surprise.
Quoting the SQL/92 standard:

If a store assignment would result in the loss of non-<space>
characters due to truncation, then an exception condition is raised.
Post by Giudice, Salvatore
Run whichever DB you want. It's your choice, as always. You are
certainly free to sit in your office frothing all over yourself in
your own twisted PostgreSQL fantasy.
I don't see anyone but you frothing at the mouth in this discussion.
:-)

-tih
--
Don't ascribe to stupidity what can be adequately explained by
ignorance.
_______________________________________________
Asterisk-Users mailing list
Asterisk-***@lists.digium.com
http://lists.digium.com/mailman/listinfo/asterisk-users
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
snacktime
2005-03-16 22:02:59 UTC
Permalink
On Wed, 16 Mar 2005 16:48:14 -0500, Giudice, Salvatore
Post by Giudice, Salvatore
Then go post your findings to the mysql bug lists as a noncompliance
issue or contribute a patch so you can get your error message. The fact
remains that you do not have to have your data truncated nor ever see an
error message, if your fields are large enough to contain the data you
will insert into them. So, while you are waiting for your fix, put some
validation in your app or create a bigger field in your db -> then go
froth yourself.
I'm starting to understand why his project was terminated:)
Chris Albertson
2005-03-18 00:48:24 UTC
Permalink
What is the best truck? A recent survey finds that
there are far more Ford Rangr pickup trucks on the road
then there are Frightliner 18 wheelers

In another survey we find that Chevy outnumbers Porche.

Closer to home in the computer world, more people use
MS Windows than Solaris.

I think Budwieser outsells every other beer.

In most organizations followers outnumber the leaders

The poor will always outnumber the rich.

Still interrested in that database poll?

What's the "best" DB. First you must define "best".
After you do that the answer is easy.
Post by David Brodbeck
-----Original Message-----
Post by Giudice, Salvatore
SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL -
8%.
I see they created this with Mysql,
78 + 55 + 44 + 8 = 185%
I'm sure if you add in the others we would get to something
around 300%
deployment.
Presumably some sites had more than one type of database in use.
_______________________________________________
Asterisk-Users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users
http://lists.digium.com/mailman/listinfo/asterisk-users
Chris Albertson
Home: 310-376-1029 ***@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189 ***@aero.org
KG6OMK



__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/
Mike Sander
2005-03-19 00:08:05 UTC
Permalink
But Budwieser tastes like water to most Australian beer drinkers.


(Now I'm in trouble!)

Mike

----- Original Message -----
From: "Chris Albertson" <***@yahoo.com>
To: "Asterisk Users Mailing List - Non-Commercial Discussion"
<asterisk-***@lists.digium.com>
Sent: Friday, March 18, 2005 11:48 AM
Subject: RE: [Asterisk-Users] OT: Best DB
Post by Chris Albertson
What is the best truck? A recent survey finds that
there are far more Ford Rangr pickup trucks on the road
then there are Frightliner 18 wheelers
In another survey we find that Chevy outnumbers Porche.
Closer to home in the computer world, more people use
MS Windows than Solaris.
I think Budwieser outsells every other beer.
In most organizations followers outnumber the leaders
The poor will always outnumber the rich.
Still interrested in that database poll?
What's the "best" DB. First you must define "best".
After you do that the answer is easy.
Post by David Brodbeck
-----Original Message-----
Post by Giudice, Salvatore
SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL -
8%.
I see they created this with Mysql,
78 + 55 + 44 + 8 = 185%
I'm sure if you add in the others we would get to something
around 300%
deployment.
Presumably some sites had more than one type of database in use.
_______________________________________________
Asterisk-Users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users
http://lists.digium.com/mailman/listinfo/asterisk-users
Chris Albertson
Cell: 310-990-7550
KG6OMK
__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/
_______________________________________________
Asterisk-Users mailing list
http://lists.digium.com/mailman/listinfo/asterisk-users
http://lists.digium.com/mailman/listinfo/asterisk-users
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 15/03/2005
Andrew Kohlsmith
2005-03-19 00:20:25 UTC
Permalink
Post by Mike Sander
But Budwieser tastes like water to most Australian beer drinkers.
No, it tastes like piss to pretty much everyone. They just have a great
marketing budget.

-A.

Continue reading on narkive:
Loading...