Optimizing Hibernate and Mysql Performance

Posted on Posted in Java, Technology Center

Optimizing Hibernate and Mysql Performance

I would like to share some real-world experiences in improving Hibernate and MySQL performance that we have encountered for the past couple of years. Some of the large applications we developed handles about 500 concurrent users on 8 gigabytes worth of data. For such application size, performance becomes a concern and optimal settings are necessary to maintain acceptable response time.

Here are some things you may consider to improve application performance.

1.) Review table indexes. Table index is a basic concept in database. However, as your application and schema grows, checking indexes become more complex and your ORM tool cannot automatically define the indexes for you. To review your indexes, enable show_sql and check the SQL statements if attributes in where clause have proper indexes. Also, review that indexes are created on foreign keys. In short, do not trust that your ORM tool will automatically define the indexes for you.

Note: If possible, make your indexed columns not-nullable. MySQL has better indexing performance on not-nullable columns.

2.) Identify slow-query for optimization. Turn on slow-query-log and analyze, optimize these SQL statements.

3.) Check for n+1 hibernate queries. With show_sql enabled, observe the pattern of SQL statements issued in your application. Whenever you see a bunch of similar SQL statements being executed unnecessarily, you are likely experiencing the n+1 select syndrome – which is a common problem in Hibernate. Review your query operation and consider rewriting it to return only the specific data needed.

4.) Check Mysql server settings. Make sure your Mysql settings (my.cnf) are properly configured. Do not be intimated with these settings because there are really only a few settings that really matters (in most cases). The ones your should review include key_buffer_size, query_cache_size, table_cache_size, thread_cache_size, sort_buffer_size, read_buffer_size.

Note: As a general rule for InnoDB engines, key_buffer_size value should be between 70%-80% of your machine’s physical memory on dedicated database server.

Note: Make sure that you do not exceed your memory settings against the physical memory, otherwise, you will be storing data into the swap memory or ran out of address space.

5.) Use second level cache on read-only entities. Setup second level cache (e.g. eh-cache) on read-only entities (e.g. country, state, etc.). This eliminates the database operation and keeps result in the application layer cache.

In addition to the tips above, you may also be interested in checking the following:

  • If your application is slow while the server is barely doing anything, you need to find the bottleneck and one possible bottleneck is your maximum database connection pool size. Check your maximum connection pool size on both Mysql and Hibernate settings. If the value is low (e.g. less than 50) consider increasing it (e.g. more than 100) as necessary.
  • If you are using MyISAM, check for table locking. This causes your entire database operations to be queued thereby slowing your server response time. The quickest solution is to switch to InnoDB. InnoDB uses row-level locking instead of table locking.
References:
High Performance Mysql (2nd Edition) – Baron Schwartz, et al. 2008

One thought on “Optimizing Hibernate and Mysql Performance

  1. Your posting rutsck a chord with me. This is one of the things on my to-do list that never seems to quite make it to the top. It’s a very chicken and egg thing, but I frequently find myself wanting to refactor some of the “old baggage” from a DBA perspective, with the php apps that have their roots in MySQL 3.23 or 4.x.The problem I always hit is the app devs always resist, because they feel a need to code to the lowest common denominator (due to people using shared hosting etc), which is frustrating because resisting progress, is the antithesis to our industry. Hosting companies will never upgrade their MySQL offerings if the customers don’t demand it; and the customers will never demand it, as long as apps are coded to MySQL 3.23 standards and never evolve to their full potential by using the DB to full effect. Why cascade on delete in the PHP code and risk orphan records, when you can do it in DB more quickly, easily and safely in the DB?I’ve noticed a few projects starting to push the bounds, Drupal, Magento etc, but sadly they are the minority. Oh well, it’s on the list.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.