Bug #75592 | "SHOW VARIABLES WHERE" is expensive. | ||
---|---|---|---|
Submitted: | 23 Jan 2015 0:30 | Modified: | 28 Apr 2015 15:01 |
Reporter: | Domas Mituzas | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | Connector / J | Severity: | S3 (Non-critical) |
Version: | 5.1.34 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[23 Jan 2015 0:30]
Domas Mituzas
[23 Jan 2015 12:12]
MySQL Verification Team
Hello Domas Mituzas, Thank you for the report. Confirmed that on every connection Connector/J triggers "SHOW VARIABLES WHERE". Thanks, Umesh
[23 Jan 2015 12:15]
MySQL Verification Team
// Test script import java.sql.Connection; import java.sql.Timestamp; import java.sql.DriverManager; import java.sql.PreparedStatement; class Test1 { public static void main(String[] args) { Connection conn; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); String url = "jdbc:mysql://address=(protocol=tcp)(host=localhost)(port=15000)(user=root)/test"; conn = DriverManager.getConnection(url, "", ""); PreparedStatement stmt = conn.prepareStatement("INSERT INTO t1 (created_time) values (?)"); stmt.setTimestamp(1, new Timestamp(1300883333501L)); stmt.execute(); } catch (Exception e) { System.out.println("Exception: "+e); e.printStackTrace(); } } } // : javac Test1.java // Run script couple of times and check general log : java -classpath '.:/home/umshastr/bugs/mysql-connector-java-5.1.34/mysql-connector-java-5.1.34-bin.jar' Test1 // From gen log bin/mysqld, Version: 5.6.22 (MySQL Community Server (GPL)). started with: Tcp port: 15000 Unix socket: /tmp/mysql_ushastry.sock Time Id Command Argument .. . 57630 Query select @@version_comment limit 1 150123 12:59:42 57630 Query SELECT DATABASE() 57630 Init DB test 150123 13:00:11 57630 Query create table t1(created_time datetime) 150123 13:03:37 57631 Connect root@localhost on test 57631 Query /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_n ame = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Va riable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_n ame = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect' 57631 Query /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SELECT @@session.auto_increment_increment 57631 Query SET NAMES latin1 57631 Query SET character_set_results = NULL 57631 Query SET autocommit=1 57631 Query SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES' 57631 Query select @@session.tx_read_only 57631 Query INSERT INTO t1 (created_time) values ('2011-03-23 13:28:53.501') 150123 13:04:17 57630 Query select * from t1 150123 13:06:22 57632 Connect root@localhost on test 57632 Query /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_n ame = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Va riable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_n ame = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect' 57632 Query /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SELECT @@session.auto_increment_increment 57632 Query SET NAMES latin1 57632 Query SET character_set_results = NULL 57632 Query SET autocommit=1 57632 Query SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES' 57632 Query select @@session.tx_read_only 57632 Query INSERT INTO t1 (created_time) values ('2011-03-23 13:28:53.501') 150123 13:06:25 57633 Connect root@localhost on test 57633 Query /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_n ame = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Va riable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_n ame = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect' 57633 Query /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SELECT @@session.auto_increment_increment 57633 Query SET NAMES latin1 57633 Query SET character_set_results = NULL 57633 Query SET autocommit=1 57633 Query SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES' 57633 Query select @@session.tx_read_only 57633 Query INSERT INTO t1 (created_time) values ('2011-03-23 13:28:53.501') 150123 13:06:26 57634 Connect root@localhost on test 57634 Query /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_n ame = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Va riable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_n ame = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect' 57634 Query /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SELECT @@session.auto_increment_increment 57634 Query SET NAMES latin1 57634 Query SET character_set_results = NULL 57634 Query SET autocommit=1 57634 Query SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES' 57634 Query select @@session.tx_read_only 57634 Query INSERT INTO t1 (created_time) values ('2011-03-23 13:28:53.501')
[12 Mar 2015 18:16]
Eric Bergen
Somewhere in 5.6 LOCK_plugin_delete was added into the show variables path which reduces the incoming connection rate from connector/j from about ~16,000/s to ~2,500/s. This is a pretty significant performance regression.
[28 Apr 2015 15:01]
Daniel So
Added the following entry to the Connector/J 5.1.36 changelog: "At every connection, Connection/J executed a SHOW VARIABLES WHERE statement over a multitude of variables, which consumed a lot of time and memory. To improve the efficiency of the code, this fix replaces the statement with the more efficient SELECT @@variable query and also implements some other related changes."
[10 Jul 2015 8:02]
Charles Lip
Tested Connector/J-5.1.36 against MySQL-5.0.80, and found this performance-enhancement change in ConnectionImpl is breaking backward-compatibility. MySQL-5.0.80 is unable to handle "SELECT @@language"; not sure if this is also applicable to all other 5.0.x releases.
[10 Jul 2015 14:26]
Eric Bergen
The connector knows which version of mysql it is connected to before issuing these queries. You can easily decide to do different things for different versions.
[13 Jul 2015 1:09]
Charles Lip
As said: MySQL-5.0.80 is unable to handle "select @@language". However, in Connector/J-5.1.36, method com.mysql.jdbc.ConnectionImpl.loadServerVariables() triggered by ConnectionImpl.getInstance() call is issuing "select @@language" at around line #3855. This is shown by code snippet: if (!versionMeetsMinimum(5, 5, 0)) { nameToFieldNameMap.put("language", "@@language"); } As a result, JDBC client would hit SQLException on attempt to connect to MySQL-5.0.80 with this driver:- java.sql.SQLException: Unknown system variable 'language' at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1432) at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java :3870) at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImp l.java:3284) at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2 293) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2079) at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794) at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct orAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC onstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:400) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java :325) at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(Poole dConnection.java:307) There was no such issue with Connector/J prior to 5.1.36 release.