This past Monday, May 5th, I had the opportunity to speak at Community One on Scaling MySQL.  The presentation was based on several months of work to integrate and tune MySQL in to our fastest supported database.  MySQL is the fourth database I have developed on, and while working with it I have found a bunch of nice features that are simple to use which made HQ run extremely fast.  Unfortunately MySQL has some big issues with their query optimizer that made the work a bit more tedious than it needed to be.

Here are my top 3 keys to tuning MySQL ->

1) Query Tuning
Always run “explain” on your queries.  Make sure they use indexes where applicable and do not table scan where you can help it.
Try to avoid using tmp tables where you can in queries.  If you cannot avoid them make sure the tmp table size is as small as possible.

2) Use of Indexes
Make sure you use your clustered index / primary key wisely.  For a table which is written a lot don’t use too many indexes due to overhead of CRUD operations, and on the flip side for a static table make sure it has plenty of index access points and use covering indexes where applicable.

3) Know the MySQL server options and know how to monitor them to make sure they are not out of control.

innodb_buffer_pool_size
innodb_flush_log_at_trx_commit
tmp_table_size, max_heap_table_size, and max_tmp_tables
innodb_flush_method

For slow query management, make sure you monitor the tmp table status from “show global status”
Created_tmp_files, Created_tmp_tables and especially Created_tmp_disk_tables

HQ offers a very easy and effective way to facilitate this monitoring.  If these status vars grow too fast make sure to turn on your slow query log and debug the slow queries.  There is usually always something that can be done.

Also, feel free to check out http://support.hyperic.com/confluence/display/DOC/MySQL+DB+Preparation for our recommended options.  It is not a one size fits all, but a good place to start in order to tune the MySQL server options.

HQ running on MySQL exemplifies the potential that the database has to scale any application.  It still has a ways to go in terms of features to compete in all spaces, but every major release it keeps getting better.

Here is a link to my presentation from Community One:

http://download.hyperic.com/documentation/commone-mysql.ppt

Yesterday I had the opportunity to listen to Marten Mickos deliver the opening keynote at the MySQL conference here in Santa Clara. As usual, Marten does an enviable job at delivering a presentation which talks about MySQL’s business, its new relationship with Sun Microsystems, and what this all has to do with Open Source.

I’ve been lucky to hear Marten speak at a number of events, and have found that one of the most consistent themes can be distilled down to this (which we at Hyperic fully subscribe to):

Build a great product, Empower your Users, Build a great business

He also reiterated the importance of the GPL as an essential element to drive empowerment of users. This lets them adopt products and participate while creating an opportunity for businesses to deliver value people are willing to pay for. A virtuous cycle, right? Sun seems to think so also (a billion dollars is not the type of money any company throws around lightly)

Ironically, as I was in the middle of writing this post, Slashdot’s editors let out this gem in a post claiming that MyS^H^H^HSun had “begun to close source MySQL”. Luckily, Marten was able to set the knee-jerk cable-news-inspired Slashdot post straight by again explaining to the readers that MySQL is a business which has been able to build a great free, open, GPL database by creating value they don’t necessarily give away. He’s candid about the fact that they are experimenting trying to arrive at the best business model that balances all the elements of the above equation. Obviously, it’s not easy and someone’s undoubtedly going to get upset with the result. Apparently some people on Slashdot think MySQL’s database is written by monks who are morally opposed to any compensation.

Why is this important? Well, because software doesn’t build itself. Another admirable (if slightly more outspoken) guy named Marc Fleury made a big point of that with JBoss’ “Professional Open Source” business model. Everyone got what they wanted: the community got a great, free, open J2EE app server, and JBoss got paid to hire more people to continue to build it.

Recently there were a few examples of failed companies in the management space which either directly used open source or had some connection to it in their businesses. Many wonder if the failure had something to do with open source, their choice of license, or the business model. Few (if any) looked at the more obvious part: the products offered by those companies. Just because something is free and open source, it doesnt mean people will flock to it. Without that first element of the equation, there’s no adoption and no opportunity to create value people will pay for. The result is a failed business. The formula for creating that value is still being developed and is different for every company. We’ve chosen one which so far has brought us much success, but might not work for others.

