Accessing Data Through Hibernate; What DBAs Should Tell Developers - - PowerPoint PPT Presentation

Accessing Data Through Hibernate; What DBAs Should Tell Developers and Vice Versa

Marco Tusa and Francisco Bordenave Percona

2

Who Are We?

Marco Francisco

3

Agenda

• 1. Basic description of Hibernate architecture
• 2. Basic description of MySQl architecture
• 3. What is an Object entity in Hibernate?
• 4. What it means for a DB?
• 5. JDBC
• 6. Simple example with One table (employees) as object
• 7. What it is employees as DB object?
• 8. CRUD example with SQL generation
• 9. What is the overhead in using Hibernate?

10.What is a composite object and what is the impact? 11.Can we do better?

4

Introduction / Disclaimer

the important … is to have clear ideas and coordinated direction

What is this fish? I didn’t ask for sushi Java? Why is he talking about coffee, I

• rdered sushi!

5

Hibernate Basic Architecture

Benefit from developer’s point of view:

• Object-relational mapping (ORM)
• Makes it easy to access data
• Rows become objects
• Don’t have to deal with how data is accessed
• Data persist in the application after query
• I can access multiple sources with

same logic/code In short my life is easier without dealing with SQL

6

DBA

Let’s see a very high level and basic description of internals

7

DBA

8

DBA

EASY RIGHT?

9

DBA

Now being serious

MySQL is the RDBMS InnoDB is the engine Data is organized in 16kb pages Clustered Primary Key and then secondary indexes

1

DBA

Now being serious

MySQL is the RDBMS InnoDB is the engine Data is organized in 16kb pages Clustered Primary Key and then secondary indexes

InnoDB supports transactions (i.e. BEGIN/COMMIT) and has ACID capabilities:

• Atomicity
• Consistency
• Isolation
• Durability

Isolation means transactions should not affect other transactions when running concurrently.

1 1

Data Access Using Hibernate

• So I have a DB
• My code
• JDBC
• Hibernate

If I have Hibernate why I need to know all these additional things? I just need:

• Hibernate SessionFactory (heavy to create

and one for Application or by data store)

• Hibernate Session, light, ephemeral,

Open-close it inside nuclear operations

1 2

What is an Entity? And States?

I need to know that I have this:

1 3

Simple example with One table employees

• Mapping definition
• Code

What is an Entity?

<hibernate-mapping package="net.tc.employees"> <class name="Employees" table="employees" catalog="employees"

• ptimistic-lock="version">

<id name="empNo" type="int"> <column name="emp_no" /> <generator class="assigned" /> </id> <property name="birthDate" type="date" > <column name="birth_date" length="10" not-null="true" /> <property name="firstName" type="string" > <column name="first_name" length="14" not-null="true" />

public class Employees implements java.io.Serializable { private int empNo; private Date birthDate; private String firstName; private String lastName; private char gender; private Date hireDate; private Set titleses = new HashSet(0); private Set salarieses = new HashSet(0); private Set deptEmps = new HashSet(0); private Set deptManagers = new HashSet(0); public Employees() { } public Employees(int empNo, Date birthDate, String firstName, String lastName, char gender, Date hireDate) { this.empNo = empNo; this.birthDate = birthDate; this.firstName = firstName; this.lastName = lastName; this.gender = gender; this.hireDate = hireDate; }

DBA

So what’s an entity in relational database?

DBA

So what is an entity in a relational database?

Simple: it is a row in the table

show create table employees\G *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec) show create table salaries\G *************************** 1. row *************************** Table: salaries Create Table: CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

1 6

Connection Pool + JDBC Pollution

Connections:

Important to know that a connection=thread in MySQL. Thread allocates buffers (tmp_table_size, sort_buffer_size, etc) --> Thread = CPU and Memory work. The more the threads, the more the CPU and Memory pressure.

1 7

Connection Pool + JDBC Pollution

Connections:

Inside MySQL 2 ways of handling threads (thread_handling variable):

• 1 thread per each connection - one-thread-per-connection (thread_cache_size)
• pool of threads - loaded_dinamically (thread_pool_size) - Enterprise feature
• Percona Server has a thread pool library pool-of-threads

Rule of thumb:

• In MySQL most of use cases are handled properly with
• ne_thread_per_connection
• Thread pool fits only in few use cases: normally high number of threads running

very short queries.

1 8

Connection Pool + JDBC Pollution

Connections

Out of MySQL JDBC

