-1

I'm running into an issue with my MySQL database and I would like to know if any of you could please shed some light.

Background:

I currently have entire schema configured with InnoDB. This database is currently being used by a real-time application that generate thousands (close to a million) of records per day in one of the tables (i.e. attempt). We heavily query this table all day long to parse the data and store in a separate table in a JSON format (using the JSON type introduced on version 5.7).

Issue:

For some reason it looks like that the MySQL optimizer is not using the correct indexes (on this case the most important index exists on "created_at"). Keep in mind that we were using this same exact query for almost 2 months but all of a sudden it started to perform very poorly. Below you can find the details:

Query:

SELECT
    att.id AS attempt_id,
    app.id AS application_id,
    ca.id AS campaign_id,
    IFNULL(aff.private, 0) AS affiliate_id,
    app.ping_tree_id AS ping_tree_id,
    att.request_timeout AS request_timeout,
    att.response_time AS response_time,
    type.state AS state,
    att.approved AS approved,
    att.price AS price,
    att.created_at AS created_at
FROM
    attempt att
JOIN
    application app ON (app.id = att.application_id)
JOIN
    loan_application type ON (type.id = app.id)
JOIN
    campaign_payout_rule cpr ON (cpr .id = att.campaign_payout_rule_id)
JOIN
    campaign ca ON (ca.id = cpr.campaign_id)
LEFT JOIN
    affiliate aff ON (aff.id = app.affiliate_id)
WHERE
    att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31'
    AND att.parsed_at IS NULL
    AND (
        att.parse_status = '1'
        OR att.parse_status = '11'
    )
    AND app.test = 0
    AND ca.id = '31'
UNION
SELECT
    att.id AS attempt_id,
    app.id AS application_id,
    ca.id AS campaign_id,
    IFNULL(aff.private, 0) AS affiliate_id,
    app.ping_tree_id AS ping_tree_id,
    att.request_timeout AS request_timeout,
    att.response_time AS response_time,
    type.state AS state,
    att.approved AS approved,
    att.price AS price,
    att.created_at AS created_at
FROM
    attempt att
JOIN
    application app ON (app.id = att.application_id)
JOIN
    loan_application_external_lookup type ON (type.id = app.id)
JOIN
    campaign_payout_rule cpr ON (cpr.id = att.campaign_payout_rule_id)
JOIN
    campaign ca ON (ca.id = cpr.campaign_id)
LEFT JOIN
    affiliate aff ON (aff.id = app.affiliate_id)
WHERE
    att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31'
    AND att.parsed_at IS NULL
    AND (
        att.parse_status = '1'
        OR att.parse_status = '11'
    )
    AND app.test = 0
    AND ca.id = '31'
ORDER BY
    approved=1 DESC, created_at ASC

Indexes:

MySQL [db]> show index from attempt;
+---------+------------+----------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name             | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| attempt |          0 | PRIMARY              |            1 | id                      | A         |    12528955 |     NULL | NULL   |      | BTREE      |         |               |
| attempt |          1 | IDX_18EC02663E030ACD |            1 | application_id          | A         |     1426571 |     NULL | NULL   |      | BTREE      |         |               |
| attempt |          1 | IDX_18EC02669E82DDEA |            1 | integration_id          | A         |        8495 |     NULL | NULL   |      | BTREE      |         |               |
| attempt |          1 | IDX_18EC0266FA64550D |            1 | campaign_payout_rule_id | A         |       10319 |     NULL | NULL   |      | BTREE      |         |               |
| attempt |          1 | IDX_18EC0266E4640ADF |            1 | campaign_credential_id  | A         |       11885 |     NULL | NULL   |      | BTREE      |         |               |
| attempt |          1 | IDX_18EC0266A354F9DC |            1 | tier_id                 | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| attempt |          1 | IDX_18EC02661500F99B |            1 | request_method          | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| attempt |          1 | IDX_18EC02667C57D81D |            1 | approved                | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| attempt |          1 | IDX_18EC02668B8E8428 |            1 | created_at              | A         |     3030286 |     NULL | NULL   |      | BTREE      |         |               |
| attempt |          1 | IDX_18EC02665552D714 |            1 | parsed_at               | A         |     1284185 |     NULL | NULL   | YES  | BTREE      |         |               |
| attempt |          1 | IDX_18EC0266720357E8 |            1 | parse_status            | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Explain:

