Using advanced options in MariaDB Connector/J

MariaDB java Connector Diego DUPIN MariaDB Connector Engineer MariaDB Corporation

PROGRAM - Getting started - Best practice - Advanced topic - ORM

MARIADB JAVA CONNECTOR MariaDB is 100% java driver, initially fork from drizzle JDBC 4.2 compliant First release 2012 400K DL per month (maven stats)

JAVA CONNECTOR Why not using MySQL connector ? ● MariaDB specific implementation ○ Bulk batch ○ Authentication (GSSAPI, ed25519 …) ● Performance : exemple on query “SELECT * FROM mysql.user”

QUICK START - INSTALLATION ● 1.x java 7 ● 2.x java 8+ maven <dependency> <groupId>org.mariadb.jdbc</groupId> <artifactId>mariadb-java-client</artifactId> <version>2.4.0</version> </dependency>

QUICK START - CREATE CONNECTION auto-registration for “jdbc:mariadb” try (Connection conn = DriverManager.getConnection("jdbc:mariadb://localhost/db?user=root")) { System.out.println("Connection succeed"); } catch (SQLException sqle) { System.out.println("Connection failed with err: " + sqle.getMessage()); } DataSource datasource = new MariaDbDataSource("jdbc:mariadb://localhost/db?user=root"); try (Connection conn = datasource.getConnection()) { System.out.println("Connection succeed"); } catch (SQLException sqle) { System.out.println("Connection failed with err: " + sqle.getMessage()); }

QUICK START - CONNECTION STRING jdbc:mariadb:[replication:|failover:|sequential:|aurora:]//<hostDescription>[,<hostDescription>...]/[database][ ?<key1>=<value1>[&<key2>=<value2>]] "jdbc:mariadb://localhost:3306/myDb?user=myUser&password=MyPwd&useSSL=true” Essential options ● User ● Password ● connectTimeout ● localSocketAddress … 87 options in total

