Hi, Dylan.

BerkeleyDB (or as those of us in industry sometimes call it Sleepycat, is an industrial, real, honest to goodness database. It passes the ACID test, supports real transactions, and is otherwise hardened. If you're building a database app, you'd be hard-pressed to find a better bare-bones low level database than BerkeleyDB.

MySQL isn't a database. It's ... something else. It doesn't (didn't?) support transactions. Berkeley DB does have a limit on the size of a single transaction, because transactions require page table locks, and locks take up memory. So there's a real simple solution: if you want to operate on (say) 50,000 entries at once, don't do it under a single transaction. Or, if you don't need transactional semantics -- for example, if you are just reading entries out of the database for display on a web page -- just don't use them! Voila. if you're not using a transaction to read from the DB, then you're not going to run out of locks.

In other words, if MT has problems running out of DB locks, then that's a problem with the application, not with BerkeleyDB. Maybe I'll drop the MT folks a line and see if I can help them work them out.

The other aspect of Dylan's post that I find troubling is that he's sort of blaming BerkeleyDB because his ISP changed versions of the software out from under him. Of course everything broke. He should yell at his ISP.

in any event, I don't think you even have to start worrying about bumping up against the page table lock exhaustion issue until you get over about 20,000 entries, by which point hopefully the MT folks will have fixed those bugs in their app.

-Peterb

Switch to a MySQL backend fast. The Berkeley DB thrashed and trashed my entire comments database when a version changed in the hosted environment.

OK, the hosted environment is a virtual server and I was the one who upgraded Debian stable to testing. But still! The danger is there ;-)

Hmmm, just saw the comment from Peter. Regardless of the technical merit of the Berkeley/SleepyCat database, tons of MovableType users have been screwed by it. Google for "Movable Type database corrupt" and you'll see a ton of problems experienced by users.

So for bloggers, MySQL is a better choice due to the fact that compatibility in tables is much better preserved through upgrades to the system. You're talking about an ideal word, and Diego and I are talking about today.

Ugh.

peterb is spewing FUD, pure and simple.

I don't suppose you'd bother to say -how- I'm spreading FUD, jeremy? No, I thought not.

-I- thought I was giving a pretty detailed description of the issues behind intelligently using a transactional database. But I guess you know better, what with your detailed rejoinder.

I did a little research, and found that you CAN have transaction support with mySQL!

...if you have mySQL use BerkeleyDB as a backend to provide transaction support.

Ha ha ha.

Oops, did a little more research and read up on InnoDB. Ok, so that's two transactional backing stores. That's cool.
I've heard (and I am not trying to spread FUD here, I welcome a correction if I am wrong), that if you use MySQL transactionally it becomes...well, as slow as all the alternatives (which I don't think is a dig at MySQL; it just plain makes sense.)

Can you enlighten us, Jeremy?

Peter, for future reference please avoid flamebait in comments on my weblog.

To get to the points, I don't think Dylan was particularly "blaming" BerkeleyDB. He was just describing his problem. Additionally, he didn't say BDB was unusable, or that it didn't have transactions. He just commented on something he read, both for MySQL and BDB.

For the record here's what Jeremy was probably thinking about when he made his first comment:

http://www.mysql.com/products/mysql/index.html

"MySQL Standard includes the standard MySQL storage engines and the InnoDB storage engine. InnoDB is a transaction-safe, ACID-compliant storage engine with commit, rollback, crash recovery and row-level locking capabilities. This version is for users who want the high-performance MySQL database with full transaction support."

Prior to that MySQL started to support the BerkeleyDB storage engine on v3.2x
http://www.mysql.com/doc/en/BDB_overview.html

As far as the performance cost of transactions with MySQL DB it is clear that there would be a performance hit. However, benchmarks that are generally considered "standard" such as TPC (www.tpc.org) do not include either MySQL or Sleepycat. This probably has to do in part due to the requirements of running the test (more oriented towards big players, etc). In any case, at a minimum they are going to be similar, but I'd suspect that the increasingly enterprise-focus of MySQL DB would force them to push the envelope in this area.