Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
Hi Aftab,
Here is the answer to the first question you had about my problem.
Thanks,
--Tim
> 1. Explain Select ...# for all slow queries
# Query_time: 21.268844 Lock_time: 0.000030 Rows_sent: 8 Rows_examined: 152314
SET timestamp=1237404288;
SELECT item_t0.PK FROM genericitems item_t0 WHERE ( item_t0.p_user = 1133044444295776 OR item_t0.p_user IS NULL) AND (item_t0.TypePkString = 23084444443799984 ) ORDER BY item_t0.p_date DESC;
+----+-------------+---------+------+----------------+----------------+---------+-------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------------+---------+-------+--------+-----------------------------+
| 1 | SIMPLE | item_t0 | ref | typepkindex_99 | typepkindex_99 | 8 | const | 180775 | Using where; Using filesort |
+----+-------------+---------+------+----------------+----------------+---------+-------+--------+-----------------------------+
# Query_time: 9.721920 Lock_time: 0.000185 Rows_sent: 1 Rows_examined: 81130
SET timestamp=1237404288;
SELECT item_t0.PK ,CASE WHEN item_t3.UniqueID IS NULL THEN 'ZZZZZZZZZZZZZZZZZZ' ELSE item_t3.UniqueID END as userUID, CASE WHEN item_t2.Code IS NULL THEN 'ZZZZZZZZZZ' ELSE item_t2.Code END as ugCode, CASE WHEN item_t0.PG =0 THEN 0 ELSE 1 END as pgStatus, CASE WHEN item_t0.ProductPK =0 THEN 0 ELSE 1 END as productStatus FROM taxrows item_t0 JOIN taxes item_t1 ON item_t0.taxPK = item_t1.PK LEFT JOIN enumerationvalues item_t2 ON item_t0.CG = item_t2.PK LEFT JOIN users item_t3 ON item_t0.UserPK = item_t3.PK WHERE (( item_t0.ProductPK = 288613444441760 ) OR ( item_t0.ProductPK = 0 AND item_t0.PG = 0 )) AND (((item_t3.TypePkString IS NULL OR item_t3.TypePkString IN ( 2344444444406800 , 23444444444406736 , 23444444444406624 )) AND item_t0.TypePkString = 2344444444421520 AND (item_t2.TypePkString IS NULL OR ( item_t2.TypePkString = 23444444444421616 ) ) AND item_t1.TypePkString = 23444444444408352 )) ORDER BY userUID ASC,ugCode ASC,productStatus DESC,pgStatus DESC, item_t1.Code ASC;
+----+-------------+---------+--------+------------------------+------------------+---------+---------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+------------------------+------------------+---------+---------------------------+-------+---------------------------------+
| 1 | SIMPLE | item_t1 | ref | PRIMARY,typepkindex_47 | typepkindex_47 | 8 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | item_t0 | ref | typepkindex_1054 | typepkindex_1054 | 8 | const | 14263 | Using where |
| 1 | SIMPLE | item_t2 | eq_ref | PRIMARY | PRIMARY | 8 | bobloblawb.item_t0.CG | 1 | Using where |
| 1 | SIMPLE | item_t3 | eq_ref | PRIMARY | PRIMARY | 8 | bobloblawb.item_t0.UserPK | 1 | Using where |
+----+-------------+---------+--------+------------------------+------------------+---------+---------------------------+-------+---------------------------------+
# Query_time: 5.608035 Lock_time: 0.000066 Rows_sent: 351007 Rows_examined: 702014
SET timestamp=1237408249;
SELECT item_t0.PK , item_t0.TypePkString , item_t0.Code FROM medias item_t0 WHERE ( item_t0.TypePkString IN ( 23444444444408832,23444444444419904,23444444444418064,23444444444427216,23444444444419968,23444444444418176,23444444444420640,23444444444427040,23444444444427072,23444444444427136,23444444444420592,23444444444427168 )) AND (item_t0.TypePkString IN ( 23444444444420592 , 23444444444427040 , 23444444444426784 , 23444444444419904 , 23444444444418064 , 23444444444420640 , 23444444444418176 , 23444444444426896 , 23444444444426944 , 23444444444419968 , 23444444444427072 , 23444444444427168 , 23444444444427136 , 23444444444427216 , 23444444444408832 ) ) ORDER BY item_t0.createdTS ASC, item_t0.PK ASC;
+----+-------------+---------+------+----------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | item_t0 | ALL | typepkindex_30 | NULL | NULL | NULL | 405088 | Using where; Using filesort |
+----+-------------+---------+------+----------------+------+---------+------+--------+-----------------------------+
# Query_time: 8.411537 Lock_time: 0.000104 Rows_sent: 1 Rows_examined: 2
SET timestamp=1237408470;
SELECT item_t0.PK ,CASE WHEN item_t0.StartDate IS NULL THEN 2 ELSE 1 END as drOrd FROM pricerows item_t0 WHERE ((( item_t0.ProductPK =0 AND item_t0.PG =0 ) OR item_t0.ProductPK =284444444444480 ) AND (( item_t0.UserPK =0 AND item_t0.CG =0 ) OR item_t0.UserPK =1144444444440864 ) AND item_t0.currencyPK = 9244444444447776 AND (( item_t0.StartDate IS NULL AND item_t0.EndDate IS NULL ) OR ( item_t0.StartDate <='2009-03-18 21:34:22' AND item_t0.EndDate >='2009-03-18 21:34:22' ))) AND (item_t0.TypePkString = 23444444444421344 ) ORDER BY item_t0.unitPK ASC, item_t0.minQtd ASC, item_t0.matchValue DESC, item_t0.netFlag ASC,drOrd ASC;
+----+-------------+---------+-------+----------------------------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+----------------------------------+-----------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | item_t0 | range | typepkindex_1055,matchindex_1055 | matchindex_1055 | 18 | NULL | 4 | Using where; Using filesort |
+----+-------------+---------+-------+----------------------------------+-----------------+---------+------+------+-----------------------------+
# Query_time: 8.413654 Lock_time: 0.000174 Rows_sent: 1 Rows_examined: 25
SELECT SUM( item_t0.p_stock ) FROM products item_t0 WHERE ( item_t0.p_baseproduct = 284444444444208) AND (item_t0.TypePkString IN ( 23444444444427552 , 23444444444427488 , 23444444444427600 , 23444444444416624 ) AND ((EXISTS (SELECT item_t1.PK FROM products item_t1 WHERE ( item_t1.PK = item_t0.p_baseproduct ) AND (item_t1.TypePkString IN ( 23444444444416624 , 23444444444427552 , 23444444444427488 , 23444444444427600 , 23444444444408896 ) AND ((((item_t1.TypePkString IN ( 23444444444427488 , 23444444444427600 , 23444444444416624 , 23444444444427552 ) )) OR (EXISTS (SELECT item_t2.PK FROM categories item_t2 JOIN cat2prodrel item_t3 ON item_t2.PK = item_t3.SourcePK WHERE ( item_t3.TargetPK = item_t1.PK ) AND ((item_t2.TypePkString IN ( 23444444444424128 , 23444444444422000 ) AND ((EXISTS (SELECT item_t4.PK FROM cat2princrel item_t4 WHERE ( item_t4.SourcePK = item_t2.PK AND item_t4.TargetPK IN ( 1144444444441584, 1444444444444192 )) AND (item_t4.TypePkString = 23444444444424800 ))) AND ( item_t2.p_catalogversion IN (164444444444723728))) AND item_t3.TypePkString = 23444444444422048 ))))) AND ( item_t1.p_approvalstatus = 25444444444461424 ) AND (( item_t1.p_onlinedate IS NULL OR item_t1.p_onlinedate <= '2009-03-18 00:00:00' ) AND ( item_t1.p_offlinedate IS NULL OR item_t1.p_offlinedate >= '2009-03-18 00:00:00' ))) ))) AND ( item_t0.p_approvalstatus = 25444444444461424 ) AND (( item_t0.p_onlinedate IS NULL OR item_t0.p_onlinedate <= '2009-03-18 00:00:00' ) AND ( item_t0.p_offlinedate IS NULL OR item_t0.p_offlinedate >= '2009-03-18 00:00:00' ))) );
+----+--------------------+---------+--------+-----------------------------------------------+----------------+---------+----------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+--------+-----------------------------------------------+----------------+---------+----------------------------------+------+-------------+
| 1 | PRIMARY | item_t0 | ref | typepkindex_1,baseIDX_1,visibilityIDX_1 | baseIDX_1 | 9 | const | 4 | Using where |
| 2 | DEPENDENT SUBQUERY | item_t1 | eq_ref | PRIMARY,typepkindex_1,visibilityIDX_1 | PRIMARY | 8 | bobloblawb.item_t0.p_baseproduct | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | item_t3 | ref | linktarget_143,typepkindex_143,linksource_143 | linktarget_143 | 8 | bobloblawb.item_t1.PK | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | item_t2 | eq_ref | PRIMARY,typepkindex_142,versionIDX_142 | PRIMARY | 8 | bobloblawb.item_t3.SourcePK | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | item_t4 | ref | typepkindex_613,linktarget_613,linksource_613 | linksource_613 | 8 | bobloblawb.item_t2.PK | 1 | Using where |
+----+--------------------+---------+--------+-----------------------------------------------+----------------+---------+----------------------------------+------+-------------+
# Query_time: 5.216312 Lock_time: 0.000066 Rows_sent: 43 Rows_examined: 152597
SET timestamp=1237451733;
SELECT item_t0.PK FROM genericitems item_t0 WHERE ( item_t0.p_user = 1144444444446368 OR item_t0.p_user IS NULL) AND (item_t0.TypePkString = 23444444444499984 ) ORDER BY item_t0.p_date DESC;
+----+-------------+---------+------+----------------+----------------+---------+-------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------------+---------+-------+--------+-----------------------------+
| 1 | SIMPLE | item_t0 | ref | typepkindex_99 | typepkindex_99 | 8 | const | 180831 | Using where; Using filesort |
+----+-------------+---------+------+----------------+----------------+---------+-------+--------+-----------------------------+
# Query_time: 5.224691 Lock_time: 0.000152 Rows_sent: 1 Rows_examined: 81130
SET timestamp=1237452449;
SELECT item_t0.PK ,CASE WHEN item_t3.UniqueID IS NULL THEN 'ZZZZZZZZZZZZZZZZZZ' ELSE item_t3.UniqueID END as userUID, CASE WHEN item_t2.Code IS NULL THEN 'ZZZZZZZZZZ' ELSE item_t2.Code END as ugCode, CASE WHEN item_t0.PG =0 THEN 0 ELSE 1 END as pgStatus, CASE WHEN item_t0.ProductPK =0 THEN 0 ELSE 1 END as productStatus FROM taxrows item_t0 JOIN taxes item_t1 ON item_t0.taxPK = item_t1.PK LEFT JOIN enumerationvalues item_t2 ON item_t0.CG = item_t2.PK LEFT JOIN users item_t3 ON item_t0.UserPK = item_t3.PK WHERE (( item_t0.ProductPK = 284444444444392 ) OR ( item_t0.ProductPK = 0 AND item_t0.PG = 0 )) AND (((item_t3.TypePkString IS NULL OR item_t3.TypePkString IN ( 23444444444406800 , 23444444444406736 , 23444444444406624 )) AND item_t0.TypePkString = 23444444444421520 AND (item_t2.TypePkString IS NULL OR ( item_t2.TypePkString = 23444444444481616 ) ) AND item_t1.TypePkString = 23444444444408352 )) ORDER BY userUID ASC,ugCode ASC,productStatus DESC,pgStatus DESC, item_t1.Code ASC;
+----+-------------+---------+--------+------------------------+------------------+---------+---------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+------------------------+------------------+---------+---------------------------+-------+---------------------------------+
| 1 | SIMPLE | item_t1 | ref | PRIMARY,typepkindex_47 | typepkindex_47 | 8 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | item_t0 | ref | typepkindex_1054 | typepkindex_1054 | 8 | const | 14263 | Using where |
| 1 | SIMPLE | item_t2 | eq_ref | PRIMARY | PRIMARY | 8 | bobloblawb.item_t0.CG | 1 | Using where |
| 1 | SIMPLE | item_t3 | eq_ref | PRIMARY | PRIMARY | 8 | bobloblawb.item_t0.UserPK | 1 | Using where |
+----+-------------+---------+--------+------------------------+------------------+---------+---------------------------+-------+---------------------------------+
...And variations of these queries.