MySQL ~ the sequel

At organizations where IT coffers are small, IT managers have to be not only be creative, but cash-conscious with their implementations. Additionally, these systems have to be built to last. Often, when users don’t have the option of purchasing expensive proprietary technologies, they turn to open source.

When most people think open source software, the primary applications that come to mind are Linux and the Apache Web server, arguably the two most widely deployed free software applications. But one upstart database, MySQL — owned by Uppsala, Sweden-based MySQL AB — is strengthening its install base around the globe.

In fact it grew 30 per cent last year alone, according to figures released in January by Evans Data Corp. in Cupertino, Calif. The company says it has five million installations currently active around the world.

At its core MySQL is a relational database management system that relies on SQL for processing data. Although its early use centred mainly on Web applications, it has grown to pose a threat to proprietary database software vendors. Today the company offers MySQL Standard under the General Public License and a commercial version, dubbed MySQL Pro, with the same feature set, which costs US$595.

Users can also purchase support agreements from the firm, which start at US$25,000 per year for the company’s premium, enterprise-level support. MySQL also offers basic support services starting at US$2,500.

Tight belts equal unconventional solutions

At a time when many organizations are just getting used to tightening their belts, running IT operations on a tight budget is old hat for Red Deer College in Red Deer, Alta.

Robert Bastell, Webmaster at Red Deer College, has been using MySQL for the past seven years to run the school’s Web site. In fact, when Bastell was hired specifically to improve the college’s Web presence he set out immediately to find a cost-effective solution. “We needed some sort of a data warehouse for Web use that was very cost-effective and didn’t require large hardware because at that time we didn’t have it,” Bastell explained.

Bastell examined Mini SQL from Hughes Technologies Pty. Inc. in Australia, Canadian-based PostgreSQL, IBM Corp.’s DB2, Microsoft Corp.’s SQL Server and Microsoft’s Access. Initially, Bastell gravitated to Mini SQL because it was inexpensive. But while reading a book about the technology he discovered MySQL.

“I fell in love with the functions right away and I switched over to MySQL,” he explained. “At that time (there were) — and I still think there are — more tools available for the programmer.”

Seven years ago, Bastell custom-built the MySQL server and Red Deer College relies on Bastell to support the system. Another reason why Bastell is a fan of MySQL is because it’s scalable. Three years ago, the college gave Bastell $50,000 to spend on hardware for its Internet presence with the condition that hardware had to last Red Deer for five years. Bastell’s yearly IT budget is only $2,000.

The college runs a five-server Apache Web server cluster including MySQL on an Intel Xeon box.

While Bastell says that most databases on the market have all the tools a user would need, he really enjoys working with the MySQL language.

“It really makes a lot of sense,” he said. “We do some work with Oracle here and a lot of the functions have nomenclature that doesn’t make sense. For me, if I’m writing code, I can’t be spending lots of time digging through books to figure out the nomenclature.”

“In the Oracle and Microsoft SQL Server and PostgreSQL, there’s a lot more framework to start off with. Out of the box, you install [one of them], you’ve got so much more framework just to get to your data. With MySQL the framework is substantially reduced,” he added. “As a programmer it makes my job that much easier. We get the results that we’re looking for and we just can’t beat that.”

Red Deer also runs a Microsoft SQL Server 2000 and an Oracle database. He says that while both of those servers are great, he finds working in the MySQL environment easier and more intuitive.

The Oracle database is the data back-end for the student information system, and SQL Server is the data back-end for a student-built public timetable. With the public timetable, users can view courses on the college’s Web site including information such as how many spots are available in each class. However, there are plans to migrate the data from Microsoft SQL Server over to MySQL.

Accessing research data

The Genome Sciences Centre (GSC), part of the BC Cancer Agency in Vancouver, has also turned to MySQL to stretch its limited resources and to more easily share its research with the community. The GSC stores between 1TB and 2TB of data.

Dr. Asim Siddiqui, bioinformatics group leader at the GSC, said the Centre chose MySQL for two reasons. First, the college didn’t have enough money for a proprietary database and secondly, because his team needed something fast.

Siddiqui said not only was the database simple to install but the Centre was also able to avoid vendor lock-in. The Centre likes to publish its database in order to share it with other research institutions. If the GSC used a proprietary database, then the institutions it collaborated with would also need to have the matching database to extract and use the data. But using an open source database like MySQL allows similarly cash-strapped researchers to more easily access GSC’s data.

However, Siddiqui has some concerns about MySQL — in some early versions there is limited support for transactions and limited foreign language support. Where lack of support for foreign keys has been an issue, GSC has used PostgreSQL instead.

