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

Who Are We? Francisco Marco 2

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

Introduction / Disclaimer What is this fish? the important … is to have clear ideas and coordinated direction I didn’t ask for sushi Java? Why is he talking about coffee, I ordered sushi! 4

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

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

DBA 7

DBA EASY RIGHT? 8

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

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

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

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

What is an Entity? <hibernate-mapping package="net.tc.employees"> Simple example with One table employees ● Mapping definition <class name="Employees" ● Code table="employees" catalog="employees" optimistic-lock="version"> public class Employees implements java.io.Serializable { 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" length="10" not-null="true" /> public Employees() { } <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; 1 this .gender = gender; 3 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)

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

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

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

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

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

Now For Some More Insight

Developer CRUD Code Hibernate Read Insert 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(); se.disconnect(); Employees employee = new Employees(); 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(); 2 se.disconnect(); 2 se.close();