Category Archives: Databases

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.

ulimits and upgrading from Oracle MySQL 5.0 to Percona patched MySQL 5.1

After upgrading to Perconas patched MySQL 5.1*, end users were having connectivity problems, and reporting errors such as:

OperationalError: (2003, "Can't connect to MySQL server on 'db-amo-ro' (110)")

TimeoutError: Request timed out after 5.000000 seconds

OperationalError: (1135, "Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug")

We had these same problems a while back, before increasing ulimit settings in /etc/sysconfig/mysqld. Oracles MySQL startup script specifically sources this file:

[ -e /etc/sysconfig/$prog ] . /etc/sysconfig/$prog

However, we saw these errors again when we upgraded to Perconas MySQL 5.1. At first we thought that it was because Oracles startup script is /etc/init.d/mysqld and Perconas is named /etc/init.d/mysql (so we would put ulimits in /etc/sysconfig/mysql). However, when we looked, we saw that Perconas startup script does NOT source anything in /etc/sysconfig.

So then we put the following in /etc/security/limits.d/99-nproc-mysql.conf:
root soft nproc 32768
root hard nproc 65535

We restarted MySQL and all was good. Even though we are long past having this problem, I thought it was important enough to blog about.

* We finished upgrading all of our servers to MySQL 5.1 at the end of 2012. We ran into this interesting snag that I wanted to blog about, even though were in the middle of upgrading to MySQL 5.5 right now (and by the end of the year, we will upgrade to MySQL 5.6 the performance schema stuff is definitely something we want to utilize).

MySQL User Group Video – Determinism and Databases

The May Boston MySQL User Group featured John Hugg of VoltDB talking about determinism and databases. I have uploaded the hour-long video to http://www.youtube.com/watch?v=mTDLyRauJtw. Seasoned MySQLers will nod their heads because the problems described are familiar, and those who are not exactly sure what determinism and databases means will learn a lot.

Enjoy!

(As always, videos are free on YouTube with no login or attempt to solicit your e-mail address or any other information)

Upgrading support.mozilla.org databases

A while ago (November 2012 to be exact), we upgraded the support.mozilla.org databases from Percona 5.1 to MariaDB 5.5 (the next step, happening soon, is upgrading them to Oracle’s MySQL 5.6). One of the engineers and I had a conversation where he mentioned that one of our worst performing views on SUMO is doing waaaayyy better with the upgraded databases, that it seems more stable and that I stopped receiving MySQL went away or disconnected emails which came in once in a while.

Its always nice to see upgrades actually making a difference. In our case we saw a lot less CPU wait, though that might also be partially due to tuning the memory settings on the machines and adding in another read slave to handle queries. As a result, network traffic throughput went from less than 1 Mb/sec to about 18 Mb/sec, because the machines were just handling more queries per second, period.

(I had this e-mail as a draft for a while and decided to clean it up and publish it now!)

Women in Science and Engineering (WISE) Computing Skills Boot Camp

Software Carpentry is running a 2-day software skills boot camp in Boston, June 24-25th 2013,  for women in science, engineering, medicine, and related research areas. Registration is $20.

Boot camps alternate short tutorials with hands-on practical exercises. You are taught tools and concepts you can use immediately to increase your productivity and improve confidence in your results. Topics covered include the Unix shell, version control, basic Python programming, testing, and debugging — the core skills needed to write, test and manage research software.

This boot camp is open to women at all stages of their research careers, from graduate students, post-docs, and faculty to staff scientists at hospitals and in the public, private, and non-profit sectors.

Registration is $20; to sign up, or find out more, please visit the announcement at http://software-carpentry.org/blog/2013/04/announcing-wise-bootcamp.html. If you have questions, there is an e-mail link on the announcement page.

For those curious, they are using sqlite, not MySQL or PostgreSQL, and I will be helping out with the SQL parts. There are about 2 months left but the boot camp is about 2/3 full right now, so I wanted to make sure this opportunity was spread to as many people as possible so they do not hear about it too late.

