Fishpool

Monday 27 April 2009

If MySQL's core server development and release process has been somewhat of a frustration to the userbase over the past few years, clearly another part of the ecosystem has thrived in ways which brought exciting fruit to the Expo part of this year's conference. MySQL has become a hub of innovation in both transactional and analytics databases in ways which have turned many of my concerns to enthusiasm.

I've already discussed the technologies for data analytics on MySQL, in particular Infobright's storage engine technology. This year I took the opportunity to learn a bit more about their appliance-based competitor Kickfire as well, and it certainly looks like a solid product. I still don't completely understand what the "SQL chip" in their appliance does, but certainly the combination of a special-purpose columnar storage, high-speed memory interface and high-performance indexing should form basis for a great analytics system. How it compares in practice to Infobright's software-only approach, time will tell. I'd be interested in real-world experiences, so if you have some to share, please get in touch. Finally, I missed the Calpont info myself, but once it is released, I'll try to get the time to try it out.

I'm even more excited about the new solutions on the transactional side of things. I've certainly been among the people frustrated by MySQL/InnoDB's scaling issues on modern hardware, and glad to see that the optimization work done by Google, Innobase and Percona is being accepted to the "mainline" MySQL Enterprise Server. However, what I did not expect to see were the solutions shown by Virident and Schooner for accelerated, Flash-based storage appliances. It's interesting how both of these companies have chosen to apply their platforms to accelerate both InnoDB and Memcached, and I'm looking forward to the chance to spend more time with both solutions. While both are Flash-based approaches, they seem to have taken very different architectural choices in the way they're exposing the memory to the software layer, and I'm curious to see the impact those choices have on both IO and storage capacity scaling. In any event, these are unique technologies unlike what I've seen for other platforms at this time. I need to learn how they plan to work with the community and Sun/Oracle in keeping the solutions functionally compatible with standard MySQL server.

The ecosystem doesn't end at the appliances, though. On the software side of things, I was pleasantly surprised by the state of Primebase's PBXT storage engine as well as Continuent's new Tungsten Replicator. While both are still early in their development path, they seem to hold a lot of promise for improving the performance of MySQL's built-in functionality in InnoDB as well as in the replication subsystem. Robert Hodges's demo of Tungsten's set-up and management also looked like it will greatly simplify replication administration, which is a big deal for anyone who has to manage 20+ replicated database systems. What's more, if Robert and his team crack the multi-threaded replication problem, and major scalability concern is lifted.

[Be sure to check out my earlier posts of the conference learnings as well!]

Sunday 26 April 2009

The development model for MySQL Enterprise took a big step forward with the new community process Karen Padir announced in her Tuesday keynote. This is great for both the open source server as well as enterprise customers, because the closer the tie between the community and the development path, the better the quality and faster the progress towards new functionality. I'm not entirely sure everyone at Sun still completely understands why a working community process is a benefit for the enterprise customer base, but I'm happy steps are made in the right direction, and it seems to me that Karen Padir is going to be a good leader for the product.

A big improvement, for sure, and still there's more to improve here. To borrow the words of Baron Schwartz, MySQL currently "has" a community, while it would really be in everyone's benefit if instead MySQL would "be" a community. I would suggest that the goal should be not monthly "community" releases from Sun, but a completely out-in-the-open development process with the community members being on the driving seat regarding patch acceptance, quality management and releases, much like the Fedora process works. Sure, there's a role for corporate sponsorship and project management, but it's a distinct difference of responsibility. The Drizzle project is another good example of how this can work. An important point to realize here is that there is a difference between the community, an active partner in the process of making the software better, and the unpaid userbase. The latter is an acquisition and conversion vehicle for the former, but they're separate entities.

The announcement of the 5.4 server was at the same time an encouraging as well as confusing example of the changes. I would like to be enthuastic about it, but we've seen MySQL (if not Sun) announce pre-announce releases that didn't appear before, and it's a long way to the promised release time. I asked two questions from many, many MySQL staff members during the week: why is it that 5.4 was announced now, but is slated to be released GA only in December when it clearly demonstrates massive scalability improvements already, and why is it that the feature list for the final 5.4 release is much longer than what's already completed? I did not get a really coherent answer from anyone. Best I could decipher, there is somewhere a faceless "marketing" which decided that a) there should only be one release announced and b) 40% demonstrated improvement is not good enough when it's not the only improvement that can be made. I also learned that it's not unlikely that much of the work which has gone to 5.4.0-beta would be backported to the 5.1 branch and released in a 5.1 point release before the actual 5.4 release, because in fact they can be considered bugfixes.

