Relevant System Info: Aurora Mysql 8.0.mysql_aurora.3.03 rg6.xl instances (1 writer, 2 read replicas) Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot)
I have been working on migrating mariadb databases deprecated tokudb engine to RDS Aurora Mysql. With some fine tuning of the parameters in RDS, there has been one behavior I cannot understand.
The main database houses tables created by each year, so lets use TABLE_2023 as an example. In this table, there are two indexes; the primary and the secondary index. PRIMARY index(ID, DATE) and Secondary(DATE). If I take a query like the following, and thrown an
EXPLAIN, the output shows NULL for the key column. I found this odd since if I run the same query, but against the existing mariadb server, it will output with one of the indexes in that column. If I force the query to use an index (SELECT * FROM TABLE_2023 FORCE INDEX(PRIMARY) WHERE ID = '3' AND TIME >=202301010000 AND <=202304230000;), the forced index appears in this column. No surprise there.
When I run this query, and many like it, RDS takes 5-10times longer to complete. If I force the query, its extremely fast (though, still not as fast as the mariadb server on our current EC2 instances).
So here comes the question; am I wrong to think that Aurora isn't picking an index to use when presented with similar queries? When I use EXPLAIN for the same query on our EC2 instances, I can see that one of the indexes will be used in the results. But with Aurora, it is providing no such thing (just NULL).
There are obviously other caveats here; the table for this year alone is over a billion rows. There are also things that need to be tweaked with some of the queries that the data engineers are throwing at this thing, but I am stumped on how to understand the RDS Aurora behavior.
I cannot find anywhere in AWS documentation that explains this result. I know if I drop the time range in the same query above, and then run EXPLAIN in front of it..the results SHOW that Aurora Mysql will use the PRIMARY index. So, I know it can work as expected.
In case people ask:
- The data was migrated from EC2 instance using DMS tool. I noticed after the migration that the secondary index was missing. So, instead, I dropped all the tables, created them again WITH the secondary index as well, and then loaded the tables using DMS. My thought is maybe the indexes need to be reindexed, but this would require me to drop the index and re-add...which takes almost 3 days for 2022's data.
- OPTIMIZE TABLE does not make the situation any better because its just creating a new table, taking the data from old table and putting it into the new table)
- I tried ALTER TABLE DISABLE KEYS and then ENABLING them again, hoping I would get better results...but still slower than EC2 instance running mariadb and same results when using EXPLAIN.
SELECT * FROM TABLE_2023 WHERE TIME >= 202304170000 AND TIME <= 202304172359 AND ID IN ( 1206 ,1332 ,1919 ,2878 ,8694 ,15452 ,18323 ,18501 ,19908 ,20142 ,20881 ,33090 ,34131 ,36806 ,37402 ,37672 ,37673 ,37853 ,38840 ,38978 ,39078 ,39128 ,42530 ,51730 ,51964 ,70686 ,71504 ,74520 ,74521 ,74522 ,74523 ,74524 ,74525 ,74526 ,74527 ,74528 ,74529 ,74530 ,74531 ,75237 )
This produces 1822 rows and took 4min and 3 seconds without forcing index. When forcing the PRIMARY index, these requests take 2-3 seconds (for this size query). And yes, this is how long it takes when the query is not cached.
EXPLAIN ANALYZE results for the same query (again not forcing index):
-> Filter: ((TABLE_2023.TIME >= 202304170000) and(TABLE_2023.TIME <= 202304172359) and (TABLE_2023.ID in () [Using parallel query (2 filters, 1 exprs; 0 extra)] (cost=404.85 rows=1823) (actual time=18271.466..244722.077 rows=1822 loops=1) -> Table scan on TABLE_2023 [Using parallel query (6 columns)] (cost=404.85 rows=2296760704) (actual time=18271.459..244721.615 rows=1822 loops=1) [parallel query actual (total time= 244722.063, rows returned= 1822)]
select count(*) on this year's table brings back 1809962901 rows.
CREATE TABLE Results
CREATE TABLE `TABLE_2023` ( `SOMETHING_ID` mediumint NOT NULL, `TIME` bigint unsigned NOT NULL, `COLUMN3` tinyint DEFAULT NULL, `COLUMN4` smallint DEFAULT NULL, `COLUMN5` smallint DEFAULT NULL, `JSON` varchar(2048) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, PRIMARY KEY (`SOMETHING_ID`,`TIME`), KEY `TIME` (`TIME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Users that use the api can request a range of data. However, the query behavior is almost always asking the database for time series data for either an single ID or multiple IDs for the same time series. Currently, the code behind the API uses the "TIME>='' and TIME<=''" for a time range, not BETWEEN. Nor does it use "ID='1'" for a single station, but rather "in". I have recommended the developers to change the code to use "=" if one station is used, to avoid the table scan.
Final Thoughts Can we get away with forcing the index we want the query to use? Of course. But the behavior we are seeing with Aurora Mysql not being able to pick the index without being forced to bothers me. Especially when I do the same queries against our current databases environment (which will use an index, regardless of being forced). The only time I am able to get an index used is by using simple queries;
EXPLAIN select * from TABLE_2023 where SOMETHING_ID in (1206); --index PRIMARY "used" EXPLAIN select * from TABLE_2023 where SOMETHING_ID = '1206'; --index PRIMARY "used" EXPLAIN SELECT * FROM TABLE_2023 where TIME = '202304170000'; --index TIME "used"
Update as of 05/01/2023
I dropped the year table (2023) and reimported all the data from our source database using DMS (again). After this was done, I added the secondary index (TIME) since DMS will usually only grab the primary index. After this was done, I took the same example query and still my EXPLAIN results show no usage of an index.
I came across this document when reviewing the methods I was loading data into tables. Turns out AWS recommends dropping Primary Index if doing a full table load. Going to give this a try and load the data, then re-add just the PRIMARY index.
Update as of 05/02/2023
After creating a new table w/o the PRIMARY index and secondary index, I loaded the data into the table via DMS. Once completed, I waited 6+ hours for the PRIMARY key index to create. Unfortunately, the behavior still exists. However, I do have a scheduled call with AWS reps that I hope to get an answer for this behavior. Once an answer is vetted, I will post here.
Update as of 05/05/2023
After meeting with AWS account managers, they will be relaying technical information to their engineers to take a look at. In the meantime, I conducted a few more tests:
Scenario 1: Create table with primary key index only Load 38,000 rows, run 'EXPLAIN' with the example query-primary index would have been used.
Scenario 2: Create table with primary key index only Load 23 million rows, run 'EXPLAIN' with the example query-primary index identified, but not used
Scenario 3: Drop primary index in table from Scenario 2 Re-add primary index, run 'EXPLAIN' with the example query-primary index identified, but not used
Scenario 4: Create table without primary key index Load 23 million rows, add primary key index, run 'EXPLAIN' with the example query-primary index identified, but not used
Scenario 5: Create table without primary key index Load 38,000 rows, add primary key, run 'EXPLAIN' with example query-primary index identified and used
Scenario 6: Create table without primary key index Load around 12 million rows, add primary key, run 'EXPLAIN' with example query-primary index identified but not used.
Update as of 05/08/2023 Tried suggested answer, same behavior. Ended up doing the following but AWS Aurora MySQL behaved the same
ALTER TABLE TABLE_2023 ADD PRIMARY KEY (`SOMETHING_ID`,`TIME`), ADD UNIQUE KEY `TIME_SOMETHING_ID` (`TIME`,`SOMETHING_ID`);
Update as of 05/10/2023 As suggested Rolando, I dropped the TIME index and added the following
ALTER TABLE TABLE_2023 ADD UNIQUE INDEX `TIME_SOMETHING_ID` (`TIME`,`SOMETHING_ID`);
I then ran the example query in the beginning of this post. Here are the results:
1822 rows in set (52.513 sec)
So, again, same behavior. Before I reverse the Primary Key order (as I did before), waiting to see response from Rolando for any further suggestions.
Update as of 05/16/2023 I have another meeting planned with AWS,but this time with an Aurora Mysql Specialist. Before I have the meeting, they suggested to turn off aurora_parallel_query.
Upon doing this, I ran the same example query and was shocked to see the results. The Explain results showed
And the query itself, when ran, completed extremely quickly.
However, before I write this off as the answer, I am curious to why this is the solution. Amazon markets Aurora Parallel Query as a benefit for moving to Aurora, so my use case must not benefit from this. I will post the details of the meeting here when I have them.
Update as of 05/25/2023 Same behavior with 3.03.1. Sending AWS a snapshot of our TABLE_2023 with a bug report.
Update as of 06/12/2023
AWS internally identified the "bug" and the fix is set to be released in the public versions of 3.04.0 and 3.03.2. These are projected to come out at the end of this quarter, or the beginning of next quarter.