Re: [Asterisk-Users] OT: Best DB
But Budwieser tastes like water to most Australian beer drinkers. (Now I'm in trouble!) Mike - Original Message - From: Chris Albertson [EMAIL PROTECTED] To: Asterisk Users Mailing List - Non-Commercial Discussion asterisk-users@lists.digium.com Sent: Friday, March 18, 2005 11:48 AM Subject: RE: [Asterisk-Users] OT: Best DB 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. --- David Brodbeck [EMAIL PROTECTED] wrote: -Original Message- From: Steven Critchfield [mailto:[EMAIL PROTECTED] Top Deployed Databases poll shows following databases in use: 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 Asterisk-Users@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 Albertson Home: 310-376-1029 [EMAIL PROTECTED] Cell: 310-990-7550 Office: 310-336-5189 [EMAIL PROTECTED] KG6OMK __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ ___ Asterisk-Users mailing list Asterisk-Users@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 -- 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 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On March 18, 2005 07:08 pm, Mike Sander wrote: 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
First, for an on-topic comment :-) Which database you choose will largely have the most to do with what applications you need to integrate your Asterisk databases with. If those applications are based on MySQL, you may need to use that. Ditto with Oracle, MS SQL, etc. My personal favorite is PostgreSQL, though. CDR and conf storage are not demanding in and of themselves, so you will want to look into what else is available to do what you want. For example, maybe you want it to connect to a billing app. Now for the off-topic portion Giudice, Salvatore wrote: So, let me see if I am right. You run a support shop? You want your database to validate your data for you instead of leaving that logic to your application? You validate your data in both places. You validate it in the application in order to prevent certain types of problems, but... If you don't validate it in the database, you have the possibility that a programming bug in your application could render your large database worthless because your data may not have a consistant meaning. Here is the thing: A good relational database manager will allow you to automate three things: storage, mainenance/enforcement, and presentation of your data. The first is handled using tables, the second using triggers, and the third using views. Without these three, you do not have a real robust database manager. MySQL is largely built for storage only. I.e. it cannot handle the other two aspects reasonably at all. If you are writing a content management system, this is OK, but if you are trying to build a complex data warehouse supporting multiple frontends, this breaks down very quickly. Again, the more front-ends you have the more you have to worry about application bugs introducing bad data into your database. Finally, on this point, you assume that the application and the database are extremely tightly coupled. In larger deployments, these are usually maintained separately. So the issue with numeric datatypes being truncated in MySQL is a *big deal* because the application cannot be expected to know what the database thinks the max size is for the field. Same with strings but the effects are less severe because usually people are not doing mathematical operations on strings This problem also becomes more severe when an application must support different database managers which have different limitations. You see where I am going? The RDBMS is the *only* place you can be *sure* to enforce your data constraints properly. Usually, a database is considered to be an asset worth protecting from unvalidated user input.Also, do you routinely try to insert text strings into fields, which are not created large enough to accept these strings? This is somewhat disturbing. The lack of a warning is virtually unimportant, if you know your data before you insert it. Ok, so consider the following scenario: You have a database application that supports PostgreSQL and Firebird 1.0. Firebird 1.0 does NOT have a TEXT type, and you have a large comments field in one of your tables. So the maintainer of the Firebird database schema sets the equivalent comments field to 2048 in length assuming that this will always be enough. SO now the application has two possible limits: 1GB or 2048 characters. What happens if someone tries to insert 3000 characters? Is it reasonable to just truncate the string? Well, it may be or may not be depending on your application. But I think that it is fair to say that the default should be to raise an error, and that this can be overridden if necessary by custom triggers. If nothing else, this will require that the programming team be notified of the error rather than *silently* truncating your data. Now, FWIW, PostgreSQL used to default to truncating strings. They fixed this in the 7.x series. If you are running into those kinds of problems, you need better programmers or at the very least better DBA's to design better database schemas. MySQL supports schemas now? Oh wait--- a MySQL database is just what the rest of us call a schema. Anyway, the issue has more to do with larger deployments of critical data (say bank transactions) rather than smaller, simpler apps. With regard to your performance examples, I can not agree nor disagree with your observations. The largest of my past applications involved a ridiculously high number of batch/blind inserts and periodic data condensation with replicated storage for high level report optimization. I ran this app using a Beowulf cluster for parsing and two 8-way cpu servers running MySQL with a 2-terrabyte ultra160 storage array. I realize this is not the typical user experience, but I can tell you that we were able to handle a peak of 700k inserts per hour. Ok, so lots of inserts, no updates, few selects. Complex data processing on separate cluster. MySQL should be pretty good at this. However, if you need to handle
Re: [Asterisk-Users] OT: Best DB
David Brodbeck wrote: 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 begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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. --- David Brodbeck [EMAIL PROTECTED] wrote: -Original Message- From: Steven Critchfield [mailto:[EMAIL PROTECTED] Top Deployed Databases poll shows following databases in use: 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 Asterisk-Users@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 Albertson Home: 310-376-1029 [EMAIL PROTECTED] Cell: 310-990-7550 Office: 310-336-5189 [EMAIL PROTECTED] KG6OMK __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On 15 Mar 2005, at 23:52, Giudice, Salvatore wrote: 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/ ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On March 15, 2005 06:04 pm, Giudice, Salvatore wrote: 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! 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
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. 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. ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
On Tue, 2005-03-15 at 13:00 -0500, Giudice, Salvatore wrote: 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 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. 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 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 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: [EMAIL PROTECTED] Right to Life of Michigan | Web: http://www.rtl.org ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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. ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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:[EMAIL PROTECTED] Sent: Wednesday, March 16, 2005 6:44 AM To: asterisk-users@lists.digium.com Subject: Re: [Asterisk-Users] OT: Best DB On March 15, 2005 06:04 pm, Giudice, Salvatore wrote: 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! 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. ___ Asterisk-Users mailing list Asterisk-Users@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 ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
-Original Message- From: Giudice, Salvatore [mailto:[EMAIL PROTECTED] 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
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: [EMAIL PROTECTED] so that those people who think that mustard tastes better than ketchup have somewhere more appropriate to argue. Thanks, Jon. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
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. ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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:[EMAIL PROTECTED] Sent: Sunday, March 13, 2005 2:27 PM To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [Asterisk-Users] OT: Best DB 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. 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. 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 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On March 15, 2005 01:00 pm, Giudice, Salvatore wrote: 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-Users@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
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 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:[EMAIL PROTECTED] Sent: Tuesday, March 15, 2005 1:40 PM To: asterisk-users@lists.digium.com Subject: Re: [Asterisk-Users] OT: Best DB On March 15, 2005 01:00 pm, Giudice, Salvatore wrote: 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-Users@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 image001.gif___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
Giudice, Salvatore wrote: Sticks and stone still break my bones, but PostgreSQL is still a dog. Enough, take it off list, PLEASE! ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Mar 15, 2005, at 11:21 AM, Giudice, Salvatore wrote: x-tad-biggerSticks and stone still break my bones, but PostgreSQL is still a dog./x-tad-bigger x-tad-bigger /x-tad-bigger Market share: x-tad-biggerAccording to CD Times magazine dated July 1, 2004/x-tad-bigger x-tad-bigger Top Deployed Databases poll shows following databases in use:/x-tad-bigger x-tad-bigger SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL - 8%. /x-tad-bigger 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On March 15, 2005 02:21 pm, Giudice, Salvatore wrote: 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
From my personal experience, pgsql outperforms mysql when using tables with over 30.000.000 records. For small tables mysql is faster, but also locks up more when 1 thread takes a long time. We used mysql for years, then had to move on to pgsql and never turned back. (we still have some 300 queries / second databases running on mysql for historic reasons.) It all depends on what features you need, most of the time both will do fine, you just need to learn how to optimize your queries and your database config. Oh and SER can also use pgsql so its not needed to stick to mysql, thats not a good reason and pgsql has no problems with 700.000 inserts an hour. Zoa. Giudice, Salvatore wrote: 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:[EMAIL PROTECTED] Sent: Thursday, March 10, 2005 2:41 AM To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [Asterisk-Users] OT: Best DB Richard Cook wrote: 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. ___ Asterisk-Users mailing list Asterisk-Users@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 signature.asc Description: OpenPGP digital signature ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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 adamaged CD-R disc. It's a nice OS, mind you, but it's not as stable as some people would lead you to believe. ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
On Tue, 2005-03-15 at 14:21 -0500, 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%. 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 [EMAIL PROTECTED] ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
-Original Message- From: Steven Critchfield [mailto:[EMAIL PROTECTED] Top Deployed Databases poll shows following databases in use: 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 Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
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 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
Giudice, Salvatore wrote: MySQL: Speed, Power and Precision Thanks, I will file this in my MySQL Appointment Book under Feb 31. Oh, you mean that is not a valid date? MySQL had no problem with it... Seriously though, precision and accuracy are not strongpoints of MySQL. MySQL really has been designed to work extremely well for content management systems. And it does this quite well. However, for applications where the precision of your data manners, MySQL is not worth trusting under any circumstances, IMO. For example: Feb 31 is a valid date, as is Feb 29, 2005. -00-00 is also a valid date. If you create a numeric(4,2) field, and insert into it a value like 100 it will truncate your number without even raising an error. Text fields are truncated without so much as a warning. Under certain circumstances, a MyISAM talbe can be created where an Innodb table was specified, thereby running inserts/updates/deletes on that table outside transactional control. Again, an error is not reported. All of the above behaviors are violations of the ANSI SQL standards which under certain circumstances can endanger the integrity of your data. Look, I am not saying Don't use MySQL. I am saying that I don't use MySQL because I have no use for it. Also, MySQL does perform faster on simple selects with low concurrency than PostgreSQL does (1-2 clients). But if you get up to 32 concurrent users, some reports indicate that MySQL will actually take more time to run the queries concurrently than that serially, but YMMV. PostgreSQL does scale better for high concurrency usage under every single benchmark I have seen. With modern versions, it doesn't even require tuning unless you want to use that system solely as your database manager. Also, MySQL does not have many features I use for my more advanced work. It support for subselects is somewhat immature, and it has no support for views, stored procedures, triggers, schemas, complex data types, and the like. Iirc, it has no group or role permissions either, meaning that if you have a large number of users, managing the security can be a bear (yes, I have added emulation to some MySQL databases of this feature, but it is easier to add in other database managers). If you want to use MySQL, go ahead. That is fine. My business will even support you if you do. We just think that there are deficiencies in the database manager, so we run all our operations on PostgreSQL and only support MySQL for some customer applications. If you are so keen on paying for something, try buying support - MySQL AB. With PostgreSQL, you could get support from a mom and pop shop... However, either way you will save tons of money over Oracle. You could get PostgreSQL support from a mom-and-pop shop. Same with MySQL... Of course that is not the only place you can get support. Fujitsu is now selling a version of PostgreSQL which they support, for example. PostgreSQL, Inc. also sells support and pays at least some members of the core development team. Then there are businesses such as Metatron Technology Consulting (shameless plug, we will support both, BTW), SRA, and Command Prompt, each of which offer high quality support. Best Wishes, Chris Travers begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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, its 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:[EMAIL PROTECTED] 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. ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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:[EMAIL PROTECTED] Sent: Tuesday, March 15, 2005 5:57 PM To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [Asterisk-Users] OT: Best DB Giudice, Salvatore wrote: MySQL: Speed, Power and Precision Thanks, I will file this in my MySQL Appointment Book under Feb 31. Oh, you mean that is not a valid date? MySQL had no problem with it... Seriously though, precision and accuracy are not strongpoints of MySQL. MySQL really has been designed to work extremely well for content management systems. And it does this quite well. However, for applications where the precision of your data manners, MySQL is not worth trusting under any circumstances, IMO. For example: Feb 31 is a valid date, as is Feb 29, 2005. -00-00 is also a valid date. If you create a numeric(4,2) field, and insert into it a value like 100 it will truncate your number without even raising an error. Text fields are truncated without so much as a warning. Under certain circumstances, a MyISAM talbe can be created where an Innodb table was specified, thereby running inserts/updates/deletes on that table outside transactional control. Again, an error is not reported. All of the above behaviors are violations of the ANSI SQL standards which under certain circumstances can endanger the integrity of your data. Look, I am not saying Don't use MySQL. I am saying that I don't use MySQL because I have no use for it. Also, MySQL does perform faster on simple selects with low concurrency than PostgreSQL does (1-2 clients). But if you get up to 32 concurrent users, some reports indicate that MySQL will actually take more time to run the queries concurrently than that serially, but YMMV. PostgreSQL does scale better for high concurrency usage under every single benchmark I have seen. With modern versions, it doesn't even require tuning unless you want to use that system solely as your database manager. Also, MySQL does not have many features I use for my more advanced work. It support for subselects is somewhat immature, and it has no support for views, stored procedures, triggers, schemas, complex data types, and the like. Iirc, it has no group or role permissions either, meaning that if you have a large number of users, managing the security can be a bear (yes, I have added emulation to some MySQL databases of this feature, but it is easier to add in other database managers). If you want to use MySQL, go ahead. That is fine. My business will even support you if you do. We just think that there are deficiencies in the database manager, so we run all our operations on PostgreSQL and only support MySQL for some customer applications. If you are so keen on paying
Re: [Asterisk-Users] OT: Best DB
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. Giudice, Salvatore wrote: 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, its 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:[EMAIL PROTECTED] *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. ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
quote who=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 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Tue, 15 Mar 2005 18:52:00 -0500, Giudice, Salvatore [EMAIL PROTECTED] wrote: So, let me see if I am right. You run a support shop? You want your database to validate your data for you instead of leaving that logic to your application? 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. 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. 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Tuesday 15 March 2005 06:34 pm, Robert Hajime Lanning wrote: quote who=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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Tue, 2005-03-15 at 19:04 -0600, Jon Gabrielson wrote: On Tuesday 15 March 2005 06:34 pm, Robert Hajime Lanning wrote: quote who=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 [EMAIL PROTECTED] ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
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. 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. 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 begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
[EMAIL PROTECTED] wrote: 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. 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 Asterisk-Users@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 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On March 10, 2005 07:14 pm, Giudice, Salvatore wrote: 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. 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.) 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Fri, Mar 11, 2005 at 01:56:47PM -0500, Giudice, Salvatore wrote: 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 [EMAIL PROTECTED] || best ICQ# 16849755 | Space reserved for other protocols | friend ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Fri, Mar 11, 2005 at 04:25:59PM -0500, David Filion wrote: 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. 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 [EMAIL PROTECTED] || best ICQ# 16849755 | Space reserved for other protocols | friend ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
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 Mohit Muthanna wrote: On Thu, 10 Mar 2005 19:14:36 -0500, Giudice, Salvatore [EMAIL PROTECTED] wrote: 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Fri, 2005-03-11 at 08:51 -0600, Matthew Boehm wrote: 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 [EMAIL PROTECTED] ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: 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-Users@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 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
My god. WTF is doing 700,000 inserts/hour for 2TB of data? -Matthew Giudice, Salvatore wrote: 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-Users@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
RE: [Asterisk-Users] OT: Best DB
Security events generated from IDS. -Original Message- From: Matthew Boehm [mailto:[EMAIL PROTECTED] 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 Giudice, Salvatore wrote: 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-Users@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 ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
quote who=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 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
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 [EMAIL PROTECTED] wrote: Security events generated from IDS. -Original Message- From: Matthew Boehm [mailto:[EMAIL PROTECTED] 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 Giudice, Salvatore wrote: 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-Users@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 ___ Asterisk-Users mailing list Asterisk-Users@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 -- 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-Users@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
Re: [Asterisk-Users] OT: Best DB
Giudice, Salvatore wrote: 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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: 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 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Wed, 9 Mar 2005, [EMAIL PROTECTED] wrote: 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 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Thu, Mar 10, 2005 at 01:04:35AM -0700, Forrest W. Christian said: 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
Walt Reed wrote: On Thu, Mar 10, 2005 at 01:04:35AM -0700, Forrest W. Christian said: 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 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On March 10, 2005 08:44 am, Walt Reed wrote: 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? 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. ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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- From: Steven Critchfield [mailto:[EMAIL PROTECTED] 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Thu, Mar 10, 2005 at 08:55:53AM -0500, David Filion said: Walt Reed wrote: 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. ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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 ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
On Thu, 2005-03-10 at 08:57 -0600, Jay Milk wrote: 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. -Original Message- From: Steven Critchfield [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Thu, 2005-03-10 at 09:17 -0500, Andrew Kohlsmith wrote: On March 10, 2005 08:44 am, Walt Reed wrote: 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 [EMAIL PROTECTED] ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Thu, Mar 10, 2005 at 09:09:09AM -0600, [EMAIL PROTECTED] said: 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. ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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- From: Steven Critchfield [mailto:[EMAIL PROTECTED] Sent: Thursday, March 10, 2005 10:45 AM To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: RE: [Asterisk-Users] OT: Best DB On Thu, 2005-03-10 at 08:57 -0600, Jay Milk wrote: 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. ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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:[EMAIL PROTECTED] Sent: Thursday, March 10, 2005 2:41 AM To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [Asterisk-Users] OT: Best DB Richard Cook wrote: 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Thu, 10 Mar 2005 19:14:36 -0500, Giudice, Salvatore [EMAIL PROTECTED] wrote: 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-Users@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
[Asterisk-Users] OT: Best DB
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] ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Wed, 2005-03-09 at 14:50 -0500, [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Wed, Mar 09, 2005 at 02:50:47PM -0500, [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] || best ICQ# 16849755 | Space reserved for other protocols | friend ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
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 [EMAIL PROTECTED] To: asterisk-users@lists.digium.com Sent: Wednesday, March 09, 2005 3:18 PM Subject: Re: [Asterisk-Users] OT: Best DB On Wed, Mar 09, 2005 at 02:50:47PM -0500, [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] || best ICQ# 16849755 | Space reserved for other protocols | friend ___ Asterisk-Users mailing list Asterisk-Users@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 --- [This E-mail scanned for viruses by Declude Virus] --- [This E-mail scanned for viruses by Declude Virus] ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Wed, 9 Mar 2005 15:43:46 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] To: asterisk-users@lists.digium.com Sent: Wednesday, March 09, 2005 3:18 PM Subject: Re: [Asterisk-Users] OT: Best DB On Wed, Mar 09, 2005 at 02:50:47PM -0500, [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] || best ICQ# 16849755 | Space reserved for other protocols | friend ___ Asterisk-Users mailing list Asterisk-Users@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 --- [This E-mail scanned for viruses by Declude Virus] --- [This E-mail scanned for viruses by Declude Virus] ___ Asterisk-Users mailing list Asterisk-Users@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 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Wed, 2005-03-09 at 15:43 -0500, [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Wed, Mar 09, 2005 at 03:02:03PM -0600, Steven Critchfield said: On Wed, 2005-03-09 at 15:43 -0500, [EMAIL PROTECTED] wrote: 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. ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
On Wed, 2005-03-09 at 15:02 -0600, Steven Critchfield wrote: On Wed, 2005-03-09 at 15:43 -0500, [EMAIL PROTECTED] wrote: 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 ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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- From: Adam Goryachev [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 09, 2005 7:40 PM To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [Asterisk-Users] OT: Best DB On Wed, 2005-03-09 at 15:02 -0600, Steven Critchfield wrote: On Wed, 2005-03-09 at 15:43 -0500, [EMAIL PROTECTED] wrote: 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 ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
On Thu, 2005-03-10 at 00:37 -0600, Jay Milk wrote: 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 [EMAIL PROTECTED] ___ Asterisk-Users mailing list Asterisk-Users@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
RE: [Asterisk-Users] OT: Best DB
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 [EMAIL PROTECTED] T: 705-223-2000 x2010 ___ Asterisk-Users mailing list Asterisk-Users@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
Re: [Asterisk-Users] OT: Best DB
Richard Cook wrote: 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. begin:vcard fn;quoted-printable:=CE=91=CF=80=CF=8C=CE=BB=CE=BB=CF=89=CE=BD =CE=9A=CE=BF=CF=85=CF=84=CE=BB= =CE=AF=CE=B4=CE=B7=CF=82 n;quoted-printable;quoted-printable:=CE=9A=CE=BF=CF=85=CF=84=CE=BB=CE=AF=CE=B4=CE=B7=CF=82;=CE=91=CF=80=CF=8C=CE=BB=CE=BB=CF=89=CE=BD email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ___ Asterisk-Users mailing list Asterisk-Users@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