What is Hibernate doing to my Database?

[Mike Sanderscone]

Hey all. I'm new here, and I have lots of questions!
I'm on the database end (ms sql) of Hibernate.

I really want to learn more about what Hibernate/JDBC is doing.
We get a problem almost weekly, where the DB is thrashing, and I need to understand what Hibernate is doing.
But, I can't seem to find Hibernate-Datasets anywhere, it's almost like it doesn't have any!
I need to get more info on our H-System... like:

How can we monitor?

How can we control Caching?

How can we control predictive Data?

What datasets/objects is H pulling from?

The devs don't really seem to know, they just say: Hibernate is wonderful, and it knows the best optimized path to get data.. but they have no supporting evidence, and I dont think they really know! It's a big mystery!

[Roel De Nijs]

Hibernate is wonderful indeed, certainly for developers who (mostly) don't like to write SQL statements. But if you have some flaws in your entity mappings or configuration, it can result in poor performance too.

In short: based on a set of entities (plain old Java objects with some annotations or a mapping file) Hibernate will generate SQL statements to query the database and convert the records from the database into objects. It also generates queries to perform inserts, updates and/or deletes. Hibernate has also excellent documentation.

If you want to see the executed SQL statements, this can be easily be done by enabling the hibernate.show_sql property<property name="hibernate.show_sql">true</property> <property name="hibernate.format_sql">true</property>More info you'll find in the configuration and logging sections.

[Karthik Shiraly]

Welcome to CodeRanch !

The devs don't really seem to know, they just say: Hibernate is wonderful, and it knows the best optimized path to get data.. but they have no supporting evidence, and I dont think they really know! It's a big mystery!

It is a big mystery. Any dev who spends lots of time with Hibernate can become a very good relationship counselor (get it? "relationship", because hibernate is an ORM - object-relational mapper! ha), that is if they aren't burnt out and in an asylum. Btw, I'm a dev and I don't really think Hibernate is wonderful.

Jokes aside, I don't know answers to all your questions but have suggestions for some of them:

I can't seem to find Hibernate-Datasets

Do you mean datasets from monitoring hibernate performance? Its performance tuning is very very application and query specific.
I doubt any comprehensive public datasets exist because they are probably not useful, neither to publish nor to consume.

How can we monitor?

Hibernate has its own performance indicators.

Hibernate can use different caching solutions like EHCache or JBoss Cache (your devs would know what is being used).
Each of these have their indicators.

In addition, the application that's using Hibernate likely runs in some kind of a application server (like JBoss)
or web server software (like Tomcat), all of which also publish their performance indicators.

All the above are running in a Java VM process, which also has its own performance indicators like number of threads, and heap usage.

Lastly, the Java VM process itself runs on some OS, which has its indicators related to CPU load, disk usage, or memory usage.

I mention all that because what hibernate does can affect performance of all the above. So it's not enough to monitor only hibernate.
The first 4 - hibernate, cache, server software and Java VM - publish their indicators through a java performance monitoring standard called JMX.