I consider myself not an entirely unexperienced in the decision processes for release management, and know intimately the clarity hindsight provides to well-intentioned choices made with best available information. I know there are many areas to consider, and every decision made is a compromise. I still can't bring myself to completely understand what exactly led to this particular approach. Lets recap:

Improvements already made are announced and made available in beta test form, but beta does not contain everything planned for the release
Final release is intentionally delayed by 7 months adding significant project risk to it, despite having no previously committed release schedule
Former release version is planned to by improved by making significant performance-altering changes in a point release in order to offset the delay
Such a release adds risk to maintenance roadmap and steals away upgrade motivation from the upcoming version

How this plan serves either Sun, the community, the free userbase or the enterprise customers is a mystery to me. It would certainly seem far simpler and clearer to take an aggressive quality assurance and release testing position with the intent to push 5.4 out as a rock-solid replacement upgrade to 5.1 as soon as possible, and only then continue with further updates as a 5.5 release. This would definitely be welcomed by everyone but the class of enterprise customers who like to hear about future versions two years in advance - but keep in mind that such conservative enterprises are not MySQL's primary customer base anyway, and if MySQL is to make inroads there, rapidly improving the quality and performance of the product in the meantime would still be a sensible step.

There is the argument that if I want to get those performance features now, I can use Percona/XtraDB or MySQL 5.1 plus the InnoDB Plugin. While technically that route does work, and clearly is worth pursuing as a user, it does have its drawbacks in terms of requiring multiple sources and it's hard to see how it supports MySQL/Sun's commercial interests, the latter surely having been a consideration in the 5.4 release plans.

Thus far in the argument I have ignored one new component - Oracle. That's because to my understanding the process I've discussed did not consider the acquisition, which was unknown to most people before Monday. Clearly this changes a few points. It's not necessarily in the interests of Oracle for MySQL to continue making inroads to enterprise customers, though if someone's going to be cannibalizing Oracle's database sales, it might as well be Oracle. InnoDB Plugin will also be a product from the same company as MySQL Server in the near future - in fact, in a future likely to be fact before the final GA release of MySQL 5.4. What is the role of a delayed 5.4 release in this equation, then?

By Osma on Sunday 26 April 2009, 07:27

This was an interesting week for sure. Of course, we all know it started with a bit of a shock news, but that's not nearly the most interesting bit about the conference. I'm posting a series of cleaned-up notes and opinions about what I saw there as I finish them. Will also try to link to further information where I've seen good notes. Please leave more links in the comments if you have any!

My own presentation materials and the extended articles on data domains and log processing
MySQL 2009-2010 roadmap
Database innovation on MySQL
Community outlook for MySQL

Thursday 23 April 2009

My MySQL Conference presentation on Tuesday discussed my practical findings on how Infobright's technology works in developing a MySQL-based data warehouse. I also touched on a more high-level question of how to select a technology for a different kinds of data-related problem areas, and this article expands on that discussion.

Wednesday 22 April 2009

Completed my MySQL Conference presentation 45 minutes ago. Seemed to go over ok, got some followup questions. Trouble is, I got hit by amazing jetlag half an hour before the session, and almost fell asleep myself during the presentation. Fortunately, survived that anyway, and as far as I could see, was the only one having problems staying awake. Below is an embedded version of the slides, which should also appear on the conference proceedings site later. Now for a beer at the expo. Will blog with more description of the stuff later (update: see this follow-up article).

Mining for insight - Upload a doc

Tuesday 21 April 2009

So, waking up this morning to prepare for the first day of the MySQL Conference, the first news I pick up is that it's now Oracle Conference instead. Been speaking with a few people through the day and confusion as to how this is going to impact the company, development community, users or customers reigns. Personally, I'm more apprehensive than excited about it at this point - but frankly, I've spent more time thinking of how to apply MapReduce to large-scale ETL processes than about the acquisition today.

However, I'll keep digesting this for a while, and hopefully after a couple of days of discussing it with people here I can form a better opinion. I don't know Oracle that well - it never seemed like a very easily approachable company to me, and what connection I've had with the technology its felt a bit baroque and legacy, but I haven't even looked at it in a few years. It is interesting though - Oracle's acquisition of InnoDB a couple of years back was certainly one reason why there's today a number of development projects for other transactional storage engines for MySQL. Sun has been an active corporate sponsor to a number of such projects, and it doesn't seem very likely that Oracle would want to continue that. Dunno.

