MySQL upgrades and pricing changes for extremely large processes

We will be making two changes to our MySQL service offering, one of which will be related to the pricing of large processes, and one related to upgrading the version (and distribution) of MySQL we use.

I wanted to put the technology change first, because it’s way more interesting and affects a lot more people, but I know pricing changes always attract the most attention, so I will start there instead.

Traditionally, we have avoided charging for space used by MySQL processes. Most processes are very small, and there simply wasn’t enough usage or problems to merit hassling people for a few extra pennies. Unfortunately, that’s no longer true. We need to take steps to either rein in a few cases of excessive usage or recover enough money to cover the costs of the resources we must allocate to them. Here are some of the numbers:

  • The median size of a MySQL process is about 5.2 megabytes.
  • Over 90% of all MySQL processes are less than 40 megabytes in size.
  • For the first time in our history, the top 5 MySQL processes are all over 10 GiB in size.
  • The largest 10 (not 10%, just 10) of MySQL processes use almost 34% of the total space.
  • The largest 1% of MySQL processes use almost 65% of the total space.

Something is wrong if two people are paying the same for MySQL but one is using (literally) thousands of times more than the other. And obviously we need to fix that. But we need to do that in a way that has absolutely no impact on the people (i.e. almost everybody) whose usage is both reasonable and within our expectations.

In order to ensure that we don’t negatively impact people unnecessarily, we have taken the 90th percentile size (40 megabytes) as a cut-off point. If your usage is less than that, nothing about your MySQL pricing will change based on space used. If your usage is more than that, then on September 1st, 2012, we will begin charging an additional fee of $0.002/MiB-month for MySQL storage over 40MiB per process. This will be calculated and billed exactly the same as site storage currently is, except that it is five times cheaper, and the first 40MiB of space used by each MySQL process will be exempt.

In addition, there is also a second pricing problem we need to solve, which is heavy database I/O loads. This problem frequently goes hand-in-hand with large processes, because with small ones a little inefficiency is typically too small to notice. We have noticed that occasionally some databases run into problems due to poorly-optimized queries (i.e. missing or inefficient indexes). For example, we have seen a couple of places where a site depends on a MySQL query of a large table with no indexing that results in large temporary tables being written to disk. This takes ages — a good rule of thumb is that if your query is writing temporary tables to disk, you’re doing it wrong — and so requests from that site start to pile up, and you wind up with the MySQL process trying to write the same huge temporary tables to disk 8 or 10 times at once. This is so intensive that in our shared hosting environment, other people’s processes start to suffer because disks only spin so fast and they’re starving to death waiting for their turn. Of course we act promptly when these conditions occur, and since they are almost always the result of poor optimization, they are typically easily for the member to resolve once we bring it to their attention. But occasionally we get a response like “why should I?” and unfortunately we need an answer to that question. Therefore, effective September 1st, if we determine that a process is using a disproportionate amount of I/O and the responsible member is unwilling or unable to resolve the issue, the fee for MySQL storage will apply to the whole process (no 40MiB exemption), and will be set to a higher rate as determined by us as necessary to cover the cost of resources being used. This will give us some flexibility to support larger MySQL requirements than we currently can with the current one-size-fits-all model. We will, of course, retain the option to refuse service to use cases that we find truly abusive or unsupportable on our infrastructure. Please keep in mind that this change will affect the pricing of about five MySQL processes. (Again, not 5%, just 5.) So if you’re worried that this will affect you, it almost certainly won’t, and we will contact you individually (if we haven’t already) before it does.

So, to sum up the pricing changes:

  • Less than 10% of our MySQL-using members will see pricing changes.
  • People who are affected are using dozens, hundreds, or thousands of times more resources than everybody else.
  • Of the affected 10%, most will pay a few cents a month more and about 75% will pay less than $2.00/month more.
  • MySQL processes using more than 40MiB of space will be charged $0.002/MiB-month for the excess.
  • We will be able to set custom pricing for MySQL processes with unusually intense I/O needs.
  • These changes will go into effect September 1st, 2012.
  • This will improve MySQL performance for everybody by creating incentives not to waste resources and by allowing us to use money collected from heavy MySQL users to allocate more resources to them, so they have less effect on everyone else.

With the pricing stuff out of the way, I want to move on to the other part of the announcement. After all that, we’re getting rid of MySQL! Well, OK, sort of, but not really.

