In an IT organization, one of the larger significant costs in an annual budget is for software licensing and maintenance. That used to be the case at Novell. Databases required a significant portion of our annual budget. In this article, we'll show how Novell drastically decreased the budget amount for database software and maintenance and still got the job done. These changes have remarkably impacted the bottom line investment, but didn't negatively impact any project results. In fact, you'll discover why you should consider MySQL as a reliable alternative to your database.
The MySQL community is very active. Find out more about this fantastic project by visiting the Web site at mysql.org.
Novell is a heavy user of MySQL and has been since 2001. We're running versions as old as 4.0.18 up to version 5.0.24. Novell IS&T supports more than a dozen MySQL servers, with databases ranging in size from a few KB (with one or two tables) up to more than 17 GB in size (with close to 100 tables). Our biggest implementation of MySQL is with Bugzilla, an open source bug-tracking Web application. As of today, we have more than 13,303,543 records. The Bugzilla instance of MySQL is in the Provo data center and can be accessed from any Novell office around the world. Find more detail about our instance of Bugzilla in a separate case study at novell.com/connectionmagazine/2006/10/tech_talk_2.html.
Of course, we have a story behind each release of MySQL we run. We still have 4.0 because of some legacy apps that won't work with anything newer. Several servers are running 4.1, as that was the current release during our heaviest push to start using MySQL. Some of the servers are being used so much, we had to add more servers to keep up with the workload. Finally, we have additional servers running 5.0, the latest "ready-for-prime-time" release of MySQL.
One nice thing about MySQL is that it can run on servers with limited resources. For example, our older 4.0 server is also hosting Oracle and PostgreSQL. It runs Apache to serve up phpPgAdmin (mentioned later herein). It does all of this with two processors and 2 GB of memory. To date, we have not had any scalability issues using MySQL.
It can stay up for months, even years, at a time. The only time we ever have to bring down MySQL is to upgrade it to the latest version. During all the years we have used it, we haven't had any outages because of a MySQL bug or issue. We have purchased support from MySQL, and to date, we have opened two tickets with them. Both tickets were to ask questions, not to address a production outage. It is a rock-solid database platform.
MySQL security is another feature we have heavily used. We can lock down our servers and limit access only to given hosts. This has efficiently shielded and protected our sensitive data. Of course, you still need to do some routine things with each MySQL installation, such as secure the root account and remove the anonymous account.
The MySQL response time for small Web-based applications is much faster than other database platforms. Response time is immediate because there is no transactional overhead. The one downside with earlier versions is that, without transactions, rolling back is nearly impossible. Also, point-in-time recovery would be difficult. MySQL now provides for transactions, but the vast majority of what we do with it does not take advantage of these features.
The only other problem we have experienced is the lack of a procedural language within the database itself. Until recently, MySQL did not offer view, triggers or procedures. Any type of programming had to be done externally. Some of this functionality is now available within MySQL 5. Although this functionality is limited, they are definitely making great improvements in this area.
You can get MySQL software in three basic forms. 1. One is the source code. Using this, you must compile MySQL yourself. This involves making sure you have the correct libraries, compilers, and so forth. This can be confusing if you're not used to compiling software. 2. You can also get the software in Linux RPM packages. These work well for the first installation, but can be cumbersome when it comes time to upgrade. 3. For Novell's MySQL implementations, we use the binary distributions. These are basically compressed files that contain the precompiled software. This makes installing upgrades easy. We place the installation into the /usr/local directory and create a symbolic link named /usr/local/mysql. This method has been much easier to manage than using RPM packages or compiling source code.
phpMyAdmin: With the number of MySQL databases and servers we have to manage, we needed a simple, yet powerful tool to manage them all. phpMyAdmin (phpmyadmin.net) is the best solution we have found. We can easily manage all servers from one central location. We can create databases, modify users, create and modify objects, and the like. Adding servers into the system is as simple as cutting and pasting a few lines of code on the server. Database administrators who don't use MySQL daily can still use phpMyAdmin for support and on-call issues. We are running version 188.8.131.52 of the phpMyAdmin tool.
The application supports every version of MySQL we run. The only issue we've had was because of the php version being used. The MySQL libraries that were involved use an older method of password hashing, so we simply use the old_password function for users connecting via the phpMyAdmin tool.
Big Brother: For monitoring our production database servers, we use another open source product named Big Brother (bb4.org, commercial version also available at bb4.com). We wrote custom scripts for monitoring our open source database and servers, as well as various other services and applications. Most of the custom scripting was written in Perl and uses the DBD modules for database communications.
Big Brother has been very flexible. We have written scripts to monitor log files for errors, check on database backup processes, monitor and validate mail processes, MySQL replication status, database blocks, nfs mount status, and more. It easily monitors hundreds of servers and receives thousands of status updates with no performance issues. It sits atop Apache 2 and doesn't require any other software. We use three different servers for monitoring: two for production and one for all other systems. (See Figure 3.)
All our MySQL databases are running on various releases of SUSE Linux. We have some on SUSE Linux Enterprise Server 8, most on SUSE Linux Enterprise Server 9, and a few on SUSE Linux Enterprise Server 10. Most installations are using 32-bit MySQL, but several of our main production servers are running 64-bit versions. All of these are running without problem.
We use the SSH (Secure Shell) method to work on the servers themselves. SSH uses RSA keys to authenticate the user to the server and the server to the user. You can find more information about SSH at linux.ie/articles/tutorials/ssh.php. This is a secure method, superior to telnet and other means. We disable all unnecessary services to further secure the servers.
We run several automated jobs each night to backup and optimize the databases. We use both Mysqldump (dev.mysql.com/doc/refman/5.0/en/mysqldump.html) and mysqlhotcopy (dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html), as well as basic commands to optimize the tables and indexes and check them for errors. We then use Veritas, a third-party vendor, to write the backup files to tape. We do tests each quarter to ensure this backup process is functioning correctly. We have successfully recovered a copy of the databases each time.
We use MySQL for a variety of applications, including Bugzilla, Wikis, blogs, plone and others. We utilize MySQL Master/Server Replication for redundancy. What we've done is simple. We have one server as the master and one as the slave. We then bind a secondary IP address to the master server and have all clients use that address for connections. The slave runs in a read-only mode, so the chances of it getting out of sync are minimized. If the master fails and cannot be brought back in a timely manner, we would move the secondary address over to the slave, remove the read-only status on it, and we would be back up and running.
For Bugzilla, we actually use the slave server for reporting. The application controls which server is used for what purpose. This improves performance in the system. The master server handles the online, transactional type activity, and the reports and queries run on the slave server.
Statistics for various MySQL servers:
|Statistic||Bugzilla||OpenSUSE.Org||General 4.1 Production|
|Maximum concurrent connections||189||153||418|
|traffic received||4 GB, 11 MB per hour||1 GB, 897 kb per hour||28 GB, 79 MB per hour|
|traffic sent||3 GB, 9 MB per hour||73 MB, 56 kb per hour||387 GB, 1 GB per hour|
|queries sent to server||242,000,000+ in 13 days||1,900,000,000+ in 55 days||250,000,000+ in 15 days|
|database size||17.7 GB||536 MB||23.4 GB|
|number of rows||13,280,159||342,798||25,970,497|
Novell-sponsored external Web sites powered by MySQL:
We use the PostgreSQL database to implement Red Carpet, a Novell service which assists customers in keeping their SUSE Linux implementations updated. We use version 7.3.4, on a server with four Intel Xeon 3.20 ghz processors and 5 GB of memory. Because this service is used on thousands of servers, the database sees a lot of action. For instance, during the last three days as of this writing, the server has had 5,902,191 connections, which have read 218,063,950 blocks from disk and had 11,619,107,628 blocks read from cache.
Since PostgreSQL is delivered with the Red Carpet software, we simply use that version. It is installed via RPM packages when Red Carpet is installed.
We made changes to the installation to tune it and have it run faster. First, we had to increase several kernel parameters:
Second, we made the following changes to the postgresql.conf file:
shared_buffers -> increased from 1600 to 20000
sort_mem -> changed from 1024 to 8096
wal_buffers -> set to 24
checkpoint_segments -> set to 100
commit_delay -> set to 100
effective_cache_size -> increased from 1000 to 8000
log_min_duration_statement -> set to 10000 (just causes queries over 10 seconds to get logged to the postgres log)
Third, we examined slow-running queries and added indexes where they were needed. As you can tell by the metrics given at the beginning of this section, these changes made a significant difference and this tool has been valuable in our implementation.
For Novell, MySQL has saved thousands of dollars to the bottom line. It is a product on which we heavily rely and which has never faltered. It stands up to every test we give it and has always come shining through. We've noted a few points in this article where there is still potential growth; however, if your needs are not in this area, you should strongly consider MySQL. We have found that over time not only has it saved us incredible amounts of money, but it has also allowed us to get the job done with no degradation in performance while not increasing the need for someone to maintain it.
The fact that this community is so active with many developing and giving back to the open source community, desired features/functionality seem to be released quickly. If you are still hesitant to give MySQL a try, consider doing what Novell did. We started with a couple of smaller nonbusiness-critical applications and when we were comfortable, we continued to migrate other applications using MySQL as their back end. As we began new development, we performed analysis to determine if this new project was right for using MySQL. As you can see, we use MySQL for many things now and it has become our standard choice for back-end databases unless there is a compelling reason to use something else. So the next time you're faced with either decreasing your overall budget (and who isn't?) or you need to implement an application using a database, give MySQL a try; you'll be glad you did.