0

Few queries are performing very poorly after the upgrade. Here are the examples.

    ****Example 1**:**
SELECT 
    x.CI_TITLE 'ciTitle',
    x.DEPARTMENT_MEANING 'departmentMeaning',
    x.RESIDUAL_FINANCIAL_IMPACT_LOSS 'residualFinancialImpactLoss',
    x.INHERENT_CONFIDENTIALITY 'inherentConfidentiality',
    x.PRIMARY_FLAG 'primaryFlag',
    x.MEDIA_IMPACT_COMMENT 'mediaImpactComment',
    x.OPEN_CHALLENGE 'openChallenge'
FROM
    omb_risk_latest_eval_v x
WHERE
    x.COMPANY_ID = 187
ORDER BY x.LAST_UPDATE_DATE DESC
LIMIT 30 OFFSET 0

The above query is taking average of 1.2 sec in 5.7.24, however the same query is taking 26 secs in 8.0.19.

Here are certain things I tried my level best.

  1. All the tables have indexes present 2)Rebuilt all the tables in 8 version using alter table engine=innodb;
  2. Checked the optimizer switch variables. Compared in both versions. Found the below 3 were extra skip_scan=on hash_join=on

Tried by disabling them, but no impact 4)googled many forums for such kind of issues. They suggested the below but these didn't work.

condition_fanout_filter=off

internal_tmp_mem_storage_engine=MEMORY

suggestions from the below sites..

http://dimitrik.free.fr/blog/archives/2018/04/mysql-performance-testing-80-with-less-blood.html https://www.percona.com/blog/2018/05/04/how-binary-logs-affect-mysql-8-0-performance/

Also we are confident that there could be some server side settings which should be disabled explicitly in 8.x version of MYSQL, as these queries are performing very well in 5.7.

Wanted to share the exec plan for both versions but not sure how to do it as we have only image as attachment

Any suggestions/help please...

I

3
  • 1
    You should run the EXPLAIN in MySQL commandline client - that will give a nice text output of the plan - the plans for both versions are quite important for anyone trying to help. You should add the CREATE TABLE statement too to show exact columns, data types and index definitions.
    – jkavalik
    Sep 24 at 13:28
  • Hi, and welcome to dba.se! You can run (on 8) the new EXPLAIN ANALYZE command which gives far more detail... Sep 24 at 13:35
  • Please consider reading this advice
    – mustaccio
    Sep 24 at 13:41
0

Do you have this composite index?

INDEX(COMPANY_ID, LAST_UPDATE_DATE)

If you need further discussion, please provide SHOW CREATE TABLE omb_risk_latest_eval_v and EXPLAIN FORMAT=JSON SELECT ....

Oh, that's a VIEW. (The SHOW would have pointed that out.)

VIEWs are 'syntactic sugar'; they do not provide any added performance. It sees that this View is causing a performance problem that got worse with 8.0. Please file a bug report about the regression: bugs.mysql.com .

3
  • Actually omb_risk_latest_eval_v is a view. It has internal sub views for other calculation purposes. My concern is these views were working really well in 5.7 version...but very poor in 8.0.19 version...So i doubt there must be some extra configuration settings which are enabled/disabled in 8.0.19 version, making this view to perform very poorly...Please correct if i am wrong...
    – Satya Ammu
    Sep 27 at 6:08
  • @SatyaAmmu still, the EXPLAIN results comparison can tell you, we don't have a crystal ball :)
    – jkavalik
    Sep 27 at 8:57
  • I added to my answer.
    – Rick James
    Sep 27 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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