0

I am running a larger DB with tables > 200M datasets on a dedicated Ubuntu 20.04 machine.

Since a few days queries that use group by are extremly slow. I suspect that this is new since the last apt-get update of Ubuntu which might have updated MySql from 8.0.25 to 8.0.26 but I am not sure.

Current Version: mysql Ver 8.0.26-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Here is a sample query:

SELECT
  UNIX_TIMESTAMP(DATE) as time_sec,
  m.name AS POS,
  AVG(RATING)
FROM   merchants_product_ratings mpr
INNER JOIN merchants m ON mpr.merchant_id = m.id
INNER JOIN manufacturers_products p ON mpr.SKU = p.SKU
LEFT JOIN manufacturers_brands b ON p.brand_id = b.ID
WHERE
    MERCHANT_ID IN ( '1','2','4' )
  AND p.MANUFACTURER_ID = 19130
  AND FIND_IN_SET(b.ID_PARENT, '14255,113,124')
  AND CASE WHEN 0 = 1 THEN p.focus=1 ELSE TRUE END -- show only focus PZNs? 
  AND DATE BETWEEN FROM_UNIXTIME(1625335554) AND FROM_UNIXTIME(1630519554)
GROUP BY DATE, mpr.MERCHANT_ID
ORDER BY 
    date, m.name

The query is unmodified and worked before in <1s

SQL Mode: `sql-mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,IGNORE_SPACE,NO_ENGINE_SUBSTITUTION"

Now I am wondering how to fix it as it was previsously running and if it is a result of a wrong SQL mode or wrong group by. There are several other examples of the same type where it also comes down to the fact that a query on each merchant alone runs < 100ms but once there are more to group by it slows down to > 30s.`

From MySQL status on used RAM:

InnoDB Buffer Pool Data 40 GiB
Key Buffer Size 16 MiB
InnoDB Log Buffer Size 16 MiB

Total RAM 128G Buffer Pool Size: 104G

1 Answer 1

0

FIND_IN_SET(mpr.MERCHANT_ID, '1,2,4') is not sargable. Change to mpr.MERCHANT_ID IN (1,2,4). Ditto for the other FIND_in_set.

MERCHANT_ID IN ( '1','2','4' ) is the same as above. The redundancy may be harmful to performance.

What is the value of innodb_buffer_pool_size? How much RAM do you have? What does SHOW TABLE STATUS say for the relevant tables? I am concerned that the tables recently grew in size, leading to being I/O-bound instead of running in cache.

Also, provide SHOW CREATE TABLE for the tables. A common mistake is not to index many:many tables (like manufacturers_products) in the optimal way. Another possibility relates to 'composite' indexes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.