Slashdot Mirror

Try MySQL 5.6 when that's released: 90,000 read transactions per second... The preview from back in April was already faster than 80,000 per second and that's without the later performance improvements.

Then there's MySQL Cluster:

MySQL Cluster 7.2 achieves 4.3BN reads per minute, 72 million per second
MySQL Cluster 7.2.7 achieves 1BN update transactions per minute, 19.5 million per second

And that's not a toy or unproven database, it's what a lot of mobile phone systems use and has been around in the MySQL server form since 2004, with a long history before that.

Oracle still owns Innobase Oy and the InnoDB technology.

The third party engines that people mostly are interested in (PBXT, for example) also offer ACID transactions and foreign key constraints, so even without InnoDB, MySQL wouldn't be completely useless.

Unfortunately CHECK constraints still aren't in the picture, and even InnoDB lacks niceties such as deferred checking.

Oracle still owns Innobase Oy and the InnoDB technology.

The third party engines that people mostly are interested in (PBXT, for example) also offer ACID transactions and foreign key constraints, so even without InnoDB, MySQL wouldn't be completely useless.

Unfortunately CHECK constraints still aren't in the picture, and even InnoDB lacks niceties such as deferred checking.

Reread it again. It's GPL.

InnoDB plugin:

And I quote:

"The built-in InnoDB storage engine distributed with MySQL is licensed to end users exclusively by Sun/MySQL under contract with Innobase as part of the MySQL database server (both Enterprise and Community editions) under the GNU Public License (GPL) V2. The InnoDB Plugin is also available, from this website, in open source under GPL V2 terms."

So I can download the plugin directly from the InnoDB website, under the GPLv2 terms (as long as I also adhere the GPL). They also mention they license it to Sun under the GPL.

InnoDB:

http://www.innodb.com/products/innodb/license/

"Innobase Oy provides InnoDB exclusively to Sun/MySQL, which distributes and supports InnoDB within its product offerings. InnoDB is included under the open source GNU Public License (GPL) V2 in the MySQL Enterprise Server and is suitable for a broad range of users. The MySQL Community Edition, which is likewise is available in open source under the terms of the GPLv2, also includes InnoDB."

So I can download MySQL Community Edition from Sun, under the terms of the GPLv2, and use it, under the terms of GPLv2.

I'm not missing anything. Both InnoDB and InnoDB Plugin is GPLed. You can fork it. Please read it again.

Oracle owns the InnoDB engine. That could REALLY gum up the idea of forking.

InnoDB is GPL

Well, this could be something to breath life into MySQL which seemed to be in dire straits recently. After Oracle's low blows removed both of transactional engines, it looks like everyone is abandoning MySQL for Postgres.

There was no doubt what Oracle did was predatory, but if MySQL didn't use commercial InnoBase component they would not have had any issues.

Lets hope many contributors and users dump MySQL and go to PostgreSQL. I have used PostgreSQL, MySQL, and Oracle. I often thought PostgreSQL was underrated and suspect those switching will be happy.

You're missing my point entirely. I was pointing out that MySQL AB is one of Innobase's customers. MySQL AB is also their largest customer, which means the company has at least a little bit of input into what direction that InnoDB gets developed into. Why isn't MySQL AB pushing Innobase to add this as a new feature, especially considering that it would probably cost the company nothing to do so? I was implying in no way that MySQL AB should fork the development of InnoDB. Considering that MySQL AB is paying to be able to dual license InnoDB under both the GPL and a commercial license, that doesn't even seem like a possibility to begin with. Further more, odds are that I can't pressure Innobase to do anything. Just check out the licensing page on Innobase's website.

As for whether or not I'm a paying customer, the answer is not yet :). But I do have a great deal of code deployed with some of my company's clients that uses MySQL as a backend. We can quite easily become worth around $10k a year to MySQL AB if we need help with a problem we just can't solve.

Localman,

Innobase Oy still does all the InnoDB Hot Backup sales through our website.

Do not be afraid to order :).

Best regards,

Heikki

Aah, but InnoDB is dual licensed, just like MySQL:

If you want to combine MySQL and InnoDB to a product which you distribute, and which is not open source, or for which the user has to pay a fee to you, you have to purchase a commercial license from MySQL AB.

So MySQL AB could fork off InnoDB, but they'd then have to change their own licensing model for MySQL, moving it from dual license to plain ol' GPL as well -- which completely wrecks the scheme they've built their business on. Or they're back to having to pay Oracle for a commercial license.

Not yet apparently, according to http://www.innodb.com/todo.php, but at least it looks like progress is being made.

In progress: Add FULLTEXT indexes on InnoDB tables. A sponsor for this project has been found, and a developer has been hired. Appears probably in 2006.

