the First Open Source Database Clustering Solution?

csn

MySQL AB Launches MySQL Cluster – the First Open Source Database Clustering Solution

http://www.mysql.com/news-and-events/press-release/release_2004_14.html

The first? Really? C'mon.. I guess mysql has another table/engine type - NDB.

The core NDB engine is a new storage engine inside MySQL. It provides transactions, replication, on-line backups, crash recovery, hash and tree indexes, on-line index builds, auto-detection of a failed node and re-sync when it comes back up. There are rolling upgrades, which provide a way to upgrade things without a disruption of service.

Read all the other news from mysql's big conference in Orlando:

http://jeremy.zawodny.com/blog (warning - extremely biased, one-sided, and close-minded)
http://www.mysql.com/news-and-events

Widenius: MySQL Ready to Take on the World
Oracle VP: MySQL Cluster Not a Threat
Analysts: MySQL's Cluster Version Puts Pressure on Big Database Vendors

http://www.eweek.com/category2/0,1738,1237934,00.asp

And, finally, I guess mysql is further tweaking their sketchy license:
http://www.eweek.com/article2/0,1759,1531359,00.asp

Sxooter

Sleepycat's Berkely DB has had clustering for quite some time.

Matt Dillon's (currently fairly primitive) database has clustering as part of the core design of it.

MySQL tends to say things like "most popular open source database" when it's pretty obvious that no matter how popular it gets, berkely style db tables will be more popular since most unix systems come with a half dozen of them already installed and running under applications like apache or ldap.

csn

I'm learning subversion - that uses Berkeley DB too. Transactions were the main reason for selecting it.

csn

Does mysql have any advantages over Berkeley DB? I couldn't find any "berkeley db vs. mysql" articles.

csn

Check this out:

OPEN SOURCE DATABASE SOFTWARE COMPARISON
http://www.geocities.com/mailsoftware42/db/

I forgot mysql has the berkeleydb table type - same thing?

csn

Originally posted by csn
Check this out:

OPEN SOURCE DATABASE SOFTWARE COMPARISON
http://www.geocities.com/mailsoftware42/db/

Postgres forks on every incoming connection - and the forking process and backend setup is a bit slow

True?

Sxooter

Postgres forks on every incoming connection - and the forking process and backend setup is a bit slow

Yes, PostgreSQL forks on all incoming connections.

So does Apache 1.3

And OpenLDAP 2.x

But forking on each connection isn't what's expensive. On Linux or BSD, forking is basically no more expensive than launching another thread. Unlike solaris or windows, where forking costs more than on linux or BSD.

Further, the biggest cost isn't in forking, it's in setting up the rest of the connection and authenticating etc...

This is why you use connection pooling for big busy sites.

However, running pgbench on my big box at work (dual 2800MHz with 2 gig ram and RAID-1 on a battery backed controller) I can execute 250 transactions per second with 250 clients in parallel. That's one transaction per second per client, including the time to connect / disconnect.

In closing, I'd like to point out that the phrase "PostgreSQL is powerful but slow" is now as outdated as the phrase "PHP is easy, but lacks object oriented features" or "Reiserfs is fast but unstable" or "apache is slow but very configurable"

Times have moved on, and PostgreSQL now hauls ass. while MySQL can still beat it or just about any other database when only a single reader is connected to it, when it comes to handling dozens or hundreds of connections, with a mixed read / write load, PostgreSQL is one of the fastest databases I've ever seen, and is easily a match for Oracle on a 2 to 4 CPU machine.

csn

What alternatives are there to forking on a Linux system (i.e. what does mysql use)? In my use, it appears that mysql creates/uses a separate process (not thread) for each connection.

Sxooter

MySQL uses threads. The problem with threads is that different OSes have different libraries that aren't always compatible. while the threading models are becoming more and more compatible with each other, small problems still crop up. MySQL had a bug on BSD a while back that caused it to crash that was related to the BSD threading model (I believe this was on FreeBSD, but I'm not sure...)

MySQL uses threads on Linux. On Linux, the difference between threads and processes on Linux is a single bit that tells the OS how to handle them. Actual difference in speed between forking and creating a new child thread is almost nothing on Linux.

The advantage of a multi-process based design is that any single crashed backend process can only corrupt the shared memory segment, nothing more. In a threaded environment, a single crashed thread generally kills the whole process, i.e. the whole database goes down.

PostgreSQL is built for reliability and correctness first, and no one has shown a compelling case of a threaded version being faster, and threaded versions HAVE been built. Many of the earlier 7.0 through 7.2 ports to windows included a multi-thread design, and while they were quite fast on windows, compared to the same load running on the process oriented version on Linux, they were no faster.

csn

Makes sense to me 🙂.

Is there any way (other than probably RTFMing) to tell if a program is using forking or threads?

poison