Much like MySQL, we’re eager to experiment with different ways in which we can deliver the best products to the largest audiences while delivering financial success for our employees and investors. Over the course of this year, you’ll see us continue to enhance and deliver more features and functionality to our open source platform, while continuing to enhance the value of our various subscription offerings. Also like MySQL, we’re eager to engage our community directly as we experiment and learn how to continue to build the best solution to managing large scale web infrastructure.

The first release of Hyperic HQ 3.2 Beta is now available for download. This exciting new release is designed to provide a more powerful ?single pane of glass? to monitor, diagnose and manage today?s complex, custom, web-based IT environments. In addition, significant infrastructure enhancements to the core Hyperic HQ platform were added to deliver the most scalable and manageable enterprise monitoring software available in open source.

New Diagnostics and Visibility
Today?s IT administrators deal with an increasing burden of disparate technologies, and changing resources to monitor and maintain. Typically, they rely on a variety of utilities and diagnostic tools to ensure and restore availability to their systems. Hyperic HQ 3.2 introduces three new capabilities that provide both new and existing users of HQ a single console to manage all layers of their infrastructure:

• Live Exec Data ? Instantly see your most CPU intensive processes (top), disk usage, network connections and more across your entire cluster of Windows and *nix platforms in a single view!
• Currently Down Resources ? This quick look screen gives users the ability to view all down resources and their length of down time at a glance in one page. The view can be more narrowly filtered by generic or specific resource type, and can also be sorted by resource name, type, exact down time, and length of down time.
• Nagios Import Utility ? This new utility automatically imports Nagios hosts, commands and service object. This utility provides administrators currently using Nagios easy access to all the custom checks and configuration currently supported in their Nagios environment, while extending all the additional benefits and features of the Hyperic HQ platform to these users with the click of a button.
• Improved Nagios Integration ? The Hyperic HQ 3.2 Nagios plugin has been extended significantly to provide Nagios administrators the ability to run Nagios plugins natively and view Nagios Services, Hosts, and Statuses in a way that is identical to the Nagios Service Details interface.

More Scalable, Manageable Infrastructure
Hyperic HQ users run some of the largest website and web applications in the world. Providing monitoring and management capablities at this scale can be a daunting task, but it doesn?t have to be. Hyperic HQ 3.2 introduces powerful infrastructure enhancements to ensure maximum visibility with minimum overhead:

• Scalability Improvements ? Hyperic aims to provide the most scalable systems monitoring and management software available in open source with Hyperic HQ 3.2. Significant investment in powering more concurrent checks, collecting more log data, and increasing the database performance for both queries and data insertion will demonstrate immediate results for large scale enterprises. Significant benchmark testing will be conducted in parallel with this Beta, and results will be shared.
• MySQL Database Support - Full support for MySQL database to be used as backend data store for Hyperic HQ 3.2, leveraging the proliferation of MySQL in the enterprise and existing DBA expertise. The performance of MySQL is expected to be on par with the other backend databases we currently support: Oracle, and Postgres.
• HQ Health Check ? The Hyperic HQ Server now supports self-diagnostics on the performance of the server itself, providing users and Hyperic Support a better way to be assured their monitoring system is always available.
• Global Alert Disable - For scheduled maintenance or planned outages, the HQ administrator can globally disable all alerts on-demand in HQ 3.2, thereby preventing unwanted alerts from being generated during these times.

This release is intended for early preview and community participation in perfecting this release. It is not recommended to replace your current HQ production environments. The GA release of Hyperic HQ 3.2 will be available later this winter and will support a direct upgrade from your current Hyperic HQ 3.x environment. For more information on the Hyperic HQ 3.2 Beta see the release notes. Download today and be sure to participate in our user forums and bug forums.

Expanded Community Rewards Program
To encourage the community even further to participate in improving the quality of both the new Hyperic HQ 3.2 Beta, as well as Hyperic HQ installations in production today, we have extended the Hyperic Community Rewards Program. The program now provides members new points awarded for identifying new bugs, fixing reported bugs and contributing new plugins or HOWTOs for the HQ platform. Various rewards are available to power users who contribute at significant levels.

The built-in HQ database is PostgreSQL. Recently, users have been discovering PostgreSQL has a certain limitation: it will not execute more than 2 billion transactions between vacuums. In rare cases, an HQ built-in database can get into this state.

If this happens, the database will stop accepting connections and HQ, which needs a data store, will obviously cease to operate properly. The immediate symptom will be that users will not be able to log in to HQ and the message displayed on the screen will be The backend datasource is unavailable.

