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.