QUICK START - INSERT try (PreparedStatement prep = conn.prepareStatement("INSERT INTO myTable(firstName, lastName) VALUES (?, ?)")) { prep.setString(1, "john"); prep.setString(2, "Hancock"); prep.execute(); } catch (SQLException sqle) { //handle error }

QUICK START - SELECT try (PreparedStatement prep = conn.prepareStatement("SELECT firstName, lastName FROM myTable WHERE id = ?")) { prep.setInt(1, 1000); ResultSet rs = prep.executeQuery(); rs.next(); System.out.println(rs.getString(1) + " " + rs.getString(2)); } catch (SQLException sqle) { //handle error }

QUICK START Statement stmt = conn.createStatement(); stmt.execute("INSERT INTO myTable(firstName, lastName) VALUES ('" + firstName + "', '" + lastName + "')"); Not using ? parameters : SQL INJECTION lastname = “‘) , (‘other name’, ‘other last name’); CREATE USER hack@’%’”

SSL - one way authentication Server must be configured for SSL (yassl/openssl) One-Way SSL Authentication . Not reusing context try (Connection con = DriverManager.getConnection("jdbc:mariadb://localhost/myDb?user=myUser&password=MyPwd&useSSL=true”)) { try (Statement stmt = con.createStatement()) { stmt.execute("select 1"); } } CREATE USER 'myUser'@'%' IDENTIFIED BY 'MyPwd'; GRANT ALL ON db_name.* TO 'myUser'@'%' REQUIRE SSL;

SSL - mutual authentication Server must be configured for SSL (yassl/openssl) mutual SSL Authentication . try (Connection con = DriverManager.getConnection("jdbc:mariadb://localhost/myDb?user=myUser&password=MyPwd&useSSL=true&clientCertificateKe yStoreUrl=/tmp/…” )) { try (Statement stmt = con.createStatement()) { stmt.execute("select 1"); } } CREATE USER 'myUser'@'%' IDENTIFIED BY 'MyPwd'; GRANT ALL ON db_name.* TO 'myUser'@'%' REQUIRE X509;

SSL - some options useSSL Force SSL/TLS on connection. trustServerCertificate When using SSL/TLS, do not check server's certificate. serverSslCert Permits providing server's certificate in DER form, or server's CA certificate. keyStore File path of the keyStore file that contain client private key store and associate certificates keyStorePassword Password for the client certificate keyStore trustStore File path of the trustStore file (similar to java System property "javax.net.ssl.trustStore"). trustStorePassword Password for the trusted root certificate file (similar to java System property "javax.net.ssl.trustStorePassword"). enabledSslProtocolSuites Force TLS/SSL protocol to a specific set of TLS versions (comma separated list). enabledSslCipherSuites Indicate permited ciphers disableSslHostnameVerification checks the hostname against the server's identity as presented in the server's certificate keyStoreType Indicate key store type (JKS/PKCS12). default is null, then using java default type. trustStoreType Indicate trust store type (JKS/PKCS12). default is null, then using java default type.

POOLING Basically a database connection cache implementation Connections are expensive. On local DB: ● 2.4ms for a basic connection ● 0.05ms for a simple query Problem : correctness and reliability

POOLING Lots of pools : apache dbcp2, HikariCP, c3p0 Best is HikariCP : ● Good default ● Widely used ● Very fast ● Show connection leaks ● Doesn’t always validate connection (500ms) ● Reliable : ○ Readonly, autocommit, isolation, schema, network timeout reset ○ rollback ● Pool handle connection creation in dedicated thread

POOLING - Implementation to handle query pikes Example with a pool that is configured to have a maximum of 50 connections. actual connection number is 5. With a basis of a connection creation taking 2.4ms, and query taking 0.05ms (example on a local server). Everything is quiet, and then ... Boom! ... 100 queries on the pool at once, wanting a connection.

POOLING ● Good for failover recovery Just ● configure “maxLifetime” option according to Server @@wait_timeout ● Don’t set “connectionTestQuery” option ● Avoid setting “minimumIdle” option ● If always disabling autocommit (hibernate): ○ autoCommit option to false ○ "jdbc:mariadb://localhost/db?user=user&sessionVariables=autocommit=0";

POOLING Connection pools SIZING error Example 10 000 user simultaneously, 20 000 transaction per second. What value to connectionLimit (max connection number in pool) ? 100 ? 500 ? 1000 ?

Streaming Goal : Avoid loading all in memory ● Streaming resultset -> avoiding loading large resultset totally into memory ● Streaming sent -> sending buffer by chunk

Streaming resultset Limitations : ● Server net_write_timeout. ○ For a command: SET STATEMENT net_write_timeout=10000 FOR XXX ○ For a connection: ”&sessionVariables=net_write_timeout=31536000” stmt.setFetchSize(100); ResultSet rs = stmt.executeQuery("SELECT charValue FROM testBatchString"); int counter = 0; while (rs.next()) { System.out.println(rs.getString(1)); }

Streaming - sending try (FileInputStream fis = new FileInputStream(tmpFile)) { try (PreparedStatement ps = sharedConnection.prepareStatement("insert into bigblob values(?)")) { ps.setCharacterStream(1, new InputStreamReader(fis, StandardCharsets.UTF_8)); ps.executeUpdate(); } } Limitations : ● Server net_read_timeout ● max_allowed_packet

BULK ● One after One ● Pipelining (default) ● Rewrite (rewriteBatchedStatements) ● Bulk insert (useBulkStmts) int[] executeBatch() throws SQLException

MULTI-HOST load balancing and high availability jdbc:mariadb:[replication:|failover:|sequential:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=< value1>[&<key2>=<value2>]] Sequential multi-master with failover support, always using in sequence until fails Failover multi-master with failover support Replication multi-master + multi-slave with failover support

MULTI-HOST client Master Slave B Slave A "jdbc:mariadb:replication://master,slaveA,slaveB/db" Pools with 4 connections

MULTI-HOST Using standard JDBC try {Connection conn = DriverManager.getConnection("jdbc:mysql:replication://master1,slave1/db?user=root")) { Statement stmt = conn.createStatement(); stmt.execute("SELECT 1"); // will execute query on the underlying master1 connection conn.setReadOnly(true); stmt.execute("SELECT 1"); // will execute query on the underlying slave1 connection }

MULTI-HOST Using Hibernate Using Spring data jpa : automatic findxxx() automatically setReadOnly(true) @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public void createUser(EntityManager em) { User user = new User("Jean claude", "Van Damme"); em.persist(user); } @Transactional(readOnly = true, propagation = Propagation.REQUIRED) public User findUser(EntityManager em, Integer id) { return em.find(User.class, id); }

SPRING DATA JPA Spring data JPA is the actual reference in java. Need configuration for optimisation. Example with : repository.save(new User("Jack", "Bauer")); repository.save(new User("Chloe", "O'Brian")); List<User> users = new ArrayList(); users.add(new User("Kim", "Bauer")); users.add(new User("David", "Palmer")); users.add(new User("Michelle", "Dessler")); Iterable<User> savedUsers = repository.saveAll(users); savedUsers.forEach(user -> { System.out.println(user); }); public class User { public User(String lastName, String firstName) { this.lastName = lastName; this.firstName = firstName; } @Id @GeneratedValue(strategy=GenerationType.AUTO) private Integer id; private String lastName; private String firstName; }

SPRING DATA JPA set autocommit=0 set autocommit=0 select next_val as id_val from hibernate_sequence for update update hibernate_sequence set next_val= ? where next_val=?, parameters [2,1] COMMIT set autocommit=1 insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Bauer','Jack',1] COMMIT set autocommit=1 set autocommit=0 set autocommit=0 select next_val as id_val from hibernate_sequence for update update hibernate_sequence set next_val= ? where next_val=?, parameters [3,2] COMMIT set autocommit=1 insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['O'Brian','Chloe',2] COMMIT set autocommit=1 set autocommit=0 set autocommit=0 select next_val as id_val from hibernate_sequence for update update hibernate_sequence set next_val= ? where next_val=?, parameters [4,3] COMMIT set autocommit=1 set autocommit=0 select next_val as id_val from hibernate_sequence for update

SPRING DATA JPA With HikariCP configured with autocommit : false and adding “autocommit=0” to connection string (dialect MySQL5Dialect) select next_val as id_val from hibernate_sequence for update update hibernate_sequence set next_val= ? where next_val=?, parameters [2,1] COMMIT insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Bauer','Jack',1] COMMIT select next_val as id_val from hibernate_sequence for update update hibernate_sequence set next_val= ? where next_val=?, parameters [3,2] COMMIT insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['O'Brian','Chloe',2] COMMIT select next_val as id_val from hibernate_sequence for update update hibernate_sequence set next_val= ? where next_val=?, parameters [4,3] COMMIT select next_val as id_val from hibernate_sequence for update update hibernate_sequence set next_val= ? where next_val=?, parameters [5,4] COMMIT select next_val as id_val from hibernate_sequence for update update hibernate_sequence set next_val= ? where next_val=?, parameters [6,5] COMMIT insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Bauer','Kim',3] insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Palmer','David',4] insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Dessler','Michelle',5] COMMIT

SPRING DATA JPA With HikariCP configured with autocommit : false and adding “autocommit=0” to connection string (dialect MariaDB103Dialect) select nextval(hibernate_sequence) insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Bauer','Jack',1] COMMIT select nextval(hibernate_sequence) insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['O'Brian','Chloe',2] COMMIT select nextval(hibernate_sequence) select nextval(hibernate_sequence) select nextval(hibernate_sequence) insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Bauer','Kim',3] insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Palmer','David',4] insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Dessler','Michelle',5] COMMIT

SPRING DATA JPA Changing default strategy auto to native : public class User { public User(String lastName, String firstName) { this.lastName = lastName; this.firstName = firstName; } @Id @GeneratedValue(strategy = GenerationType.AUTO, generator = "native") @GenericGenerator(name = "native", strategy = "native") private Integer id; private String lastName; private String firstName; }

SPRING DATA JPA With HikariCP configured with autocommit : false and adding “autocommit=0” to connection string (dialect MariaDB103Dialect + strategy native) insert into User (firstName, lastName) values (?, ?), parameters ['Bauer','Jack'] COMMIT insert into User (firstName, lastName) values (?, ?), parameters ['O'Brian','Chloe'] COMMIT insert into User (firstName, lastName) values (?, ?), parameters ['Bauer','Kim'] insert into User (firstName, lastName) values (?, ?), parameters ['Palmer','David'] insert into User (firstName, lastName) values (?, ?), parameters ['Dessler','Michelle'] COMMIT

SPRING DATA JPA With HikariCP configured with autocommit : false and adding “&sessionVariables=autocommit=0&useBulkStmts&rewriteBatchedStatements” to connection string (dialect MariaDB103Dialect) select nextval(hibernate_sequence) insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Bauer','Jack',1] COMMIT select nextval(hibernate_sequence) insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['O'Brian','Chloe',2] COMMIT select nextval(hibernate_sequence) select nextval(hibernate_sequence) select nextval(hibernate_sequence) insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Bauer','Kim',3] insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Palmer','David',4] insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Dessler','Michelle',5] COMMIT