that uses Berkeley DB too. Transactions were the main reason for selecting it.

uuhm...maybe I'm having illusions...if not...I'm using transactions in MySQL for approx. 2 years...
and of course this could be just made up -> http://dev.mysql.com/doc/mysql/en/ANSI_diff_Transactions.html
but I don't think so since InnoDB exists for quite a while now and is /fully/ ACID compliant...
regards

csn

If that's true, doesn't matter (I heard mysql's implementation of transactions was incomplete - like a lot of other things). There are too many negatives to use mysql over other dbs, like postgres, berkeleydb, firebird, etc.

The data housed within Subversion repositories actually lives inside a database, specifically, a Berkeley DB Data Store. When the initial design phase of Subversion was in progress, the developers decided to use Berkeley DB for a variety of reasons, including its open-source license, transaction support, reliabil-ity, performance, API simplicity, thread-safety, support for cursors, and so on.

Berkeley DB provides real transaction support—perhaps its most powerful feature. Multiple processe aaccessing your Subversion repositories don't have to worry about accidentally clobbering each other' ddata. The isolation provided by the transaction system is such that for any given operation, the Subver-sio rrepository code sees a static view of the database—not a database that is constantly changing at th hhand of some other process—and can make decisions based on that view. If the decision made happen tto conflict with what another process is doing, the entire operation is rolled back as if it never happened aand Subversion gracefully retries the operation against a new, updated (and yet still static) view of th ddatabase.

Another great feature of Berkeley DB is hot backups—the ability to backup the database environmen wwithout taking it “offline”. We'll discuss how to backup your repository in the section called Reposi- ttory Backup”, but the benefits of being able to make fully functional copies of your repositories with-ou aany downtime should be obvious.

Berkeley DB is also a very reliable database system. Subversion uses Berkeley DB's logging facilities wwhich means that the database first writes to on-disk logfiles a description of any modifications it i aabout to make, and then makes the modification itself. This is to ensure that if anything goes wrong, th ddatabase system can back up to a previous checkpoint—a location in the logfiles known not to be cor-rupt aand replay transactions until the data is restored to a usable state. See the section called Manag- iing Disk Space” for more about Berkeley DB logfiles.

But every rose has its thorn, and so we must note some known limitations of Berkeley DB. First, Berke-le DDB environments are not portable. You cannot simply copy a Subversion repository that was create oon a Unix system onto a Windows system and expect it to work. While much of the Berkeley D ddatabase format is architecture independent, there are other aspects of the environment that are not. Sec-ondly SSubversion uses Berkeley DB in a way that will not operate on Windows 95/98 systems—if yo nneed to house a repository on a Windows machine, stick with Windows 2000 or Windows XP. Finally yyou should never keep a Subversion repository on a network share. While Berkeley DB promises to be-hav ccorrectly on network shares that meet a particular set of specifications, almost no known shares ac-tuall mmeet all those specifications.

Sxooter

Originally posted by poison
uuhm...maybe I'm having illusions...if not...I'm using transactions in MySQL for approx. 2 years...
and of course this could be just made up -> http://dev.mysql.com/doc/mysql/en/ANSI_diff_Transactions.html
but I don't think so since InnoDB exists for quite a while now and is /fully/ ACID compliant...
regards

Two points. One, he wasn't saying MySQL DIDN'T Have transactions, he was saying he chose subversion because it did.

Second, innodb table types can't be fully ACID compliant until such time as MySQL supports data constraints. The C in ACID stands for consistency, and while pk/fk relationships define part of the C there, contraints define the rest, and MySQL doesn't have them at all. I.e. if I'm making a database, I might make a constraint that says that the sum total of three fields in a row have to add up to 100 or you can't change the row. In any database but MySQL this is possible to do in the database. In MySQL, I can have no such constraints.

There are an unlimited number of possible data constraints one might put on a database, and MySQL cannot support any but the unique constraint. If MySQL didn't support the unique constraint would you still consider it ACID compliant? No, of course not. And lacking generic constraints means it doesn't quite qualify yet. But it is getting closer every day, so who knows, maybe in 5.0 they'll have constraints in it.

Sxooter

For more info on the database by Matt Dillon I mentioned in an earlier post, look here:

http://www.backplane.com/faq.html

It's still very much in pre-alpha development, but it's got a great developer working on it, and some great ideas built in from the ground up. For instance transactions and clustering are part of the native design, so you don't have to wonder how to add them or make them work.

csn

So many db's, so little time 😉.

Sxooter - are you familiar with sqllite (which will be shipping with PHP5) at all? Think it will displace mysql use much?

Sxooter

I would SQL Lite is the perfect replacement for MySQL as a content management store. Very easy to use, very fast, designed for one writer at a time (i.e. NOT a heavy lifter like firebird or pgsql) and it works well and has friendlier licensing than MySQL does for commercial uses.