Charles Lee, VP Engineering, Hyperic.
I read somewhere that MySQL has a 44% share of the global RDBMS market and adding thousands of new users and customers annually. In fact, it stands for the "M" in the acronym, LAMP. Hyperic HQ recognizes the important role MySQL plays in the application stack and has always had a strong support for the management of the MySQL database, as well as the rest of the LAMP stack. For HQ 3.0, we migrated our EJB2 persistence layer to Hibernate and hoped to gain database portability. Once that work has been done, it would only make sense that we leverage the expertise and user base that has been built around MySQL to not only manage MySQL, but also use MySQL to run HQ. This would also prove that our migration efforts were not in vane (well, we did have some other benefits to get out of it). In the last week, with the help of an affable MySQL consultant named Anand Pandey, we did just that.
We definitely had some challenges. While Hibernate had full support for MySQL (we chose InnoDB storage engine because of our read/write/transactional needs), we still had our work cut out for us. First and biggest problem is the generation of the primary key for the database tables. At one point we maintained our own ID generator in the application, but that proved to be somewhat of a performance and transaction bottleneck, so we switched to rely on sequences in Oracle and PostgreSQL. After all, databases are good at those atomic operations. Now, we have two requirements for the ID generation, which were fulfilled by the use of database sequences:
1. We have certain entities that require hard-coded IDs that get pre-populated in the database during installation
2. We start the sequences at 10001 so that we can reserve numbers for these hard-coded IDs
MySQL does not support or use sequences, it uses an auto-incrementing column, which is fully supported by Hibernate. However, we can fulfill neither of the requirements with auto-incrementing columns. The solution for us was to create a table that would hold all the sequence numbers and a MySQL database function that can increment and return a new ID value. We extended the MySQL5InnoDB dialect class to use this scheme by implementing the sequence support APIs.
After resolving this major hurdle, the remaining tasks fell into place. We had to modify a couple of the column type definitions in the schema because of unexpected MySQL database types that Hibernate would choose based on the definition. The installer had to be modified to add support for deploying to MySQL, and the build scripts had to be knowledgeable about the additional database type. All this work has gone into the branch for the summer release of HQ and is currently on HEAD in the code repository. Unfortunately, the changes are extensive enough such that merging down to the current HQ 3.0 branch would be much too risky and cause undue instability. I plan to speak in greater detail about our Hibernate migration and MySQL port at the MySQL Conference & Expo in April. If you are impatient or adventurous enough, I will put together a how-to document on how to port HQ 3.0 to use MySQL after the conference. However, when the summer release beta becomes available, everyone will have access to an HQ that can use MySQL as a backend store. We are very interested at this point what the scalability and performance of MySQL is compared to our other two supported databases. So if you would like to be an alpha tester and run HQ on MySQL in your environment before the betas become available, please e-mail me at
charles.lee@hyperic.com.
Discussion:
http://www.theserverside.com/news/thread.tss?thread_id=44918#230747
Hibernate identifier generators:
http://blog.hibernate.org/cgi-bin/blosxom.cgi/2007/04/09#enhanced-id-gen
Edited 1 time(s). Last edit at 04/27/2007 12:41PM by Edwin DeSouza.