1

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 <= 9900
ORDER BY Loan.date_created DESC, Bicycle.number DESC

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)
3
  • 1
    Please post TEXT results of SHOW CREATE TABLE bicycles; SHOW TABLE STATUS WHERE name LIKE 'bicycles'; SHOW CREATE TABLE loans; SHOW TABLE STATUS WHERE name LIKE 'loans'; for analysis. Jan 27 at 19:25
  • Is this the Eloy Ruiz from Spanish cycle fame between 2009 and 2013? If so, pleased to meet you. Congratulations on your accomplishments. Not an easy path. Jan 28 at 21:02
  • Thanks for your answer, @WilsonHauck! Sorry, this is not me :) I added the information you asked me.
    – Eloy Ruiz
    Jan 30 at 11:32

1 Answer 1

1

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 <= 9900 could be AND Bicycle.number between 0 and 9899 and could reduce time required.

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.

8
  • 1
    Thanks for your suggestions and observations. I was doing the query again to give you comparision numbers before creating the index and I had no speed problems. The query was fast and everything has worked fine which makes think that there must be something else that I can't see in the server side or in the RDS configuration. I'll try to reproduce the slow query, maybe in a few hours users activity loads the server and the query is slow again. Then I'll show before and after of the create index.
    – Eloy Ruiz
    Feb 7 at 7:52
  • 1
    @EloyRuiz Posting TEXT results of EXPLAIN SELECT .... would be most helpful to reveal NDX's being used to produce results. Feb 7 at 14:24
  • 1
    ANALYZE TABLE shows no error in the implied tables. Two days ago the query was slow again and I added the CREATE INDEX you suggested but it didn't change query speed. I also modified the condition by using "between 0 and 9899" but had no speed improvement. Then I optimized the users table, bicycles table and loans table and only after this last one the query was fast again. Time ago loans table optimization solved the problem but then slow queries returned after a 2-3 weeks. I don't know exactly what's happening, it's a strange behaviour.
    – Eloy Ruiz
    Feb 9 at 8:23
  • 1
    @EloyRuiz From your last sentence above, Are you using ANALYZE or OPTIMIZE to clean up the 'loans' table? Feb 11 at 14:04
  • 1
    I used both but it was only after running OPTIMIZE on loans that everything went back to normal (a few seconds) query times.
    – Eloy Ruiz
    Feb 13 at 11:32

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.