Are there any benchmarks comparing these three databases, or maybe to somewhat similar comparing against the same database? Anyone got anything to say about the various ones, pros, cons, etc.? -Morten ------------------------------------------------------------- How to reply to email: http://home.sol.no/~vidaandr/news/OBSquoting.html (norsk) http://home.sol.no/~vidaandr/news/FAQquoting.html (english) Who is the bigger fool? The fool or the fool who follows him?
http://www.mysql.com/benchmark.html and http://www.mysql.com/crash-me-choose.htmy This is from MySQL so certainly should be taken with a grain of salt. I think the low down is that while MySQL is very fast it is not as scalable or as true a RDBMS as PostGreSQL. MS SQL 7.0 I will not comment about since I would never run a production website with that product. Enjoy, J
From: "Morten W. Petersen" <morten@src.no> Date: Mon, 8 May 2000 13:58:18 +0200 (CEST) To: zope@zope.org Subject: [Zope] MySQL - PostgreSQL - ZODB
Are there any benchmarks comparing these three databases, or maybe to somewhat similar comparing against the same database?
Anyone got anything to say about the various ones, pros, cons, etc.?
-Morten ------------------------------------------------------------- How to reply to email: http://home.sol.no/~vidaandr/news/OBSquoting.html (norsk) http://home.sol.no/~vidaandr/news/FAQquoting.html (english)
Who is the bigger fool? The fool or the fool who follows him?
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
On Mon, May 08, 2000 at 08:43:45AM -0400, J. Atwood wrote:
I think the low down is that while MySQL is very fast it is not as scalable or as true a RDBMS as PostGreSQL. MS SQL 7.0 I will not comment about since I would never run a production website with that product.
I guess this has the potential for a mid-sized flamewar, but also have a look at Ben Adida's recent article "Why Not MySQL ?" (http://openacs.org/why-not-mysql.html) and the ongoing discussion on Slashdot (http://slashdot.org/articles/00/05/05/0920247.shtml). Gregor
Sorry.. I didn't mean to open up a flame war of any size. Just opinion and experience based. J
From: Gregor Hoffleit <flight@mathi.uni-heidelberg.de> Date: Mon, 8 May 2000 15:01:58 +0200 To: "J. Atwood" <jatwood@bwanazulia.com> Cc: "Morten W. Petersen" <morten@src.no>, zope@zope.org Subject: Re: [Zope] MySQL - PostgreSQL - ZODB
On Mon, May 08, 2000 at 08:43:45AM -0400, J. Atwood wrote:
I think the low down is that while MySQL is very fast it is not as scalable or as true a RDBMS as PostGreSQL. MS SQL 7.0 I will not comment about since I would never run a production website with that product.
I guess this has the potential for a mid-sized flamewar, but also have a look at Ben Adida's recent article "Why Not MySQL ?" (http://openacs.org/why-not-mysql.html) and the ongoing discussion on Slashdot (http://slashdot.org/articles/00/05/05/0920247.shtml).
Gregor
Possibilities for flaming aside, I must say that I break from the pervasive attitude of Zope culture and defend MySQL a bit. MySQL doesn't implement Foreign Keys. It doesn't implement transactions. For better or for worse, those are the two things, and the only two things that really count that would cause you not to use it in production. If these two things are very important to your personal sense of well-being, then I would emphaticly join the others in saying DON'T USE MYSQL. It's not the right choice for you. However, if you are indifferent to those, or don't know enough about SQL or RDBMSs to have an opion yet, please consider the following: (I'm going to contrast against Oracle, due to current personal involvement, but only as a convenient source of examples) 1) MySQL is intended for a different audience. In Oracle, you write code (stored procedures) that are stored in the database. The schemas are stored in the database. The development environment is in the database. It is intended as an almost sub-operating system. You can live your whole development life and never set foot outside the database. MySQL, on the other hand, is a place for people to put things and refer to them later in a relational manner. The intended users are programmers or other power-users. (Much like, other flame-war aside, VI and EMACS) The same people who like the extended power for the end user at the expense of ease of use, would like MySQL. 2) MySQL doens't do things for you. As mentioned earlier, it expects you to be a responsible programmer. It will let you do what you want. If you want to define a column with a logical foreign key to a look-up table, and then insert into that column an un-defined value, MySQL won't stop you. (It won't even know) Oracle will throw up an exception and say Foreign Key Constraint violated. People says this makes things safer, because it forces the data to be ok They also say that MySQL would be a pain because you have to write code to make sure you are inserting valid data each time. Well, you have to do the same in Oracle on the other end of the stick, because you have to write code to handle the exception the database will throw if you are careless. The difference here is clean code rather than a shotgun and a shovel to handle the exceptions. Nothing is actually gained in data integrity by this, because you also have to define the constraints, (which would be part of the program design) and the constraint can just as easily be coded elsewhere. 3) MySQL is smaller. MySQL is faster. Period. If speed or size are a determing factor, MySQL is for you. Because MySQL treats itself as a relational storage medium, rather than as a mom for your intentions towards your data, it can be optimized for speed and size in ways that aren't possible for Oracle. Our Oracle product directory contains 438M of stuff. That's not even data. 4) The biggest complait I've heard from the Zope World is that MySQL doesn't support transactions, and since Zope is highly transactional, there is an inherent incompatability. This is the one that irks me the most. Like the foreign key checking from earlier, this just doens't buy you what you may think it buys you. Zope's undo funciton is wonderful and quite useful. But the transactions in an RDBMS don't get you that. You can't un-commit comitted transactions, and as far as the web-world goes, if you are going between screens with open transactions, I really feel for where the state of your data is going to be. Once you've inserted and commited in the method that you posted to, there's a Zope undo just waiting for you, and you are NOT going to be able to undo anything in the database. You can shut down the server, trim off some transactions from the transaction log, and run things back in, but that's not a real time activity. (You can do that in MySQL, too, I might add) Transactions in the database world are most useful in stored procedures to prevent the effects of a program inserting its data half-way and then dying, leaving corrupt data. As MySQL has no stored procedures, this is almost a non-issue, and again something that program design can completely do without. Since MySQL is small, fast, and I forgot to metion, stable, stuff crapping out mid-transaction is almost never a problem as far as MySQL itself is concerned. 5) I must add a fifth (sorry, I actually didn't mean for this to get so long) To quote dear Mr. Greenspun: """ Isolation The results of a transaction are invisible to other transactions until the transaction is complete. For example, suppose you have a page to show new users and their photographs. This page is coded in reliance on the publisher's directive that there will be a mugshot for every user and will present a broken image if there is not. Jane Newuser is registering at your site at the same time that Bill Olduser is viewing the new user page. The script processing Jane's registration does inserts into several tables: users, mugshots, users_demographics. This may take some time if Jane's mugshot is large. If Bill's query starts before Jane's transaction commits, Bill won't see Jane at all on his new-users page, even if Jane's insertion into some of the tables is complete. """ Case in point for good program design. The program uploads the mugshot file into memory before inserting it (which should then go quite quickly if the program runs on a machine in the same LAN) Insert the mug-shot, then the demographics, then the user key info in the users table, causing the display of users still not to be bothered with the on going insert until the key record is there (This would not be possible in the world of Foreign Keys, mind you, where you would need the user row there before you could insert the slower data -- the 'deficiencies' work quite nicely together, no?) I guess the main thing I'm trying to say is, it isn't better or worse, necessarily. Nor is it necessarily less stable. Either way can cause vast amount of crying and gnashing of teeth if you go into it with the wrong mindset. If you try to program Python like it's C or Perl like it's Python, you'll probably be fairly disappointed. However, if you realize that, when viewed under the light in which each was intended, it all comes down to a matter of style. I mean, Lisp is powerful as hell, but stylisticly clashes with every bone in my body. But I'm still not going to claim that it isn't stable or that you shouldn't run production data on it, because then I'd be joining in the attitudes of all the MS people who think that running an open-source scripting-language based system is silly. And I think we can all agree on the answer to that. Monty (Sorry for the ridiculous size) Gregor Hoffleit wrote:
On Mon, May 08, 2000 at 08:43:45AM -0400, J. Atwood wrote:
I think the low down is that while MySQL is very fast it is not as scalable or as true a RDBMS as PostGreSQL. MS SQL 7.0 I will not comment about since I would never run a production website with that product.
I guess this has the potential for a mid-sized flamewar, but also have a look at Ben Adida's recent article "Why Not MySQL ?" (http://openacs.org/why-not-mysql.html) and the ongoing discussion on Slashdot (http://slashdot.org/articles/00/05/05/0920247.shtml).
Gregor
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Gregor Hoffleit wrote:
On Mon, May 08, 2000 at 08:43:45AM -0400, J. Atwood wrote:
I think the low down is that while MySQL is very fast it is not as scalable or as true a RDBMS as PostGreSQL. MS SQL 7.0 I will not comment about since I would never run a production website with that product.
I guess this has the potential for a mid-sized flamewar, but also have a look at Ben Adida's recent article "Why Not MySQL ?" (http://openacs.org/why-not-mysql.html) and the ongoing discussion on Slashdot (http://slashdot.org/articles/00/05/05/0920247.shtml).
Gregor
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Monty Taylor wrote:
Possibilities for flaming aside, I must say that I break from the pervasive attitude of Zope culture and defend MySQL a bit.
As I see it, it is sensible to choose MySQL over an ACID-compliant database when you'll be performing 99.9% read operations. The comparison charts on mysql.com, astounding as they are, appear to be correct. MySQL is indeed very fast, especially compared with PostgreSQL. However, using MySQL, if someone is writing to multiple tables in the database while someone else is reading it, the database will not appear to be consistent to the reader until the write operation is complete. That is not a bug. It is part of the design of MySQL. Apparently MySQL is faster because of it. So, as always, choose your database wisely. If you want another chaotic but high-capacity Slashdot, use MySQL. If you want perfect integrity even during an onslaught of write operations, use something else. It is also my understanding that Zope, when it is capable of using an SQL database as its primary storage, will not be tied to any specific database type. Shane
It is also my understanding that Zope, when it is capable of using an SQL database as its primary storage, will not be tied to any specific database type.
This is a good point. You could, for example, have three or four different database types behind a Zope installation. LDAP doing usernames/password, MySQL for a stuff that needs a lot of reads and PostgreSQL for something that is really table/relational oriented. My new Zope saying... "Zope, the best of ALL worlds" J
Monty Taylor wrote:
MySQL doesn't implement Foreign Keys. It doesn't implement transactions. For better or for worse, those are the two things, and the only two things that really count that would cause you not to use it in production.
The main part concern is transactions - foreign keys can be worked around by program design (checking and rechecking), transactions can't.
If these two things are very important to your personal sense of well-being, then I would emphaticly join the others in saying DON'T USE MYSQL. It's not the right choice for you.
Yes, don't use it for "valuable" data - use it for anything that comes in abundance and small losses and inaccuraces are acceptable
However, if you are indifferent to those, or don't know enough about SQL or RDBMSs to have an opion yet, please consider the following: (I'm going to contrast against Oracle, due to current personal involvement, but only as a convenient source of examples)
You can just say RDBMS instead of Oracle ;)
1) MySQL is intended for a different audience. In Oracle, you write code (stored procedures) that are stored in the database. The schemas are stored in the database. The development environment is in the database. It is intended as an almost sub-operating system. You can live your whole development life and never set foot outside the database. MySQL, on the other hand, is a place for people to put things and refer to them later in a relational manner. The intended users are programmers or other power-users. (Much like, other flame-war aside, VI and EMACS) The same people who like the extended power for the end user at the expense of ease of use, would like MySQL.
2) MySQL doens't do things for you. As mentioned earlier, it expects you to be a responsible programmer. It will let you do what you want.
It will do what you tell it to do, not what you want ;) If you want the an RDBMS to do what you want, you must first tell it the rules (foreign keys, checks, triggers)
If you want to define a column with a logical foreign key to a look-up table, and then insert into that column an un-defined value, MySQL won't stop you. (It won't even know) Oracle will throw up an exception and say Foreign Key Constraint violated. People says this makes things safer, because it forces the data to be ok They also say that MySQL would be a pain because you have to write code to make sure you are inserting valid data each time. Well, you have to do the same in Oracle on the other end of the stick, because you have to write code to handle the exception the database will throw if you are careless. The difference here is clean code rather than a shotgun and a shovel to handle the exceptions.
There are often speed advantages to doing the try/except thing when 99% of the foreign keys are ok.
Nothing is actually gained in data integrity by this, because you also have to define the constraints, (which would be part of the program design) and the constraint can just as easily be coded elsewhere.
You gain a lot if your database is accessed by mor than one concurrent users, specialli in absence of transactions: user 1 user 2 check referenced key - OK check if ok to delete key - OK delete key insert referencing key and you have an inconsistent state you had no way to detect user1 could of course do another check after inserting the referencing key and undo the insert by doing a delete, but then he would need to do all the check again and he could never stop being sure that he is OK, unless he uses transactions which he can't do in MySQL
3) MySQL is smaller. MySQL is faster. Period.
MySQL is faster for simple queries. Not so for more complex ones (i.e. it realli is a file system with an SQL interface)
If speed or size are a determing factor, MySQL is for you. Because MySQL treats itself as a relational storage medium, rather than as a mom for your intentions towards your data, it can be optimized for speed and size in ways that aren't possible for Oracle. Our Oracle product directory contains 438M of stuff. That's not even data.
I've been told that oracle's install is now even bigger than the DB, itself ;)
4) The biggest complait I've heard from the Zope World is that MySQL doesn't support transactions, and since Zope is highly transactional, there is an inherent incompatability.
There are other parts of Zope that are non-transactional (like mailhosts) but no other DAs.
This is the one that irks me the most. Like the foreign key checking from earlier, this just doens't buy you what you may think it buys you.
It depends on what you may think it buys you ;) (see the comment on corresponding "why not MySQL" thread on Slashdot about DBAs jumping from skyscrapers and yelling ROLLBACK! befor hitting ground)
Zope's undo funciton is wonderful and quite useful.
UNDO != transactions Zope has much more than a DB-style transactions - it has also long-running transactions (aka versions) as well as UNDO, which has nothing to do with transactions at all.
But the transactions in an RDBMS don't get you that.
They are not meant to. Their sole purpose is to ensure consistency for multi-user DBs.
Transactions in the database world are most useful in stored procedures to prevent the effects of a program inserting its data half-way and then dying, leaving corrupt data.
More often it is for concurrent updates.
As MySQL has no stored procedures, this is almost a non-issue, and again something that program design can completely do without.
Only if you disallow concurrent access
Since MySQL is small, fast, and I forgot to metion, stable, stuff crapping out mid-transaction is almost never a problem as far as MySQL itself is concerned.
what does small and fast have to do with it ;)
5) I must add a fifth (sorry, I actually didn't mean for this to get so long) ... Case in point for good program design. The program uploads the mugshot file into memory before inserting it (which should then go quite quickly if the program runs on a machine in the same LAN) Insert the mug-shot, then the demographics, then the user key info in the users table, causing the display of users still not to be bothered with the on going insert until the key record is there (This would not be possible in the world of Foreign Keys, mind you, where you would need the user row there before you could insert the slower data -- the 'deficiencies' work quite nicely together, no?)
No! Foreign keys can be enforced at transaction commit time, when all the needed inserts/updates are done.
I guess the main thing I'm trying to say is, it isn't better or worse, necessarily.
Nor is it necessarily less stable.
No. It just has intermittent inconsistent states, which can result in wrong dtata in concurrent use scenarios.
Either way can cause vast amount of crying and gnashing of teeth if you go into it with the wrong mindset.
But if someone loses money _with_ transactions it can be surely traced to programmer error, when using MySQL you can point fingers at MySQL and prove that this was unavoidable ;) ...
But I'm still not going to claim that it isn't stable or that you shouldn't run production data on it,
If you care for your data (or their representation at all times) and have enough updates you can't ensure they are done serially, you should not. With low update volumes you may be able to arrange your updates so that you can.
because then I'd be joining in the attitudes of all the MS people who think that running an open-source scripting-language based system is silly.
Being able to imply some suprficial ties of your thinking to the supposed contrary of what MS people are supposed to think is a very weak argument in technical fields. Grow up! -------------- Hannu
Hannu Krosing wrote:
Being able to imply some suprficial ties of your thinking to the supposed contrary of what MS people are supposed to think is a very weak argument in technical fields. Grow up!
Agreed. Sorry about that one... the tired but had bitten a bit too hard.
Hello Zopistas, I have some columns with float numbers in oracle (e.g.. values like 1e-50) and it seems that dcoracle is translating those values into straight zeros. Is there any way to read and display these number in scientific notation? Any help would be greatly appreciated, Thank you Christian
On Tue, 9 May 2000, Christian Sonntag wrote:
I have some columns with float numbers in oracle (e.g.. values like 1e-50) and it seems that dcoracle is translating those values into straight zeros. Is there any way to read and display these number in scientific notation? Any help would be greatly appreciated,
One of my clients is reporting a similar problem. They are using DCOracle in a pure python context as well as a Zope context. They report that float data is turned into integers. The Oracle consultant took a look and has this to say (they are using Oracle 8i, and I note that the DCOracle documentation does not mention 8i): ----------- After analyzing the DCOracle package it appears the specific problem lies in how DCOracle uses the Oracle Call Interface(oci) function "odescr()". This function is obsolecent in version 8i of Oracle but was present in earlier versions of Oracle. The function appears to work under Oracle 8i, but it must be some wrapper around the 8i functions -- it does have some problems. Two of the function parameters (precision, and scale), are supposed to return the size of the number and the number of decimals, but this does not happen. We are not sure if this is a real problem -- since no one else has reported it -- or if this is an incompatibility with Oracle 8i OCI. ----------- Can some DCOracle guru comment on this? --RDM
participants (8)
-
Christian Sonntag -
Gregor Hoffleit -
Hannu Krosing -
J. Atwood -
Monty Taylor -
Morten W. Petersen -
R. David Murray -
Shane Hathaway