Friday, January 22, 2010

5.1 + new InnoDB Plugin

If you're wondering what MySQL 5.1 with the new InnoDB Plugin is all about you'll want to tune into the webinar I am hosting next Tuesday. I'll cover the why, what and how behind the immediate performance and scale improvements that can be had, especially on modern (> 4 CPU) servers, by enabling the new plugin in MySQL 5.1. Learn more and get registered, it should be good time!

You can also take it to a deeper level by joining Brian Miezejewski from our PS team on 3/9 for a practical guide to using and tuning the new plugin features to improve performance and scale. Hope you can join us!

Thursday, January 14, 2010

One reason a no indexes approach is nice

Like a lot of you, I’ve been following with interest Percona’s testing of the open source column databases. One thing I think is pretty cool about some column databases that work with MySQL is that they don’t require you to create indexes. The reason is, in general, the column is the index. Not having to create indexes is nice because lots of indexes can really bog down a database if you’ve got a lot of load or DML activities because the indexes have to be maintained for all data input and alterations.

In Percona’s test, they showed the load time for all the different databases, but I noticed that the times didn’t include the index creation for LucidDB or MonetDB. I decided to follow Vadim’s link on the LucidDB index creation and totaled up the time it took to create the indexes. For the index and statistics times, it was 384,314 seconds and when you add that to the 140,736 seconds for the table load, you get 6 days just to create the database. That’s quite a difference from the 6 hours for InfiniDB and 14 hours for InfoBright, both of which don’t need or use indexes.

I’m sure indexes supply a benefit for some column DB’s in various use cases, but if the database was real dynamic and required a lot of new objects with indexes be added, continuous heavy loads, or DML, it would seem that indexes could really put a ding in things. In that case, it would seem column DB’s thatdon’t require indexes could have an edge there.