Slashdot Mirror

The difference isn't just the SQL support but more the transaction handling abilities of the DB specifically the ACID properties of the database. The ACIDity of a database is what makes it a RDBMS. Acid properties are

Atomicity:
This means that transactions are either fully completed or never begun at all. Any updates that a transaction might make on a system are completed in its entirety. If any error ccurs during the transaction keeping it from completing all its steps then the DB is rolled back to its previous state before the transaction began. E.g. Let's say a transaction consists of money being removed from a checking account and stored in a savings account. If there is an error after the money is removed from the checking account but not stored in the savings account then the transaction can be rolled back to when the money was still in the checking account.

Consistency:
This means that the DB is always in a valid state after a transaction. Thus if an error occurs during a transaction, the DB can be rolled back to the last valid state. E.g. From the above example the total amount of money in both accounts is constant and is equal to the sum of both amounts. If an error occurs while transferring money from the checking account to the savings account then the total of both accounts is not consistent with the amounts actually in the accounts. This inconsistency is handled by rolling back the DB to the last consistent state.

Isolation:
Each transaction appears to the only one being carried out by the system at that time. If there are two transactions both performing the same function and both running at the same time they will be invisible to each other. This ensures that the system is consistent because if transactions do not run in isolation the they may access data from the DB that is inconsistent due to the fact that some other transaction is in the middle of performing its tasks. E.g. if my paycheck is being deposited in my checking account at the same time as when I am viewing an account summary I will not be able to see the changes being made to my account until the deposit transaction is completed. This stops unsavory things like seeing the total amount in the account remaining constant while the recent deposits field may already contain the deposit.

Durability:
Once changes have een made to the DB they are permanent. There are safeguards that will prevent loss of information even in the case of system failure. By logging the steps that each transaction takes the state of the system can be recreated even if the hardware has failed.


Here's an article that explains why mySQL is not a real RDBMS since it's support for transactions are lacking. PostrgreSQL on the other hand supports transactions. For instance, people who use a DB that supports transactions don't have to lock the tables themselves when accessing the DB from their code instead stuff like that is handled by the DB.

PS: Also not all of SQL is supported by mySQL (e.g. Foreign Keys for specifying integrity constraints).

The OpenACS team is happy to take a closer look at MySQL as it matures. However, it doesn't seem that the MySQL team understands the concepts and importance of true ACID capabilities: The MySQL Todo mentions "transactions" in a long list that includes questions such as "do sleeping threads take CPU." Furthermore, the MySQL manual claims that MySQL will soon implement "atomic operations" through the use of table locks, but without rollback. This is a blatant misuse of the term "atomic," which implies that either none or all operations will complete. A hardware or power failure in the middle of a set of statements will break the atomicity of the block if there is no rollback capability.

I'd suggest reading the rest of the article -- it reveals many other caveats and limitations, including the fact that MySQL uses only table-level locking. Basically, it seems like MySQL is a great alternative to using a filesystem to store structured data, but it is not a substitute for a relational database.

By the way, PostgreSQL has supported transactions for at least the last two years.

~wog

In a discussion on OpenACS.org, one of the project leaders said that there is a pervasive programming error in the ACS toolkit that breaks atomicity and makes "Oracle an expensive version of MySQL." Although this alleged error is supposedly being fixed, it raises a question (beyond the obvious one): if true, how come photo.net has been able to meet its heavy loads? Is Oracle really as necessary as Greenspun argues?

Fault tolerance was a big issue. We've started by load balancing anything that could easily be balanced, but balancing MySQL is harder. We're funding development efforts with the MySQL team to add database replication and rollback capabilities to MySQL (these improvements will of course be rolled into the normal MySQL release as well).

__________________________________________________ ___

set handle [ns_db gethandle]

Using ns_db with other arguments you then get to do your queries/inserts/etc (simplifying just a little):

ns_db $handle "select * from table"

ns_db dml $handle "insert into table (field1, field2) values ($value1, $value2)"

Then, when you are done, you just release the handle:

ns_db releasehandle $handle

Each AOLServer process can hold 8 database connections open.

AOLServer 2.3.3 is free, non-open source. AOLSerer 3.0 is free and open source, thanks to the efforts of Philip Greenspun and company.

Philip has written an intro to AOLServer that was published in Linux Today, it is now available in two parts here:

Part 1

Part 2

A quick quote from part 1: "AOLserver runs as a single Unix process. You can deliver the 20 dynamic pages per second of our example without your server having to start any new programs. If those pages need to connect to Oracle, they simply ask AOLserver to let them use an already-open connection from a configurable pool. Note that this ability to pool database connections is a consequence of AOLserver's one-process-with-threads architecture. With a process-pool Web server such as Apache, nothing stops you from linking in the Oracle C libraries. Your Apache server can then function as an Oracle client. However, there would be no way to share a database connection among Apache server processes. What's the bottom line difference? A site like http://photo.net can serve 700,000 hits per day, to about 120 simultaneous users at once, with one AOLserver process holding open eight connections to Oracle. That's a total of nine Unix processes (one AOLserver, eight Oracle). With Apache, providing the same level of service from photo.net would require 120 Apache server process, each of which held open two connections to Oracle: 360 processes total.