We are going to start moving from MySQL 5.0 to MariaDB 5.3. NearlyFreeSpeech has been around long enough that when we started offering MySQL, it was “owned” by its own open-source friendly company. Later in its life, it was acquired by Sun, and things started to get a little weird, leading to a number of releases that in our opinion were of very poor quality and thus we never adopted. Then, to make things worse, Sun was then acquired by Oracle. This has had a number of negative influences on us, as we have depended on both OpenSolaris and MySQL in the past. In our opinion, for a variety of reasons, Oracle is not a good entity for us to have external dependencies on. We feel their handling of the open source community has left a lot to be desired, and that their support for MySQL has continued in the wrong direction.

Fortunately, as a project with roots an open source, MySQL has options and alternatives. The one we have chosen to pursue is MariaDB. This is a fork of the MySQL project by the original creator of MySQL, Monty Widenius. It uses some of the original code, some replacement code which is much better (e.g. it uses XtraDB, a drop-in replacement for InnoDB), and has (in our opinion) better features and quality control than Oracle’s MySQL releases. This will not only improve performance, but also let people take advantage of a lot of newer MySQL features that aren’t available in the 5.0 release series. There have been huge improvements to stored procedures and functions, for example.

We’ve been running MariaDB 5.3 as our core internal database for awhile now, and it’s done very well for us. And, more good news, moving to MariaDB 5.3 from MySQL 5.0 doesn’t require the clunky system table upgrades that 4.x to 5.0 did. You won’t have to do anything to take advantage of the change, and everything will continue to work just like before, except faster and with more optional features available for those who want them. However, we will also be taking this opportunity to rebuild all of our MySQL nodes with the freshest OS and software updates and additional resources allocated. This will require brief downtime (typically about 5 minutes) for each MySQL process as we migrate it from an old node to a new one.

These upgrades will be on a node-by-node basis, and we will preannounce the moves on Twitter. You can see what node your MySQL process is using at any time in our member interface. For example, the first new node (which is currently out of service) is m2, and the first move will be from old node m1 to new node m2, and we hope to announce that in a couple of days. Then, once m1 is empty, we will take it offline, rebuild it, and announce a migration from another node to that one. Then repeat the process on down the line until everyone is on the new hotness.

There are still 54 MySQL processes running on an old MySQL 4.x node (m6). Extended support for MySQL 4.x ended in 2008. It’s 2012, so we’re going to go ahead and push those processes through this upgrade. The processess should continue to work, but will require those members to upgrade their system tables as described in our FAQ before using their process with phpMyAdmin or changing any usernames or passwords in the future. We strongly urge those few people still using MySQL 4.x to avail themselves of our free “Upgrade MySQL 4.1 Process” assistance request to upgrade to MySQL 5.0 at a time of their choosing rather than waiting for an involuntary upgrade.

Update: While converting everyone to MariaDB, we found a way to reduce the per-process overhead, bringing the median process size down to 3.25 MiB, and the 90th percentile down to about ten times that (32.70 MiB). Despite this, the base space allocation will remain at 40MiB — it’ll just give everybody that much more headroom.

SOPA blackout option

If you want to participate in the SOPA/PIPA blackout, we’ve got your back. We’ve added a UI option on the site info panel for each site that, if enabled, will make your site look like this. If you select this, it’ll last until about Thursday at 7am UTC. Then, we’ll switch it off and convert it for people who want to be able to switch it on and off over the next week.

We set all the appropriate status and caching headers so that this shouldn’t have any long-term consequences or impact on search engine rankings. (But of course we can’t guarantee that since search engines are notoriously tight-lipped about how such things are determined.) We will return status 503 (Server temporarily unavailable) which should convince the automated world that we’re simply down. Not so good for us, but worth it for the cause. We’re also telling the world not to cache the results, to keep trying hourly, and that the content expires Thursday at 7am UTC.

That’s it!

Domain pricing increase effective Jan 15

As previously pre-announced via our status feed, the cost of domain registrations will be going up on January 15th, 2012 from $8.99/year to $9.49/year.

There’s really not much to add other than that. This is in response to a change by our registrar, who are responding to Verisign once again exercising the “because we said so” clause in their license to print money, which allows them to raise prices by a percentage every year for no reason at all.

We do have long-term plans in this area, including trying to break loose pricing on gTLDs other than .com to help them compete, but nothing coming to fruition in a timeframe that would affect any decision making anyone has to do about this.

Welcome SOPA Refugees

We have seen a massive surge in signups (something like 15x usual) over the past couple of days and I think most people can figure out why.

Welcome to all of the people voting against SOPA. (If corporations are people, and money is speech, I think that means spending is voting. I may be hazy on the details.)

Since it shot straight to the top of the “frequently asked questions” list: yes, we oppose SOPA.
Continue reading Welcome SOPA Refugees…

PHP 4 and Apache 1.3 end-of-life

