Hibernate tuning for high rate of inserts and selects per second

Question

Hello,

We have a data acquisition application with two primary modules interfacing with DB (via Hibernate) - one for writing the collected data into DB and one for reading/presenting the collected data from DB.

Average rate of inserts is 150-200 per second, average rate of selects is 50-80 per second.

Performance requirements for both writing/reading scenarios can be defined like this:

1. Writing into DB - no specific timings or performance requirements here, DB should be operating normally with 150-200 inserts per second
2. Reading from DB - newly collected data should be available to the user within 3-5 seconds timeframe after getting into DB

Please advice on the best approach for tuning the caching/buffering/operating policies of Hibernate for optimally supporting this scenario.

BTW - MySQL with InnoDB engine is being used underneath Hibernate.

Thanks.

P.S.: By saying "150-200 inserts per second" I mean an average rate of incoming data packets, not the actual amount of records being inserted into DB. But in any case - we should target here a very high rate of inserts per second.

Answer 1

I would read this chapter of the hibernate docs first.

And then consider the following

Inserting

• Batch the inserts and do a few hundred per transaction. You say you can tolerate a delay of 3-5 seconds so this should be fine.

Selecting

• Querying may already be ok at 50-80/second provided the queries are very simple
• Index your data appropriately for common access patterns
• You could try a second level cache in hibernate. See this chapter. Not done this myself so can't comment further.