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 View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3872618#3872618 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3872618 ------------------------------------------------------- SF email is sponsored by - The IT Product Guide Read honest & candid reviews on hundreds of IT Products from real users. Discover which products truly live up to the hype. Start reading now. http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click _______________________________________________ JBoss-user mailing list JBoss-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jboss-user