Evite-ing new technologies

Another MySQL user has also expressed concern about MySQL’s previous lack of foreign key support. Evite, an online invitation service based in Los Angeles —owned by InterActive Corp. — also uses the open source database.

Phillip Morelock, senior systems administrator for Evite, said the firm has been using MySQL, running on Gentoo Linux, for about a year. He said it started out as a research and development project to test performance and certain capabilities Evite had heard about. For example, Evite had heard that MySQL could store binary large objects (BLOBs).

“The performance of MySQL has been, in our experience, superior to just about anything else in terms of BLOB storage and retrieval,” he noted. “We can encapsulate everything that a user is doing on our Web site and support that as one object in the database.” For example, it takes a user several steps to create an invitation such as previewing and examining different designs. Morelock said Evite stores and tracks that information and MySQL lets them store it all in one spot.

“[Previously] whenever someone would click around the site we would insert data into our main customer database. Before that data is actually being used it’s a bad idea to put it in there because it actually ends up fragmenting the database and dragging down its performance,” he explained. Evite uses Oracle as its main customer database.

“What we wanted to do was to preserve everything a user was doing and then at the time if they want to commit their transaction, we go ahead and take the information we stored in MySQL temporarily and make use of it and store it in our customer database,” he added.

Morelock said he’s found both Oracle and MySQL to have separate and distinct advantages.

“The advantages of MySQL are its ease of use, ease of maintenance and ease of installation, as well the extremely fast performance of the software,” he said. “The advantages of Oracle are the transactional and referential integrity of the database and the ability to recover from any kind of system crash very quickly.”

He said Oracle has evolved primarily to protect data and has been made not to be broken. “It seems to me the philosophy of MySQL is that it wants to be really easy to use, fast performing and efficient with resources on your machine,” he said.

Although Evite uses Oracle for its mission-critical data, Morelock said he is familiar with other users who have used MySQL to store their mission-critical data. But Evite has no plans to get rid of Oracle simply because its code is tightly integrated with the way Oracle works and because of the data integrity Oracle provides.

Evite also uses MySQL to track all the e-mail Evite sends to individuals for parties and events, which runs between one million and five million per week, depending on the season.

“Our customer service team can basically — without any type of technical intervention — go in and look when e-mail was delivered, when customers received it and all those sorts of things,” Morelock said.

Evite never considered deploying anything else other than Oracle and MySQL, Morelock said, and didn’t even consider Microsoft SQL Server because the company has no Microsoft technology installed on its network.

Competitors see no threat

However, Darren Massel, product manager for SQL Server at Microsoft Canada Co. in Mississauga, Ont., said the company does not see MySQL as an imminent threat to SQL Server’s install base.

“Something to think about is that core relational database management and the engine is important, but that alone doesn’t really help customers become successful in their business,” he explained. “Customers today are really looking for that complete data management solution.”

For example, he said that as businesses grow, customers will want solutions that can help them build reports, analyze data and distribute data to mobile devices.

“If you look at MySQL, it’s deployed at the department level, it’s deployed by a developer— who doesn’t want to go through an IT procurement process — at the application level attempting to address the application,” Massel said. SQL Server, however, will offer the additional features, such as analytics and report building, as integrated features, he added.

Oracle Corp. also does not view MySQL as a competitive threat. “The most important thing for customers is applications and Oracle is the most popular database for applications. All the popular applications run on Oracle and virtually none run on MySQL. So for most customers that is a major point,” said Robert Shimp, vice-president, marketing technology at Oracle in Redwood Shores, Calif. Oracle is supported by major ISVs such as PeopleSoft Inc. and SAP AG.

Shimp said that MySQL is missing many modern capabilities inherent in Oracle such as support for triggers and stored procedures. “You will find that customers will develop simple applications on MySQL and when they hit the wall they jump to a commercial database,” Shimp said.

As for cost, Shimp said it’s simply a myth that Oracle is too expensive for small-to medium-sized businesses. Oracle sells an entry-level database dubbed the Standard Edition 1 that comes with a five-user license for US$749. A commercial copy of MySQL costs US$595 but it is for unlimited users.

“We certainly serve the highest of the high-end users but we also sell to many small- to medium-sized businesses,” he said.

Related Download
3 reasons why Hyperconverged is the cost-efficient, simplified infrastructure for the modern data center Sponsor: Lenovo
3 reasons why Hyperconverged is the cost-efficient, simplified infrastructure for the modern data center
Find out how Hyperconverged systems can help you meet the challenges of the modern IT department. Click here to find out more.
Register Now