From Charles Lee, VP of 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 dot lee at hyperic dot com.

"At one point we maintained our own ID generator in the application, but that proved to be somewhat of a performance and transaction bottleneck" It turns out that you are not the only application availability monitoring vendor to make this same mistake. HP OpenView which in your company blog is referred to as one of the "traditional goliaths" has made this very same mistake in their ITIL service and configuration management products. Article: Database Concurrency - Sequence Generators http://www.jinspired.com/products/jxinsight/concurrency.html "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." How does this compare in terms of performance to the built-in auto-incrementing? Have you any figures under appropriate loads? William

Hi William, Thanks for the sympathy vote :-). We are trying very hard to not repeat the mistakes those "goliaths" made, it's the reason why we exist. As for performance, upon a cursory evaluation, the difference is negligible and inconclusive (different in milliseconds both ways). From the application perspective, since we've made the ID generation a function, we don't incur the cost of the 2 roundtrips to the database. Of course the proof will be in the performance underload. We have not had a chance (or the resources currently) to do that test, and that's why we're not shipping a product with MySQL support enabled just yet. We're looking to our community of users to help us with this evaluation, henceforth my call for alpha testers. I will report back as soon as we have something conclusive. Charles http://www.hyperic.com

The speed at which the world economy is moving is truly incredible. Thus, the importance of power availability cannot be emphasized enough.paperwisdom.co.uk/essay

"At one point we maintained our own ID generator in the application, but that proved to be somewhat of a performance and transaction bottleneck"

Interesting.... Why is this a bottleneck ?... Ed

I am absolutely amazed at how terrific the stuff is on this site. I have saved this webpage and I truly intend on visiting the site in the upcoming days. Keep up the excellent work!
Admission In UK

Well that's superb! I really loved this post. You write about this topic very well. I really enjoy reading your blog and I will definitely bookmark it! Keep up the interesting posts.....<a href="http://www.pk-forum.ch/">Vorsorgen</a>

Thank you for sharing to us.there are many person searching about that now they will find enough resources by your post.I would like to join your blog anyway so please continue sharing with us Hair transplant Lahore

There are definitely a lot of details like that to take into consideration. That may be a nice point to bring up. Pensionskasse

Starting in 3.2.3 (released just last week), I started including 2 new identifier generators targetted at portability. You can see a lengthy discussion of them between the wiki and the documentation bundled with the 3.2.3 release. Specifically, the one I would suggest for your stated requirements would be org.hibernate.id.enhanced.SequenceStyleGenerator, using initial_value="10001". I'd also recommend specifying an increment_size, but you'd need to play with them to find the ideal value; try starting with 10 and working from there. Further I would suggest applying either the hilo or pooled optimizer. As for the column types... well report what you think they should be to the Hibernate JIRA. I can't change those mappings if I don't know there is a problem ;)

Good to know, Steve. I will definitely check out the new releases for the ID generators. We decided to peg the baseline implementation on MySQL 5.0 due to what was more widely used by people. Perhaps we can explorer embedding a MySQL database that has the new ID generators? The Hibernate column types mapping aren't wrong per se. It's more about the default types that were chosen based on the HBM files that we defined. It's just a matter of being specific about column widths in certain places, because Oracle and Postgres had other default values. Thanks. Charles http://www.hyperic.com

Sorry for the meta comment, but I don't see how you get from the body of this post to the headline. Am I just slow? ;)

Sorry for the meta comment, but I don't see how you get from the body of this post to the headline. Am I just slow? ;)

James, this is how: http://en.wikipedia.org/wiki/Yellow_journalism TSS is yet another tabloid. What did you expect - engineering forum? :)

Sorry for the meta comment, but I don't see how you get from the body of this post to the headline. Am I just slow? ;)

James,

this is how: http://en.wikipedia.org/wiki/Yellow_journalism

TSS is yet another tabloid. What did you expect - engineering forum? :)

Well, thank you. But consider: the post was mentioning the difficulties in using MySQL, and how Hibernate did a good job of solving them - thus Hibernate enabled MySQL. In any case, that's actually the title submitted by... the poster. If you want an engineering forum, then sometimes you have to put up with forum-like titles/posts/contents.

Joseph, It was not meant as an offense. Sorry it if came out that way. IMHO, TSS is doing very well (traffic and exposure-wise) and I can only congratulate and applaud you. So, it's all good... - as far as we don't try to hide anything, including the sensationalism that has become a trend here, lately. :-) P.S. The title might have been what author submitted but TSS News is a moderated publication so it's not really an excuse - we both know that. ;)

The article was all about changes we made to support MySQL. Yeah, we could have included more about why EJB2 was a hindrance and why supporting MySQL was reliant upon a major structural change, which in this case meant a Hibernate migration. Was the title "sexed up"? Well, sure. However, the question I have had is... how many other engineering groups are in a similar position - they want to support MySQL and will need to make significant changes to do so. -John Mark Hyperic Community Manager