Percona Live Has No Code of Conduct

I am not at Percona Live this week because I opted to stay home after a crazy year of travel (41 talks in 11 different countries on 3 continents in the past year). However, I realized today that Percona Live has no Code of Conduct.

I will not be attending any Percona Live events until there is an acceptable Code of Conduct. MySQL is the worlds most popular open source database; the community deserves a Code of Conduct.

ETA: I have contacted Kortney, the conference organizer for Percona Live, and asked for a Code of Conduct to be put in place ASAP.

ETA: If you want to know why this is an issue, see http://adainitiative.org/what-we-do/conference-policies/

ETA: This is my personal statement, and not a statement of what any of my Mozilla colleagues may feel. Other colleagues, including employees under me, may choose to attend or even present at any events they wish. I personally do not feel comfortable at a conference with no Code of Conduct, this is not a reflection on the technical merits of any conference.

The 3 Hidden Messages in Tomas Ulin’s Keynote

This morning I watched Tomas Ulins Keynote at Percona Live: MySQL Conference and Expo, delivered yesterday. I missed this live as I am not at Percona Live (I am on a conference hiatus from March through September for personal reasons). As far as the technical content in it, there have been a few posts about the Hadoop Applier and MySQL 5.7, so theres not much of a need to delve in there.

Message #1: Failure
I was impressed that Ulin spoke of failure. Around 7:27 in the video above, Ulin says, We really failed with 5.0, and even 5.1 we werent fully and back on track when we released. He spoke about the new way MySQL 5.5 and 5.6 were engineered, a hybrid agile/milestone development cycle. There are some hidden messages here:

Hidden Message #1: Oracle is a great steward for MySQL

MySQL 5.0 was GA on October 2005 and MySQL 5.1 was GA on Nov 2008. This was before Oracle was ever in the picture. Ulin said MySQL 5.0 and 5.1 failed, mentioning that the ship cycle was rushed and features were released when they were not ready, causing technical debt. MySQL 5.5 and 5.6 are different, and the hidden message is that Oracle had a part in making this better. And honestly, I believe that. Say what you will about Oracle, but this cannot be argued: they do know how to develop and ship a product.

When Sun bought MySQL, I was pretty hopeful. I knew a bunch of folks within MySQL that were unhappy, and from what I gathered, MySQL did not really need a parent company, they needed a *parent*. It looks like Oracle has been great for getting MySQL releases in shape MySQL 5.5 had a LOT of great features from the community, when previously it could take years before a community patch was accepted, and MySQL 5.6 has a lot of innovative features from strong developers.

Hidden Message #2: Oracle is more reliable for MySQL releases

With 5.5 and 5.6, the 2-year development cycle has been almost exact MySQL 5.5 was GA in Dec 2010, 25 months after 5.1, and MySQL 5.6 was GA in Feb 2013, 26 months after 5.5. I remember the agonizing wait for MySQL 5.0, and it looks like under Oracle we will not have a debacle like that again. Ulin specifically mentioned a 24-month cycle.

Speaking about cycles, have you noticed that Oracle has not stopped providing the MySQL binaries and code, even for the EOLd products? I have a blog post I want to write about the lifecycle policy and how it has evolved, so stay tuned for that.

Message #2: Oracles Investment in MySQL
Ulin mentioned Oracles investment in MySQL a lot. Why? Well, in 2009 Oracle made a written 5-year commitment to MySQL. It is now 2013, and some folks have been wanting Oracle to make another promise. Frankly, I think it is ridiculous to ask a company to make a commitment in writing so far ahead, and nobody demands that of any other company. Oracle has doubled the number of MySQL engineers and tripled the number of MySQL QA staff, and has the largest team of MySQL developers of any company anywhere. Unfortunately we did not get exactly how many people that isit is only a little bit impressive if you tripled the team from 1 person to 3 people, but more impressive if you tripled the team from 10 people to 30 people.

