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
[email protected]
https://lists.sourceforge.net/lists/listinfo/jboss-user