MySQL [db]> explain extended SELECT att.id AS attempt_id, app.id AS application_id, ca.id AS campaign_id, IFNULL(aff.private, 0) AS affiliate_id, app.ping_tree_id AS ping_tree_id, att.request_timeout AS request_timeout, att.response_time AS response_time, type.state AS state, att.approved AS approved, att.price AS price, att.created_at AS created_at FROM attempt att JOIN application app ON (app.id = att.application_id) JOIN loan_application type ON (type.id = app.id) JOIN campaign_payout_rule cpr ON (cpr .id = att.campaign_payout_rule_id) JOIN campaign ca ON (ca.id = cpr.campaign_id) LEFT JOIN affiliate aff ON (aff.id = app.affiliate_id) WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31' UNION SELECT att.id AS attempt_id, app.id AS application_id, ca.id AS campaign_id, IFNULL(aff.private, 0) AS affiliate_id, app.ping_tree_id AS ping_tree_id, att.request_timeout AS request_timeout, att.response_time AS response_time, type.state AS state, att.approved AS approved, att.price AS price, att.created_at AS created_at FROM attempt att JOIN application app ON (app.id = att.application_id) JOIN loan_application_external_lookup type ON (type.id = app.id) JOIN campaign_payout_rule cpr ON (cpr.id = att.campaign_payout_rule_id) JOIN campaign ca ON (ca.id = cpr.campaign_id) LEFT JOIN affiliate aff ON (aff.id = app.affiliate_id) WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31' ORDER BY approved=1 DESC, created_at ASC;
+----+--------------+------------+------------+--------+----------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------+------+----------+---------------------------------+
| id | select_type  | table      | partitions | type   | possible_keys                                                                                            | key                  | key_len | ref                     | rows | filtered | Extra                           |
+----+--------------+------------+------------+--------+----------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------+------+----------+---------------------------------+
|  1 | PRIMARY      | ca         | NULL       | const  | PRIMARY                                                                                                  | PRIMARY              | 4       | const                   |    1 |   100.00 | Using index                     |
|  1 | PRIMARY      | cpr        | NULL       | ref    | PRIMARY,IDX_D5BDECA3F639F774                                                                             | IDX_D5BDECA3F639F774 | 4       | const                   |    1 |   100.00 | Using where; Using index        |
|  1 | PRIMARY      | att        | NULL       | ref    | IDX_18EC02663E030ACD,IDX_18EC0266FA64550D,IDX_18EC02668B8E8428,IDX_18EC02665552D714,IDX_18EC0266720357E8 | IDX_18EC0266FA64550D | 4       | dois.cpr.id             | 1226 |     0.00 | Using where                     |
|  1 | PRIMARY      | type       | NULL       | eq_ref | PRIMARY                                                                                                  | PRIMARY              | 4       | dois.att.application_id |    1 |   100.00 | NULL                            |
|  1 | PRIMARY      | app        | NULL       | eq_ref | PRIMARY,IDX_A45BDDC1D87F7E0C                                                                             | PRIMARY              | 4       | dois.att.application_id |    1 |    50.00 | Using where                     |
|  1 | PRIMARY      | aff        | NULL       | eq_ref | PRIMARY                                                                                                  | PRIMARY              | 4       | dois.app.affiliate_id   |    1 |   100.00 | NULL                            |
|  2 | UNION        | ca         | NULL       | const  | PRIMARY                                                                                                  | PRIMARY              | 4       | const                   |    1 |   100.00 | Using index                     |
|  2 | UNION        | cpr        | NULL       | ref    | PRIMARY,IDX_D5BDECA3F639F774                                                                             | IDX_D5BDECA3F639F774 | 4       | const                   |    1 |   100.00 | Using where; Using index        |
|  2 | UNION        | att        | NULL       | ref    | IDX_18EC02663E030ACD,IDX_18EC0266FA64550D,IDX_18EC02668B8E8428,IDX_18EC02665552D714,IDX_18EC0266720357E8 | IDX_18EC0266FA64550D | 4       | dois.cpr.id             | 1226 |     0.00 | Using where                     |
|  2 | UNION        | type       | NULL       | eq_ref | PRIMARY                                                                                                  | PRIMARY              | 4       | dois.att.application_id |    1 |   100.00 | NULL                            |
|  2 | UNION        | app        | NULL       | eq_ref | PRIMARY,IDX_A45BDDC1D87F7E0C                                                                             | PRIMARY              | 4       | dois.att.application_id |    1 |    50.00 | Using where                     |
|  2 | UNION        | aff        | NULL       | eq_ref | PRIMARY                                                                                                  | PRIMARY              | 4       | dois.app.affiliate_id   |    1 |   100.00 | NULL                            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL    | NULL                                                                                                     | NULL                 | NULL    | NULL                    | NULL |     NULL | Using temporary; Using filesort |
+----+--------------+------------+------------+--------+----------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------+------+----------+---------------------------------+

