Accessing Data Through Hibernate

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 

8 . DBA EASY RIGHT? 8 

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="net.tc.employees"> Simple example with One table employees <class ● Mapping definition name="Employees" ● Code table="employees" catalog="employees" public class Employees implements java.io.Serializable { 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://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 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(); se.save(employee); int i = 0; Iterator it = employees.iterator(); } while(it.hasNext()) { se.getTransaction().commit(); Employees myEmp = (Employees) it.next(); 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(rs.next()) { 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] @ [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); 

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

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()); ... 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(); } } 

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] @ [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