3

So few weeks ago we had an alert on our production MySQL database due to a CPU usage spike. We identified the issue as a query that had been there for several months that suddenly was misbehaving. The execution plan of that query is:

    +----+-------------+------------+------------+------+----------------------------------------------------------+----------------------------+---------+----------------------------+------+----------+----------------------------------------------+
    | id | select_type | table      | partitions | type | possible_keys                                            | key                        | key_len | ref                        | rows | filtered | Extra                                        |
    +----+-------------+------------+------------+------+----------------------------------------------------------+----------------------------+---------+----------------------------+------+----------+----------------------------------------------+
    |  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL                                                     | NULL                       | NULL    | NULL                       | 2485 |   100.00 | NULL                                         |
    |  2 | DERIVED     | c          | NULL       | ALL  | PRIMARY                                                  | NULL                       | NULL    | NULL                       | 7420 |    10.00 | Using where; Using temporary; Using filesort |
    |  2 | DERIVED     | app        | NULL       | ref  | applications_status_ix,applications_ibfk_3               | applications_ibfk_3        | 4       | prod_v3.c.id               |  750 |     0.41 | Using where                                  |
    |  2 | DERIVED     | inv        | NULL       | ref  | app_id                                                   | app_id                     | 4       | ref.app.app_id             |    1 |   100.00 | NULL                                         |
    +----+-------------+------------+------------+------+----------------------------------------------------------+----------------------------+---------+----------------------------+------+----------+----------------------------------------------+

What we did was to add an index hint to use applications_status_ix and then the query performance went back to normal and the CPU usage went down:

+----+-------------+------------+------------+--------+-------------------------------+-------------------------------+---------+----------------------------+--------+----------+---------------------------------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys                 | key                           | key_len | ref                        | rows   | filtered | Extra                                                               |
+----+-------------+------------+------------+--------+-------------------------------+-------------------------------+---------+----------------------------+--------+----------+---------------------------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                          | NULL                          | NULL    | NULL                       |   5077 |   100.00 | NULL                                                                |
|  2 | DERIVED     | app        | NULL       | range  | applications_status_ix        | applications_status_ix        | 1       | NULL                       | 464405 |    10.00 | Using index condition; Using where; Using temporary; Using filesort |
|  2 | DERIVED     | c          | NULL       | eq_ref | PRIMARY                       | PRIMARY                       | 4       | app.company_id             |      1 |    10.00 | Using where                                                         |
|  2 | DERIVED     | inv        | NULL       | ref    | app_id                        | app_id                        | 4       | app_id                     |      1 |   100.00 | NULL                                                                |
+----+-------------+------------+------------+--------+-------------------------------+-------------------------------+---------+----------------------------+--------+----------+---------------------------------------------------------------------+

Basically on the query we want certain application status that have a minimum appearance on the table. That's why is better using that index even with this cardinality:

+---------------------+------------+---------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name                        | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+---------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| applications        |          1 | applications_status_ix          |            1 | status            | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| applications        |          1 | applications_ibfk_3             |            1 | company_id        | A         |       16240 |     NULL | NULL   |      | BTREE      |         |               |
+---------------------+------------+---------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

So I have two questions:

  1. Why would the query planner suddenly decided use a different execution plan on a query that has been in use for a while? I do not have hard proof, but I really doubt the cardinality changed materially from what we have now.
  2. Trying to find a solution we found a about Histograms (values distribution is called on other DBMS). But unfortunately they are only available on MySQL 8 and we are still using version 5.7. Do you happen to know any other workaround on our MySQL versions?

EDIT1:

So the query being executed is

SELECT sum((CASE
                WHEN `alias_50656806`.`status` = 'CAPTURED' THEN `alias_50656806`.`amount`
                ELSE `alias_50656806`.`due_by` END - `alias_50656806`.`recovered_amount`))
FROM (SELECT `app`.`app_id`,
             `app`.`status`,
             `app`.`amount`,
             `app`.`purchases`,
             `app`.`due_by`,
             sum(CASE
                     WHEN (`inv`.`paid_at` IS NULL AND DATEDIFF(current_date(), `inv`.`created_at`) < 10) THEN 0
                     ELSE IFNULL(`inv`.`amount_due`, 0) END) AS `recovered_amount`
      FROM `applications` AS `app`
               JOIN `companies` AS `c` ON `app`.`company_id` = `c`.`id`
               LEFT OUTER JOIN `application_invoices` AS `inv` USING (`app_id`)
      WHERE (NOT (`app`.`repurchases`) AND
             `app`.`status` IN ('CAPTURED', 'LOCKED', 'ERROR') AND `c`.`is_test` = FALSE AND 1 = 1)
      GROUP BY `app`.`credit_app_id`, `app`.`status`, `app`.`credit_amount`, `app`.`cashless_repurchases`,
               `app`.`due_by`) AS `alias_50656806`;

An the table sizes are:

  • applications has 12701431 records.
  • companies has 7500 records
  • the status distributions is
STATUS          COUNT (*)
RECOVER_FUNDS   46400
ERROR           18792
LOCKED          3
CAPTURED        151854

And applications table DDL is

