The latest from the MySQL community

Several days ago MySQL AB made new storage engine Falcon available for wide auditory. We cannot miss this event and executed several benchmarks to see how Falcon performs in comparison to InnoDB and MyISAM.
The second goal of benchmark was a popular myth that MyISAM is faster than InnoDB in reads, as InnoDB is transactional, supports Foreign Key and has an operational overhead. As you will see it is not always true.

For benchmarks I used our PHPTestSuite which allows to test wide range tables and queries.
The script and instruction are available here:
http://www.mysqlperformanceblog.com/files/benchmarks/phptestsuite.stable.tar.gz

We used table "normal" table structure which corresponds to typical structure you would see in OLTP or Web applications - medium size rows, auto increment primary key …

Today I set up a mysqld connected to my not-quite-HA cluster at home here to replicate from my MythTV database into cluster. The idea behind this is to eat an increasing amount of my own dogfood around the house.

To do this, I also set up the MySQL Instance Manager to manage the now multiple instances of MySQL Servers on a box here. I found it a pain to do, it should be a lot simpler, but isn’t. At least now things are going okay…. but the feature wish list I have is rather long (perhaps I should hack some stuff up in this “spare time” i’ve been hearing so much about).

I’m also about 10 minutes (or however long the build takes) off moving one of the data nodes off the machine so it will be a real 2 node system (but I still have to move the management server to a third machine to have any real HA… and I have a PowerPC machine marked for that, I just have to await some patches to make it work :)

Currently …

I've made the 1.0 release I promised yesterday. I am very happy to declare innotop production-ready and stable. In this article I talk a little bit about my plans for the future, and look back to the project's humble beginnings as a script I ran inside watch. Check out the screenshot! OMG!

My main development work of November/December is still under review by Sergei. Just working on fixing bugs and right now looking at a facinating bug in the Heap/Memory storage engine...

Everyone knows the Internet Movie Database, and I wanted to use their public data as a large data set to create some complex queries. The MySQL world database is excellent to teach SQL basics, but not sufficient to teach performance tuning or real life queries.

Since the data comes in a bunch of clumsy text files, it is not easy to parse and import them. Fortunately, this work was done by some German guys. All you need, is their JMDB software, a java environment, of course a MySQL database and... lots of time.

They claim that it's possible to load all the data within 2 hours, but it took my little laptop almost a day. After the import procedure, you end up with a database called "jmdb" and a nice number of huge tables. In total, it's more than 20 million rows in 44 tables.

It's all MyISAM tables, which is fast enough as the import doesn't run parallel queries. And for reading, MyISAM will also be the best option, …

Its been a while since I have posted on my blog. I should at least note that I will be speaking at MySQL User Conf in Santa Clara as well as php|tek in Chicago. Aside from that I am working a little bit on improving Doctrine a very promising DBAL/ORM based on PDO for PHP. Anyways lets get back to the original topic which has nothing to do with PHP or databases directly, but might also be of concern to all of you still. Since I am now in the consulting business I am spending a lot of time working at client premises using my laptop. I really miss an external monitor though. I find it highly unproductive to alt+tab between my editor and the browser or some documentation all the time. …

I read through a lot of blogs each day in a variety of technology categories. I've always fashioned myself as a jack-of-all-trades and that helps me a great deal with my current position. However, it does mean consuming a lot of feeds to try and stay up in all the areas I have a profound interest in. Here are my shared feeds on share.opml.org.

Google Reader has a nice feature where I can share items I find interesting. There are a ton of good blog posts each day, so I've started marking them to be shared. There are two ways to view these shared items: one is the web page and the …

My fellow MySQLers who I have chatted with recently on IRC, know how much the AUTO-INC table lock in MySQL has been bugging me. Whenever my server gets an increased number of concurrent INSERTs, I start experiencing thrashing.

Yesterday night I spent all night trying to find the bottleneck within my system which happened to be none other than disk bound.

Everyone knows that InnoDB is excellent for its concurrency, thanks to its row level lock granularity. What comes as a surprise, to some, is that if you happen to use an AUTO INCREMENT column, then InnoDB needs to acquire a special, table level, AUTO-INC lock.

Everything was fine until I started finding many threads in InnoDB queue waiting to acquire this lock.

iostat -xnz 5 reported disk busy at 100% most of the times.

…

Recently, I found out that I'll be speaking at the upcoming MySQL Conference in Santa Clara, California. Last year, it was a lot of fun and the conference was very successful. I can't wait to see everyone there again.

A couple of weeks ago I submitted a request to open a new project on Sourceforge for the innotop MySQL and InnoDB monitor. I want to make it easier for others to collaborate, especially package maintainers. Yesterday I got word of its approval. I have done a quick-and-dirty import of the source code into its new home, and I'm now continuing work on the next major version, which I've been working on for about six weeks. This post is about Sourceforge, what I've gotten done, and also to ask for your help.