Good evening,

Am am a similar situation, using MySQL and am wanting to over-ride the MySQLDialect class so that our application can handle sequences.

You mention this in your article: 

"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."

Do you by chance have any of the solution code posted anywhere (or would you be willing to post it), specifically the class(es) that you wrote that over-rides MySQL5InnoDB class?  If this is possible I would also be interested in seeing the code for the database function and the columns in your new database table to hold the sequence information.

I understand this is a old post, but this would help out a great deal if you could help in any way,

Thanks in advance, Wes

 

 

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.

We'd come across the same concerns with sequencing, and I addressed it by implementing a Sequence Service (Singleton JMX) charged with providing a "read-ahead" number of available IDs and dispensing from the cache as provided. It's backed by one database table, with one row per sequence required. This allowed us to manually set all IDs, know them ahead of time, and provide an offset (also 10000) and increment (only even numbers are dispensed, in case we'd like to insert inbetween for some unknown reason). As far as performance/bottlenecks are concerned, if the read-ahead covers enough ground (say 100 next IDs), you'll only make a DB hit once per every 100 inserts per table. S, ALR

Ah, this is actually similar to the scheme we employed when we developed our own sequencing in the application. We actually allowed individual EJBs to define the increment range depending on how often new entities were being generated. However, we ran into a couple of problems. 1. We deploy HQ in a clustered environment, so you have a race condition if multiple nodes are reading and then updating the sequence number (you can resolve this by making the operation atomic as a database function or procedure, but then it's more work to write functions on both sides. 2. When we were using this "read-ahead" method, we were in EJB2 and JBoss 3.x. Since we had about 70-80 tables that used incrementing IDs, the CMP in the application server would often lock up the entire sequence table and performance degrades and deadlocks happen. Perhaps this has been resolved by the combination of Hibernate and some bug fixing in JBoss, but it was easy enough to stick to databases that provided us with real sequences. We had other application issues like our dispenser was skipping IDs (that is, of course, our own failure as application developers). But all-in-all, there were enough problems there for us to decide not to use this scheme again. Thanks for sharing, Andrew. This is certainly not a unique problem to us, and I know that people have different solutions. I'm just trying to offer one for MySQL that we've found to be pretty straightforward and simple to implement. Charles http://www.hyperic.com

Thanks for sharing, Andrew. This is certainly not a unique problem to us, and I know that people have different solutions. I'm just trying to offer one for MySQL that we've found to be pretty straightforward and simple to implement.

Definitely. Cool to see that you'd extended the dialect; I hadn't thought of that. :) Thanks for pointing out your problematic experiences; I'll be careful to review those areas once we go to clustering, and have more users and demand on the system in production. S, ALR

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.

We had no problem with those requirements: our hard-coded IDs are negative.

That's interesting, I wasn't aware that MYsql had a 44% share of the RDMB market. Has this statistic changed since you first mentioned it?

i want to be a bit tests, in fact i will like to use it in my organization. I had sent you an email but haven't had any reply yet....hope to hear form you soon

Pelo que tem visto não é nada fácil conseguir um bom curso de <a href="memorizacaohttp://www.memorizacao.net/">memorizacao</a>. muito obrigado

I stumbled upon this website, HVC, that boasts about using Dynamic Database Framework. I'm not sure if mysql, coupled with Hibernate is capable of that feature. Is is possible for the fields or the number of fields in a structured database created using mysql to be changed or edited at a future time? 

I think MySQL is compared to our other two supported database.

 

I never knew EJB2 could be migrated to persistence layer to Hibernate and hoped to gain database portability. MySQL is a real giant holding enough shares to pull enough workers and customers. This is a great post and has just proven to be where I need to obtain information for my InnoDB storage engine. Many thanks for this post. I do appreciate it.

 

I'm confused still as to what Hibernate is? can you elaborate?

I have seen projects failing because bad argumented decissions. Some important boss decided we couldnt use hibernate for prototyping, engineers were changing schema every week and developers were spending weeks just adjusting to schema changes. Time inverted in this task prototyping if you use Hibernate? Zero.

Thanks.

http://www.thealhambra.es

Muito bom o site, mas conseguiria trazer mais depoimentos se fosse tratado as tecnicas de <a href="http://www.comoaumentaropenis.com/depoimentos.htm">como aumentar o
penis</a>

Muito facil os segredos estão no site como aumentar o penis e estão todos apropriados.

I think its amazing how many programs utilize MySQL... I'm not suprised to see that MySQL has 40% of the market. Orlando Wildlife Removal

There are definitely a lot of details like that to take into consideration. That may be a nice point to bring up.<a href="http://www.sleepcheaprochester.com">Rochester Furniture Stores</a>

Enjoyed reading every line of the article, thanks for the writer!

research paper topics at http://essaylib.com

Wow! so nice and great post. I like your post very much.

http://www.missneopoints.com/