Java MySQL Connector & Connection Pool

(1)

Java MySQL Connector &

Connection Pool

Features & Optimization

Kenny Gryp <[email protected]> November 4, 2014

(2)

DISCLAIMER

Please excuse me for not being a Java developer

(3)

What I Don’t Like • _{Brussels Sprouts} • _Taxes • _Calories • _Java(’s chattiness) 3

(4)

MYSQL CONNECTORS

CONNECTION POOLS

(5)

MYSQL CONNECTORS

CONNECTION POOLS

Connectors

Configuring Connector Creating A Database

Connection

Prepared Statements Example Transaction

(6)

MySQL Connector/J & MariaDB Java Client • _{MySQL Connector/J} – Oracle – _{5.1.33 Latest} – Compatible with • _MySQL • Percona Server • _MariaDB 6

(7)

MySQL Connector/J & MariaDB Java Client

• _{MariaDB Java Client} • MariaDB

• _{Fork from Drizzle Connector} • Latest 1.1.7 • _{Compatible with} – MySQL – _{Percona Server} – MariaDB 7

(8)

MySQL Connector/J Features

• _{Enterprise Plugin: Query Analyzer} • MySQL Fabric Integration

• _{Load Balancing} • Failover

• _Replication

(9)

MYSQL CONNECTORS

CONNECTION POOLS

Connectors

Configuring Connector

Creating A Database Connection

(10)

