Performance problems in production environment together with mysql

Hibernate ORM
1

Hi,

I am maintaining an application that is using Hibernate 5.4.21.Final on JDK 1.8 with a REST interface. As a backend we have a MySQL db. Approx 85 GB of data.

Now we are experiencing performance problems in production. Storing/fetching data slows down. Sometimes database connections are closed and the application throws exceptions related to it.

I have worked a lot with RDBMS and JDBC but not with Hibernate. But I suspect that there inefficiencies in the fetching of data that can be very costly as data grows.

Q1: I have read that code annotations like this:
"@OneToMany(mappedBy = "productRevision", cascade = { CascadeType.MERGE }, fetch = FetchType.LAZY)"

Could affect the perfomance and could be replaced with something with @NamedEntityGraph.

@NamedEntityGraph(name="graph.productRevision.levels",attributeNodes =@NamedAttributeNode(value="levels"))

So FetchType.LAZY should not be used. Since the line:
"@OneToMany(mappedBy = "productRevision", cascade = { CascadeType.MERGE }, fetch = FetchType.LAZY)"

is removed I guess I don’t need to care about the cascade.

Then in my ProductRevisionDao I add the following:

private ProductRevision getProductRevisionById(Long revisionId) {
//Using EntityGrap to make query to avoid n+1 problem.
EntityGraph graph = entityManager.getEntityGraph(“raph.productRevision.levels”);
Map<String, Object> hints = new HashMap<>();
hints.put(“javax.persistence.fetchgraph”, graph);
return getById(ProductRevision.class, revisionId,hints);
}

Will this increase the performance in the application?

Q2: I can see that we do:

entityManager.merge(entity)

and then call flush afterwards. Like

Session session = getHibernateSession();
session.flush();

I have read that there is no need to use flush since hibernate handles it.I have read that it can make application slow. Can anyone confirm it?

Can I remove it?

I also checked the db log and can see that there is only a few insert but there is a lot of find/select.

Q3: We used Java Flight Radar to profile the application. But there was really nothing strange. No thread problem. However I thougth there was a lot of threads called:

http-nio-8080-exec-#
And when I checked with Java Mission Control I could see that they try to connect to db for some time.

image
image1349×543 44.9 KB

I just know that this is “WorkerThreads” that are spawned for each request for REST to make it responsive. I guess these worker threads runs the queries to db.

I would appreciate if you, more experienced hibernate developers,could assist me with comments and advise.

br,

//mike

2

Q1: Mapping associations as lazy is a good practice and you shouldn’t change that IMO. Using appropriate entity graphs in places where it makes sense is the way to go. Whether it will improve performance depends on a lot of factors.

Q2: Whether Hibernate does flushing automatically depends on a setting, but unless you messed with that, the default is to flush before commit. See https://docs.jboss.org/hibernate/orm/5.6/userguide/html_single/Hibernate_User_Guide.html#flushing

Q3: I have no idea what your application does, so unless you have a very specific question about Hibernate, I think you should consult someone with expertise in performance analysis.

I mean overall, you are not even telling what the exact problem is. First, try to isolate the problem and understand which request is causing problems. Then you can analyze what happens during such requests. Understand what queries are executed, how long they take and analyze which take a long time to execute as well as where these come from in your code. Maybe you are just missing an index? Also consult your DBA about this.

3

Hi @beikov

Thanks for advice!

We now found a SQL query that takes over a 1 min and 40 seconds to execute in production.

SELECT pra.id AS rev_id FROM product_baseline_association assoc JOIN product_revision pra ON assoc.baseline_id = pra.revision_baseline_id WHERE assoc.product_revision_id IN ( )

I could not type all ids that are within the paranthesis since there is more than 38.000 ids.

I guess this design worked good for small dbs but with many products and revisions ( large db) I guess there will be a lot of time spent to evaluate.

Is there a way in hibernate to improve this performance?

br,

//mike

4

There is nothing Hibernate can do here since it is the databases that is consuming the time. The problem is the way you are querying though. I am pretty sure, that these 38.000 ids are fetched by a query before, so the proper way to solve this is to inline this id fetching query into that other select query so that the database can maybe do a more efficient join. Or maybe you don’t need all these 38.000 ids at all? Maybe you just need to apply a limit and work on 10-20 of the ids? I simply don’t know your application, so you will have to figure this out yourself.

5

In such situations, it migh also be worthwhile to look into breaking down the queries and ensure that at least one aspect is able to hit 2nd level caches. (and of course enable 2nd level caching).

Also don’t forget the ABC of performance tuning: get an execution plan of the SQL and check appropriate indexes are in place.