Re: Got timeout writing communication packets
Posted by: Peter Brawley
Date: August 06, 2021 12:03PM

First thing I think is to take the JDBC client out of the loop. What is innodb_buffer_pool_size? How much RAM is available to MySQL?

Are you running the MySQL query cache? If so, it's muddying your waters, turn it off.

If an upgrade to 8.0 isn't soon possible, unless I misunderstand this query I think you need to rework it such that you run its code logic ...

    item_t0.p_productcode as productcode, item_t0.p_currency as currency, 
    SUM( item_t0.p_totalorders ) totalorders, 
    SUM( item_t0.p_totalvisits ) totalvisits, 
    SUM( item_t0.p_totalproductviews ) totalproductviews, 
    SUM( item_t0.p_totalcartadds ) totalcartadds, 
    SUM( item_t0.p_totalunits ) totalunits, 
    SUM( item_t0.p_customscore1 ) customscore1, SUM( item_t0.p_customscore2 ) customscore2, 
    SUM( item_t0.p_customscore3 ) customscore3
  FROM productanalytics item_t0 
  WHERE ( item_t0.p_datetime >= '2021-01-01 00:00:00' AND item_t0.p_datetime <= '2021-07-28 23:59:59') 
    AND (item_t0.TypePkString=8796126871634 ) 
  GROUP BY item_t0.p_productcode, item_t0.p_currency
in.. just once, save that result to a temp table, then move the subsetting logic of your non-performing Left Joins to a pivot query on the above result.

Options: ReplyQuote

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.