SPRING DATA JPA using sequence in pool: @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator") @GenericGenerator( name = "sequenceGenerator", strategy = "enhanced-sequence", parameters = { @org.hibernate.annotations.Parameter( name = "optimizer", value = "pooled" ), @org.hibernate.annotations.Parameter( name = "initial_value", value = "1" ), @org.hibernate.annotations.Parameter( name = "increment_size", value = "5" ) } ) private Integer id;

SPRING DATA JPA With HikariCP configured with autocommit : false and adding “&sessionVariables=autocommit=0&useBulkStmts&rewriteBatchedStatements” to connection string (dialect MariaDB103Dialect) select nextval(hibernate_sequence) select nextval(hibernate_sequence) insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Bauer','Jack',1] COMMIT insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['O'Brian','Chloe',2] COMMIT insert into User (firstName, lastName, id) values (?, ?, ?), parameters ['Bauer','Kim',3],['Palmer','David',4],['Dessler','Michelle',5] COMMIT

AVOID TCP-IP layer for local connection Connection options for local server - UNIX domain (option “localSocket”) - Windows named pipe (option “pipe”) const mariadb = require('mariadb'); mariadb.createConnection({ socketPath: '/tmp/mysql.sock', user: 'root' }) .then(conn => { ... }) .catch(err => { ... });

METADATA ● Select query = row datas + metadata Metadata = datatype, format, and lots of additional infos: schema, table name, table alias, column name, column alias, … Not always needed ResultSet rs = state.statement.executeQuery("select * from mysql.user LIMIT 1"); rs.next(); rs.getString(1); rs.getString(2); … vs s.getString("Host"); rs.getString("User"); ...

What next ? Performance improvement Pool + Failover improvement ADBA / R2DBC

Using advanced options in MariaDB Connector/J

More Related Content

What's hot

Similar to Using advanced options in MariaDB Connector/J

More from MariaDB plc

Recently uploaded

Using advanced options in MariaDB Connector/J