MySQL Forums
Forum List  »  Performance

Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
Posted by: Rick James
Date: March 20, 2009 08:25PM

max_connections takes essentially no resources until the connections are used.

Case 1:
"Rows_sent: 8 Rows_examined: 152314 " -> good clue that it needs help.

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;

"OR" -- usually poorly optimized. Try

( SELECT  item_t0.PK
    FROM  genericitems item_t0
    WHERE  item_t0.p_user = 1133044444295776
      AND  item_t0.TypePkString = 23084444443799984
)
UNION DISTINCT
( SELECT  item_t0.PK
    FROM  genericitems item_t0
    WHERE  item_t0.p_user IS NULL)
      AND  item_t0.TypePkString = 23084444443799984
)
    ORDER BY  item_t0.p_date DESC;
I hope you have
INDEX (TypePkString, p_user)

Case 2:
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;
Again, "OR", but I'm not going to tackle this one.
Not that the only part of 3 things ANDed at the outer level that does not have an OR is item_t1, which is the first thing it picked in the EXPLAIN. Then it picked t0 next, but had to scan a lot of rows.

Case 3:
# Query_time: 5.608035 Lock_time: 0.000066 Rows_sent: 351007 Rows_examined: 702014
5 sec for 351K rows -- not bad. And it seems to have hit twice that many rows. This query worries me less than some.

Munch on those. Maybe I will look at some more of the slow queries.

Please use [ code ] and [ / code ] around any code, SQL or output

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
2969
March 20, 2009 08:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.