RDS slow queries after few weeks



  • Since I updated from MySQL 5.6 to 5.7 I started to have very slow queries in my Aurora RDS instance. In my local XAMPP the same queries took a few seconds but in RDS some of them were about 6 minutes. Trying to offer a fast solution for my customer I migrated the database from Aurora RDS to a normal MariaDB RDS instance (probably overkill but I needed to do something fast). After the migration query times were similar to local environment, acceptable times, but it's been three weeks since the migration and queries are very slow again, about 6 minutes. CPU rises to almost 50% and there is only one connection in DB, the one doing the slow query. I've checked and modified parameters in RDS, specially the optimizer_switch, but they had no visible effect in the query speed. I've also optimized tables with no improvement. I was thinking about a log table or similar which could be growing too much after three weeks and therefore slowing my queries but it's just an idea. At this point I'm a bit lost. Tables in these slow queries are between 63 a 200K+ rows.

    The slowest query is this:

    SELECT *
    FROM bicycles AS Bicycle
    LEFT JOIN loans AS Loan ON Loan.uuid = (
      SELECT 
      loans.uuid
      FROM loans
      WHERE loans.bicycle_uuid = Bicycle.uuid
      ORDER BY loans.date_created DESC
      LIMIT 1
    )
    WHERE Bicycle.status = 'SCRAPPED'
    AND Bicycle.number 961 rows in set (6 min 23.28 sec)

    Here's the explain for the query:

    +----+--------------------+---------+------------+--------+---------------+--------------+---------+------+------+----------+----------------------------------------------+
    | id | select_type        | table   | partitions | type   | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                        |
    +----+--------------------+---------+------------+--------+---------------+--------------+---------+------+------+----------+----------------------------------------------+
    |  1 | PRIMARY            | Bicycle | NULL       | ALL    | number        | NULL         | NULL    | NULL | 2082 |     9.99 | Using where; Using temporary; Using filesort |
    |  1 | PRIMARY            | Loan    | NULL       | eq_ref | PRIMARY       | PRIMARY      | 16      | func |    1 |   100.00 | Using where                                  |
    |  2 | DEPENDENT SUBQUERY | loans   | NULL       | index  | bicycle_uuid  | date_created | 5       | NULL |  428 |    10.00 | Using where                                  |
    +----+--------------------+---------+------------+--------+---------------+--------------+---------+------+------+----------+----------------------------------------------+
    3 rows in set, 2 warnings (0.00 sec)
    

    Indexes for table bicycles:

    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | bicycles |          0 | PRIMARY  |            1 | uuid        | A         |        2082 |     NULL | NULL   |      | BTREE      |         |               |
    | bicycles |          0 | number   |            1 | number      | A         |        2082 |     NULL | NULL   |      | BTREE      |         |               |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    2 rows in set (0.00 sec)
    

    Indexes for table loans:

    +-------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name                  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | loans |          0 | PRIMARY                   |            1 | uuid         | A         |      219864 |     NULL | NULL   |      | BTREE      |         |               |
    | loans |          1 | anchor_uuid               |            1 | anchor_uuid  | A         |        1047 |     NULL | NULL   |      | BTREE      |         |               |
    | loans |          1 | use_uuid                  |            1 | use_uuid     | A         |        4076 |     NULL | NULL   | YES  | BTREE      |         |               |
    | loans |          1 | date_created              |            1 | date_created | A         |      213634 |     NULL | NULL   |      | BTREE      |         |               |
    | loans |          1 | bicycle_uuid              |            1 | bicycle_uuid | A         |         428 |     NULL | NULL   |      | BTREE      |         |               |
    | loans |          1 | date_created_bicycle_uuid |            1 | date_created | A         |      212288 |     NULL | NULL   |      | BTREE      |         |               |
    | loans |          1 | date_created_bicycle_uuid |            2 | bicycle_uuid | A         |      218719 |     NULL | NULL   |      | BTREE      |         |               |
    +-------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    7 rows in set (0.00 sec)
    

    I'm using a db.r3.large instance, 2vCPUs, 15.25 GiB RAM, Not EBS Optimized. MySQL version is 5.7.34 with MariaDB. Storage is an IOPS provisioned SSD (io1), 100GiB assigned storage, 5000 provisiones IOPS.

    As per user request I add the next tables information:

    +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                 |
    +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | bicycles | CREATE TABLE `bicycles` (
      `uuid` binary(16) NOT NULL,
      `number` int(14) NOT NULL,
      `date_created` datetime NOT NULL,
      `status` varchar(20) COLLATE utf8_spanish_ci NOT NULL DEFAULT 'CIRCULATION',
      `is_electric` tinyint(1) NOT NULL DEFAULT 0,
      PRIMARY KEY (`uuid`),
      UNIQUE KEY `number` (`number`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci |
    +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.000 sec)
    

    +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
    | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
    +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
    | bicycles | InnoDB | 10 | Dynamic | 2082 | 78 | 163840 | 0 | 81920 | 0 | NULL | 2022-01-28 12:13:25 | 2022-01-28 12:13:25 | NULL | utf8_spanish_ci | NULL | | | 0 | N |
    +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
    1 row in set (0.001 sec)

    +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table |
    +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | loans | CREATE TABLE loans (
    uuid binary(16) NOT NULL,
    use_uuid binary(16) DEFAULT NULL,
    bicycle_uuid binary(16) NOT NULL,
    anchor_uuid binary(16) NOT NULL,
    date_created datetime NOT NULL,
    type_access enum('UNKNOWN','CARD','APP','SYSTEM','WORKER') COLLATE utf8_spanish_ci NOT NULL,
    PRIMARY KEY (uuid),
    KEY anchor_uuid (anchor_uuid),
    KEY use_uuid (use_uuid),
    KEY date_created (date_created),
    KEY bicycle_uuid (bicycle_uuid),
    KEY date_created_bicycle_uuid (date_created,bicycle_uuid),
    CONSTRAINT fk_loans_anchors FOREIGN KEY (anchor_uuid) REFERENCES anchors (uuid) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT fk_loans_bicycles FOREIGN KEY (bicycle_uuid) REFERENCES bicycles (uuid) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT fk_loans_users FOREIGN KEY (use_uuid) REFERENCES users (uuid) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci |
    +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.000 sec)

    +-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
    | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
    +-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
    | loans | InnoDB | 10 | Dynamic | 216920 | 99 | 21561344 | 0 | 75284480 | 7340032 | NULL | 2022-01-28 12:37:06 | 2022-01-28 12:37:22 | NULL | utf8_spanish_ci | NULL | | | 0 | N |
    +-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
    1 row in set (0.001 sec)

    This the EXPLAIN for the sentence after adding suggested INDEX:

    +----+--------------------+------------+------------+--------+-----------------------------------+----------------------------+---------+-------------------------------+------+----------+-----------------------------------------------------------+
    | id | select_type        | table      | partitions | type   | possible_keys                     | key                        | key_len | ref                           | rows | filtered | Extra                                                     |
    +----+--------------------+------------+------------+--------+-----------------------------------+----------------------------+---------+-------------------------------+------+----------+-----------------------------------------------------------+
    |  1 | PRIMARY            | Bicycle    | NULL       | range  | number,bicycles_ndx_status_number | bicycles_ndx_status_number | 66      | NULL                          |  961 |   100.00 | Using where; Using index; Using temporary; Using filesort |
    |  1 | PRIMARY            | Loan       | NULL       | eq_ref | PRIMARY                           | PRIMARY                    | 16      | func                          |    1 |   100.00 | Using where                                               |
    |  1 | PRIMARY            | LoanAnchor | NULL       | eq_ref | PRIMARY                           | PRIMARY                    | 16      | bicicas.Loan.anchor_uuid      |    1 |   100.00 | NULL                                                      |
    |  1 | PRIMARY            | LoanBench  | NULL       | eq_ref | PRIMARY                           | PRIMARY                    | 16      | bicicas.LoanAnchor.bench_uuid |    1 |   100.00 | NULL                                                      |
    |  1 | PRIMARY            | User       | NULL       | eq_ref | PRIMARY                           | PRIMARY                    | 16      | func                          |    1 |   100.00 | Using where                                               |
    |  3 | DEPENDENT SUBQUERY | users      | NULL       | ref    | uuid                              | uuid                       | 16      | bicicas.Loan.use_uuid         |    2 |   100.00 | Using index condition; Using filesort                     |
    |  2 | DEPENDENT SUBQUERY | loans      | NULL       | ref    | bicycle_uuid                      | bicycle_uuid               | 16      | bicicas.Bicycle.uuid          |  452 |   100.00 | Using index condition; Using filesort                     |
    +----+--------------------+------------+------------+--------+-----------------------------------+----------------------------+---------+-------------------------------+------+----------+-----------------------------------------------------------+
    7 rows in set, 3 warnings (0.00 sec)
    


  • Suggestion/Observations to consider to reduce time required for query completion.

    CREATE INDEX bicycles_ndx_status_number ON bicycles (status, number);  
    

    Test your query to see if same results are provided but faster.

    EXPLAIN SELECT (your query) and observe ROWS column value.  Posted EXPLAIN had ROWS at 2,082.  The index should reduce the ROWS number significantly, saving time.
    

    Observation, AND Bicycle.number

    Observation 2 singular vs plural table name qualifiers. Is confusing to me when both are used but may be best for you.

    Observation 3 first line of defense when any query is slow ANALYZE TABLE table_name;

    EACH table to ensure indexes are CURRENT (they could have somehow become corrupted). This should be first attempt at correcting any SLOW query.

    Would not help you much because you NEED the additional BICYCLES multi-column index for high performance.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2