A Tale of Two MySQL Upgrades

At the beginning of 2013, Mozillas MySQL databases were a mix of MySQL 5.0, Perconas patched MySQL 5.1, Perconas patched MySQL 5.5 and MariaDB 5.5. MySQL 5.1 was released in November 2008 so at the beginning of the year, we still had databases with no new major features in 4 years. Currently we have almost all our databases at Oracle’s MySQL 5.6 — the only stragglers are our cluster running TokuDB and a few machines that are no longer in use. Heres a graph showing the state of our machines you can see that in the first half of the year we concentrated on upgrading our 5.0 and 5.1 servers to 5.5, and then in the second half of the year we upgraded everything to MySQL 5.6 (click on the image to get the .png file):

After running some tests, we determined that MariaDB 5.5 was the best option for us and our particular workload. For most of our servers, it did not matter whether we use Percona, MariaDB or Oracles MySQL, but our Bugzilla servers really benefited from MariaDBs better subquery optimization, so we went with that. We had set up some Percona 5.5 servers over the spring/summer of 2012, when we moved some of our infrastructure to a new data center.

We upgraded to MySQL 5.5 to be on a recent version of MySQL. In the middle of the year, we had a choice should we stay where we were, or should we upgrade? We had no particular directive from developers to upgrade for the new MySQL 5.6 features. However, we have been doing more and more digging into our systems, and we really wanted the performance_schema features so we could dig even more. We want to be able to parse queries in real-time, perhaps with Anemometer without having to take an offline log file and run pt-query-digest on it.

So, we chose to upgrade to MySQL 5.6. Unfortunately, there were no other GA products to test against by mid-2013, neither MariaDB nor Percona had a GA 5.6 product, so our bake-off was functional only, not performance-related. Oracles MySQL 5.6 passed with flying colors, and so we proceeded to upgrade.

Now, we have a recent and consistent version of MySQL installed, that we can work with to gain insights into our systems. A pretty great goal to have been met for 2013!

MySQL Workbench “Clean up SQL” Feature

I was playing around with MySQL Workbench earlier in the week, and ran across the “clean up SQL” feature, which I thought was neat. Here’s a picture-based demonstration – you can click on the pictures to make them bigger, so … Continue reading

Learn MySQL for Free with MySQL Marinate, Season 3!

The 3rd season of MySQL Marinate begins October 1st! Join the meetup group and RSVP at season 3 to join! You can do the work on MySQL, or if you prefer, MariaDB or Percona.

If you do not have the book yet, you can still do the first week by using the online material from Browse Contents on the OReilly book page for Learning MySQL. There is homework for week 1, see the master list for all the information.

If you would like to learn MySQL from the ground up, consider joining us. This is for beginners If you have no experience with MySQL, or if you are a developer that wants to learn how to administer MySQL, or an administrator that wants to learn how to query MySQL, this course is what you want.

If you are not a beginner, you are welcome to join too maybe you need a refresher, or maybe you just want to test your knowledge or earn badges. Thats OK too!

The format of a virtual self-study group is as follows:

Each participant acquires the same textbook (Learning MySQL, the butterfly OReilly book, published 2007). You can acquire the textbook however you want (e.g. from the libary or from a friend) but if you buy the book, we ask that you buy it from our Amazon Store, to help pay for meetup fees.

Each participant commits to read one chapter per week, complete the exercises and post a link to the completed work. Tweet using the hashtag #mysqlmarinate.

Each participant obtains assistance by posting questions to a discussion area set up on the Virtual Tech Self Study Message Board for each chapter.

Each participant receives a badge upon finishing each chapter and all assignments.

Note: There is no classroom or video instruction.

How do I get started?

Become a member of the Virtual Tech Self Study Meetup Group.

Register for MySQL Marinate. RSVP to this event: Yes

Acquire the book (the only item that may cost money). Get your hands Learning MySQL see if your local library has it, if someone is selling their copy, or buy it from our Amazon Store (this helps pay for meetup fees).

When your book arrives, start your virtual learning by reading one chapter per week. Complete the exercises; if you have any questions, comments or want to learn more in-depth, thats what the forums are for!

Learning MySQL

FAQs:

Q: How long will the course last?

A: We will cover 12 lessons (chapters) in the book, so 12 (twelve) weeks starting October 1st, though we will have one week that is a break so that you can catch up if you need to or you have a week off if you need it. Refer to the MySQL Marinate Season 3 Master Discussion List for specific dates.

By January 1st, 2014, you will know MySQL!!

Q: Can I get ahead?

A: Sure! This is go-at-your-own-pace. To prevent spoilers, please put comments in the appropriate chapter threads.

Q: Does this cover the Percona patch set or MariaDB forks?

A: This covers the basics of MySQL, which are immediately transferable to Perconas patched MySQL or MariaDB builds.