Wednesday 8 April 2009

Apart from the primary "here's how we ended up using Infobright for data warehousing and how is that working out" topic I'm going to discuss in my MySQL Conf presentation I'll touch on another application, the use of Infobright's open-source Community Edition server for collection and storage of event logs. This is a system we've implemented in the past couple of months to solve a number of data management problems that were gradually becoming problematic for our infrastructure.

We've traditionally stored structured event log data in databases for ease of management. Since Habbo uses MySQL for most everything else, putting the log tables in the same databases was pretty natural. However, there are significant problems to this approach:

MyISAM tables suffer from concurrency issues and crash-recovery is very slow due to table consistency check
InnoDB tables suffer from I/O bottlenecks and crash-recovery is very slow due to rollback segment processing
Both scale badly to hundreds of millions of rows (especially if indexing is required), and mixing them is not a recommended practice
Storage becomes an issue over time, especially as indexes can easily require many times as much disk than the data, and an event log is going to have a LOT of rows
Partitioning has only recently become available, and before that, managing "archive" tables needed manual effort
Perhaps worst of all (as it's very hard to measure), if any of this is happening on the primary DB servers, it's competing for buffer pool memory with transactional tables, thus slowing down everything due to cache misses

Over the years, we've tackled these issues in many ways. However, with our initial experience of scaling an Infobright installation for data warehousing needs, a pretty simple solution became apparent, and we rapidly implemented an asynchronous, buffered mechanism to stream data into an ICE database. We're early with this implementation, but it has turned out to be a satisfactory high-performance solution. Even better, it's a very simple thing to implement, even in a clustered service spanning many hosts, as long as log tables don't need to be guaranteed 100% complete or up-to-date to the last second. Here's a description of the simple solution; extending that to the complex solution providing those guarantees is left as an exercise to the reader.

Rather than running single INSERTs to a log table or writing lines to a text file log, each server buffers a small set of events, eg for the past second in a memory buffer. These are then sent over a message bus or lightweight RPC call to a log server, which writes them to a log file that is closed and switched to a new file after every megarow or every few minutes, whichever is smaller. A second process running on this log server wakes up periodically and loads each of these files (minus the last one, which is still being written to) into the database with LOAD DATA INFILE.

This has multiple general benefits:

Buffered messaging requires much less time on the "client" servers compared to managing database connections and executing thousands of small transactions
The asynchronous processing ensures database contention can not produce random delays to the normal service operation
Batch loading of text files is implemented by every DB server, so there's little in this implementation that is proprietary or dependent on any particular DB solution

Using the Infobright ICE as the backend database provides a number of additional specific benefits:

Excellent data load performance
No index management, yet capability to run queries on the data without first extracting it to another DB
No degradation of performance as deployment size grows, as would happen even to a MyISAM table should it have any indexes
Compressed storage, so less spinning iron required
Columnar datapack organization should not require table partitioning even over long periods

This works very well for structured events. For unstructured data, a different solution is required, which I will discuss at some later date.

Update: Mark Callaghan asked in the comments for some quantified details. We have not spent the time to produce repeatable benchmarks, so all I can offer on that front is anecdotal data - it's very conclusive for us, given it's addressing our real concerns, but less so for others. That said, ICE does not support inserts, only batch loads, so the solution had to be engineered to use that, which added some complexity, but brought orders of magnitude more performance. A simple benchmark run showed that the end-to-end performance for this exceeded 100,000 events per second when running all parts of the client-logserver-database chain on a single desktop machine.

Query performance depends on the queries made. Summary data is 2-3 orders of magnitude faster to access, the bigger the dataset, the bigger the performance benefit - but expecting that for single row accesses would disappoint badly. Storage compression varies wildly depending on the data in question -- we've seen up to 15:1 compression on some real-world data sets, but others (such as storing email addresses in a varchar column) actually expand on storage. This is why I think of this as a solution for structured, quantified event logs, not for general unstructured log file storage.

Saturday 21 March 2009

I'm going to be talking in MySQL Conf 2009 about our business intelligence and data warehousing solutions for Habbo. Since this blog is syndicated on Planet MySQL, and I presume many of the people going to the conference read it, here's a question: would you like to hear about the why's of our technology selection (eg, IT management level questions), the techniques we use for analysis (for the BI analyst or startup technologist), or about the nuts and bolts of the database implementation itself (for the DBAs in the crowd)? I'm going to be touching on all three aspects, perhaps more, but can and will focus on one of the areas in more detail.

Sunday 28 December 2008

2008 is nearly over, and it's time to take a look at what happened over the year, as well as to take a peek at the the coming 2009. A year ago I made a guess that social networking services would open up and start sharing their profiles – well, practically everyone but Facebook are doing some of that, and Facebook is trying to get everyone to depend on them – not that “create dependency” isn't a part of Google's and MySpace's plan, too. Unfortunately, we haven't yet found a meaningful way for Habbo to participate in this festival, due to differences in demographies, interest areas, and the priority of running a profitable business, instead. Still looking for that solution, though.

I also guessed that productivity applications would seriously move to the cloud – and was a bit too optimistic on that one. Sure, the applications are there, but I don't really see any of them having replaced the desktop-based counterparts – nor do I see that happening next year, either. People are, rightly so, focused somewhere else, and while over the long run moving off to the cloud will make sense from both productivity and cost standpoint, it's still too much of a jump, and too expensive to make.

The increasing popularity of netbooks, Internet access via 3G networks, etc, will have an impact on that, though. Perhaps we'll all move out to the net in a completely different way: not via our old productivity apps, but via entirely new class of applications. Something else than Facebook and Twitter though, I hope.

What else? MySQL was acquired by Sun, and we're all still waiting for the next step. The Register (I can't believe I keep reading it) has somehow gotten the impression that Sun has slowed MySQL down – nah, it's been this slow for at least three times that long. Fortunately, the acquisition may have been a catalyst for the MySQL developer community to start doing something else instead of waiting, and I'm really looking forward to the improvements Percona and Drizzle are making to keep MySQL competitive. As for Sun – time to stop confusing a good thing with dubious business models and bad release engineering before you lose all your customers, I'd say. At the same time, I'm also super-interested in the stuff Sun is doing on the hardware side of database storage with SSD-optimized solutions. Can't say I paid much attention to Sun there for a while, but they're making what seems like an unlikely comeback.

