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
Regards, Rafal
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
