Category Archives: Databases

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

After upgrading to Percona’s 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. Oracle’s MySQL startup script specifically sources this file:

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

However, we saw these errors again when we upgraded to Percona’s MySQL 5.1. At first we thought that it was because Oracle’s startup script is /etc/init.d/mysqld and Percona’s is named /etc/init.d/mysql (so we would put ulimits in /etc/sysconfig/mysql). However, when we looked, we saw that Percona’s 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 we’re 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 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.”

It’s 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, 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 world’s 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 Ulin’s Keynote at Percona Live: MySQL Conference and Expo, so there’s 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 weren’t 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 EOL’d 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: Oracle’s Investment in MySQL
Ulin mentioned “Oracle’s 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 is…it 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.

Let’s take a number we did get – the QA team now has 400 person-years of experience on it. Let’s 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, that’s 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 I’m 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 it’s 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

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, 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.

Different MySQL Forks for Different Folks

At called “Different MySQL Forks for Different Folks”. The idea was to explain the differences among all the forks – Drizzle, MariaDB, Percona and of course Oracle’s MySQL. But I did not just go into technical merit of each fork; I also explained the values of each company, as that can be a big decision in deciding what software to choose.

There are PDF slides and a video on youtube. But I wanted to put some of the links I used to gather information here, as an easy place for folks to come and click links if they like, or if they want to research on their own.

At the official Drizzle documentation:
What is Drizzle?
Drizzle differences compared to MySQL, including gotchas

Oracle:
MySQL Connect, Oracle’s business/technical conference for MySQL
Virtual Developer Days, March 12th and 19th

From the Percona website:
Percona Server
Percona Software
Percona 5.5 vs. MySQL 5.5 feature comparison
Percona Live, Percona’s business/technical conferences for MySQL
Why Percona Live?

About MariaDB:
MariaDB vs. MySQL compatibility
What is in the different MariaDB Releases?
Tweet asking about making MariaDB 10, MariaDB 56
What’s in MariaDB 10

Hopefully the video gives a good overview; I know that many will learn a lot from this controversial video.