List:General Discussion« Previous MessageNext Message »
From:Rafal Kedziorski Date:April 19 2005 4:04pm
Subject:Slow performance?!
View as plain text  
Hi,

I wrote some weeks ago to the mailinglist with the same problem. The 
performance isn't still much better.

I try to find the optimal value for tmp_table_size (now set to 67108864), 
cause we have some problems. Sometimes I see, that the result data will be 
copied to tmp table:

Copying to tmp table | select distinct m.media_id from category_tree 
c_tree, media_2_category m2c, medi

This queries are slow. But in MySQLAdministrator 1.0.19 I see:

created_tmp_disk_tables = 0
created_tmp_files = 2315
created_tmp_tables = 98887

Why is MySQL copying the result set to tmp table? THe same query executed 
from command line is faaster than from our JBoss.

This query nees from command line 0,09-0,20 sec:

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 = 2
and
     c_tree.partner_id = 1
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 = 1
and
     mp.magix_product_id = 20
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 = 4 or mf.language_id is null)
and
     mf.media_file_quality_id = 4
and
     (mf.videosignal is null or mf.videosignal = 'ntsc')
and
     (c_tree.category_tree_id = 3533 or c_tree.parent_id = 3533 or 
c_tree.path like '3531/3533/%')
order by m2p.priority desc limit 36, 36

 From JBoss 0,4-1,6 sec.:

2005-04-19 17:54:41,576 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 656 millis
2005-04-19 17:56:08,836 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 657 millis
2005-04-19 17:56:11,799 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 1320 millis
2005-04-19 17:56:11,860 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 1263 millis
2005-04-19 17:56:37,947 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 450 millis
2005-04-19 17:58:20,456 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 450 millis
2005-04-19 18:00:37,389 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 453 millis
2005-04-19 18:01:29,018 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 624 millis
2005-04-19 18:02:08,169 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 452 millis
2005-04-19 18:02:12,141 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 456 millis
2005-04-19 18:02:27,084 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 452 millis
2005-04-19 18:02:29,907 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 453 millis
2005-04-19 17:52:21,821 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 660 millis
2005-04-19 17:53:39,256 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 574 millis
2005-04-19 17:54:00,722 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 573 millis
2005-04-19 17:54:03,903 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 655 millis
2005-04-19 17:54:20,486 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 644 millis
2005-04-19 17:59:06,642 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 656 millis
2005-04-19 17:59:24,124 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 657 millis
2005-04-19 18:01:18,357 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 654 millis
2005-04-19 18:01:27,095 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 632 millis
2005-04-19 18:01:28,402 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 915 millis
2005-04-19 18:01:29,468 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 667 millis
2005-04-19 18:02:59,673 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 656 millis
2005-04-19 18:03:02,092 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 1479 millis
2005-04-19 18:03:02,689 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 1640 millis

In resultset are 34 long values.

The explain of the sql statement looks so (csv separated by |):

table|type|possible_keys|key|key_len|ref|rows|Extra
mp|const|PRIMARY|PRIMARY|2|const|1|Using index; Using temporary; Using filesort
mt2mp|ref|media_type_2_magix_product__idx,media_type_id,magix_product_id|magix_product_id|2|const|18|Using

where
m|ref|PRIMARY,media_type_id|media_type_id|2|mt2mp.media_type_id|242|Using index
m2p|eq_ref|media_partner__idx,media_id,partner_id|media_partner__idx|10|m.media_id,const|1|Distinct
mf|ref|media_file_quality_language__idx,media_id,media_file_quality_id,language_id,media_file_quality__idx|media_file_quality_language__idx|10|m.media_id,const|1|Using

where; Distinct
m2c|ref|category_tree_id,media_id|media_id|8|m.media_id|3|Distinct
c_tree|eq_ref|PRIMARY,category_tree__idx,parent_id,mandant_id,partner_id,category_tree_path__idx|PRIMARY|8|m2c.category_tree_id|1|Using

where; Distinct

We are using MySQL 4.0.23a under Linux.


Regards,
Rafal 
Thread
Slow performance?!Rafal Kedziorski19 Apr