Once I change the query slightly with the index hint, you can see that the "range" type is added and the query works as expected:

MySQL [db]> explain extended SELECT att.id AS attempt_id, app.id AS application_id, ca.id AS campaign_id, IFNULL(aff.private, 0) AS affiliate_id, app.ping_tree_id AS ping_tree_id, att.request_timeout AS request_timeout, att.response_time AS response_time, type.state AS state, att.approved AS approved, att.price AS price, att.created_at AS created_at FROM attempt att USE INDEX (IDX_18EC02668B8E8428) JOIN application app ON (app.id = att.application_id) JOIN loan_application type ON (type.id = app.id) JOIN campaign_payout_rule cpr ON (cpr .id = att.campaign_payout_rule_id) JOIN campaign ca ON (ca.id = cpr.campaign_id) LEFT JOIN affiliate aff ON (aff.id = app.affiliate_id) WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31' UNION SELECT att.id AS attempt_id, app.id AS application_id, ca.id AS campaign_id, IFNULL(aff.private, 0) AS affiliate_id, app.ping_tree_id AS ping_tree_id, att.request_timeout AS request_timeout, att.response_time AS response_time, type.state AS state, att.approved AS approved, att.price AS price, att.created_at AS created_at FROM attempt att USE INDEX (IDX_18EC02668B8E8428) JOIN application app ON (app.id = att.application_id) JOIN loan_application_external_lookup type ON (type.id = app.id) JOIN campaign_payout_rule cpr ON (cpr.id = att.campaign_payout_rule_id) JOIN campaign ca ON (ca.id = cpr.campaign_id) LEFT JOIN affiliate aff ON (aff.id = app.affiliate_id) WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31' ORDER BY approved=1 DESC, created_at ASC;
+----+--------------+------------+------------+--------+------------------------------+----------------------+---------+-------------------------+-------+----------+---------------------------------------------------------------------------+
| id | select_type  | table      | partitions | type   | possible_keys                | key                  | key_len | ref                     | rows  | filtered | Extra                                                                     |
+----+--------------+------------+------------+--------+------------------------------+----------------------+---------+-------------------------+-------+----------+---------------------------------------------------------------------------+
|  1 | PRIMARY      | ca         | NULL       | const  | PRIMARY                      | PRIMARY              | 4       | const                   |     1 |   100.00 | Using index                                                               |
|  1 | PRIMARY      | cpr        | NULL       | ref    | PRIMARY,IDX_D5BDECA3F639F774 | IDX_D5BDECA3F639F774 | 4       | const                   |     1 |   100.00 | Using where; Using index                                                  |
|  1 | PRIMARY      | att        | NULL       | range  | IDX_18EC02668B8E8428         | IDX_18EC02668B8E8428 | 4       | NULL                    | 78308 |     0.00 | Using index condition; Using where; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY      | type       | NULL       | eq_ref | PRIMARY                      | PRIMARY              | 4       | dois.att.application_id |     1 |   100.00 | NULL                                                                      |
|  1 | PRIMARY      | app        | NULL       | eq_ref | PRIMARY,IDX_A45BDDC1D87F7E0C | PRIMARY              | 4       | dois.att.application_id |     1 |    50.00 | Using where                                                               |
|  1 | PRIMARY      | aff        | NULL       | eq_ref | PRIMARY                      | PRIMARY              | 4       | dois.app.affiliate_id   |     1 |   100.00 | NULL                                                                      |
|  2 | UNION        | ca         | NULL       | const  | PRIMARY                      | PRIMARY              | 4       | const                   |     1 |   100.00 | Using index                                                               |
|  2 | UNION        | cpr        | NULL       | ref    | PRIMARY,IDX_D5BDECA3F639F774 | IDX_D5BDECA3F639F774 | 4       | const                   |     1 |   100.00 | Using where; Using index                                                  |
|  2 | UNION        | att        | NULL       | range  | IDX_18EC02668B8E8428         | IDX_18EC02668B8E8428 | 4       | NULL                    | 78308 |     0.00 | Using index condition; Using where; Using join buffer (Block Nested Loop) |
|  2 | UNION        | type       | NULL       | eq_ref | PRIMARY                      | PRIMARY              | 4       | dois.att.application_id |     1 |   100.00 | NULL                                                                      |
|  2 | UNION        | app        | NULL       | eq_ref | PRIMARY,IDX_A45BDDC1D87F7E0C | PRIMARY              | 4       | dois.att.application_id |     1 |    50.00 | Using where                                                               |
|  2 | UNION        | aff        | NULL       | eq_ref | PRIMARY                      | PRIMARY              | 4       | dois.app.affiliate_id   |     1 |   100.00 | NULL                                                                      |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL    | NULL                         | NULL                 | NULL    | NULL                    |  NULL |     NULL | Using temporary; Using filesort                                           |
+----+--------------+------------+------------+--------+------------------------------+----------------------+---------+-------------------------+-------+----------+---------------------------------------------------------------------------+

