Tag Archives: mysql

MySQL on Windows Azure is expensive and provided by a third-party, spoils web site offer

I have been impressed by the changes in the June release of Windows Azure, available through a sparkling new HTML-based portal that lets you create new virtual machines and web sites with a few clicks or taps. One of the new features is multi-tenant web sites, starting from free and scaling up to multiple load-balanced instances. I even wondered about moving this blog, which is on WordPress, to run on an Azure web site.

When you create a web site on Azure, you can choose between a free MySQL database or a paid-for SQL Server database. At least, that is what was announced, and it is kind-of true. However, if you choose a MySQL database, a message about agreeing to terms from third-party ClearDB pops up. Even your subscription details will be passed to ClearDB.

image

You click the link, and discover that the free MySQL offer is not generous. In fact, it is limited to a tiny 20MB, making is useless for most applications. It also has, according to ClearDB, low performance.

image

If your database may grow to more than 1GB you need ClearDB’s Saturn offer, at $49.99 per month.

This has killed my interest in running this blog on Azure, at least via this route. I am not familiar with ClearDB, but for all I know it is a fine company. Nevertheless, if I am betting on Windows Azure, I would rather not have to bet also on an unfamiliar third-party. I also note that many ISPs offer MySQL databases with few restrictions and better terms. Take UK ISP ICUK, for example, which I use on occasion. For £3.00 per month you can get Linux web hosting with up to 10 MySQL databases. They may not have all the features of ClearDB, but as far as I am aware (don’t take my word for it) they are on a fault-tolerant cluster and backed up nightly.

As I understood it, Microsoft’s goal with the multi-tenanted web sites is to provide a quick solution for test and development, that can scale to a serious web site. Maybe enterprises will not blink, but a $49.99 monthly plan for the database takes it out of the realm of quick and cheap test and development from my perspective.

It is also unfortunate that the Azure web site gallery does not provide an option to use SQL Server for some applications in its quick-create Gallery. These include WordPress and Drupal. I agree that these applications probably work best with MySQL, but you can configure them to use SQL Server.

There are other ways to bypass ClearDB. You could set up a plain PHP web site and configure it to run WordPress on SQL Server, for example. You could also use a Linux VM, even a Small Instance, with 1 virtual CPU and 1.75GB RAM, and put MySQL on there. Thanks to Azure’s fabric, it will have some resilience: all storage is, as I understand it, in triplicate.

In the end I guess this is not unexpected. Microsoft is a Windows company and you can understand why it wants to get someone else to manage MySQL; and also why it does not wish to undercut SQL Server with too generous an offer for MySQL.

Even so, the 20MB limit is a disappointment and makes the Azure free web sites less interesting.

Why Oracle is immoveable in the Enterprise

At Oracle OpenWorld yesterday I spoke to an attendee from a global enterprise. His company is a big IBM customer and would like to standardise on DB2. To some extent it does, but there is still around 30% Oracle and significant usage of Microsoft SQL Server. Why three database platforms when they would prefer to settle on one? Applications, which in many cases are only certified for a specific database manager.

I was at MySQL Sunday earlier in the day, and asked whether he had any interest in Oracle’s open source database product. As you would expect, he said it was enough trouble maintaining three different systems; the last thing he wanted was a fourth.

Oracle: a good home for MySQL?

I’m not able to attend the whole of Oracle OpenWorld / JavaOne, but I have sneaked in to MySQL Sunday, which is a half-day pre-conference event. One of the questions that interests me: is MySQL in safe hands at Oracle, or will it be allowed to wither in order to safeguard Oracle’s closed-source database business?

It is an obvious question, but not necessarily a sensible one. There is some evidence for a change in direction. Prior to the takeover, the MySQL team was working on a database engine called Falcon, intended to lift the product into the realm of enterprise database management. Oracle put Falcon on the shelf; Oracle veteran Edward Screven (who also gave the keynote here) said that the real rationale for Falcon was that InnoDB would be somehow jiggered by Oracle, and that now both MySQL and InnoDB were at Oracle, it made no sense.

Context: InnoDB is the grown-up database engine for MySQL, with support for transactions, and already belonged to Oracle from an earlier acquisition.

There may be something in it; but it is also true that Oracle has fine-tuned the positioning of MySQL. Screven today emphasised that MySQL is Oracle’s small and nimble database manager; it is “quite performant and quite functional”, he said; the word “quite” betraying a measure of corporate internal conflict. Screven described how Oracle has improved the performance of MySQL on Windows and is cheerful about the possibility of it taking share from Microsoft’s SQL Server.

It is important to look at the actions as well as the words. Today Oracle announced the release candidate of MySQL 5.5, which uses InnoDB by default, and has performance and scalability improvements that are dramatic in certain scenarios, as well as new and improved tools. InnoDB is forging ahead, with the team working especially on taking better advantage of multi-core systems; we also heard about full text search coming to the engine.

The scalability of MySQL is demonstrated by some of its best-known deployments, including Facebook and Wikipedia. Facebook’s Mark Callaghan spoke today about making MySQL work well, and gave some statistics concerning peak usage: 450 million rows read per second, 3.5 million rows changed per second, query response time 4ms.

If pressed, Screven talks about complexity and reliability with critical data as factors that point to an Oracle rather than a MySQL solution, rather than lack of scalability.

In practice it matters little. No enterprise currently using an Oracle database is going to move to MySQL; aside from doubts over its capability, it is far too difficult and risky to switch your database manager to an alternative, since each one has its own language and its own optimisations. Further, Oracle’s application platform is built on its own database and that will not change. Customers are thoroughly locked in.

What this means is that Oracle can afford to support MySQL’s continuing development without risk of cannibalising its own business. In fact, MySQL presents an opportunity to get into new markets. Oracle is not the ideal steward for this important open source project, but it is working out OK so far.

Fixing a WordPress plugin setting

I changed the theme and plugins used on this blog recently. Along the way I managed to slightly corrupt the settings for one of the plugins, GD Star Rating, the result being that the stars in the Top Rated Posts widget would not display. I figured out the problem: the plugin stores the path to the graphics which represent the stars, and this had incorrectly been set to an https path. Since I use a self-generated SSL certificate, the result was that browsers did not trust the connection and refused to display the graphics.

Unfortunately this path is not configured directly in the plugin options, as far I can see. I temporarily changed it to display a text rating while I worked out how to fix it.

The setting had to be in the MySQL database somewhere; and I found it. It is one value in a massive 10,000 character field called  option_value, in the main options table. It seems that most of the settings for the plugin live in this single colon-separated field, even though the plugin also creates 12 tables of its own for the ratings data. Hmm, I don’t like the way this implemented. How often does this field get queried and parsed?

Still, the immediate problem was to alter the value. I ran up the MySQL interactive SQL utility and typed very carefully. This is where one false move can obliterate your WordPress install; I’m reminded of someone I knew (not me, honest) who set all his company’s customers to have the same address with a careless update missing its WHERE clause. Fortunately this is only a blog. Transactions are also good. Anyway, what could go wrong? it was a simple combination of UPDATE, REPLACE and WHERE.

It worked, the stars have returned, and I know a little bit more about the innards of WordPress and this particular plugin.