Hi,

after extending our MySQL 4.0.23a installation to master-slave configuration, two specific queries sended from our JBoss are 25-30 times slower.

In our J2EE application which runs under JBoss 3.2.2 we are generating own queries by using a connection from JBoss connection pool. This are prepared statements:

First query:

select count(distinct m.media_id) from category_tree c_tree, media_2_category m2c, media m, magix_product mp, media_type_2_magix_product mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ? and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id = m.media_id and mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?)

Second query:

select distinct m.media_id from category_tree c_tree, media_2_category m2c, media m, media_2_partner m2p, magix_product mp, media_type_2_magix_product mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ? and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id = m.media_id and m2p.media_id = m.media_id and m2p.partner_id = ? and mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?) order by m2p.priority desc limit ?, ?

The code looks so:

 public final List getMediaIdsForCategoryNode(Integer mandant_id, Integer partner_id, Integer language_id,
 Long category_tree_id, String path, Integer media_type_id, Integer start, Integer offset, Integer magix_product_id,
 Integer media_file_quality_id) {
 if (logger.isDebugEnabled()) {
 logger.debug("getMediaIdsForCategoryNode('" + mandant_id + "', '" + partner_id + "', '" + language_id + "', '" +
 category_tree_id + "', '" + path + "', '" + media_type_id + "', '" + start + "', '" + offset + "', '" +
 magix_product_id + "', '" + media_file_quality_id + "') entered");
 }

 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;

 try {
 conn = getConnection();

 if (conn != null) {
 timer.reset();

 // SQL Query
 StringBuffer query = new StringBuffer(BUFFER_LEN)
 .append("select distinct m.media_id")
 .append(" from")
 .append(" category_tree c_tree")
 .append(", media_2_category m2c")
 .append(", media m")
 .append(", media_2_partner m2p");

 // magix_product_id tables
 addMagic_ProductSQLTables(query, magix_product_id);

 if (media_type_id != null) {
 query.append(", media_type mt");
 }

 if (language_id != null) {
 query.append(", media_file mf");
 }

 query.append(" where")
 .append(" c_tree.mandant_id = ?")
 .append(" and c_tree.partner_id = ?")
 .append(" and c_tree.category_tree_id = m2c.category_tree_id")
 .append(" and m2c.media_id = m.media_id")
 .append(" and m2p.media_id = m.media_id")
 .append(" and m2p.partner_id = ?");

 // magix_product_id conditions
 if (media_type_id != null) {
 query.append(" and m.media_type_id = ?");
 }

 // magix_product_id conditions
 addMagic_ProductSQLConditions(query, magix_product_id);

 if (language_id != null) {
 query.append(" and mf.media_id = m.media_id")
 .append(" and (mf.language_id = ? or mf.language_id is null)")
 .append(" and mf.media_file_quality_id = ?");
 }

 query.append(" and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?)");
 query.append(" order by m2p.priority desc");

 boolean setLimit = false;
 if (start != null && offset != null) {
 query.append(" limit ?, ?");

 setLimit = true;
 }

 if (logger.isDebugEnabled()) {
 logger.debug("getMediaIdsForCategoryNode() query=" + query);
 }

 // set values
 int setPos = 1;
 pstmt = conn.prepareStatement(query.toString());

 // mandant_id
 pstmt.setLong(setPos++, mandant_id.longValue());

 // partner_id
 pstmt.setInt(setPos++, partner_id.intValue());
 pstmt.setInt(setPos++, partner_id.intValue());

 // media_type_id
 if (media_type_id != null) {
 pstmt.setInt(setPos++, media_type_id.intValue());
 }

 // magix_product_id
 if (magix_product_id != null) {
 pstmt.setInt(setPos++, magix_product_id.intValue());
 }

 if (language_id != null) {
 pstmt.setInt(setPos++, language_id.intValue());
 pstmt.setInt(setPos++, media_file_quality_id.intValue());
 }

 // category_tree_id
 pstmt.setLong(setPos++, category_tree_id.longValue());

 // parent_id
 pstmt.setLong(setPos++, category_tree_id.longValue());

 // path
 pstmt.setString(setPos++, path);

 // set limit
 if (setLimit) {
 pstmt.setInt(setPos++, start.intValue());
 pstmt.setInt(setPos++, offset.intValue());
 }

 // execute
 rs = pstmt.executeQuery();

 List mediaIds = new ArrayList();

 // read result
 while (rs.next()) {
 Long media_id = new Long(rs.getLong(1));
 mediaIds.add(media_id);
 }

 return mediaIds;
 }
 else {
 logger.error("getMediaIdsForCategoryNode() conn=null");
 }
 }
 catch (SQLException sqle) {
 logger.fatal("getMediaIdsForCategoryNode() failed", sqle);
 }
 finally {
 sqlUtils.closeConnections(conn, pstmt, rs);
 }

 return Constants.EMPTY_LIST;
 }

Times for execute query:

first query
- needed from JBoss 450-500 millis
- nedded from normal Java application 15-25 millis

second query
- needed from JBoss 500-800 millis
- nedded from normal Java application 19 millis

All other sql statements generated by JBoss for entity beans are fast like bevore switching to master-slave configuration. Thru this queries the speed of our service is 2-3 times slower.


After spend some hours checking our system, I have no more idea where is the problem. Today I tryed newver Java version and JBoss 3.2.5. But without any changes. The performance is still the same.

This is our MySQL configuration:

[mysqld]
datadir=/drbd/mysql


log-bin
server-id=20


set-variable = key_buffer=128M
set-variable = table_cache=512
set-variable = sort_buffer=8M
set-variable = join_buffer_size=8M
set-variable = query_cache_size=32M
set-variable = record_buffer=4M
set-variable = thread_cache_size=400
set-variable = max_connections=300
set-variable = long_query_time=10
log_long_format
log_slow_queries
innodb_data_file_path = ibdata1:2048M;ibdata2:10M:autoextend
#innodb_buffer_pool_size = 384M
innodb_buffer_pool_size = 1228M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1

We are using mysql-jdbc 3.0.16!

Has anybody have the same experience and can give me some help?


Best Regards,
Rafal