Accessing Data Through Hibernate


Marco Tusa
Marco has his own international practice for the past twenty seven years. His experience and expertise are in a wide variety of information technology and information management fields of application and cover research, development, analysis, quality control, project management and team management. Marco has move to Percona as manager of the Consulting Rapid Response Team on October 2013. Marco has moved from Italy to Ottawa, Canada in 2011 where he actually lives with his family. He has being working as employee for the SUN Microsystems as MySQL Professional Service manager for South Europe., and previously in MySQL AB. He has worked with the Food and Agriculture Organization of the United Nation since 1994, leading the development of the Organization’s hyper textual environment.Team leader for the FAO corporate database support. For several years he has led the development group in the WAICENT/Faoinfo team. He has assisted in defining the Organization’s guidelines for the dissemination of information from the technology and the management point of view. He has acted as coordinator between WAICENT and other departments; he has been a member of the FAO Information Management and Technology Committee since 1999. He designed and lead the development of some of the main web related FAO projects, and web publish architecture. He has participated in field missions in order to perform analysis, reviews and evaluation of the status of local projects, providing local support and advice.
Francisco Bordenave
Francisco has been working in MySQL since 2006, he has worked for several companies which includes Health Care industry to Gaming. Over the last 3 years he has been working as a Remote DBA consultant which help him to acquire a lot of technical and multi-cultural skills. He lives in Buenos Aires, Argentina and during his free time he likes to play football, spent time with family and friends and cook.


1. Accessing Data Through Hibernate; What DBAs Should Tell Developers and Vice Versa Marco Tusa and Francisco Bordenave Percona

2.Who Are We? Francisco Marco 2

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? 3

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 ordered sushi! 4

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 5

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

7.DBA 7


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 9

10.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 0

11.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 1

12.What is an Entity? And States? I need to know that I have this: 1 2

13.What is an Entity? <hibernate-mapping package=""> Simple example with One table employees <class ● Mapping definition name="Employees" ● Code table="employees" catalog="employees" public class Employees implements { optimistic-lock="version"> private int empNo; <id private Date birthDate; name="empNo" private String firstName; type="int"> private String lastName; <column name="emp_no" /> private char gender; <generator class="assigned" /> private Date hireDate; </id> private Set titleses = new HashSet(0); <property private Set salarieses = new HashSet(0); name="birthDate" private Set deptEmps = new HashSet(0); type="date" private Set deptManagers = new HashSet(0); > <column name="birth_date" public Employees() { length="10" not-null="true" /> } <property public Employees(int empNo, Date birthDate, String name="firstName" firstName, String lastName, char gender, Date hireDate) { type="string" this.empNo = empNo; > this.birthDate = birthDate; <column name="first_name" this.firstName = firstName; length="14" not-null="true" /> this.lastName = lastName; this.gender = gender; 1 3 this.hireDate = hireDate; }

14.DBA So what’s an entity in relational database?

15.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)

16.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 6

17.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 one_thread_per_connection - Thread pool fits only in few use cases: normally high number of threads running very short queries. 1 7

18.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 8

19.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 1 AS transaction_isolation, @@wait_timeout AS wait_timeout; 9

20. 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:// 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 2 0

21.Now For Some More Insight

22. Developer CRUD Code Hibernate Insert Read Session se = sessionFactoryEmp2.openSession(); Session se = sessionFactoryEmp2.openSession(); se.setJdbcBatchSize(1); se.setJdbcBatchSize(1); se.beginTransaction(); se.beginTransaction(); int i=1; List<Employees> employees = se.createQuery("from Employees where emp_no <999 " while(++i < 500) { ).list(); Employees employee = new Employees(); se.disconnect(); employee.setBirthDate(new Date()); se.close(); employee.setHireDate(new Date()); employee.setGender('M'); Update employee.setEmpNo(i); se.beginTransaction(); employee.setFirstName("Franco" + 1); List<Employees> employees = se.createQuery("from Employees where emp_no <999 " employee.setLastName("Castagna"+i); ).list();; int i = 0; Iterator it = employees.iterator(); } while(it.hasNext()) { se.getTransaction().commit(); Employees myEmp = (Employees); se.close(); try{myEmp.setHireDate(this.getSimpleDateFormat().parse("2015-"+ getRandomNumberInRange(1,12) +"-10"));}catch(Exception ae ) {ae.printStackTrace();} se.update(myEmp); } se.getTransaction().commit(); se.disconnect(); 2 2 se.close();

23. Developer CRUD Code Simple Code No Batch Read Insert StringBuffer sb =new StringBuffer(); String sqlHead="SELECT emp_no,birth_date,first_name,last_name,gender,hire_date Statement stmt = conn.createStatement(); FROM employees where emp_no="; int i=0; int rowReturned=0; StringBuffer sb =new StringBuffer(); while(++i < 500) { String sqlHead="INSERT INTO employees sb.append(i); (emp_no,birth_date,first_name,last_name,gender,hire_date) ResultSet rs = stmt.executeQuery(sqlHead+sb.toString()); VALUES ("; HashSet employees = new HashSet(); while( { stmt.execute("START TRANSACTION"); Employees employee = new Employees(); while(++i < 500) { employee.setBirthDate(rs.getDate("birth_date")); sb.append(i); employee.setHireDate(rs.getDate("hire_date")); sb.append(",’”+ employee.setGender( rs.getString("gender").charAt(0)); this.getSimpleDateFormat().format(new Date()) employee.setEmpNo(rs.getInt("emp_no")); +"'"); employee.setFirstName(rs.getString("first_name")); employee.setLastName(rs.getString("last_name")); sb.append(",'Franco"+ i +"'"); employees.add(employee); sb.append(",'Castagna"+ i +"'"); } sb.append(",'M'"); } sb.append(",’”+ Delete this.getSimpleDateFormat().format(new Date()) Statement stmt = conn.createStatement(); +"'"); int i=0; sb.append(")"); String sqlHead="Delete from employees where emp_no="; stmt.execute("START TRANSACTION"); stmt.execute(sqlHead+sb.toString()); while(++i < 500) { sb.delete(0,sb.length()); stmt.execute(sqlHead+i); } } 2 conn.commit(); conn.commit(); 3

24.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

25.Evidence Hibernate # Time: 2019-05-14T11:27:20.481014Z # User@Host: hibernatee[hibernatee] @ [] 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] @ [] 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] @ [] 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);

26.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] @ [] 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');

27.Developer - CRUD Results No Batch 2 7

28.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()); ...; if ( ++i % 20 == 0 ) { //20, same as the JDBC batch sizeflush a batch of inserts and release memory: se.flush(); se.clear(); } }

29.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] @ [] 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] @ [] Id: 1945 # Query_time: 0.000008 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0