Q: What do I need in order to start the course?

A: All you need is the book and access to a computer, preferably one that you have control over. Installing MySQL is chapter 2, so really, all you need is the book and a computer to start, you dont have to worry about any prerequisites. If you do not have the book yet, you can still do the first week by using the online material from Browse Contents at the OReilly Learning MySQL page.

Q: Where can I put completed assignments?

A: You will be able to put completed assignments on github. Instructions are in week 1.

Q: The book was published in 2007. Isnt that a bit old?

A: Yes! OReilly is working on new material, but it is not ready yet. The basics are still accurate, and we will let you know what in the book is outdated. We will also have optional supplemental/extra credit material for those who want to learn more right away. We are confident that this self-study course will make you ready to dive into other, more advanced material.

Soak it in!

*either tomorrow or today, depending on when and where you read this. Or it was in the past, if you have to catch up on your blog posts. Its OK, you can join us late, too. You can go at your own pace.

Code of Conduct: It’s a Misnomer

For me, a Code of Conduct is not actually guidelines for how to act. For me, a Code of Conduct is what to do if there is a problem with someones conduct. When I get on a plane, I am told what to do in case of emergency here are the exits, heres how to use your life jacket and oxygen mask. I am not told every little thing that could be an emergency I believe that would be a waste of time and invariably something would be left out.

Similarly, for conferences, listing out all the behaviors that might be problematic is a waste of time, and invariably, behaviors are left out. In my opinion, that is a waste of time. What is NOT a waste of time is giving out the information of what to do in case of emergency.

At the MySQL Connect website, under the Plan tab, is a link to the Oracle Events Code of Conduct. It says, simply, to act professionally and respectfully, and if there are any problems, contact Oracle Security and gives a phone numbers. I like that. Simple, and effective. And it was put under the Plan tab exactly where it should be. Its something to note as you plan to attend.

I put that number in my phone and, thankfully, did not have to use it. But knowing that I had a plan in case something happened made all the difference to me.

*BIG DISCLAIMER I do not speak for all people everywhere on this matter, just myself.

MySQL Puppet Module and Slides

During yesterday’s MySQL Connect conference, Brandon Johnson and I gave a joint talk about how Mozilla uses puppet to manage hundreds of MySQL servers efficiently. We also released our mysql puppet module on github, so that all can benefit. The slides for the talk are at http://bit.ly/puppet-mysql-slides.

Upgrading to MySQL 5.6 Slides

I am about to give a talk at MySQL Connect about what you need to know before upgrading to MySQL 5.6.

The PDF slides are online at http://bit.ly/upgrade56.

Video: Getting Started with Performance Schema

Recently I gave a new talk to both the Tokyo and Boston MySQL User Groups about how to get started using performance schema. I have put some resources online for those interested:

Performance Schema talk video

PDF slides

The feedback has been excellent, so I hope that you find this video useful when trying to learn how to use performance schema and dive into the depth of the information it provides you.

Indexing Talk Online

I am doing a quick blog post to announce that I have put an indexing talk online*. Most recently, I delivered this indexing talk at Confoo and Scale 11x.

The talk is on YouTube at Are You Getting the Best Out of Your MySQL Indexes? There are also PDF slides.
From the official conference description, if you want to know more:
MySQL indexes are often used to make performance better. However, they can make performance suffer if you are not using them properly. Oracle ACE Director Sheeri Cabral explains the pitfalls to avoid with indexes and how to utilize compound indexes to maximize index availability with the least amount of write overhead.

*I know I have not been posting blogs for a long time. This was a very busy year, and I took March through July off from conferences in order to buy a house and move.

2013 SkySQL and MariaDB Solutions Videos Are Online!

I do not recall seeing an announcement about it, but I went looking for the videos today and lo and behold, they were up! Forgive me if I missed a post about it. But if you also missed it, here they are:

2013 SkySQL and MariaDB Solutions Day for the MySQL Database videos

A Different Spin On the max_allowed_packet Problem

Back in November, I filed MySQL bug 67448, talking about a different type of max_allowed_packet problem.

See, an application had put data into the database, but could not retrieve it without getting max_allowed_packet. With the help of some really smart community folks (named Jesper Hansen, Brandon Johnson and Shane Bester), we determined that MySQL actually has 2 different max_allowed_packet settings: client and server.

When you change the max_allowed_packet variable, you are changing the server variable if it is in [mysqld] and the client variable if it is in [client] or [mysql] or whatever client you have. As far as we can tell, theres no way to actually view what the client variable is, as looking at both the session and global max_allowed_packet variable shows you the server variable.

If max_allowed_packet is not set by the client, it defaults to 16M. The proposed solution is to allow it to be increased for non-interactive clients, and the bug has been verified as a feature request, though it has not been implemented yet.