Why does this stuff always get propagated? There are several table types in MySQL. If you want ACID, use InnoDB and not the default MyISAM:

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

Also http://www.innodb.com/index.php

It's funny that the InnoDB tables have been available to MySQL users since the last millennium - but you people still don't have a clue.

InnoDB
fast and ACID compliant.

For our InnoDB stuff, InnoDB Hot Backup (http://www.innodb.com/order.php) was the only thing I could find that seemed to fit the bill.

It's not perfect (it does lock the tables for a few seconds at the tail end of the backup), and it costs money... but I guess it all depends on how important that consistent backup is for you.

We actually ended up doing both a hot backup and a replicated copy to another server. We use mysqldump on the replicated copy to produce a consistent dump file that can be used in the event that anything happens to our binary backup. It's also nice having a server we can quickly failover to in case the core one blows up.

If you use foreign key constraints you can't dump and restore tables unless you do so in a very specific order.

This has been fixed. You can dump them in any order using mysqldump, and then when re-importing them temporarily disable the foreign key checks with SET FOREIGN_KEY_CHECKS=0;. It's documented here

Honestly, I wouldn't want to run a site like Slashdot on MySQL, but for smaller projects it seems useful.

Slashdot runs on MySQL.

JP

No, InnoDB is multiversioning, similar to Oracle and PostgreSQL. That means that it uses non-locking reads, assuming you are running in the default isolation level of REPEATABLE READ.

The reason this is possible is due to the fact that whenever anyone goes to write a row, it creates an entire copy of the affected row, where the old transaction sees the old copy of the row while the new transactions will see the new copy.

Keep in mind this can change if you set a different isolation level. For example SERIALIZABLE runs in the highest isolation level, so it needs to set more locks than the other levels. There is more info available here and here if you want to read more.

No, InnoDB is multiversioning, similar to Oracle and PostgreSQL. That means that it uses non-locking reads, assuming you are running in the default isolation level of REPEATABLE READ.

The reason this is possible is due to the fact that whenever anyone goes to write a row, it creates an entire copy of the affected row, where the old transaction sees the old copy of the row while the new transactions will see the new copy.

Keep in mind this can change if you set a different isolation level. For example SERIALIZABLE runs in the highest isolation level, so it needs to set more locks than the other levels. There is more info available here and here if you want to read more.

• CLUSTERING IN TUNE WITH APACHE AND MYSQL (Free registration might be required. Also see Emic Application Cluster for MySQL
• InnoDB Hot Backup (with point in time backup)

MySQL/InnoDB-4.0.1 and Oracle 9i win the database server benchmark of PC Magazine and eWEEK. February 27, 2002 - In the benchmark eWEEK measured the performance of an e-commerce application on leading commercial databases IBM DB2, Oracle, MS SQL Server, Sybase ASE, and MySQL/InnoDB. The application server in the test was BEA WebLogic. The operating system was Windows 2000 Advanced Server running on a 4-way Hewlett-Packard Xeon server with 2 GB RAM and 24 Ultra3 SCSI hard drives.

eWEEK writes: "Of the five databases we tested, only Oracle9i and MySQL were able to run our Nile application as originally written for 8 hours without problems."

The whole story. The throughput chart.

• CLUSTERING IN TUNE WITH APACHE AND MYSQL (Free registration might be required. Also see Emic Application Cluster for MySQL
• InnoDB Hot Backup (with point in time backup)

MySQL/InnoDB-4.0.1 and Oracle 9i win the database server benchmark of PC Magazine and eWEEK. February 27, 2002 - In the benchmark eWEEK measured the performance of an e-commerce application on leading commercial databases IBM DB2, Oracle, MS SQL Server, Sybase ASE, and MySQL/InnoDB. The application server in the test was BEA WebLogic. The operating system was Windows 2000 Advanced Server running on a 4-way Hewlett-Packard Xeon server with 2 GB RAM and 24 Ultra3 SCSI hard drives.

eWEEK writes: "Of the five databases we tested, only Oracle9i and MySQL were able to run our Nile application as originally written for 8 hours without problems."

The whole story. The throughput chart.

To our knowledge, this is the first time a computer publication has published database benchmark results tested on the same hardware since PC Magazine did so in October 1993.

Two other MySQL products I found interesting (neither of which is open source at this time):

• CLUSTERING IN TUNE WITH APACHE AND MYSQL (Free registration might be required. Also see Emic Application Cluster for MySQL)
• InnoDB Hot Backup (with point in time backup)

MySQL/InnoDB-4.0.1 and Oracle 9i win the database server benchmark of PC Magazine and eWEEK. February 27, 2002 - In the benchmark eWEEK measured the performance of an e-commerce application on leading commercial databases IBM DB2, Oracle, MS SQL Server, Sybase ASE, and MySQL/InnoDB. The application server in the test was BEA WebLogic. The operating system was Windows 2000 Advanced Server running on a 4-way Hewlett-Packard Xeon server with 2 GB RAM and 24 Ultra3 SCSI hard drives.

eWEEK writes: "Of the five databases we tested, only Oracle9i and MySQL were able to run our Nile application as originally written for 8 hours without problems."

The whole story. The throughput chart.

To our knowledge, this is the first time a computer publication has published database benchmark results tested on the same hardware since PC Magazine did so in October 1993.

Two other MySQL products I found interesting (neither of which is open source at this time):

• CLUSTERING IN TUNE WITH APACHE AND MYSQL (Free registration might be required. Also see Emic Application Cluster for MySQL)
• InnoDB Hot Backup (with point in time backup)

MySQL/InnoDB-4.0.1 and Oracle 9i win the database server benchmark of PC Magazine and eWEEK. February 27, 2002 - In the benchmark eWEEK measured the performance of an e-commerce application on leading commercial databases IBM DB2, Oracle, MS SQL Server, Sybase ASE, and MySQL/InnoDB. The application server in the test was BEA WebLogic. The operating system was Windows 2000 Advanced Server running on a 4-way Hewlett-Packard Xeon server with 2 GB RAM and 24 Ultra3 SCSI hard drives.

eWEEK writes: "Of the five databases we tested, only Oracle9i and MySQL were able to run our Nile application as originally written for 8 hours without problems."

The whole story. The throughput chart.

Ever heard of InnoDB? MySQL lets you choose -- on a table-by-table basis -- exactly what parts of your application need to support transactions, foreign keys, etc.

Want to do hot backups? Then all of your tables must be transaction-aware InnoDB tables (and you have to pay for the priviledge of course). Ever perform multi-row inserts? All of the affected tables should be transaction-aware InnoDB tables. If any of your data relates to each other (*cough* relational database *cough*), you need foreign keys. Here come those InnoDB tables again. Got multiple clients modifying the data at the same time? InnoDB again.

So basically what you are saying is that InnoDB tables should be used unless the table in question has no data interaction whatsoever with any other table, should only ever have one-row-at-a-time inserts, single-user access, etc. So...ummm...why would anyone use non-InnoDB tables?

Odd, neither Slashdot nor Yahoo! Finance seem to be having corruption problems...

I don't browse Yahoo Finance so I can't comment on that site or the scope of MySQL's use. As far as Slashdot...ummmm... Are you kidding!?! You are using Slashdot as a metric for the reliability of MySQL? Slashdot is your example of 24/7 uptime and consistency? The site that's regularly down and relies on static renderings of their site to avoid advertising the outages: this is one of your great examples?

I must say that I greatly enjoyed InnoDB's benchmark page. The comparison in E-Week is conveniently listed next to the PostgreSQL comparison giving the impression that E-Week also prefers MySQL over PostgreSQL even though PostgreSQL wasn't in the review. Nice marketing indeed. That aside, I also like the PostgreSQL comparison section: only two years old after all. Neither package has changed substantially in two years have they? <sarcasm>And sure, I could be persuaded that the one test query is indicative of all normal queries one would encounter in normal database operation.</sarcasm> Could it be that perhaps a certain group went hunting for items that InnoDB was markedly better at and avoided the items in which it was weaker. Naw. Couldn't be.

And this part I love the best: not only was the hardware the same, but the tuning techniques were identical. 24MB shared memory buffers. That's it on a 512MB test server with two tables of 100,000 rows. Hunh? No, that doesn't sound like they tuned MySQL overall to the detriment of PostgreSQL. How well do you think MySQL+InnoDB would fare if the app tunings were done with PostgreSQL in mind and just mapped the same settings to MySQL+InnoDB?

Note: The shared buffer setting has little to do with the overall memory usage in a working system (and even less in this benchmark). Why? After shared buffers are filled up, the OS will start to aggressively cache filesystem access (like for example, the ever-accessed database tables). MySQL is written with this situation in mind. PostgreSQL depends more heavily on the shared buffers setting (among others). If both DBs are using all available memory -- which is likely when querying a couple of 100K-row tables -- which database do you think will perform much better with the artificial constraint of a 24MB shared buffer?

And don't start with the "harder to configure" crap. This wasn't an out-of-the-box install of MySQL+InnoDB here. Someone specially configured the 24MB buffer. It took as much effort as it would to properly configure PostgreSQL. There is a wealth of information about tuning PostgreSQL for hardware, for good database organization techniques that work for more than just PostgreSQL, and of course the main PostgreSQL technical document site. (All of this was found in less than two minutes

I prefer Postgres over MySQL, but I will point out that there is a method of using things like foreign keys and transactions in MySQL - InnoDB. I haven't used it yet, but if anyone is using MySQL and may be needing transaction processing or the like, they can look into this.

MySQL includes the fabulous InnoDB which offers foreign keys, great performance and row-level locking.

With the InnoDB back end for MySQL, it probably can. See this site

Sounds like your tuning was out of whack. You're not very specific about what you tuned (heck, if everyone was specific what fun would Slashdot be?) but I'd bet you had 2 wires crossed somewhere. We're running an system with, what would appear to be, a larger number of inserts and retrievals, for stock quotes and have had completely different results from your experience. We're very happy with the performance and find in a mixed insert/select situation InnoDB is quite a bit faster than MyISAM.

After your bad experience did you send an email to Mr. InnoDB, Heikki Tuuri? He's quite attentive and I'm sure would like to hear what happened with your application.

Not to mention when you're playing with databases or other vaguely unknown programmatic quantities, best not to test them in production. Perhaps if you had run the system in a test environment the lessened pressure would've enabled you to be better equipped to resolve whatever situation was occurring.

One of the nice features of MySQL is the capability of having pluggable persistence managers. An example of that is the default, MyISAM, which you are correct in saying does not support ACID. But with the release of MySQL-Max, which happened awhileago(tm), and MySQL v4 out of the box, support for 3 additional backends was added, BerkelyDB, Gemini, and InnoDB, all of which have complete ACID support. InnoDB also supports row level locking and even an initial implementation of foreign keys.

InnoDb is is in use here at Slashdot as well as a good deal of other sites demanding high-transaction throughput with full ACID support.

With the addition of foreign keys and stored procedures functionality, all of which are on the slate for the 4.x series, the reasons not to use MySQL are lessening every day.

Side note: Yeah, I know Gemini is the red-haired stepchild of the MySQL world. It's still a decent table manager.

One of the nice features of MySQL is the capability of having pluggable persistence managers. An example of that is the default, MyISAM, which you are correct in saying does not support ACID. But with the release of MySQL-Max, which happened awhileago(tm), and MySQL v4 out of the box, support for 3 additional backends was added, BerkelyDB, Gemini, and InnoDB, all of which have complete ACID support. InnoDB also supports row level locking and even an initial implementation of foreign keys.

InnoDb is is in use here at Slashdot as well as a good deal of other sites demanding high-transaction throughput with full ACID support.

With the addition of foreign keys and stored procedures functionality, all of which are on the slate for the 4.x series, the reasons not to use MySQL are lessening every day.

Side note: Yeah, I know Gemini is the red-haired stepchild of the MySQL world. It's still a decent table manager.

I hate to disappoint you but transactions, foreign keys and row-level locking are available and seem to work quite well.

come on, moderators, this is NOT informative.
Row-level locking is available in mysql by using a third-party table type...As are transactions, and page-level locking.

you use what you need, and for a lot of people, they really don't need to use correlated subqueries, they don't mind replicating the functionality of a stored procedure in perl or php, and they really don't need a 5-digit price tag.
MySQL's main feature seems to be its immense popular support among people who haven't used any of the alternatives.
really? so yahoo and NASA don't know about alternatives to mysql?

this is such total FUD...i really don't understand the motivation for everyone to tear down mysql based on these same objections and that damn phillip greenspun article.
can't everyone just let people use the solution that fits their requirements?

One is tied up in this contractual mess, but the other, InnoDB has no such issues, and may even be faster for many purposes.

That might be the understatement of the year. InnoDB touts itself as the "fastest disk-based database" currently on the market. It's a pretty tall-order, but it lives up to it. Our internal benchmarking tests for our application purposes show it to be about 7x faster than an identical PostgreSQL 7.1.2 solution. I've seen reports on the mailing lists that it can be up to 18x faster. You also get the simplicity and maturity of MySQL. The InnoDB benchmark page has their own benchmarks, which pretty much mirror what we've seen internally.

Of course, MySQL has other drawbacks, namely that it doesn't support triggers or table inheritance or some of the more complex nuances of standard SQL, but the 95% of stuff it does have is very fast, and the other 5% can be handled in code. MySQL isn't popular because it's open-source, though. It's popular because it's good, free, and most importantly, extremely easy and intuitive to use.

MySQL has two table types that support row-level locking and transactions. One is tied up in this contractual mess, but the other, InnoDB has no such issues, and may even be faster for many purposes.