Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM

[Rick James]

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