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.

8 Comments

RSS feed for comments on this post.

1. So my 550Mb MySQL process will cost about $1 a month more? Assuming my calculations are right, that sounds fair enough to me – guess I’ve been getting a free ride for too long 😉

   Comment by zekele — July 28, 2012 #

2. Curious, I have a 550MB MySQL process too… and I had no idea it was that big.

   Anyway, this is a reasonable thing to do. Thanks for the heads up.

   Comment by Keith Davies — July 30, 2012 #

3. Just some feedback:

   There are now offerings out there that provide a “free tier” managed SQL service… which is what I recently migrated one of my tiny dynamic site’s database to. I could fit it in at 5MB — this particular “free tier” offering had a ceiling of 10MB.

   I’m a very happy NFShost user otherwise, but I was too cheap to pay $0.01/day for a basically-dead site that I only kept running for legacy links 😛

   Is there any way that NFShost could configure their own free-tier for infrequently used SQL processes / tiny sites? My “legacy” site was only getting hit by old links a few times a week.

   Is there any way you could cache the running SQL process from RAM into a file on disk to wait for the next hit and thereby save performance? And provide a cheaper offering to micro-tier users?

   …just my $0.02.

   Comment by Patrick — August 2, 2012 #

4. I originally wanted to just say “We do not compete on price with free services” in response to this, but I think doing so misses the point. The service you describe is not free; it costs money to provide, it’s just that someone somewhere other than you is paying for it. If that works for you, great, but you may wish to figure out who and why, as that will help you formulate a backup plan for what to do when they decide to stop.

   It’s also worth noting that although the service you reference offers 10MB for free, 10.1MB to 500MB is $17.00/mo. With these changes, similarly-sized processes would typically cost between $0.60 and $2.00 per month here. So I actually feel like we’re offering quite a good value in this area.

   However, if our $0.60/month offering is still too heavyweight for a particular application, I would strongly recommend using SQLite instead, which is filesystem based and incurs no charges beyond space used (e.g. $0.05/month for a 5MB database). That would yield much better results than any effort to underconfigure MySQL to create a lower gimmick price.

   -jdw

   Comment by jdw — August 2, 2012 #

5. I guess I just wanted to say that this thread has been interesting to read. The fee adjustments seem very fair to me, although I’m admittedly not affected by them. Particularly interesting was the heads-up on the “poor quality” of recent releases of MySQL. It’s a shame that this project has been derailed by the muppets at Sun and Oracle. I’ve made a note to seek out MariaDB when needed for a dedicated server or VPS setup!

   Comment by Bumpy Light — August 4, 2012 #

6. Is it fair that those who use more pay more? Absolutely. That’s the whole point of NFSN 🙂

   If you’re indeed losing money from current pricing then I suppose it’s fair to increase but honestly the 0.02/day (plus 0.01 for dynamic sites) always seemed a bit too high for light users and I assume that was to cover the cost of those who “abused” the system.

   IMHO it would be fair to have a lower price on the lower usage end (or perhaps just a single price per MiB). You’d probably still make up for the loss with the price increase and it would be more consistent with the way storage/bandwidth charges work.

   Our MySQL per-day pricing reflects that all processes regardless of size use a certain baseline level of resources. I believe my previous comment makes clear that we believe our existing pricing is good, that we have no interest in lowering it, and that SQLite is a good alternative that uses less resources if MySQL is too heavyweight for a very small application. -jdw

   Comment by Atomic — August 4, 2012 #

7. Fair is fair. Actually, I feel like such a cheapskate just using html. But in the end; I’m writing fiction, and a database can be a lot of work if you don’t need it.

   It also opens you to hacking attacks, sql injections, etc. Not worth it to me. Thanks for the great service!

   Comment by Greg — August 8, 2012 #

8. Wow, so I’m one of your top 10 MYSQL processes? 11GB apparently, and like one of the previous posters, I had no idea whatsoever.

   Just looked at the database tables and see that some pretty “standard” installs of Drupal are sitting around 500MB – and I have duplicate databases for several sites (staging and live) so now see where this comes from.

   Excellent excuse for me to learn more about my database structure!

   Comment by Shamus — August 22, 2012 #

Sorry, the comment form is closed at this time.