GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Poor performance on joins after MYSQL 4 -> 5 migration

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Poor performance on joins after MYSQL 4 -> 5 migration

    Good Afternoon,

    We have just gone through an upgrade of our database (and the hardware it runs on) moving from MySQL 4.1.7-nt to 5.0.27-community-max-nt.

    We have come across a number of queries that ran slowly after the move, taking several minutes to run, when they had run in seconds. We found that we could make them run quickly again by adding the STRAIGHT_JOIN clause to the query, understanding that the slow down may be related to a query optimizer issue. We therefor have a band aid fix to most of our problems with queries we directly control.

    However have users who connect to the database via crystal reports, access, etc. who are now reporting that their queries, which used to take seconds, are now taking minutes, just as some of our systems have showed before we used the STRAIGHT JOIN. we have taken their queries and tried them in both the query browser and command line, an example from the command line, the query without "STRAIGHT_JOIN" took 3min 15.63sec, while with "STRAIGHT_JOIN" it only took 6.22 seconds.

    I tried several things this weekend on a seperate installation, first installing a fresh version of 5.0.45 and then adding the tables from a mysqldump.

    I got the equivalent results:
    SELECT sbtshrink.store_id, sbtshrink.plt_rte_no, sbtshrink.count_date,
    sbtshrink.beg_inv_date, sbtshrink.beg_inv_units, sbtshrink.net_del_units,
    sbtshrink.sold_units, sbtshrink.book_units, sbtshrink.phys_units,
    sbtshrink.shrink_units, sbtshrink.shrink_dol, area_sales.area_sales_mgr,
    sbtshrink.bread_cake_code, profit_center.profit_center_name,
    dunsmaster.duns_name
    FROM ((ecommerce.sbtshrink sbtshrink INNER JOIN ecommerce.area_sales area_sales
    ON sbtshrink.area_sales=area_sales.area_sales)
    INNER JOIN ecommerce.profit_center profit_center
    ON sbtshrink.profit_center=profit_center.profit_cente r)
    INNER JOIN ecommerce.dunsmaster dunsmaster
    ON sbtshrink.duns_id=dunsmaster.duns_id
    WHERE sbtshrink.count_date={d '2008-01-18'} AND dunsmaster.duns_name='CUSTOMER'
    ORDER BY sbtshrink.bread_cake_code, area_sales.area_sales_mgr, sbtshrink.count_date

    runs in ------ 7 min 0.69 sec



    Meanwhile

    SELECT STRAIGHT_JOIN sbtshrink.store_id, sbtshrink.plt_rte_no, sbtshrink.count_date,
    sbtshrink.beg_inv_date, sbtshrink.beg_inv_units, sbtshrink.net_del_units,
    sbtshrink.sold_units, sbtshrink.book_units, sbtshrink.phys_units,
    sbtshrink.shrink_units, sbtshrink.shrink_dol, area_sales.area_sales_mgr,
    sbtshrink.bread_cake_code, profit_center.profit_center_name, dunsmaster.duns_name
    FROM ((ecommerce.sbtshrink sbtshrink INNER JOIN ecommerce.area_sales area_sales
    ON sbtshrink.area_sales=area_sales.area_sales)
    INNER JOIN ecommerce.profit_center profit_center
    ON sbtshrink.profit_center=profit_center.profit_cente r)
    INNER JOIN ecommerce.dunsmaster dunsmaster
    ON sbtshrink.duns_id=dunsmaster.duns_id
    WHERE sbtshrink.count_date={d '2008-01-18'} AND dunsmaster.duns_name='CUSTOMER'
    ORDER BY sbtshrink.bread_cake_code, area_sales.area_sales_mgr, sbtshrink.count_date

    runs in ------ 1.97 sec

    I have tried changing the optimizer_prune_level and optimizer_search_depth neither of which seamed to give any resolution.

    On running examine for the one without STRAIGHT_JOIN (the slow query) I get
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: area_sales
    type: ALL
    possible_keys: PRIMARY
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 74
    Extra: Using temporary; Using filesort
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: sbtshrink
    type: ref
    possible_keys: duns_id,count_date,profit_center,area_sales
    key: area_sales
    key_len: 10
    ref: ecommerce.area_sales.area_sales
    rows: 641
    Extra: Using where
    *************************** 3. row ***************************
    id: 1
    select_type: SIMPLE
    table: dunsmaster
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 11
    ref: ecommerce.sbtshrink.duns_id
    rows: 1
    Extra: Using where
    *************************** 4. row ***************************
    id: 1
    select_type: SIMPLE
    table: profit_center
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 9
    ref: ecommerce.sbtshrink.profit_center
    rows: 1
    Extra:
    4 rows in set (0.00 sec)


    When running it with STRAIGHT_JOIN I see:
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: sbtshrink
    type: ref
    possible_keys: duns_id,count_date,profit_center,area_sales
    key: count_date
    key_len: 4
    ref: const
    rows: 21078
    Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: area_sales
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 9
    ref: ecommerce.sbtshrink.area_sales
    rows: 1
    Extra:
    *************************** 3. row ***************************
    id: 1
    select_type: SIMPLE
    table: profit_center
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 9
    ref: ecommerce.sbtshrink.profit_center
    rows: 1
    Extra:
    *************************** 4. row ***************************
    id: 1
    select_type: SIMPLE
    table: dunsmaster
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 11
    ref: ecommerce.sbtshrink.duns_id
    rows: 1
    Extra: Using where
    4 rows in set (0.00 sec)

    I cant think of any additional information I might provide, but if there is anything, Please don't hesitate to ask.

    I am getting an impression that there is no workaround for this aside from actually editing the user's queries (which will be difficult as we lack a list of users who use third party tools such as crystal reports)

    Please, if anyone can help, I am out of ideas.
    Thanks.

  • #2
    Have you tried ANALYZE TABLE [yourtablehere] after importing the data to the new servers?
    It could help the optimizer to choose a better join order for you.

    Comment


    • #3
      And there was much rejoicing!

      That did the trick! And I am feeling VERY stupid for not thinking of it myself. Ah well, marked up as a learning experience.

      Comment


      • #4
        Good!
        Don't feel bad about not thinking about it, we have all done that at one time or another! Just remember it the next time!

        Comment

        Working...
        X