Scaling MySQL on a 256-way T5440 server using Solaris ZFS and Java 1.7

A new era

In the past few years, I published many articles using Oracle as a database server. As a former Sybase system administrator and former Informix employee, it was obviously not a matter of personal choice. It was just because the large majority of Sun's customers running databases were also Oracle customers.

This summer, in our 26 Sun Solution Centers worldwide, I observed a shift. Yes, we were still seeing older solutions based on DB2, Oracle, Sybase or Informix being evaluated on new Sun hardware. But every customer project manager, every partner, every software engineer working on a new information system design asked us : Can we architect this solution with MySQL ?

In many cases, if you dared to reply YES to this question, the next interrogation would be about the scalability of the MySQL engine.

This is why I …

Scaling MySQL on a 256-way T5440 server using Solaris ZFS and Java 1.7

A new era

In the past few years, I published many articles using Oracle as a database server. As a former Sybase system administrator and former Informix employee, it was obviously not a matter of personal choice. It was just because the large majority of Sun's customers running databases were also Oracle customers.

This summer, in our 26 Sun Solution Centers worldwide, I observed a shift. Yes, we were still seeing older solutions based on DB2, Oracle, Sybase or Informix being evaluated on new Sun hardware. But every customer project manager, every partner, every software engineer working on a new information system design asked us : Can we architect this solution with MySQL ?

In many cases, if you dared to reply YES to this question, the next interrogation would be about the scalability of the MySQL engine.

This is why I …

You Mean, Memory Is Not Infinite? We're working hard getting MySQL Enterprise Monitor 2.0, featuring Query Analyzer, ready for release. As part of that, we started really ramping up the number of MySQL servers reporting in query data to see how we could scale. Not surprising (to me, anyway), the first efforts did not go so well. My old friend OutOfMemoryError reared its ugly head once again.
Query Cache -- It's More Than Just Results! We're big (ab)users of hibernate query caching, and more importantly to us the natural id optimized query cache. Firing up the profiler, I was not shocked to see that the second level (let's call it L2) and query caches were hogging the majority of memory. But, something didn't smell right...

What I was seeing was tons of …

For normal Innodb "hot" backups we use LVM or other snapshot based technologies with pretty good success. However having incremental backups remain the problem.

First why do you need incremental backups at all ? Why not just take the full backups daily. The answer is space - if you want to keep several generations to be able to restore to, having huge amount of full copies of large database is not efficient. Especially if it only changes couple of percents per day.

The solution MySQL offers - using binary log works in theory but it is not overly useful in practice because it may take way too long to catch up using binary log. Even if you have very light updates and can execute updates for a full day within an hour it will take over 24 hours to cover month worth of binary logs... and quite typically you would have much higher update traffic.

Another solution is …

This morning started with a nice breakfast and the opening general session. Key speakers include:Daniel J. Berg - CTO Global Sales and Services and VP of EM Systems EngineeringPeter Ryan - Execute VP Global Sales and ServicesJonathan Schwartz - CEO and PresidentHal Stern - Senior VP Systems Engineering Highlights from the General SessionOpen source is disruptive technology. Open source is

Dare Obasanjo has a nice article on caching and why we can't just let the database do it. I love the phrase "it's caching all the way down."

http://www.25hoursaday.com/weblog/2008/11/09/InMemoryCachingWhyWeCantJustTrustTheDatabaseToGetItRight.aspx

Today Sun announced the new 7000 line (aka Amber Road) of open storage appliances. Amber Road runs OpenSolaris and ZFS on industry-standard x86 hardware and includes innovative management software developed by Sun’s FISHworks (Fully Integrated Software and Hardware) group.

Our engineers worked along with Sun’s technologists on Amber Road in Sun’s labs for past few weeks, and today we are announcing support for Amber Road with both of our products Amanda Enterprise and Zmanda Recovery Manager for MySQL.

Amber Road is another example of innovation and value created by combination of open source and open systems. Combine  Amber Road with Zmanda’s open source backup …

Mark De Visser, previously VP of marketing at Zend and Red Hat, set to lead open source tools company READ MORE

This Thursday (November 13, 2008), Martin "MC" Brown will give an overview of MySQL on openSolaris. MC is an expert on MySQL and Solaris, and has helped the openSolaris team to port MySQL to that platform. The session will start at 14:00 UTC/BST (15:00 CET).

For those of you who've never heard the term before, MySQL University is an educational program for engineers from Sun/MySQL and the MySQL community. It's free and open to anyone. MySQL University focuses on MySQL internals and on Sun technology that can be used in connection with MySQL.

Here's the updated schedule for the rest of this year. Note that 14:00 BST currently translates to 14:00 UTC – sorry for getting this wrong with the last announcement. As always, check out the …

One of the things that I've been wanting to do is build MySQL.  I checked out this blog from Neel and the instructions posted on the MySQL site and it looked fairly straightforward.  After going through the steps, I can say that it proved to be quite simple.  Note that I built the latest rc candidate of MySQL on OpenSolaris using Sun's compilers.  Here are the steps for people who are interested.

1. Install OpenSolaris.  I already have OpenSolaris 2008.05 running within VirtualBox on my laptop.  If you need the ISO image, it can be downloaded from …