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.

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

NFSN