CREATE TABLE `applications` (
  `app_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `status` enum('PREAPPROVED','INCOMPLETE','DENIED','PENDING_DATA','PENDING_CC','FAILED_DATA','ERROR','READY','CAPTURED','EXPIRED','SETTLED','LOCKED','OPTED_OUT','RECOVER_FUNDS','EXCLUDED','ABANDONED','BAD_OPT') COLLATE utf8_bin NOT NULL,
  `account_code` enum('REF','NO_REF') COLLATE utf8_bin NOT NULL,
  `amount` int(10) unsigned NOT NULL,
  `customer_id` int(11) unsigned NOT NULL,
  `company_id` int(11) unsigned NOT NULL,
  `flow_id` int(11) unsigned NOT NULL,
  `app_ext_id` varchar(20) COLLATE utf8_bin NOT NULL,
  `cashless_repurchases` tinyint(1) NOT NULL DEFAULT '0',
  `random_no` float unsigned NOT NULL DEFAULT '0.5',
  `breakage_amount` int(11) DEFAULT NULL,
  `first_repurch` timestamp(3) NULL DEFAULT NULL,
  `last_repurch` timestamp(3) NULL DEFAULT NULL,
  `repurch_count` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `total_repurch_amount` int(10) unsigned NOT NULL DEFAULT '0',
  `due_by` int(10) unsigned DEFAULT NULL,
  `due_by_customer` int(10) unsigned DEFAULT NULL,
  `last_modified_by` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `app_user_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`app_id`),
  UNIQUE KEY `flow_id` (`flow_id`),
  UNIQUE KEY `app_ext_id` (`app_ext_id`),
  KEY `applications_customer_ix` (`customer_id`),
  KEY `applications_status_ix` (`status`),
  KEY `applications_ibfk_3` (`company_id`),
  CONSTRAINT `applications_ibfk_1` FOREIGN KEY (`flow_id`) REFERENCES `flow_entries` (`id`),
  CONSTRAINT `applications_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `applications_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `applications_ibfk_4` FOREIGN KEY (`status`) REFERENCES `application_statuses` (`status`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=12709869 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
6
  • 2
    Please provide the SELECT statement and SHOW CREATE TABLE for each table. Also, give some hints as to the sizes of the tables. There are many possible answers to your question; those details are needed to help narrow down the possibilities.
    – Rick James
    Feb 11, 2021 at 21:28
  • SHOW INDEX FROM table_name; will update your statistics and display cardinality by column when finished. Please post these results as well for each table involved. Test your query again, the SHOW INDEX FROM table_name; may improve your response time. Feb 12, 2021 at 3:00
  • Rick I have added mos of the information you requested (I have not added companies table DDL because that would make this question even longer). Wilson the index cardinality is obtained using SHOW INDEX FROM. I removed some of the cardinalities trying to simplify. Feb 15, 2021 at 19:22
  • Am I correct in estimating that the WHERE clause filters out rare things, thereby keeping over 98% of the rows?
    – Rick James
    Feb 18, 2021 at 15:47
  • Yes, as you can see on they counts the whole table is 12.701.431 but the records which the required status are 170K . Feb 18, 2021 at 17:55

2 Answers 2

0

The values in filtered column in both execution plans look pretty off target. Maybe it would help to disable condition_fanout_filter optimizer switch in the session executing these multi-join queries (see more about condition filtering).

Why would the query planner suddenly decided use a different execution plan on a query that has been in use for a while? I do not have hard proof, but I really doubt the cardinality changed ...

It probably takes the growing applications table size into account even if the number of records to select is mostly constant. Maybe an optimizer switch to make it prefer larger tables as leading would help to reduce the probability of similar failures in other queries (somewhat like Oracle's DB_FILE_MULTIBLOCK_READ_COUNT). But I don't see any such feature in MySQL.

Do you happen to know any other workaround

Query optimizer is somehow able to estimate the number of selected rows in applications : 464405 . But that's at least twice as more as the actual count from "table sizes" section. Maybe just ANALYZE TABLE applications may help to reduce the estimate and get the better execution plan without hints?

1
  • Thanks ichalov. I'm still need to investigate your first two points but I but just to let you know that the first thing I tried before even posting this question was to run the analyze and it did not work out :-). Thanks for the answer. Feb 23, 2021 at 15:14
0

Why would the query planner suddenly decided use a different execution plan on a query that has been in use for a while?

Change in data sizes or patterns - as things grow you can go over set limit (or a ratio between cardinalities between two objects) that causes a plan to look worse. This is why it can be important to test with realistic data sizes (including expected growth over some years, plus a bit in case things grow faster) during QA and even in dev environments.

If you look at your forced index plan, it expects to need to touch nearly half a million rows instead of the ten-thousand-ish for the other plan. I'm not sure why it would flip now and not sooner, perhaps there was a middle-ground plan it was using before (if you have a backup from before the issue became apparent, you could perhaps restore to another DB/server to check that).

the status distributions is...

That is a lot less in total than the ~12.7Mrows in the table. Are there other values you've filtered from that list, or are the rest of the rows NULL in that column?

Do you happen to know any other workaround on our MySQL versions?

Not specific for that version (this can be true non-mysql DBs too, even): it will only be able to use one index per object, and in this query you are joining on company_id and filtering on status & others. In such circumstances a compound index (i.e. on company_id, status, and perhaps the others you are filtering on to avoid extra lookups so company_id, status, repurchases, is_test) can help a lot at the expense of a little disk space & memory due to the larger index.

In other DBs (MS SQL Server, Prosgres, ...) you can reduce the space hit by removing any index you have on just company_id and the compound index will be used instead (assuming company_id is the first column in the compound key) where that would be needed, but that is not an option with mysql as this is implicitly created by the existence of the FK.

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.