Lets take a number we did get the QA team now has 400 person-years of experience on it. Lets say the QA team was 10 people before, and now it is tripled to 30 people. That means the average QA person has over 13 years experience in QA, which is about a year longer than my entire post-college IT career. If there are more engineers with less experience, thats pretty impressive for the number of people working on finding and fixing bugs, and if there are fewer engineers, they have even more years of experience.

Hidden Message #3: Oracle has an open-ended commitment to MySQL
Oracle has MySQL trainings, events and tech tours on 6 continents (none in Antarctica, but plenty in at least 3 different cities throughout Africa Nairobi, Johannesburg, Pretoria, at lesat that Im aware of because I mention it on the podcast along with SkySQL, Percona, FromDual and Tungsten events). They have doubled the engineering staff and tripled the QA staff and are still hiring. In the past year they sponsored over 40 events, delivered over 70 talks at conferences, and of course they have a huge investment in MySQL Connect just as Percona Live added a day in 2013, MySQL Connect is adding a day as well. With all that time and money invested in people and events, they are not going to stop working on MySQL any time soon.

(BTW if you missed it, MySQL Connect has a super saver registration before May 3rd, save 45%. Hard to believe its almost half price if you register now!)

Note that the hidden messages above are completely my interpretation, and represent nothing other than my opinion.

Videos from Open Database Camp

Open Database Camp was just over a week ago, Mar 16-17th at Harvard University, co-located with Northeast LinuxFest. We had a great lineup of speakers, and we have processed all 11 videos in record time! We got new video cameras at the beginning of the year, so the video quality and resolution is stellar, you can see everything. Here are the videos:

2013 Open Database Camp
and Related Northeast LinuxFest Videos

  • Fractal Tree Indexes by Tim Callaghan of Tokutek
    (45:23)
  • Elastic Database Virtualization by Amrith Kumar of ParElasticy
    (56:25)
  • Elastic Clustering with Serializable Isolation by Ariel Weisberg of VoltDB
    (27:23)
  • MariaDB: An introduction to 10 What Weve Achieved by Colin Charles of Monty Program
    (38:37)
  • Introduction to MongoDB by H. Waldo Grunenwald
    (47:08)
  • MySQL and MariaDB: Past, Present and Future by Max Mether of SkySQL
    (47:38)
  • MySQL 5.6 Replication features and usability by Giuseppe Maxia of Continuent
    (51:02)
  • Congratulations! Youre the New Linux Admin and the MySQL DBA by Dave Stokes of Oracle
    (36:44)
  • High Availability Solutions for MySQL by Max Mether of SkySQL
    (39:41)
  • Creating Multi-master Clusters with Tungsten Replicator by Giuseppe Maxia of Continuent
    (63:35)
  • MySQL Backups by Sheeri Cabral of Mozilla
    (56:48)

Enjoy!

Open Database Camp Schedule Is Up!

I have had a lot of folks wanting to know when talks would be during Open Database Camp, and we had enough space in the schedule that we did not need to vote on talks, so I present the Open Database Camp Boston Schedule, now online. Lots of MySQL talks, but also a Mongo talk thrown in for good measure! (still hoping to sneak a Postgres talk in)

As with any conference schedule, this might change. See you at Harvard University in Cambridge on Saturday and Sunday!

Deprecated, Removed and Ignored Variables in MySQL 5.6

Over at the OurSQL podcast, Gerry and I were inspired by the Percona blog post about MySQL 5.5 and 5.6 default variable values differences. We were going to do a show where we talked about that, but in researching that topic, we found there were lots (around 20 to be exact) of variables and a few features in MySQL 5.6 that are ignored, removed or deprecated.

These are variables that should be removed from your configuration so as not to cause warnings or errors. When I was writing up the show notes I realized that it was a pretty good list of variables, that anyone can just read whether or not you are willing/able to listen to the 28-minute podcast.

So if you want to see the list of variables that are deprecated, removed and ignored, complete with their workarounds/improvements, check out OurSQL Episode 130: Retired Variables.