MySQL 5.7 vs 5.6: Index usage wrong at first, but "automagically" fixed weeks later

user_3022320 0
Viewed 1000

Context:

I have a MySQL 5.6 as master, that have two replicas, one of them is also a MySQL 5.6 instance and other is a MySQL 5.7. Heavy queries are distributed evenly across the two replicas.

The 5.6 Replica is up and running for about 2 months, the 5.7 is newer, running about two weeks.

Using AWS RDS.

Weird Behaviour:

Just after the creation, I noticed that some queries were much slower at 5.7 because of “wrong index usage”, as shown by comparing the EXPLAIN result on both versions. Even some queries that had an USE INDEX clause, seemed to have no effect on 5.7 database.

Giving that it was a fresh new replica, I thought: “Maybe index statistics aren’t up to date?“.

Not the case. I have a routine that runs “analyse table” for every table on the system overnight at the master DB, so index statistics should be fine. I can confirm that the “analyse table” statement is successfully being replicated by checking the last_update on the replicas mysql.innodb_index_stats.

But some days have passed by, and without any work on my side, the bad queries started to use the right indexes! All fixed. No work done. Crazy day.

Questions:

  1. What could affect the choice for a specific query execution plan? Index statistics aren’t the only thing?

  2. Is there any “caching/warming up” stuff, that over two weeks of actual work could make The Optimiser change his mind?

  3. Shouldn’t “analyse table” be enough to guarantee that both queries would have the same execution plan?

  4. What else can explain that after two weeks of executions, the MySQL 5.7 starts to work same way as MySQL 5.6?

Remember, both databases were REPLICAS from the SAME SOURCE that receive EVENLY DISTRIBUTED traffic.

SQL Example:

The following query (this is an obfuscated version), was using the BAD index, and one day later it was using the GOOD index. Just like Magic™.

SELECT sale.number,
       seller.id,
       seller.name,
       sale.id,
       COALESCE(billed_amount, 0),
       COALESCE(billing.tax_id, '---'),
       billing.date,
       CASE COALESCE(sale.seller_name_2, '') WHEN '' THEN sale.seller_name_1 ELSE sale.seller_name_2 END,
       sale.business_id,
       sale.business_name,
       sale.total
FROM app_billing billing
         JOIN app_sale sale ON billing.sale_id = sale.id
         JOIN app_seller seller ON seller.id = sale.seller_id
WHERE billing.tenant_id = 515
  AND billing.removed = FALSE
  AND billing.date BETWEEN '2020-08-01' AND '2020-08-31'
  AND sale.status = 2
  AND sale.seller_id IN (368);

-- MySQL 5.7 (BAD, really bad estimate and counter intuitive decision [should definitely be a range scan])
-- 1st step: There are only one seller with ID 368, so thats right.
-- 2nd step: 692 is pretty accurate, there are 695 sales for the seller ID 368.
-- 3rd step: This is tricky. There are a total of 270776 billing records that matches the 695 sale_ids from the previous step. 65% of the matches, have only one correspondence, but the remaining 35% have between 1000 and 5000 correspondences. Average would be 1360.
-- Estimated total number of processed records: 942000~.
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
| 1 | SIMPLE | seller  | NULL | const | PRIMARY                                | PRIMARY       | 4 | const   | 1   | 100  | NULL |
| 1 | SIMPLE | sale    | NULL | ref   | PRIMARY,idx_seller_id                  | idx_seller_id | 4 | const   | 692 | 10   | Using where |
| 1 | SIMPLE | billing | NULL | ref   | idx_sale_id,idx_tenant_id_removed_date | idx_sale_id   | 4 | sale.id | 2   | 2.16 | Using where |

-- MySQL 5.6 (GOOD, using range scan)
-- 1st step: There are only one seller with ID 368, so thats right.
-- 2nd step: 421 is almost perfect. There are actually 422 billing records across the date '2020-08-01' AND '2020-08-31' for the tenant_id 515 that arent removed.
-- 3rd step: That's right. There's only one sale correspondence per billing,
-- Estimated total number of processed records: 421.
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
| 1 | SIMPLE | seller  | const  | PRIMARY                                | PRIMARY                    | 4 | const           | 1   | NULL |
| 1 | SIMPLE | billing | range  | idx_sale_id,idx_tenant_id_removed_date | idx_tenant_id_removed_date | 9 | NULL            | 421 | Using index condition |
| 1 | SIMPLE | sale    | eq_ref | PRIMARY,idx_seller_id                  | PRIMARY                    | 4 | billing.sale_id | 1   | Using where |
relational-database indexing mysql sql performance
2 Answers

MySQL 5.7 and later suffer quite badly from a query optimizer heisenbug, which makes it unpredictably pick an egregiously bad execution plan. It sounds like you are falling foul of that. I have seen this sort of inconsistent, unpredictable behaviour on every 5.7 installation I have seen to date (more than I can hope to enumerate).

Regarding your specific questions:

  1. Only the stats should be making a difference. However, the query plan was always also prone to getting confused when there is a large number of indexes in a table (more than approximately 10). And then there is the heisenbug I mentioned above.

  2. No, but table statistics are maintained all the time in the background. It sounds like it finally got the data distribution estimation right - eventually.

  3. It should - but often isn't. You may want to look at the following settings:

    innodb_stats_persistent_sample_pages innodb_stats_persistent

  4. optimizer_switch settings. You may want to try setting this on 5.7 to the same flag set that you have on 5.6. it sometimes helps (but not too often).

user_18426770

There are many reasons why the statistics might be slightly different, leading to a different explain plan.

Since the WHERE clause tests columns in more than one table, the Optimizer does not necessarily know which table to start with. Sometimes it will pick the "wrong" table.

Rather than chasing the statistics and the Optimizer's choice, let's improve the indexes, thereby hopefully making the index enough faster to quell the question:

billing:  (tenant_id, sale_id, removed, date, tax_id)
billing:  (removed, tenant_id, date, sale_id, tax_id)
sale:  (status, seller_id)

That set of indexes should help regardless of which table it decides to start with. The order of columns in each index is important. (There are some variations that would work equally well.)

Examples of why the Optimizer might "do the wrong thing".

  • If billing.tenant_id = 515 occurs in only one row, then starting billing would probably be fast -- fetch one row, then join to get the row(s) from the other table.
  • Similarly, if sale.status = 2 occurs infrequently, sale would be a better first choice.

There are no statistics to achieve the above. What there is: "There are about 100 distinct tenent_id values out of 5000 rows." That is, the Optimizer will assume that tenant_id=515 occurs in 50 rows.

Note: I said "about". This is because ANALYZE does not (assuming a large InnoDB table) look at every value in the table. Instead, it takes a sample. (Look for "number of dives" in the documentation.)

Some things that can lead Primary and Replica to act differently

  • When were the stats last updated? There is nothing to synchronize the updating.
  • The stats involve "random" probes. Hence slight (or big) variations can occur.
  • In some situations a slight change in the stats can lead to a big difference in the query plan chosen -- typically between a table scan and using an index. The Optimizer tries to have this quantum change at the point where there is not much difference in performance, but it can be wrong.
  • Presumably different SELECTs are un on different Replicas. SELECTs can interact with the writes coming through replication. This can lead to block splits (etc) happening at different times and/or different places in the table. This can indirectly impact the stats.
  • Etc.
user_17668310
Related