That error is not enough to say for sure that the problem is PostgreSQL avoiding wraparound failure by not accepting connections. A quick look at the hqdb.log can confirm. The telltale log entries look like this:

FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".

Happily, postgres tells you how to solve the problem. It’s not very specific, though so I’ll add some details.

The first thing to do is immediately shut HQ down (including the built-in database). Next, start just the database in single-user mode. Technically, you only really need to run a VACUUM, but since we’re here, we might as well be thorough and run VACUUM FULL ANALYZE.

I’ve created a script to start the built-in HQ database in single-user mode. It’s a slightly modified version of the db-start.sh script that is included in HQ installations. It takes one argument which is the name of the database to start up. The database name to VACUUM is specified in the hqdb.log message (it is “postgres” in the message above). Start the database with the script and you will get dropped into the psql command line. Run the VACUUM and exit. Looks something like this:

$ bin/db-start-single-user-8.1.sh postgres
PostgreSQL stand-alone backend 8.1.2
backend> VACUUM FULL ANALYZE;
VACUUM
backend>

When it’s done, you send an EOF (usually control-D) to exit the shell and shut down the database. At this point, you’re done and you can start HQ normally. Things will work and will usually perform much faster.

For more information on this PostgreSQL, check their documentation on how to avoid running into it to begin with.

It’s a question we all wonder. Who will prove their technology team can crank out the best efforts in the industry? Who will be recognized for thier prowess in this fast-moving industry of open source software? To answer this question, we have devised an extremely competitive gauntlet to decide.

The showdown begins tomorrow at 5:30 Pacific.  At the end of the contest, we will know for certian who will take the title of open source world domination. Strategy, speed and general bravado will be the key ingredients needed to succeed. Trash-talking wins you extra points.

The showdown begins at 5:30 pm at Hyperic Headquarters. Wii-tennis will be the game, and there will be Guitar Hero for those less versed in the skills of tennis. For now, the folks that I know to be competing will be:

• Hyperic - favored winner by far. Javier is the ringleader of this team, supported by fellow Wii Warriors Ryan Morgan, Alex Ma and Scott Feldstein.
• MuleSource - Dave Rosenberg and his crew are representing. Dave is reportedly going to carry a Puerto Rican flag and sport a jumpsuit for the event. I’ll be sure to take pictures! :)
• MarketCetera
• JasperSoft
• MySQL

I have also heard that Zimbra is collecting a team. LoopFuse is hiding in Atlanta. Alfresco caved early. Who else dares to compete? Funambol? XenSource? Vyatta? Be sure to send me your team members names so we can draw up the brackets.

Game on!

Released today, administrators of the Alfresco Enterprise Content Management System now have access to a fully supported, enterprise-ready systems management solution with Hyperic HQ for Alfresco. The new Hyperic HQ plugin instantly enables HQ and Alfresco administrators to take full advantage of Hyperic?s powerful management capabilities, including auto-discovery, monitoring, complex alerting and remediation. With today’s release of the Hyperic HQ for Alfresco plugin, Hyperic HQ becomes the only monitoring system to natively support Alfresco deployments on every platform and architecture.

Enterprise Content Management ensures the quick and reliable delivery, accessibility and long-term control of the most important information assets in an enterprise. These all require a strong, reliable architecture,? said John Newton, CTO and co-founder of Alfresco Software Inc. ?Hyperic HQ?s plug in for Alfresco provides a powerful new solution to monitor Alfresco in context with all their web infrastructure components, ensuring the delivery of the right message at the right time as well as the right levels of service and availability for their web sites.?

This latest plugin reflects a concerted effort from Hyperic engineering to provide the most aggressive and expansive native product support for leading technologies in the market today. The Hyperic HQ for Alfresco plugin represents the 14^th new technology managed for Hyperic in less than 60 days.

“Hyperic is committed to delivering the most complete out-of-the-box technology support for the always-on, fast moving online services businesses,” said Doug MacEachern, CTO and co-founder of Hyperic. “Enterprise content management represents a critical component to delivering a rich user experience for any online business and an obvious next step for us to extend our complete support. We?re very pleased to extend the Alfresco users the full management and monitoring support of Hyperic HQ to ensure the overall health and availability of their web infrastructure.?

