0 Replies Latest reply on Apr 4, 2005 3:28 PM by Rafal Kedziorski

    Performance problem with some sql queries

    Rafal Kedziorski Novice

      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