Can We Scale?
Before I get going I would like to beg MySQL AB/Sun to not get upset. Call it constructive criticism.
I was laying in bed last night thinking. I should have been sleeping, but I was thinking about how MySQL could create a better product. Not for version 6.0 but post-6.0. What would it really take for our little dolphin to be the number one database product in the world — not just the open-source world.
I was thinking about how in 2001 I worked at a little company called Netzero. In addition to the other 800 servers that our team of system administrators had to manage we had two large Sun servers. I don’t recall the model anymore but they were the equivalent of a modern Sunfire and could hold up to 64 gigabytes of RAM and 64 (Sparc) CPUs. They were each the size of a refrigerator and one was located on the east coast of the US and one on the west coast of the US. They ran Solaris and Oracle. It was our billing application. Each of these servers at the time had 32 gigs of RAM and 32 CPUs. I have no clue how much the hardware and software cost but I am certain the pricetag was over one million dollars (USD).
Fast forward seven years. Today I work at iContact (remember kids..the views and opinions expressed in this blog are mine and mine alone. They are not the views and opinions of my employers. Although they should be :)). We have several servers that each have two cpu chips with four cores on each chip. A total of eight cores. And eight gigs of RAM. This is not all that uncommon these days. The premium over dual-core chips was a couple of hundred dollars. The problem is that MySQL doesn’t really utilize all these cores. And, if a server has a lot of memory, it wouldn’t really utilize all of it. I know “a lot of memory” is vague and to be more precise I believe it doesn’t really use more than 64 gigs. I read that “limit” somewhere but do not quote me on it. In 2001 32 gigs was a tremendous amount of memory. Today it isn’t so much. I recently looked at a Sun server that ran either Solaris or Linux and could support up to 32 cores and 256 gigs of RAM. For an entry price (not fully loaded of course) of about $25,000.
Now, peer into your crystal ball a little. In five more years it is a fairly safe bet that you can, for a reasonable cost, buy a CPU with sixteen cores. Take a server board with two CPU slots and bingo..you have 32 cores. Now throw on 64 or 128 gigs of RAM and you have a very nice server capable of some serious processing.
This isn’t fantasy. It is what is coming down the road. I want my database program to be able to utilize a setup like this. I don’t know what it takes to parallelize queries (as much as is possible). I don’t know what it takes to break a complex query up and run it over multiple threads/cores. I don’t know what it takes to have the daemon utilize multiple cores (I am not sure it is even possible). Maybe you have to have multiple daemons running on different cores communicating between each other. I know it isn’t trivial. It probably is extremely difficult. I would guess that it is going to require a fundamental restructuring of the inner workings of MySQL. Just googling “parallel dbms” and reading some of the academic papers is kind of mind-boggling. The one thing I do know is: IT HAS TO BE DONE!
Wishing it were different won’t change it. Intel and AMD are both committed to building up the number of cores and not the megahertz. If MySQL can’t scale with the cpu count down the road it will become irrelevant. On the other hand, MySQL has the opportunity to become a market leader by building a product that is truly revolutionary. Why should a DBMS that has a price tag in the hundreds of thousands of dollars be the only one that can really utilize many many cores?
I would think that the RAM utilization issue is much simpler to resolve. I really don’t know what the issue is here so I am speaking completely off the top of my head. Probably means I am incorrect. Just remember, as time goes on, higher amounts of RAM are going to become much more common. While 16 and 32 gigs of RAM in a server are fairly common now, 64 gigs and more will be common in just a few years.
I am not going to go into any depth here, and I don’t want to start any flame-wars, but sometimes I think we get stuck on only using scale-out instead of scale-up. We scale out, not because it is cheaper (it can be argued that it isn’t really in terms of hardware footprint, energy and hosting costs and system/db admin costs) but it is easier (or seems easier). And that is OK. Scaling out makes perfect sense in many situations and if you are looking at scaling your application I would recommend that you at least understand how to scale out. As a good reference I would recommend the upcoming second edition of High Performance MySQL as they have several chapters devoted to these issues. It is going to be released in April at the MySQL Users Conference. When the discussion turns to scaling up it is almost taboo. Maybe it is because in the past MySQL didn’t scale up very well. If this trend continues and some of these “log jams” aren’t resolved companies will switch to other products that better utilize modern hardware. They can’t buy equipment and only have it 10% utilized. It doesn’t make financial sense.
Interesting that in the descriptions I see of the Falcon engine it is described as being built for 64-bit hardware/software with multiple CPUs. I don’t know how much of a difference the storage engine can make. I would think that the upper layers of the MySQL software are just as important to be built with support for multiple (16/32/64) cpus and oodles of RAM.
Obviously I glossed over a major part of the equation (I/O). You can build a great system with all this and be hobbled by your I/O. For the sake of the keeping this post below the length of War of Peace I am going to skip this part. However, it is one very good reason to scale out - not up, as extremely fast I/O can be very expensive and this doesn’t really look to change in (near) future.
It’s fun to want new features. Some of them are even very important. But to me if MySQL Server can be structured to take advantage of the cheap CPU and RAM power that will be available in the near future it will be really be quite a feat. And one that will be sure to help accelerate it’s growth. Why should Oracle have the title of the most throughput on one server (at least I think it is them…even if it isn’t .. it serves the point)?
14 Comments so far
Leave a reply
From looking at the benchmarking available from the folks at the mysql performance blog, it seems like the storage engines have much more to do with multi core performance than the common mysql layer.
Another trend in addition to hardware scale-out is virtualization.
I predict that one chief purpose of the increased scale-out you describe is to make servers capable of running dozens of virtual servers on a single piece of iron.
Combine that with MySQL Cluster, and you can employ hardware circa 2013 fully, using today’s software technology. Each node in the cluster can be managed independently, and you get scale-out from parallelization by adding nodes to the cluster.
Plus, a virtualized server farm is more ecologically friendly than a refrigerator-sized beast.
William:
That might be true at two and four cores. Not sure about above that. From what I understand there are (many) cases where even eight cores aren’t as fast performance-wise as four cores. And I believe that information comes from Peter Z. at mysql perf. blog.
Bill:
Not sure that I agree. VM is cool. It doesn’t resolve I/O issues. And databases can be very I/O intensive. I would not want dozens of servers competing for my I/O backend just because I had a separate core for each VM and a decent amount of RAM.
Great article, thanks for the insightfull thoughts. It is interesting however what you are sugessting MySQL should head into.
When MySQL first started off, it was a light-weight databse, couldnt do much really other than CRUD-like apps until MySQL 4ish, then came MySQL 5.
I mean I love MySQL that I can have a myisam or an inodb databse for my app, I can choose to have a light-weight solution, or a slighly more robust solution.
I wonder if what you are sugessting is essentially to move to a more ‘Oracle’ like solution. Is this where they want to head with the product? Another full-blown RDBMS ? MySQL 5 seems to suggest this.
I don’t know about you, but I liked how MySQL differentiates itself with the smaller stuff (like myisam vs inodb), its perfect for the Small to medium sized stuff (and even the slightly bigger stuff)
Thoughts?
MySQL Ab has been aware of this issue for a while. Here is a blog from almost a ywar ago on this:
http://www.jpipes.com/index.php?/archives/175-The-Ambiguously-Vague-Duo-Scale-Out-and-Scale-Up.html
I am sure they have known longer .. probably atleast since their now faded SAP deal.
I am not sure how easily they can fix this. Their multi threaded nature seems to make it very hard for them to address issues like this while keeping their platform stable, let alone add new features. Maybe in the end PostgreSQL will prevail, with their much simpler process oriented architecture.
Sherif:
I am sure they are heading in “that direction” — ie the direction of Oracle. Really there isn’t a choice. And especially now that MySQL has been bought by Sun. Sun plays with the “big boys” on big hardware. And they are going to want MySQL to do so also. I am glad that I can run it on my desktop and my laptop, but I want to be able to run it on the current high-end equipment and what is coming down the road. Yes, MySQL is perfect for many small and medium size businesses. And it works well in many large businesses as well. Think google, livejournal, etc. However, there is opportunity for more growth in other market areas also.
Lukas:
It won’t be an easy problem to resolve. I had read Jay’s excellent post previously. I guess my basic contention is that hardware is moving to the way of allowing much more scale-up than most people realize. It wouldn’t be to difficult to put together a server that is far more powerful than the Sun server I described in the post from 2001. And it wouldn’t cost as much as you think. The performance of servers will only continue to grow over time. I know there are really smart people at both MySQL and at Sun. I would think that they are working on this. I hope they succeed.
This is complete speculation, but in my experience, the slowest part of MySQL has been the file sort. If a quicksort were to be used, multiple threads could be working on it simultaneously.
Nice post.
Each core performance may be slightly slower, but the gist is to make mysql more aware of the CPU and RAM available.
As for VMs, it potentially is a band-aid solution to single threaded software of any description.
For DBs disk IO is primarily for the D in ACID (Durable). If you run enough in-memory dbs, geo-locally separate you get D eventually (write IO somewhere). This is DBA heresy but you make your db and/or app cope or live with the architecture.
Johnathan:
It’s not just about speeding up the application. Parallelization of query by taking a complex query apart and sending the parts to different threads is what would do this. I think –without googling — that it is called intraquery parallelization. This would help with speeding up a query. That’s good.
But it is also about parallelizing queries from different clients. This thread/core get query A, that thread/core get query B, etc. This would improve the ability of the server to handle concurrent load issues (ie your server improves from being able to handle 1,000 threads without bogging down to being able to handle 10,000 threads … or 20,000).
Understand, as I said in the post, I am glossing over a major area of this where it relates to I/O.
SMP scalability for InnoDB has gotten much better, but most of the changes have yet to be in an official release. It will get much better by the end of the year.
Filesort uses radixsort or quicksort for the in-memory sort. It is slow because it doesn’t do anything fancy for the external portion of the sort. That is, it doesn’t use async IO to do read ahead or write behind. I think that this problem could be fixed by MySQL, but there are many other features that are needed for data warehouse queries that might be higher priority (hash join, hash aggregation).
Mark:
Yes, I agree. Small scale SMP is much better. But this more of “massive” SMP issues..16/32/64 cpus. Even eight cpus currently aren’t well supported. People laugh at 64 cpus, but it is coming .. sooner than you might think
[…] Diamond Notes, Keith Murphy asks, Can We Scale? He writes, “I was laying in bed last night thinking. I should have been sleeping, but I was […]
[…] of Oracle RAC? Parallel query processing using multiple processors as I discussed in a recent post (http://www.paragon-cs.com/wordpress/?p=83) I could not imagine ten years ago where MySQL is now. Today I would be foolish if I thought that […]
[…] For the past couple weeks I’ve been catching up on the Planet MySQL blogs in preparation to start participating in the community. I figure this would be the best way to get to know the “who’s who” of the most active members and to follow the latest features and developments. I’ve also been doing a fair amount of reading/research on MySQL development and databases in general. In particular, I’ve found the new MySQL Forge site very helpful (especially the wiki), along with the “Understanding MySQL Internals” book written by Sasha Pachev. My plan is to become familiar enough with the code base to contribute new features, help fix bugs, and hopefully meet some friends along the way! In particular, I have an interest (and experience) in parallel, clustered, and distributed computing, and trying to get the most out of these multi-core processors. I was happy to see others were interested as well (for example, this post by Keith Murphy). […]