4 Replies Latest reply on Apr 21, 2005 11:32 AM by Rafal Kedziorski

    Strange behavior: slow JDBC 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:


      1.

      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 ?)
      


      2.

      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 ?, ?
      


      Times:

      1.
      - needed from JBoss 450-500 millis
      - nedded from normal Java application 15-25 millis

      2.
      needed from JBoss 500-800 millis
      - nedded from normal Java application 19 millis
      - nedded from normal Java application 20-30 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.


      This is our my.cnf:

      [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
      


      Is this a JBoss or MySQL problem?

      We are using mysql-jdbc 3.0.16!


      Regards,
      Rafal