A few notes:

  1. We're currently using AWS RDS
  2. As of right now, the attempt table has 15097025 records
  3. As of right now, the attempt table is using 50.8GB of storage
  4. The server currently has 550GB available of storage.
  5. I defragmented all the tables last night according to this article: https://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html (I assume is the same thing as OPTIMIZE?)

Questions:

  1. Is using Index hint a bad practice? We're currently using an ORM to build some of these queries which makes a little harder to add it if you decide to use their query builder. Although we can extend or change the queries to a raw format.
  2. Would you recommend me doing this query in any other way? Do you believe the number of joins could affect the performance?

Any thoughts? Thank you in advance.

Renato.

Improve this question

2 Answers 2

Reset to default
0

Using an index hint is the proper (and oftentimes only) solution if MySQL chooses the wrong index. While that can happen due to outdated statistics or an undersized sample rate, it's hard for MySQL to find a proper ranged index when an equality is available.

You can try to increase the innodb_stats_persistent_sample_pages (to e.g. 200), then run analyze table attempt and check if it improves the execution plan. It uses a larger portion of your table to create the statistics, so the estimates might get more precise for larger tables. This could explain why the effect happened only recently, although it probably won't change the plan. Since it takes a little more time to calculate the statistics, you should reset it to its original value if it does not work.

Depending on how common null is for your column parsed_at, I would recommend the composite index attempt(parsed_at, created_at) though. It could perform better than your current single column index, and will probably be used even without forcing it. So this will indirectly solve your problem too, so it's useful even if it doesn't actually speed up your query - which will be the case if (nearly) every row in the created_at range will also have parsed_at = null.

Another optimization:

It looks like you are using the union only because of the join to 2 different loan_application_*-tables. You can save up to 50% of execution time if you don't do that. A better way would be to normalize this table, e.g. have the content of both in one table, maybe id, subtype, other columns, and set subtype=1 for rows from loan_application and subtype=2 for rows from loan_application_external_lookup. Or keep both of your tables, but in addition have a parent table with id, subtype and all columns these two tables have in common. The two other table will only contain the id and the columns specific to the subtype. This will make a join to the "general" loan_application-entity much more reliable and easier. If you need columns that are specific to a subtype, you can join with that. This is called an is-a relationship (a "loan application" is a subtype 1 or 2).

It might require some rework of you orm/data model, so maybe try a view (or insert the rows into a temporary table) for that and join with this to see the effect it might have.

(This was concluded from how you used these table in the query and their names. If they actually describe completely different things that have nothing in common, that would of course not be a good idea, although they would have very misleading names.)

There might be some other optimizations possible depending on specifics about your data (e.g. if everything is 1:1), but the biggest concern seems to be the attempt-table, and you only provided details for this anyway.

Improve this answer
0

**

WHERE
    att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31'
    AND att.parsed_at IS NULL
    AND (
        att.parse_status = '1'
        OR att.parse_status = '11'
    )
    AND app.test = 0
    AND ca.id = '31'

Rewrite as

WHERE   att.created_at >= '2017-08-24 07:31:31' 
    AND att.created_at  < '2017-08-24 07:31:31' INTERVAL + 4 HOUR
    AND att.parsed_at IS NULL
    AND att.parse_status IN (1, 11)
    AND app.test = 0
    AND ca.id = '31'

(This simplifies the OR, and avoids adding an extra second to the interval.)

** And add 

INDEX(parse_at, parse_status, created_at)

This is not the same as 3 single-column indexes.

** Add parentheses:

( SELECT ... )
UNION
( SELECT ... )
ORDER BY ...

(This would tie the ORDER BY to the UNION instead of the 2nd SELECT -- I assume you wanted that??)

** If valid, use UNION ALL instead of UNION DISTINCT. (This would avoid a de-dup pass.)

** OPTIMIZE TABLE is almost always a waste of time.

** Let's see SHOW CREATE TABLE; we may be able to trim the 50GB somewhat. This will help somewhat.

** Index hints are a last resort. What helps today may hurt tomorrow (after the distribution of the data changes).

** Is there any difference between the SELECTs other than the loan_application* table?

** You can probably get rid of one JOIN -- namely to campaign ca. All references to ca.id can be replaced by cpr.campaign_id.

Improve this answer

Your Answer

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

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