Another dividend from the single-process architecture of AOLserver is that you can cache stuff in AOLserver's virtual memory. For example, consider the Bill Gates Personal Wealth Clock (http://www.webho.com/WealthClock). It gets as many as two hits per second at peaks. Yet it relies on invoking CGI scripts running at foreign Web sites where they probably wouldn't appreciate getting hammered by my server. The solution is to cache the page in AOLserver's virtual memory. Again, this is something you could do with a process-pool server such as Apache but you'd be gradually building up 120 separate copies of the same data. "

Part 2 shows how to use the "ns_db" command that I mentioned above in some detail.

P.S. Philip Greenspun developed a great open source toolkit that sits on top of AOLServer called the Ars Digita Community System (aka the ACS) and wrote a book about it that is well known as a must-read for web-heads. It is on line for free, complete with all the photos:

Philip and Alex's Guide to Web Publishing

P.P.S. There's a version of the ACS available that uses the open-source Postgresql database, if you don't want to pay for Oracle: Open ACS (they have a working beta but are waiting for Postgresql 7 to come out before making an official release).

If you don't have the time to look it up, here is a brief definition for you:

axiology The study of the nature of values and value judgments.

Most of this thread can be divided up into two categories neatly:

• MySQL rocks.
  
  
• MySQL sucks.
  
  

What some people neglect to do before posting their comments is ask themselves two questions: It rocks for whom/what? It sucks for whom/what?

To begin with, Ben Adida never claimed that MySQL sucked for everyone/everything. His point was, they were getting tired of being asked the same question over and over again: Why does ACS not use MySQL, when everybody else and his sister seem to be using it? His answer to that is very simple. For the kind of stuff that OpenACS and ArsDigita do, MySQL is an alternative to Oracle like Photoshop is an alternative to PostgreSQL.

Ben never said MySQL wouldn't work for YOU.

To repeat, Ben is not trying to convince you that you shouldn't be using MySQL for your project, because he doesn't know what your project is. All he is saying, he has darn good reasons not to use MySQL for his project. Why do people feel they have to take issue with that, if they don't even know what OpenACS is trying to accomplish. Why don't they shut down their computer and take a break to read a few books on axiology for a change?

If you don't have the time to look it up, here is a brief definition for you:

axiology The study of the nature of values and value judgments.

Most of this thread can be divided up into two categories neatly:

• MySQL rocks.
  
  
• MySQL sucks.
  
  

What some people neglect to do before posting their comments is ask themselves two questions: It rocks for whom/what? It sucks for whom/what?

To begin with, Ben Adida never claimed that MySQL sucked for everyone/everything. His point was, they were getting tired of being asked the same question over and over again: Why does ACS not use MySQL, when everybody else and his sister seem to be using it? His answer to that is very simple. For the kind of stuff that OpenACS and ArsDigita do, MySQL is an alternative to Oracle like Photoshop is an alternative to PostgreSQL.

Ben never said MySQL wouldn't work for YOU.

To repeat, Ben is not trying to convince you that you shouldn't be using MySQL for your project, because he doesn't know what your project is. All he is saying, he has darn good reasons not to use MySQL for his project. Why do people feel they have to take issue with that, if they don't even know what OpenACS is trying to accomplish. Why don't they shut down their computer and take a break to read a few books on axiology for a change?

If you don't have the time to look it up, here is a brief definition for you:

axiology The study of the nature of values and value judgments.

Most of this thread can be divided up into two categories neatly:

• MySQL rocks.
  
  
• MySQL sucks.
  
  

What some people neglect to do before posting their comments is ask themselves two questions: It rocks for whom/what? It sucks for whom/what?

To begin with, Ben Adida never claimed that MySQL sucked for everyone/everything. His point was, they were getting tired of being asked the same question over and over again: Why does ACS not use MySQL, when everybody else and his sister seem to be using it? His answer to that is very simple. For the kind of stuff that OpenACS and ArsDigita do, MySQL is an alternative to Oracle like Photoshop is an alternative to PostgreSQL.

Ben never said MySQL wouldn't work for YOU.

To repeat, Ben is not trying to convince you that you shouldn't be using MySQL for your project, because he doesn't know what your project is. All he is saying, he has darn good reasons not to use MySQL for his project. Why do people feel they have to take issue with that, if they don't even know what OpenACS is trying to accomplish. Why don't they shut down their computer and take a break to read a few books on axiology for a change?

I think the ArsDigita folks would be a little more convincing if they considered intermediate solutions instead of the extreme ends of the spectrum.

I think you would be more convincing if you took a closer look at the target article of this thread.

Basically, they're doing Ars Digita stuff using PostgreSQL (and possibly Interbase, soon). They get queries back like "Hey, why don't you use MySQL?". And they've got answers.