For Habbo, we've continued making progress on the track chosen late 2007 – revolutionary changes made incrementally. Biggest one this year, the free second currency of Pixels, was just launched a month ago. Several improvements are coming up for that, of course, and a whole lot of other stuff is in the works, or at least being thought of. We're trying not to hold anything longer than it absolutely needs to, so everything radically new continues to be launched sort-of unfinished and get improved along the way. It just ends up being so much better that way, as the feedback makes a significant contribution to the overall design.

This a weird time. The world is reeling from what indeed may be the worst economic crisis in 75 years (though I'm not well versed enough in history to be able to tell myself), and still (or because of it?), opportunities lie all around, ignored by most. It's never easy to tell which direction is most promising, but now I'm finding it incredibly hard to choose and prioritize between the possible things to focus on. Still, 2009 is definitely going to be a year to really focus on even fewer things than usual, and really kick ass on those.

If you made it this far, thanks for reading. I wish you a great year 2009, whatever it is you're doing.

Tuesday 2 December 2008

So MySQL 5.1 is declared GA, contains enough bugs to make Monty rail against it publically, doesn't (as far as I've been able to gather) have a stable cross-release pluggable storage engine ABI (despite that being a major release feature -- meaning you have to have new plugin releases for each MySQL server release before upgrading anything), and continues the madness of less frequent community releases and less stable enterprise releases against the wishes of every sensible enterprise customer and (I would guess) every community member.

I know enough about running MySQL to know that Monty's comments don't necessarily reflect much on the practicalities of someone trying to run something using MySQL rather than developing it, but the rest of the noise about this release definitely makes me want to seriously reconsider a policy of using MySQL Enterprise version and look at a real community-released version instead for production use.. There's just a "small" question of figuring out which community..

Fishpool

Tag - MySQL

Database innovation on MySQL

MySQL 2009-2010 roadmap

Recap of MySQL Conference 2009

Three domains of data

Mining for insight - presentation materials

Interesting start to MySQL Conf

Using the Infobright Community Edition for event log storage

What would you like to hear from me in MySQL Conf?

A year-end review

MySQL 5.1 continues community release madness

Search

Subscribe to this blog

Last entries