Popular infrastructure monitoring solutions like Nagios, Cacti and others can read these indicators via JMX , record them for future analysis, and display or process them in graphs.
If you are not using such a solution, there are Java tools like JConsole and VisualVM, which can display such information (though they can't record them for future analysis).

How can we control Caching?

Mostly by deployment time configuration, and some configuration changes at runtime. Depends on what caching solution you are using (your devs will know).
For example, EhCache supports some dynamic configuration via the same JMX mechanism

How can we control predictive Data?

Not sure I understand what you mean by predictive data. Can you explain what you have in mind?

What datasets/objects is H pulling from?

Configure hibernate logging. Caution: Very detailed logging will affect performance. So keep the logging data collection and performance data collection sessions separate.

[Mike Sanderscone]

Great info!! Thank you so much!!!
I quickly get overwhelmed with Java terms.. but I am learning.
I understand that Hibernate can 'Predict' what data might be asked for next.. and go out to get that data.. maybe not?
Hibernate seems like it magically goes out to grab data.. How does it know which data to get? It doesn't seem to use Views or Stored-Procedures. Does it always look at Tables. It seems like there has to be a mapping somewhere that says: If I ask for EmployeeId, then go look at MyEmployeeTable..
When you use the 'hibernate.show_sql', where does the query appear?
We typically see weird-looking queries, and call them Hibernate, does Hibernate always use those?
Example: "select book0_.id as id0, book0_.title as title0..."

I really appreciate the help and insight! I couldn't find it anywhere else

[Roel De Nijs]

Mike Sanderscone wrote:We typically see weird-looking queries, and call them Hibernate, does Hibernate always use those?
Example: "select book0_.id as id0, book0_.title as title0..."

That's definitely a typical Hibernate query, but it's not really weird-looking at all. Based on this query I can predict the Book entity the Java devs have created

@Entity
@Table(name="book")
public class Book {
  @Id
  private Long id;

  @Column(name = "title")
  private String title;

  // getters/setters
}

So each entity is a mapping containing the table and fields. Hibernate will use these information to generate the queries and convert a record into a Book instance. More info you'll find in the Mapping Entities section.

Mike Sanderscone wrote:When you use the 'hibernate.show_sql', where does the query appear?

That depends on the logging configuration. That's a property you enable during development and testing (to assess which queries are executed), but rarely use in a production environment.

Mike Sanderscone wrote:I understand that Hibernate can 'Predict' what data might be asked for next.. and go out to get that data.. maybe not?
Hibernate seems like it magically goes out to grab data.. How does it know which data to get?

That's too much credit for Hibernate The Java devs create a set of entities (like the Book class). And then when a book is requested by ISBN, Hibernate generates the query to select this book and will convert this record into a Book object. So based on the Book entity, Hibernate knows which database table it needs to query and which fields should be returned.

[Karthik Shiraly]

Mike Sanderscone wrote:I understand that Hibernate can 'Predict' what data might be asked for next.. and go out to get that data.. maybe not?

I haven't come across any such predictive behavior...perhaps your devs used 'predict' to explain some other feature in a simple way.
Generally, hibernate's attitude is "lazy fetching", that is fetch data only on demand when code is about to use it.

For example, say there are 2 tables - Employees and Companies.
When using plain SQL queries from java, we would do a SELECT JOIN query to get an employee and maybe join some fields of their company,
copy their values to some variables, and use those variables at some point later on - for example,
display their values on the screen using some (pseudo java) code like "print employee.companyName"

But when using Hibernate, we just ask hibernate to fetch the employee and it fetches only the employee,
not company until later it comes across code like "print employee.companyName", realizes it
needs companyName from company table, and then reads company table only at that point (or reads it from cache if cache is not dirty).

Hibernate seems like it magically goes out to grab data.. How does it know which data to get?
It seems like there has to be a mapping somewhere that says: If I ask for EmployeeId, then go look at MyEmployeeTable..

Nothing magical really. Java code likes to see everything in terms of classes and instances of those classes.
All employees are a class and each individual in that class is an employee object.
All companies are another class and each individual in that class is a company object.
There is a relationship between the java classes of employees and companies. This relationship is expressed one way in java code,
and in a different way as relationships in relational DBs.

Developers have to tell Hibernate how to map java classes to DB tables and columns, and
how to map java relationships to DB relationships. That's why it's called an "Object-Relational Mapper" (ORM).
Then developer writes logic that says "hibernate, fetch employee object with ID 42", and thanks to the mapping,
hibernate knows how to map employee with ID 42 to the correct DB table and column.

It doesn't seem to use Views or Stored-Procedures. Does it always look at Tables.

It doesn't create them at runtime. But developer (or db admin) can create views and map the java class to view instead of table.
Similarly, it's upto dev or admin to create stored procs. Hibernate can only call stored procs,
and any results can be mapped back to java objects like with regular queries.

When you use the 'hibernate.show_sql', where does the query appear?

Wherever the applications's logging is configured by devs. Typically that's to log files on the server on which application (and hibernate) are running. Not on the DB server.

We typically see weird-looking queries, and call them Hibernate, does Hibernate always use those?
Example: "select book0_.id as id0, book0_.title as title0..."

Yes, those aliases are generated by Hibernate, apparently to optimize things like joins. It's normal.

[Dave Tolls]

You say your db is thrashing.
What do you see when you pull up the stats for what is actually executing at that point?

If there's no query running then what do you mean by "thrashing"?

Once you know the query you can then back track to the app itself.

Is there a job with a dodgy query that runs that's badly tuned?
Is there somewhere where the load of a mapped object is not lazy, but attempts to load the object and all its relationships?

But the first thing to do is find out what is causing it to get hammered, and that's something you can do in the db, probably better than the Java devs can from their end.

[Mike Sanderscone]

Thank you all!!!
I feel like we are peeling back the onion slowly.

The 'Thrashing' comes about once a week.. where we have CPU toward 95% on the Database, with Low I/O (like its not actually producing much data)... and High DTC connections (30 or so).. This problem goes on for about 2 hours.. and users can hardly do their work.
We all just monitor, and can't tell where it's coming from or what is happening.
DBA's believe that Hibernate is over-querying the database (because there are lots of JDBC connections)..
DEV's believe that the Database is causing an issue.. because it's crawling.. but nobody 'really' knows!
My goal is just to discover what is wrong!!

[Dave Tolls]

The DBA can (or should be able to) tell you exactly what queries are running.
The Dev's should be able to find out exactly what queries are running, from their logs.
One of those two groups should also be able to say whether there are any weekly jobs that execute at the time this happens.

Devs and DBAs shouldn't be passing the buck like that...it's not healthy.

Low I/O simply means it's doing stuff in the db. A wild guess would involve locking some resource, which brings me back to some weekly event...backups, data loading, something.

Had an issue at one client where they would hit a big db slow down once a month. Turned out that was the day everyone in the company produced their reports...

[Roel De Nijs]

Mike Sanderscone wrote:DBA's believe that Hibernate is over-querying the database (because there are lots of JDBC connections)..
DEV's believe that the Database is causing an issue.. because it's crawling.. but nobody 'really' knows!

Believe It's not a guessing game. And blaming each other will not solve the issue.

Both (should) have the tools and the knowledge to know:

which queries are generated and executed from the application (DEVs)

which queries are actually running when the DB is "trashing" (DBAs)

At one project I spent (as a DEV) many hours looking at execution plans of queries (generated by another application) to see why some of the queries had a poor performance and if we could do something to improve it. So if I am able to do such a job (because we didn't have DBAs ), a DBA should definitely be able to do this.

[Dave Tolls]

At one client (which shall remain nameless as I doubt I could afford the lawsuit) they had DBAs, but they were outsourced and apparently the contract was solely to keep the database up and maintain backups. They took no stats, no monitoring of query plans or anything like that. I only discovered this when there was a space issue developing and we were supposed to figure out how long it would take to fill the remaining disks. I pointed out that Oracle had tools for monitoring table sizes and keeping track of growth rate so why not look at that, as that would give close to accurate figures for when it would fill up..."oh, but the DBAs don't do any of that, so it's not turned on".

This was a big multinational...