• API for connecting to MySQL from Java App
• Several connection pool handlers
• Hikari -> standard nowadays
• c3p0 (still common but no longer recommended)

1 9

Connection Pool + JDBC Pollution

Connection Pool:

Connection pool as external concept VS internal Connection pool In MySQL: group of connections (threads) constantly opened and re used. Out of MySQL: group of connections handled by framework which may or not be kept opened.

JDBC pollution (a.k.a. damn defaults):

Simple connection without optimization

/* mysql-connector-java-8.0.12 (Revision: 24766725dc6e017025532146d94c6e6c488fb8f1) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout;

2

Connection Pool + JDBC Pollution

Noise exists, but can be reduced:

hibernate.hikari.dataSource.cachePrepStmts">true hibernate.hikari.dataSource.defaultFetchSize">100 hibernate.hikari.dataSource.prepStmtCacheSize">250 hibernate.hikari.dataSource.prepStmtCacheSqlLimit">2048 hibernate.hikari.dataSource.useServerPrepStmts">true hibernate.hikari.dataSource.useLocalSessionState">true hibernate.hikari.dataSource.rewriteBatchedStatements">true hibernate.hikari.dataSource.cacheResultSetMetadata">true hibernate.hikari.dataSource.cacheServerConfiguration">true hibernate.hikari.dataSource.elideSetAutoCommits">true hibernate.hikari.dataSource.maintainTimeStats">false

For Hibernate you must add in URL

jdbc:mysql://127.0.0.1:3306/employees?prepStmtCacheSize=250&am p;prepStmtCacheSqlLimit=2048&amp;useServerPrepStmts=YES&amp;us eLocalSessionState=YES&amp;useSSL=false&amp;defaultFetchSize=1 00&amp;rewriteBatchedStatements=YES&amp;cacheResultSetMetadata =YES&amp;cacheServerConfiguration=YES&amp;elideSetAutoCommits= YES&amp;maintainTimeStats=false

Now For Some More Insight

2 2

Developer CRUD Code Hibernate

Insert

Session se = sessionFactoryEmp2.openSession(); se.setJdbcBatchSize(1); se.beginTransaction(); int i=1; while(++i < 500) { Employees employee = new Employees(); employee.setBirthDate(new Date()); employee.setHireDate(new Date()); employee.setGender('M'); employee.setEmpNo(i); employee.setFirstName("Franco" + 1); employee.setLastName("Castagna"+i); se.save(employee); } se.getTransaction().commit(); se.close();

Read

Session se = sessionFactoryEmp2.openSession(); se.setJdbcBatchSize(1); se.beginTransaction(); List<Employees> employees = se.createQuery("from Employees where emp_no <999 " ).list(); se.disconnect(); se.close();

Update

se.beginTransaction(); List<Employees> employees = se.createQuery("from Employees where emp_no <999 " ).list(); int i = 0; Iterator it = employees.iterator(); while(it.hasNext()) { Employees myEmp = (Employees) it.next(); try{myEmp.setHireDate(this.getSimpleDateFormat().parse("2015-"+ getRandomNumberInRange(1,12) +"-10"));}catch(Exception ae ) {ae.printStackTrace();} se.update(myEmp); } se.getTransaction().commit(); se.disconnect(); se.close();

2 3

Developer CRUD Code Simple Code No Batch

Insert

Statement stmt = conn.createStatement(); int i=0; StringBuffer sb =new StringBuffer(); String sqlHead="INSERT INTO employees (emp_no,birth_date,first_name,last_name,gender,hire_date) VALUES ("; stmt.execute("START TRANSACTION"); while(++i < 500) { sb.append(i); sb.append(",’”+ this.getSimpleDateFormat().format(new Date()) +"'"); sb.append(",'Franco"+ i +"'"); sb.append(",'Castagna"+ i +"'"); sb.append(",'M'"); sb.append(",’”+ this.getSimpleDateFormat().format(new Date()) +"'"); sb.append(")"); stmt.execute(sqlHead+sb.toString()); sb.delete(0,sb.length()); } conn.commit();

Read

StringBuffer sb =new StringBuffer(); String sqlHead="SELECT emp_no,birth_date,first_name,last_name,gender,hire_date FROM employees where emp_no="; int rowReturned=0; while(++i < 500) { sb.append(i); ResultSet rs = stmt.executeQuery(sqlHead+sb.toString()); HashSet employees = new HashSet(); while(rs.next()) { Employees employee = new Employees(); employee.setBirthDate(rs.getDate("birth_date")); employee.setHireDate(rs.getDate("hire_date")); employee.setGender( rs.getString("gender").charAt(0)); employee.setEmpNo(rs.getInt("emp_no")); employee.setFirstName(rs.getString("first_name")); employee.setLastName(rs.getString("last_name")); employees.add(employee); } }

Delete

Statement stmt = conn.createStatement(); int i=0; String sqlHead="Delete from employees where emp_no="; stmt.execute("START TRANSACTION"); while(++i < 500) { stmt.execute(sqlHead+i); } conn.commit();

DBA

Where is the overhead in:

• Additional not useful SQL
• too many temporary tables on disk
• causes IO overhead
• Duplicated SQL
• too many show/set commands
• causes CPU/Memory overhead
• Not optimized SQL (like batching and so on)
• Queries coming from framework are generally not properly optimized
• Poorly optimized queries hit everything: CPU/Memory/IO

Evidence

Hibernate

# Time: 2019-05-14T11:27:20.481014Z # User@Host: hibernatee[hibernatee] @ [127.0.0.1] Id: 1919 # Query_time: 0.000008 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 SET timestamp=1557833240; # administrator command: Close stmt; # Time: 2019-05-14T11:27:20.481484Z # User@Host: hibernatee[hibernatee] @ [127.0.0.1] Id: 1919 # Query_time: 0.000059 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 SET timestamp=1557833240; # administrator command: Prepare; # Time: 2019-05-14T11:27:20.482054Z # User@Host: hibernatee[hibernatee] @ [127.0.0.1] Id: 1919 # Query_time: 0.000091 Lock_time: 0.000029 Rows_sent: 0 Rows_examined: 0 SET timestamp=1557833240; insert into employees.employees (birth_date, first_name, last_name, gender, hire_date, emp_no) values ('2019-05-14', 'Franco1', 'Castagna2', 'M', '2019-05-14', 2);

Evidence

Standard call/code

SET timestamp=1557833376; INSERT INTO employees (emp_no,birth_date,first_name,last_name,gender,hire_date) VALUES (1,'2019-05-14','Franco1','Castagna1','M','2019-05-14'); # Time: 2019-05-14T11:29:36.974274Z # User@Host: hibernatee[hibernatee] @ [127.0.0.1] Id: 1934 # Query_time: 0.000082 Lock_time: 0.000033 Rows_sent: 0 Rows_examined: 0 SET timestamp=1557833376; INSERT INTO employees (emp_no,birth_date,first_name,last_name,gender,hire_date) VALUES (2,'2019-05-14','Franco2','Castagna2','M','2019-05-14');

2 7

Developer - CRUD Results No Batch

What Can Be Done? - Batch

Hibernate configuration

<hibernate-configuration> <session-factory > <property name="sessionFactoryName">Hikari</property> <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property> <property name="hibernate.jdbc.batch_size">20</property>

Java code Hibernate related

Session se = sessionFactoryEmp2.openSession(); se.setJdbcBatchSize(20); se.beginTransaction(); while(i < 500) { Employees employee = new Employees(); employee.setBirthDate(new Date()); ... se.save(employee); if ( ++i % 20 == 0 ) { //20, same as the JDBC batch sizeflush a batch of inserts and release memory: se.flush(); se.clear(); } }

What Can Be Done? - Batch

Hibernate

SET timestamp=1557833655; insert into employees.employees (birth_date, first_name, last_name, gender, hire_date, emp_no) values ('2019-05-14', 'Franco1', 'Castagna1', 'M', '2019-05-14', 1),...,('2019-05-14', 'Franco1', 'Castagna19', 'M', '2019-05-14', 19); # Time: 2019-05-14T11:34:15.998326Z # User@Host: hibernatee[hibernatee] @ [127.0.0.1] Id: 1945 # Query_time: 0.000024 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 SET timestamp=1557833655; # administrator command: Close stmt; # Time: 2019-05-14T11:34:15.998609Z # User@Host: hibernatee[hibernatee] @ [127.0.0.1] Id: 1945 # Query_time: 0.000008 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0

3

Developer - CRUD Results With Batch

3 1

CRUD Summary With Optimization

DBA - What is That Traffic?

• A lot of data is read from the DB over and over
• Tons of simple queries
• Queries involves also referenced tables and not only the main one

3 3

Developer - Entity and References

3 4

Developer Entity and References

Let us start with the cool things:

Session se = sessionFactoryEmp2.openSession(); se.beginTransaction(); Employees myEmp = se.find(Employees.class,10001, LockModeType.PESSIMISTIC_WRITE); Set salaries = null; salaries = myEmp.getSalarieses(); Iterator itS = salaries.iterator(); while(itS.hasNext()) { Salaries mySal = (Salaries) itS.next(); if(mySal.getToDate().toString().equals("9999-01-01")){ mySal.setSalary(mySal.getSalary() + 100); } } se.saveOrUpdate(myEmp); se.getTransaction().commit(); se.disconnect(); se.close(); Performance ALERT

3 5

Developer Entity and References

Employees myEmp = se.find(Employees.class,10001, LockModeType.PESSIMISTIC_WRITE);

• Hibernate: select employees0_.emp_no as emp_no1_3_0_... from employees.employees employees0_

where employees0_.emp_no=? for update salaries = myEmp.getSalarieses();

• Hibernate: select salarieses0_.emp_no as emp_no1_4_0_... from employees.salaries salarieses0_ where

salarieses0_.emp_no=? se.saveOrUpdate(myEmp);

• Hibernate: update employees.salaries set salary=?, to_date=? where emp_no=? and from_date=?

3 6

Developer Entity and References

• Entities and sets (References)
• Great to have them (sets) but sometime they stab you in the back!
• What is Lazy and how to control it ... if you can!
• A few tests will follow:

○ Get (select) Employees Lazy/Not Lazy Hibernate versus simple code ○ Update a Salary value for a given employee

3 7

Entity and references the Lazy factor

Remember this?

Entity and References - the Lazy Factor

<set name="salarieses" table="salaries" lazy="true|false" fetch="select" > <key> <column name="emp_no" not-null="true" /> </key> <one-to-many class="net.tc.employees.Salaries"/> </set> LAZY List<Employees> employees = se.createQuery("from EmployeesSummary where emp_no >10000 and emp_no < 20020 " ).list(); Iterator it = employees.iterator(); while(it.hasNext()) { Employees myEmp = (Employees) it.next(); logger.info("EmployeeSummary name = " + myEmp.getFirstName()+" "+ myEmp.getLastName()); Iterator it2 = myEmp.getTitleses().iterator(); ← HERE I retrieve the info Iterator it3 = myEmp.getSalarieses().iterator(); ← HERE I retrieve the info Iterator it4 = myEmp.getDeptEmps().iterator(); ← HERE I retrieve the info } NOT LAZY List<Employees> employees = se.createQuery("from EmployeesSummary where emp_no >10000 and emp_no < 20020 " ).list(); ← HERE Iterator it = employees.iterator(); while(it.hasNext()) { Employees myEmp = (Employees) it.next(); logger.info("EmployeeSummary name = " + myEmp.getFirstName()+" "+ myEmp.getLastName()); Iterator it2 = myEmp.getTitleses().iterator(); Iterator it3 = myEmp.getSalarieses().iterator(); Iterator it4 = myEmp.getDeptEmps().iterator(); }

Entity and References - the Lazy Factor

Lazy SQL

Hibernate: select ... from employees.employees employeess0_ where emp_no>10000 and emp_no<20020 19/05/15 16:34:57 INFO [PL20192]: EmployeeSummary name = Georgi Fucelli Hibernate: select ... from employees.titles titleses0_ where titleses0_.emp_no=? Hibernate: select ... from employees.salaries salarieses0_ where salarieses0_.emp_no=? Hibernate: select ... from employees.dept_emp deptemps0_ where deptemps0_.emp_no=? 19/05/15 16:35:06 INFO [PL20192]: EmployeeSummary name = Bezalel Simmel

Not Lazy SQL

Hibernate: select ... from employees.employees employeess0_ where emp_no>10000 and emp_no<10003 Hibernate: select ... from employees.dept_manager deptmanage0_ where deptmanage0_.emp_no=? Hibernate: select ... from employees.dept_emp deptemps0_ where deptemps0_.emp_no=? Hibernate: select ... from employees.salaries salarieses0_ where salarieses0_.emp_no=? Hibernate: select ... from employees.titles titleses0_ where titleses0_.emp_no=? Hibernate: select ... from employees.dept_manager deptmanage0_ where deptmanage0_.emp_no=? Hibernate: select ... from employees.dept_emp deptemps0_ where deptemps0_.emp_no=? Hibernate: select ... from employees.salaries salarieses0_ where salarieses0_.emp_no=? Hibernate: select ... from employees.titles titleses0_ where titleses0_.emp_no=? 19/05/15 16:40:54 INFO [PL20192]: EmployeeSummary name = Georgi Fucelli 19/05/15 16:41:01 INFO [PL20192]: EmployeeSummary name = Bezalel Simmel

4

Entity and References - the Lazy Factor

4 1

Entity and References - the Lazy Factor

Best way to handle it … and be safe.

4 2

Entity and References - the Lazy Factor

Best way to handle it … and be safe.

WAIT WAIT WAIT WHY FKs???

DBA HR Complains

Your application has overwritten HR data

• Data out of order
• Missing consistency
• Unexpected results

4 4

Lock Me Please

TRX 1

logger.info("I am the 1st TRX ");

EmployeesSummary myEmp = se.find(EmployeesSummary.class,10001, LockModeType.PESSIMISTIC_WRITE); Set salaries = null; salaries = myEmp.getSalarieses(); Iterator itS = salaries.iterator(); while(itS.hasNext()) { Salaries mySal = (Salaries) itS.next(); if(mySal.getToDate().toString().equals("9999-01-01")){ logger.info("1TRX Employee name Before = " + myEmp.getFirstName()+" " + myEmp.getLastName() +" " + mySal.getSalary()); mySal.setSalary(mySal.getSalary() + 1000); logger.info("1TRX Employee name After = " + myEmp.getFirstName()+" " + myEmp.getLastName() +" " + mySal.getSalary()); } } logger.info("Another Transaction is modifying the same value "); se.saveOrUpdate(myEmp); se.getTransaction().commit(); se.disconnect(); se.close(); logger.info("1TRX COmplete");

TRX 2

logger.info("I am the 2nd TRX "); EmployeesSummary myEmp = se.find(EmployeesSummary.class,10001); Set salaries = null; salaries = myEmp.getSalarieses(); Iterator itS = salaries.iterator(); while(itS.hasNext()) { Salaries mySal = (Salaries) itS.next(); if(mySal.getToDate().toString().equals("9999-01-01")){ logger.info("2TRX Employee name Before = " + myEmp.getFirstName()+" " + myEmp.getLastName() +" " + mySal.getSalary()); mySal.setSalary(mySal.getSalary() - 400); logger.info("2TRX Employee name After = " + myEmp.getFirstName()+" " + myEmp.getLastName() +" " + mySal.getSalary()); } } se.saveOrUpdate(myEmp); se.getTransaction().commit(); se.disconnect(); se.close(); logger.info("2TRX COmplete");

4 5

Lock Me Please

4 6

Lock Me Please

I am the 1st TRX Hibernate: select ... from employees.employees employees0_ where employees0_.emp_no=? for update Hibernate: select ... from employees.salaries salarieses0_ where salarieses0_.emp_no=? Hibernate: update employees.salaries set salary=?, to_date=? where emp_no=? and from_date=? I am the 2nd TRX Hibernate: select ... from employees.employees employees0_ where employees0_.emp_no=? Hibernate: select ... from employees.salaries salarieses0_ where salarieses0_.emp_no=? Hibernate: update employees.salaries set salary=?, to_date=? where emp_no=? and from_date=?

4 7

Lock Me Please - WHY?

Because the select for update is on the main object -> Employees, but I am actually updating a Salaries record so ... no lock Hibernate: select employees0_.emp_no as emp_no1_3_0_... from employees.employees employees0_ where employees0_.emp_no=? for update If using lock also on the second TRX 19/05/14 12:16:57 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper2]: Lock wait timeout exceeded; try restarting transaction Exception in thread "main" javax.persistence.PessimisticLockException: could not extract ResultSet

The right thing to do?

• If you want to operate by parent object be sure you lock them also in the other TRX
• DO NOT operate by parent and instead operate by sub (Salaries) and be sure to lock it

4 8

A Can of Worms

This is actually even more complicated, and a real...

5

What Can We Do Better?

A few thing to keep in mind:

• Select by pager (yes you can do it)
• Use reduced object when you don't need all columns (use different classes)
• Use Lazy
• Use LockModeType.PESSIMISTIC_WRITE only when needed, but use it
• Use high JDBC fetch size in Hibernate conf
• Leave sequence to DB
• Do not detach / attach entities lightly
• Release connection after transaction
• Use connection Pool with Hibernate
• Do not use Hibernate unless you really need to
• Apply JDBC optimizations

Thank You to Our Sponsors

5 5

Rate My Session