This is just a short announcement to let people know that both PHP 4 and Apache 1.3 are well past their supported lifetimes from the developers. We’ve been maintaining them internally (updated as recently as last week), but it’s getting more and more difficult; our development resources are not unlimited and our efforts in this area are veering dangerously close to necromancy.

Both Apache 2.3 and PHP 5.4 are on the horizon, and we think it will best support our members if we focus more on developing new technology and less on reanimating zombies.

As a result, we are now warning those customers who are still using server types based on PHP 4 and/or Apache 1.3 that they should upgrade their site type to a newer version (the current default is Apache 2.2 with PHP 5.3) as soon as possible. Anytime after January 1, 2012, we may start performing involuntary upgrades on sites still using these outdated technologies.
Continue reading PHP 4 and Apache 1.3 end-of-life…

Security flaw with login corrected

One of our members informed us a couple of days ago that due to a strange combination of actions and circumstances, he hit a flaw in our login system that enabled him to access the membership of another member with a similar name.

Of course we promptly investigated; the problem has been permanently fixed.
Continue reading Security flaw with login corrected…

A small billing error corrected

A few days ago, we noticed a tweet from one of our members mentioning that he had been charged twice in one day for his MySQL process, but that he didn’t plan to look into it. We, however, did. What we eventually uncovered was that for three of our services: MySQL, RespectMyPrivacy, and email forwarding, our system could hit a very rare case where it would “stutter” and double-bill somebody’s daily charge.

Of course, the first thing we did was turn off the billing on those services until the problem could be identified and fixed. So, pretty much everybody using MySQL, email forwarding, or RespectMyPrivacy received a day or two free this week.

In the case of MySQL, each charge “advanced the clock” by one day, so if a person got double-billed one day, they wouldn’t be billed at all the next; they were “early charged” rather than “overcharged.” Email and privacy work a little differently, however, and so people in a few cases did get double-billed for an extra penny. Fortunately, we do keep detailed billing records for active accounts, and were able to find all the cases where this had ever happened and over the past couple of days we have credited the accounts funding all affected email and privacy services. It turned out to be easier to credit both the original charge and the duplicate, rather than just the duplicate, so we did that.

A small minority of our members’ accounts were affected, and the amounts involved were fairly small (the average credit issued, which includes both the original charge and the duplicate, about 3 cents per incident). All told, we were off by around $56 over five years.

About $12 of that was related to accounts that no longer exist. As we do not have the ability to identify who those people were (nor would there be any viable way to refund a few cents to them even if we could), that amount will be included in our monthly donation to the EFF.

It may be tempting to blow this off due to the small amount of money involved. The original Twitter poster who mentioned it didn’t seem too concerned. But we did not blow it off. Being able to bill accurately is the cornerstone of our service. We take it incredibly seriously and no discrepancy like this, however small, will be tolerated.

We’re sorry this happened. To make it right, we’ve fixed the cause, credited the difference, and because we believe in transparency, we’re letting everyone know about it. And we appreciate the Twitter poster for bringing it to our attention.

Scheduled maintenance November 22 and December 15

We are scheduling two maintenance windows in the next month to move some equipment:

Date: November 22nd, 2010
Window: 9am to noon UTC (4am to 7am US Eastern, 1am to 4am US Pacific)
Affecting: MySQL nodes m2, m3, and m21

Date: December 15th, 2010
Window: 8am to 1pm UTC (3am to 8am US Eastern, midnight to 5am US Pacific)
Affecting: File servers f2 and f5

Each server should be offline for about one hour, not the whole window. This will cause some downtime. While the MySQL nodes are offline, those MySQL processes hosted on them will be unreachable. While the file servers are offline, sites hosted by those file servers will show an official maintenance page.
Continue reading Scheduled maintenance November 22 and December 15…

Removing deprecated IP block

Many years ago, we were assigned the IP address block 64.238.220.0/23 by one of our upstream network providers. We officially deprecated the use of that block way back in 2008, and we will be returning it on December 1st, 2010, so it will not work after that point.
Continue reading Removing deprecated IP block…

Brief Network Maintenance July 20-22

This is just a quick announcement about some upcoming network maintenance.

Due to our load-balancing capabilities, most of this will be done with no disruption to our services. There are a few exceptions, though. We’ll be doing maintenance over the next few days in the early morning hours (between 1am and 5am US Eastern time — 5am and 9am UTC), the following services will be briefly disrupted (should be about 5-10 minutes each):
Continue reading Brief Network Maintenance July 20-22…

Entries Feed and comments Feed feeds. Valid XHTML and CSS.
Powered by WordPress. Hosted by NearlyFreeSpeech.NET.