openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MiƂosz Tylenda <mtyle...@o2.pl>
Subject Re: [ot] mysql connections, always making lots of calls
Date Thu, 18 Dec 2008 10:11:28 GMT
Fernando,

Yes, it was noticed, see for instance [1], but no solution was found.

Have you experimented with Connector/J properties or tried to change the pool implementation?

Cheers,
Milosz

[1] http://n2.nabble.com/slow-performance-on-MySql-5.0-tp782401p782401.html


> So I turned on logging of mysql queries (mysql connector/j), and it 
> looks like everytime a connection is gotten from the connection pool, it 
> executes a handful of queries against mysql.
> 
> I was wondering if anyone has run into this, and if there is a way to 
> avoid all of the extra calls..
> 
> 
> 
> 
> 
> 
> 2008-12-17 13:16:02,244 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [SLOW QUERY]    at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 2 ms,
> connection-id: 1980, statement-id: 99, resultset-id: 99, message: The 
> following query was executed with a bad index, use 'EXPLAIN' for more 
> details: /* mysql-connector-java-5.1.6 ( Revision: ${svn.Revision}
> ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 
> '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 Variable_name = 
> 'character_set' OR Variable_name = 'character_set_server' OR 
> Variable_name = 'tx_isolation' OR Variable_name = 
> 'transaction_isolation' OR Vari
> able_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_allo
> wed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 
> 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 
> 'query_cache_size' OR Variable_name = 'init_connect'
> 2008-12-17 13:16:02,246 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [QUERY]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 2 ms,
> connection-id: 1980, statement-id: 99, resultset-id: 99, message: /* 
> mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} ) */SHOW 
> VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_ti
> meout' OR Variable_name = 'interactive_timeout' OR Variable_name = 
> 'wait_timeout' OR Variable_name = 'character_set_client' OR 
> Variable_name = 'character_set_connection' OR Variable_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_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR
>   Variable_name = 'query_cache_type' OR Variable_name = 
> 'query_cache_size' OR Variable_name = 'init_connect'
> 2008-12-17 13:16:02,250 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [FETCH]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 2 ms,
> connection-id: 1980, statement-id: 99, resultset-id: 99
> 2008-12-17 13:16:02,253 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [SLOW QUERY]    at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 1 ms,
> connection-id: 1980, statement-id: 100, resultset-id: 100, message: The 
> following query was executed with a bad index, use 'EXPLAIN' for more 
> details: SHOW COLLATION
> 2008-12-17 13:16:02,254 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [QUERY]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 1 ms,
> connection-id: 1980, statement-id: 100, resultset-id: 100, message: SHOW 
> COLLATION
> 2008-12-17 13:16:02,255 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [FETCH]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 2 ms,
> connection-id: 1980, statement-id: 100, resultset-id: 100
> 2008-12-17 13:16:02,257 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [QUERY]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 0 ms,
> connection-id: 1980, statement-id: 999, resultset-id: 0, message: SET 
> NAMES latin1
> 2008-12-17 13:16:02,258 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [FETCH]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 0 ms,
> connection-id: 1980, statement-id: 999, resultset-id: 0
> 2008-12-17 13:16:02,260 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [QUERY]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 1 ms,
> connection-id: 1980, statement-id: 999, resultset-id: 0, message: SET 
> character_set_results = NULL
> 2008-12-17 13:16:02,261 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [FETCH]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 0 ms,
> connection-id: 1980, statement-id: 999, resultset-id: 0
> 2008-12-17 13:16:02,262 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [QUERY]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 0 ms,
> connection-id: 1980, statement-id: 999, resultset-id: 0, message: SET 
> autocommit=1
> 2008-12-17 13:16:02,263 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [FETCH]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 0 ms,
> connection-id: 1980, statement-id: 999, resultset-id: 0
> 2008-12-17 13:16:02,265 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [QUERY]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 1 ms,
> connection-id: 1980, statement-id: 999, resultset-id: 0, message: SET 
> sql_mode='STRICT_TRANS_TABLES'
> 2008-12-17 13:16:02,266 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [FETCH]         at 
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:81)

> duration: 0 ms,
> connection-id: 1980, statement-id: 999, resultset-id: 0
> 2008-12-17 13:16:02,267 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [QUERY]         at 
> org.apache.openjpa.lib.jdbc.DelegatingConnection.setAutoCommit(DelegatingConnection.java:198)

> duration: 0 ms, connec
> tion-id: 1980, statement-id: 999, resultset-id: 0, message: SET autocommit=0
> 2008-12-17 13:16:02,268 [32336015@qtp0-0] INFO  MySQL - Profiler Event: 
> [FETCH]         at 
> org.apache.openjpa.lib.jdbc.DelegatingConnection.setAutoCommit(DelegatingConnection.java:198)

> duration: 0 ms, connection-id: 1980, statement-id: 999, resultset-id: 0
> 

Mime
View raw message