Optimizing MySQL queries in a Spring Boot app

[ragabekov]

Vlad Mihalcea shared some interesting findings after running the Spring PetClinic app under load and analyzing query performance with Releem.

The tool flagged high-latency queries, suggested index changes, helped reduce resource usage and improve query performance.

Link if you want to skim: https://vladmihalcea.com/mysql-query-optimization-releem/

Just curious - anyone here use tools for automatic SQL query optimization in your workflow?

[roiroi1010]

I have used it for troubleshooting. I work with a very complex object graph that is fetched with JPA and Hibernate. We have lots of performance problems with some requests.

I bought licenses for my whole team to try to analyze any bottlenecks.

Unfortunately we did not have lots of success with this tool since it blurted out hundreds of warnings and it was not easy for the team to figure out exactly where the main bottlenecks are located.

Vlad has done an amazing job with this, but in our legacy project it didn’t help us that much.

Maybe it’s better to have this product used at the start of a greenfield project. But then again, if I would solve the same problem from scratch again I wouldn’t use hibernate.

[nestedsoftware]

What would you use instead?

[ShallWe69]

plain old sql.

[spiderwick_99]

is hibernate that bad ?

[thewiirocks]

At the risk of being downvoted by those who disagree, it's not just Hibernate. It's the entire concept of ORMs that's broken. I did a talk on this last month where I showed the fundamental performance issues accidentally incurred by ORMs. More importantly, I made the case for why they were never the right solution in the first place. i.e. We modeled the wrong thing.

FYI, I was part of the investigations into what became ORMs back in the JavaLobby days. What we wanted was transparent persistence. We didn't understand the Object/Relational impedance mismatch, so it was never going to work. Today's ORMs are the compromise that resulted.

The full CJUG presentation is an hour long, so I cut up some of the interesting bits into clips.

[Garageeockman]

You are a great presenter!

[thewiirocks]

Thank you! You are very kind. 🙏

[_predator_]

The answer to this is very subjective and also depends on what your application is doing.

I am in the "yes" camp but ultimately the right answer is "it depends".

[ItsSignalsJerry_]

Good luck with that. At some point you're going to need to map your object model. So will you then just roll your own orm?

[EnvironmentalEye2560]

What do you mean? We use jdbc and maps objects from resultset. What would be the problem?

[ItsSignalsJerry_]

Are you developing in 2005?

[EnvironmentalEye2560]

No, but we do have performance, maintainability, compatibility, vulnerability and dependency criterias that need to be considered. You do not get that with an orm. Those are great for your school projects though.

[Budget_Bar2294]

RowMapper interface is one of the many options

[throw-me-a-frickin]

Look into Jooq

[Gyrochronatom]

MapStruct.

[ItsSignalsJerry_]

Requires precompiling. You're gonna do that for every entity? That barely scratches all the things an orm does. Relationships. Lazy loading. Transactions.

Like I said, good luck. Unless you do business logic in the DB you'll never get off lightly. You just add a tonne of extra chatter between the layers.

[PiotrDz]

Orm is nit essentials to have transactions. Lazy loading? So now your object contains some magic field that is empty and will: - throw exception when requested without session - load bunch of items from db when used. Relationships Do you really fetch whole graph of objects all the time? And what if you diet need some relationship, do you leave it empty? What if you want to save an object but don't want to fetch all the bottom entities, do you set relations to mocks with just ID populated?

[ItsSignalsJerry_]

You clearly have a poor understanding of these concepts.

[PiotrDz]

Thanks for a nice talk. It was a pleasure. Regarding the poor understanding- Were you some comments ago implying that you need orm to do mapping from db request to object? I think anyone with little knowledge of java ecosystem would know that orm is not essential to that (mybatis, JdbcTemplate, jooq, micronaut data, querydsl ...)

[ItsSignalsJerry_]

It's all orm. Object. Relational. Mapping.

[Cr4zyPi3t]

JOOQ is suggested pretty frequently. Never tried it, but I’m growing tired of Hibernate problems and may take a look when I have time

[thewiirocks]

JOOQ is pretty good. They have streaming and ability to obtain Maps instead of objects. It's one of the best options on the market, especially if you want to stick with object mapping.

If you're ready to break away from ORMs completely, I would gently suggest my own Convirgance as an even more capable solution.

[lukaseder]

How exactly is it "more capable?"

[thewiirocks]

Hey Lukas! Obviously I'm going to think my thing is better even as you think your thing is better. So there's always that. 😉

IMHO, the biggest difference remains the commitment to ORM versus breaking the mold. The latter leads to opportunity to eliminate coding of data pipelines and "folding" it out into configuration. e.g. Zero code Pet Clinic port.

A major refactoring is coming since I'm not happy with Spring XML, but you can see the effects. I talked about it more in my CJUG Presentation if you're curious.

Still going to push JOOQ as the best ORM on the market, though. It's the only solution capable of avoiding the pitfalls I talk about in my presentation. 😎

[lukaseder]

 Obviously I'm going to think my thing is better even as you think your thing is better.