About the plugin
The new plugin monitors the health and performance of every published performance statistic for Alfresco and its infrastructure components. For a full list of management metrics see the Alfresco page on the HyperFORGE. The plugin allows Hyperic HQ to automatically discover the availability of the complete set of Alfresco components, including the web server, database backend, application server, and indexing services.

In addition to the Alfresco plugin, a Sendmail plugin was added to the HyperFORGE. See the HyperFORGE Sendmail page for further details.

Availability
The Hyperic HQ plugin for Alfresco and Sendmail are available today and are supported for all current Hyperic 3.0 releases. The plugins, along with all other supported and user contributed plugins, are available on Hyperic?s HyperFORGE.

Hyperic is happy to announce the first beta of HQ 3.1. Be sure to download the beta and give it a spin. This release will include the following new features:

• Expanded Alert Features
  Introduces the Alert Center for central management of alerts in a flat hierarchy, Alert Resolutions descriptions track how to resolve problems, expanded alert email detail to include indicator metric data and previous alert resolution.
• Performance and Scalability Enhancements
  Dramatically improves out-of-the-box scalability for large deployments.
• MySQL
  MySQL 5.0 and above is now a supported database backend for the Hyperic HQ environment, and the HQ plugin for MySQL has been further enhanced for greater management detail.
• Embedded Contextual Help
  Improved context-sensitive help detail, configurable option to display up-to-date help remotely from support.hyperic.com or locally, and local web pages can be customized and expanded for unique deployment environments.
• Localizable UI
  The entire Hyperic HQ UI and help pages are now customizable for users to alter or translate UI elements. Sharing of translations is encouraged through a new area on HyperFORGE.
• Extended Product Coverage
  The new Hyperic HQ distribution bundles support over 13 new products, including notables such as Zimbra, Informix, and OpenLDAP, now deployed in the HQ Agent by default.

3.1 Beta Resources on Hyperic.com

• New feature descriptions
• Beta product page
• Download page

NetworkWorld?s Barry Nance just posted his bakeoff of open source management tools. This test focused on the open source products available from 3 vendors, Hyperic, Zenoss and Groundwork. The test was based on functionality alone, and did not consider other specialized topics such as scalability, setup or any special management of specific technologies. In Barry?s words:

We tested each product’s ability to discover, manage, administer, monitor, report on, diagnose, troubleshoot, reset, reconfigure and secure our network devices, applications, servers and clients.

Barry?s test scored Hyperic?s Open Source software a 3.6 on a scale of 5, and in the middle of the other two vendors. (Lowest a 3.1, highest a 4). We didn?t win the clear choice award, but we?re actually very proud of this review! Why? Well, Barry spent some time with us yesterday and talked about the two things he thought we needed to succeed - group alerting and automatic control actions. When we discussed that these were in the enterprise version, he suggested we take our Enterprise software to compete in the commercial arena. (Which we will, providing the editor gives us the chance!)

Knowing this, and knowing we have even more packed into the Enterprise Extensions we?re very happy with this review. But it does beg the question ? if those features make the functionality more complete, then why are they not open source? Don?t we want to win every bake-off contest?

We?re not interested in trading off between bake-off contests and a solid business plan that has enabled us to win in the market, and the market to win with us. See, we designed HQ to be the easiest to install, fastest time-to-value, and functionally complete software on the market today. Both our open source and our enterprise subscription offerings clearly illustrate that. Our users, customers, and partners agree. In fact, a lot of commercial open source projects out there make money on manageability of their systems, like JBoss, MySQL and MuleSource who all use Hyperic to provide value to their users.

So we are not bummed we didn?t win. It?s our plan to be an innovative, focused open source software provider for systems management that provides the absolute best-in-class software for the demanding scale and complexity of online services businesses. Our open source users can get a complete systems and application management solution up and running quickly ? and when they get to an appropriate level of scale (usually in excess of 100 machines), it actually becomes more cost effective to engage us for our subscription offering which is tailored specifically for their needs and provides the fastest time to value in the business. And even with the paid-for software, it?s still the most cost effective, comprehensive, and downright sexy software they can own in this space. Period. And being cost-effective for our customers means that we get paid, and ensures our open source?s projects longevity and ensuring the advancement and sophistication of the software for our customers. It?s a fundamental principle of our business plan which ensures that everyone in the Hyperic ecosystem is a winner. Go ask our ever-growing community of users and customers if you’re curious.