Creating Connection Connection con = ! ! DriverManager.getConnection! ! (“jdbc:mysql://node2/employees?! ! ! user=connj&password=test");! Statement stmt = con.createStatement();! String query = !

! "select * from employees !

! ! where emp_no = 20000;";! ResultSet rs = stmt.executeQuery(query);! ...! ! MariaDB: jdbc:mariadb://node2/employees! ! ?user=connj&password=test" 10

(11)

Creating Connection -‐ Tomcat w. JDBC-‐Pool context.xml (local):! <Resource name="jdbc/test" ! auth="Container" ! type="javax.sql.DataSource"! username=“jdbc-pool" password="test" ! driverClassName="com.mysql.jdbc.Driver"! url=“jdbc:mysql://node2:3306/employees”! />! ! MariaDB: driverClassName="org.mariadb.jdbc.Driver"! 11

(12)

Creating Connection -‐ JDBC URL ! jdbc:mysql://node2:3306/employees? useServerPrepStmts=true&...! ! 12

(13)

MYSQL CONNECTORS

CONNECTION POOLS

Connectors

Configuring Connector

Creating A Database Connection

(14)

Connector/J -‐ Creating Connection

SHOW VARIABLES WHERE !

! Variable_name ='language' OR…!

SELECT !

! @@session.auto_increment_increment;!

SET NAMES latin1;!

SET character_set_results = NULL;!

SET autocommit=1;!

SET sql_mode=!

! 'NO_ENGINE_SUBSTITUTION,STRICT_TRAN

S_TABLES';!

(15)

MariaDB -‐ Creating Connection

set autocommit=1;!

USE employees;!

show variables like 'sql_mode';!

(16)

Creating Connection -‐ Defaults • _Connector/J: ! ! ! ! • MariaDB Java  Client: 16

(17)

Connector/J & MariaDB Java Client -‐ Verbosity

• _{Connector/J is more verbose when starting a}

connection

• Usually not a problem:

– _{connection pools are commonly used} 

(more coming soon…)

– connections are reused

– _{Actually I like but not too much.}

(18)

Optimization

• _{MariaDB Java Client vs MySQL Connector/J} • Prepared Statements

(19)

Connector Performance -‐ SELECT 1 localhost 19 QPS 0 4000 8000 12000 16000 localhost ConnectorJ MariaDB 15.213 13.477

(20)

Connector Performance -‐ MariaDB %faster 20 Fas te r % 5% 10% 15% 20%

MariaDB Connector +Speed% ConnectorJ SELECT1 LO

(21)

Connector Performance -‐ MariaDB %faster 21 Fas te r % 5% 10% 15% 20%

MariaDB Connector +Speed% ConnectorJ

SELECT1 LO SELECT1 net pk range 0% 4%

4% 13%

(22)

MYSQL CONNECTORS

CONNECTION POOLS

Connectors

Connection

Prepared Statements

Example Transaction

(23)

Client or Server Side Prepared Statements

• _{Server side Prepared statements:} – reduce network traffic

– _{query is already optimized on server.} • Support:

– _{MariaDB Java client only supports client side} – Connector/J default in client side

(24)

Server Side Prepared Statements

PREPARE stmt1 FROM!

! select * from employees !

! ! where emp_no = ?;!

EXECUTE # API CALL!

! select * from employees !

! ! where emp_no = 20000;!

DEALLOCATE PREPARE stmt1;

(25)

Connector/J: Server Side Prepared Statements

• _{useServerPrepStmts = false}_! – _{disabled by default}

• Mind looking at:

– _{cachePrepStmts = false}_!

• _do_{PREPARE, EXECUTE, DEALLOCATE}

every time…, 3 round trips?

– _{prepStmtCacheSize = 25}_!

– _{prepStmtCacheSqlLimit = 256}_! • low defaults

(26)

Benchmark: Prepared Statements 26 QPS 900 1800 2700 3600

MariaDB CONN/J CONN/J SRV CONN/J SRV+Cache 3.506QPS

2.047QPS 3.342QPS

3.400QPS

select * from employees_alotofindexes

where first_name='moss' and birth_date > "1954-‐06-‐14" and gender="M" and hire_date > "1998-‐01-‐01"\G

(27)

Cracked!!

(28)

MYSQL CONNECTORS

CONNECTION POOLS

Connectors

Connection

Prepared Statements

Example Transaction

(29)

Connector/J Optimization + Default JDBC-‐Pool

Connection con = ds.getConnection();!

con.setTransactionIsolation! ! !

(Connection.TRANSACTION_READ_COMMITTED);!

con.setAutoCommit(false);!

PreparedStatement stmt =

con.prepareStatement("select * from

employees where emp_no = ?");!

stmt.setInt(1, 20000);! ResultSet rs = stmt.executeQuery();! stmt.close();! rs.close();! con.commit();! con.close(); 29

(30)

Connector/J Optimization + Default JDBC-‐Pool

SET SESSION TRANSACTION !

! ! ISOLATION LEVEL READ COMMITTED;!

!

SET autocommit=0;!

!

# administrator command: Prepare;!

!

select * from employees !

! ! ! where emp_no = 20000;!

!

# administrator command: Close stmt;!

!

commit;

30

(31)

Connector/J Optimization • _{useConfigs=maxPerformance}_! – _{cachePrepStmts=true}_! – _{cacheCallableStmts=true}_! – _{cacheServerConfiguration=true}_! – _{useLocalSessionState=true}_! – _{elideSetAutoCommits=true}_! – _{alwaysSendSetIsolation=false}_! – _{enableQueryTimeouts=false} 31

(32)

Connector/J Optimization

• _{useLocalTransactionState=true}

commit() / rollback()

(33)

Connector/J Optimization -‐ Tuned

JDBC URL: useConfigs=maxPerformance&!

useServerPrepStmts=true:!

!

! where emp_no = 20000;!

commit;

(34)

MariaDB Java Client Optimization

! ! ISOLATION LEVEL READ COMMITTED;!

!

! ! ! where emp_no = 20000;!

!

COMMIT;!

(35)

MariaDB Java Client Optimization -‐ Code if ! ( ! con.getTransactionIsolation() != !! ! ! Connection.TRANSACTION_READ_COMMITTED! )! {! con.setTransactionIsolation! ! ! (Connection.TRANSACTION_READ_COMMITTED);! } 35

(36)

MariaDB Java Client Optimization -‐ Interceptors

SELECT @@tx_isolation;!

select * from employees ! ! where emp_no = 20000;!

COMMIT;!

!

Still @@tx_isolation. Now add JDBC Interceptor:

<Resource name="jdbc/test"! auth="Container"! factory=! "org.apache.tomcat.jdbc.pool.DataSourceFactory"! jdbcInterceptors="ConnectionState"! driverClassName="org.mariadb.jdbc.Driver"! url="jdbc:mysql://node2:3306/employees"/>! 36

(37)

MariaDB Java Client Optimization -‐ Optimized!

! where emp_no = 20000;!

COMMIT;

(38)

Benchmark -‐ Connector Concurrency -‐ SELECT 1

38

HikariCP-‐bench with JDBC Pool, 4 Threads, SELECT 1 (4,8,16,32 Pool Size)

QPS 17.500 35.000 52.500 70.000 MariaDB CONN/J

(39)

Benchmark -‐ Connector Concurrency -‐ TRX

39

HikariCP-‐bench with JDBC Pool, 4 Threads, TRX (4,8,16,32 Pool Size)

QPS

4.750 9.500 14.250 19.000

(40)

MYSQL CONNECTORS

CONNECTION POOLS

(41)

MYSQL CONNECTORS

CONNECTION POOLS

Connection Pools Issues Resetting Environment Testing Connectivity Pool Sizing Lingering Transactions Analysis Examples Graceful Failover

Conn/J Extra Features

(42)

Java Connection Pools

– _{The Usual:} – C3P0

– _{Commons-‐DBCP (v1&v2)}

– JDBC Pool (fork commons-‐DBCP) – _{Out In The Wild:}

– Vibur-‐DBCP – _HikariCP

– BoneCP

(43)

Java Connection Pools

– _{The Usual:}

– C3P0

– _{Commons-‐DBCP}_(v1&_v2₎

– JDBC Pool (fork commons-‐DBCP) – _{Out In The Wild:}

– Vibur-‐DBCP – _HikariCP

– BoneCP

(44)

Connection Pool Key Points

• _{Connection Management} • Pool Sizing

• _{Connection Testing}

• Avoid Lingering Transactions

(45)

MYSQL CONNECTORS

CONNECTION POOLS

Connection Pools Issues Resetting Environment Testing Connectivity Pool Sizing Lingering Transactions Analysis Examples Graceful Failover

(46)

Connection Pool Issues

• _{Coming from DBA side, I do not like ‘chattiness’}

(47)

(48)

(49)

(50)

WHY DOES IT HAVE TO DO THAT?

Because of ‘application bugs’

(51)

Connection Pools -‐ Why Chattiness Examples

• _{*maybe* forgot to}_{COMMIT / ROLLBACK}_! • wanting AUTOCOMMIT=1 

but a previous TRX set it to 0

• _{Changing TRX Isolation Level} • Is connection still working?

(52)

MYSQL CONNECTORS

CONNECTION POOLS

Connection Pools Issues Resetting Environment Testing Connectivity Pool Sizing Lingering Transactions Analysis Examples Graceful Failover

(53)

Connection Pool -‐ Resetting Status

53

JDBC-‐Pool C3P0 DBCP2 HikariCP

Rollback rollbackOnReturn=false autoCommitOnClose=false rollbackOnReturn 

=true always rollback()

Commit commitOnReturn=false autoCommitOnClose=false n/a n/a

Avoid see above forceIgnoreUnresolvedTrans

actions=false see above

Auto

Commit Driver Driver enableAutoCommit

(54)

MYSQL CONNECTORS

CONNECTION POOLS

Connection Pools Issues Resetting Environment Testing Connectivity Pool Sizing Lingering Transactions Analysis Examples Graceful Failover

(55)

Connection Pool -‐ Testing

• _{Making sure the connection is still active} • If not, maybe reopen a connection

• _{Not recommended as DB} • However, applications:

• _{do not like errors}

• do not retry gracefully

(56)

• _{If connections REALLY need to be tested…} • do not specify test query like:

• _{SELECT 1}_!

• _{SELECT * FROM DUAL}_!

• _{Leave default, all of the connection pools use:} 

JDBC4 isValid();

(57)

57

JDBC-‐Pool C3P0 DBCP2 HikariCP

Test Before testOnBorrow=false testConnectionOnCheckOut 

=false

testOnBorrow=false n/a

Test After testOnReturn=false testConnectionOnCheckIn 

=false

testOnReturn=false n/a

Test While Idle testWhileIdle=false idleConnectionTestPeriod 

=0

testWhileIdle=false n/a

JDBC4 isValid() default default default jdbc4ConnectionTest 

=true (default) Query validationQuery  (isValid) preferredTestQuery=null validationQuery  (isValid) connectionTestQuery  =none

(58)

• _JDBC:_{validationInterval=30s}

WHY? It defeats the whole purpose!

(59)

MYSQL CONNECTORS

CONNECTION POOLS

Connection Pools Issues Resetting Environment Testing Connectivity Pool Sizing Lingering Transactions Analysis Examples Graceful Failover

(60)

Connection Pool -‐ Pool Sizing

• _{Funnelling on Application Level, is good} • Smaller Number is Better

• _{+-‐ * CPU’s on DB}

• maybe a bit more (waiting on IO…) • _{all application servers combined}

• Response Time vs Throughput

(61)

Connection Pool -‐ Pool Sizing

61

JDBC-‐Pool C3P0 DBCP2 HikariCP

Amount of

Connections maxActive=100 maxPoolSize=15 maxTotal=8 maximumPoolSize=10

Maximum Idle

Connections maxIdle=100 maxIdleTime=0** maxIdle=8 n/a

Minimum Idle

Connections minIdle=10 minPoolSize=3 minIdle=0 minimumIdle=max

(62)

MYSQL CONNECTORS

CONNECTION POOLS

Connection Pools Issues Resetting Environment Testing Connectivity Pool Sizing Lingering Transactions Analysis Examples Graceful Failover

(63)

Connection Pool -‐ Avoid Lingering Transactions

• _{Application forgets to return the connection} • Statements that take longer than …

!

• Avoid this!

• _{Fix Application}

(64)

Connection Pool -‐ Avoid Lingering Transactions 64 KILL Warning JDBC-‐Pool removeAbandoned=false  removeAbandonedTimeout=60  abandonWhenPercentageFull=0 suspectTimeout=0 C3P0 unreturnedConnectionTimeout=0 n/a DBCP removeAbandoned=false  removeAbandonedTimeout=300  Only When:   getNumIdle() < 2 and getNumActive() > getMaxTotal() -‐ 3) n/a

(65)

MYSQL CONNECTORS

CONNECTION POOLS

Connection Pools Issues Resetting Environment Testing Connectivity Pool Sizing Lingering Transactions Analysis Examples Graceful Failover

(66)

Connection Pools -‐ How To Look At Workload?

• _{Slow Query Log} • _tcpdump_!

• _{pt-query-digest}_! • Percona Cloud Tools

(67)

MYSQL CONNECTORS

CONNECTION POOLS

Connection Pools Issues Resetting Environment Testing Connectivity Pool Sizing Lingering Transactions Analysis Examples Graceful Failover

(68)

Connection Pool -‐ Example Transaction

Connection con = ds.getConnection();!

con.setTransactionIsolation! ! !

(Connection.TRANSACTION_READ_COMMITTED);!

con.setAutoCommit(false);!

PreparedStatement stmt =

con.prepareStatement("select * from

employees where emp_no = ?");!

stmt.setInt(1, 20000);! ResultSet rs = stmt.executeQuery();! stmt.close();! rs.close();! con.commit();! con.close(); 68

(69)

For Connectors -‐ RECAP

• _{MySQL Connector/J}

• _{useConfigs=maxPerformance}_! • _{useServerPrepStmts=true}_!

• MariaDB Java Client • _{JDBC-‐Pool:}

jdbcInterceptors="ConnectionState"

• Other Pools: UNKNOWN

(70)

Connection Pool -‐ TRX JDBC

! ! ! where emp_no = 20000;!

!

commit;

70

(71)

Connection Pool -‐ TRX C3P0

! ISOLATION LEVEL READ COMMITTED;!

SET autocommit=0;!

! ! ! where emp_no = 20000;!

commit;!

SET autocommit=1;!

! ! ISOLATION LEVEL REPEATABLE READ;

71

(72)

Connection Pool -‐ TRX C3P0

mysql> set global !

! tx_isolation=“READ-COMMITTED”;!

!

forceIgnoreUnresolvedTransactions=true!

72

(73)

Connection Pool -‐ TRX DBCP

SET autocommit=1;!

# administrator command: Ping;!

SET autocommit=0;!

! ! ! where emp_no = 20000;! commit;! rollback;! SET autocommit=1;! 73

700

(74)

Connection Pool -‐ TRX DBCP testOnBorrow=false! rollbackOnReturn=false! enableAutoCommitOnReturn=false! ! jdbcUrl: useLocalTransactionState=true! 74

200

(75)

Connection Pool -‐ TRX HikariCP

! ISOLATION LEVEL READ COMMITTED;!

SET autocommit=0;!

! ! ! where emp_no = 20000;!

commit;!

rollback;!

SET autocommit=1;!

! ! ISOLATION LEVEL REPEATABLE READ;!

75

(76)

Connection Pool -‐ TRX HikariCP

mysql> set global !

! tx_isolation=“READ-COMMITTED”;!

!

autoCommit=false!

! !

--> will always do rollback!!!!!

76

(77)

Connection Pools

(78)

MariaDB vs. Connector/J

(79)

MYSQL CONNECTORS

CONNECTION POOLS

Connection Pools Issues Resetting Environment Testing Connectivity Pool Sizing Lingering Transactions Analysis Examples Graceful Failover

(80)

Connection Pools -‐ Graceful Failover

(81)

• _{HAProxy ‘stats socket’}

/etc/haproxy/haproxy.cfg!

global!

. . . !

stats socket /tmp/haproxy.sock level admin! • Disable Node

# echo "disable server database/node1"  

| socat stdio /tmp/haproxy.sock

(82)

(83)

• _{During ‘maintenance’, what do we do?} • KILL old connections?

• _{Wait until connections are closed? (Define}

lifetimes?)

• Ignore it?

(84)

• _{Some connection pools can close connections}

gracefully, when idle.

• For ‘synchronous’ replication systems • _{using JMX} • No Application Errors! 84 Method JDBC-‐Pool purgeOnReturn() C3P0 softResetAllUsers() DBCP n/a

(85)

(86)

(87)

(88)

• _{0 Application Errors} • Completely seamless

(89)

MYSQL CONNECTORS

CONNECTION POOLS

Connection Pools Issues Resetting Environment Testing Connectivity Pool Sizing Lingering Transactions Analysis Examples Graceful Failover

Conn/J Extra Features

(90)

Connector/J -‐ Extra Features • Load Balancing • jdbcUrl: ”jdbc:mysql:loadbalance:// node1,node2/db? loadBalanceConnectionGroup=lb&  loadBalanceEnableJMX=true”! • _{loadBalanceStrategy} (random/bestResponseTime)! • Failover • ReplicationDriver (setReadOnly)

• Combining with Connection Pools is less useful

• Fabric

(91)

Victory!

(92)

Java MySQL Connector &  

Connection Pool Optimization

• http://dev.mysql.com/doc/connector-‐j/en • _{https://mariadb.com/kb/en/mariadb/client-‐libraries/mariadb-‐java-‐client/} • http://tomcat.apache.org/tomcat-‐7.0-‐doc/jdbc-‐pool.html • _{http://www.mchange.com/projects/c3p0} • http://commons.apache.org/proper/commons-‐dbcp/ • _{https://github.com/brettwooldridge/HikariCP} 92

MYSQL CONNECTORS

CONNECTION POOLS

Kenny Gryp <[email protected]> November 4, 2014