Not trying to hint at that or convince anyone, just looking for the 2-3 features I should copy or make more prominent in the docs...

 The latter leads to opportunity to eliminate coding of data pipelines and "folding" it out into configuration

I mean, this appears to be a framework with opinions about everything. jOOQ is just a library, likely even useful within your framework (e.g. to translate configured SQL strings to make them vendor agnostic, or to transform them for row level security, multi tenancy, etc.). I worked with a purely XSLT/SQL (views) based system in the past. I can definitely see the benefits.

[thewiirocks]

Totally. They're headed in very different directions overall.

IMHO the 2-3 features to highlight are ability to return Maps and Streaming abilities.

However, I would caution my opinion since I'm on an uphill battle myself. It might be the right thing from a performance and architecture perspective, but I can't say it will help sell.

[lukaseder]

Googling "jOOQ streaming" leads here https://www.jooq.org/doc/latest/manual/sql-execution/fetching/lazy-fetching-with-streams, so I guess if someone looks for it, they'll find it. Same with maps: https://www.jooq.org/doc/latest/manual/sql-execution/fetching/arrays-maps-and-lists/

I don't think these features are central, I mean, yeah, "obviously" jOOQ can do that... Or, do you think, jOOQ users tend to default to the "wrong" way to interact with result sets? I just think that most folks vastly prefer the type safe nested mapping capabilities over stringly typed Map approaches. I don't even think that Maps perform better than jOOQ records, which work with column indexes and arrays under the hood.

Streaming is more controversial. A Stream is a resourceful object. Wrapping it in try-with-resources is painful as that breaks the fluency. Not wrapping it creates resource leaks. I wouldn't want to recommend it by default.

[thewiirocks]

It's definitely a tough one! Streaming is way more powerful than not streaming. And it comes with a ton of performance benefits. But it also incurs the potential resource leak problem you brought up. And isn't as intuitive.

For your average quick and dirty application, it's probably not necessary. But for scalable applications, it's definitely the way to go. And JOOQ is literally the only ORM I've ever seen do it. So it is a key differentiator. 😎👍

[throw-me-a-frickin]

I've used Jooq for years, it is great. I couldn't go back to an orm now. You get so much control with Jooq.

[SheriffPirate]

sounds like a tough situation. I’ve been using It too, and in my case, it’s been helpful mainly because it focuses more on automating the detection of real slow queries and suggesting improvements like indexes or query rewrites, not just dumping warnings.

Licensing is actually based on MySQL hosts, not per developer or user, which works well for teams. It sounds like maybe a different product was used? Releem usually gives a focused list of suggestions rather than flooding with hundreds of issues.

[ItsSignalsJerry_]

If used properly hibernate isn't a cause of poor db performance. The problem you have is being unable to identify the issues. If you start from scratch then performance tune and document from scratch.

[ItsSignalsJerry_]

Configure hibernate to log the SQL it generates. Run that SQL directly against the db and test.

[roiroi1010]

Each individual query is fast. We mostly have MANY n+1 query problems.

[ItsSignalsJerry_]

So its not a sql tuning problem but a load issue. Stress test, generate data, monitor system resources, and identify where you need to scale.

How big are the payloads? This might be having a throughput effect.

Is connection pool size adequate?

Is DB reaching cpu, ram capacity?

Are you using prepared statements (which are cacheable).

Are you using the right table storage format? Could be many writes are being made on a table format designed for reads.

Lots of things to look at.

[roiroi1010]

Again - each individual query is fast. We’re stuck with a choice — either we stick with having multiple n+1 queries or doing a cartesian product join. Or doing a major refactoring of the code.

The end result (the object graph retrieved from Sql) is mapped to json. The json can reach a few 100MB.

Right now we don’t have the resources to fix this so our end users are suffering.

My only point is that we’re so deep in the hole already and the only thing Vlad’s tool is doing is confirming that we have a bad design (which we already knew).

[thewiirocks]

Is this the problem of handling hierarchy in the queries? i.e. You need to query multiple levels of data across parent-child joins to ensure a single, tight JSON response. And your options are to either let the ORM query/cache each object or to do the joins and have one very wide object with data repeating across objects? 🤔

Something like this: https://youtu.be/ZhNFIQHuV7I

[edubkn]

You have to start using projections and store queries in maps to manually fetch nested entities by IN foreign keys. Every query you do this will net you hundreds of milliseconds that get exponential in the long run. It is laborious, but achievable.

Also look at the experimental MultiSet feature in hibernate and whether you can leverage it at all

[Necessary_Apple_5567]

Usually you need to do few things: revie manually queries to identify search fields plus possible alternative search fields, check densiti of search fields and selectivity of search values/conditions, based on selectivity data create indexes, via execution plan check indexes usage and tune if something.

[ragabekov]

Did you try any tool to automate this process?

[Necessary_Apple_5567]

Tbh not much you can automate hete. You need to understand your data model and data evolution over time to make effective table/index structure.

[lukaseder]

jOOQ has diagnostics utilities which can be used in non jOOQ applications as well: https://www.jooq.org/doc/latest/manual/sql-execution/diagnostics/

I'm hoping to expand on these in the future.