0

I'm trying to figure out why a simple select with a LIMIT 1 clause (admittedly, on a really bloated table with a lot of rows and indices) is sometimes taking 30+ seconds (even 2 minutes, sometimes) to execute on an AWS RDS Aurora instance. This is on a writer instance.

It seems to occur for the first query from a client, only on a particular select that looks through hundreds of thousands of rows, and only sometimes.

The query is in the form:

SELECT some_table.col1, some_table.col2, some_table.col3, some_table.col4, 
  MAX(some_table.col2) AS SomeValue 
FROM some_table 
WHERE some_table.col3=123456 LIMIT 1;

And 'explain' outputs:

+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+
| id | select_type | table         | type | possible_keys | key     | key_len | ref   | rows   | Extra |
+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+
|  1 | SIMPLE      | some_table    | ref  | col1          | col1    | 4       | const | 268202 | NULL  |
+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+

I managed to reproduce the issue and captured the profile for the query in PhpMyAdmin. PhpMyAdmin recorded the query as taking 30.1 seconds to execute, but the profiler shows that execution itself takes less than a second:

PhpMyAdmin profile for select query

So it looks like the execution itself isn't taking a lot of time; what could be causing this latency issue? I also found the same query recorded in RDS Performance Insights:

enter image description here

This seems to occur for the first query in a series of identical or similar queries. Could it be a caching issue? I've tried running RESET QUERY CACHE; in an attempt to reproduce the latency but with no success. Happy to provide more information about the infrastructure if that would help.

More info

SHOW VARIABLES LIKE 'query_cache%';

enter image description here

SHOW GLOBAL STATUS LIKE 'Qc%';

enter image description here

Rows examined and sent (from Performance Insights):

Screenshot of rows examined and sent from AWS Performance Insights

SHOW CREATE TABLE output:

CREATE TABLE `some_table` (
`col1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col2` int(10) unsigned NOT NULL DEFAULT '0',
`col3` int(10) unsigned NOT NULL DEFAULT '0',
`col4` int(10) unsigned NOT NULL DEFAULT '0',
`col5` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`col6` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`col7` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`col1`),
KEY `col2` (`col2`),
KEY `col3` (`col3`),
KEY `col4` (`col4`),
KEY `col6` (`col6`),
KEY `col7` (`col7`)
) ENGINE=InnoDB AUTO_INCREMENT=123456789 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
4
  • 2
    SHOW VARIABLES LIKE 'query_cache%'; and SHOW GLOBAL STATUS LIKE 'Qc%'; If it was captured in the slowlog, please provide the entry there; there are other clues like "Rows examined" and "Rows sent".
    – Rick James
    Dec 9, 2020 at 23:38
  • 1
    Thank you! Added those now. I'll have a look for a slowlog entry. edit Unfortunately, the slow log was off. I'll turn it on and try to reproduce the issue. Dec 9, 2020 at 23:59
  • 386K rows -- Is it doing a COUNT(*) on a large table? Or maybe a LIMIT 1 after some big filtering? How big is the table? How big is your VM? Please provide EXPLAIN SELECT ... for that query.
    – Rick James
    Dec 10, 2020 at 6:02
  • Can you create an index on your Col3 column? Dec 31, 2020 at 6:44

1 Answer 1

1
+100

Consider creating this composite index,

CREATE INDEX some_table_indx_col3_col2 ON some_table(col3,col2);

You should remove the col3 index to avoid index redundancy and conserve storage space.

Considering these existing details, 
Query_cache_size        2,556,593,152 
less Qcache_free_memory 2,555,753,592 
                       ---------------- 
Query_cache bytes in use      839,560 
Qcache_queries_in_cache     /     288 
You have avg bytes per query    2,915 in use 

Consider query_cache_limit of 192K  for ~ 64 * avg bytes per query 
query_cache_min_res_unit          512  the minimum to store more queries in your cache 
query_cache_size   50M   for ~ 64 * Query_cache bytes in use - from ~2.5G size. 

To conserve 98% of CPU cycles used each time qcache_lowmem_prunes are required.

Stalls can be observed if innodb tables are deleted that have involvement with flushing from innodb_buffer_pool change activity before the requested table delete can be completed.

Please let us know of your progress/frustration and consider awarding the bounty. Happy New Year

5
  • 1
    @false_azure What is the new response time? Jan 4, 2021 at 22:31
  • 1
    The issue is still a little tricky to reproduce, but your answer looks really promising. So far, I've yet to see a response time above 30 seconds (and most take only a few milliseconds). Jan 5, 2021 at 1:50
  • 1
    @false_azure Could you post a current - Rows examined and sent (from Performance Insights) ? View my profile, Network profile for free downloadable Utility Scripts to assist with performance tuning, particularly findfragtables.sql to remind you to OPTIMIZE tables with more than 10% Free Space occupied. Jan 5, 2021 at 12:14
  • 1
    Thanks for that script! Unfortunately Performance Insights isn't displaying rows sent/examined for recent executions of that query (I have no idea why). findfragtables.sql output this for the table in question. DATA_SPACE: 44459.00MB; FREE_SPACE: 3.00MB; TABLE_ROWS: 132827118. It is huge! In fact, I didn't realise that it takes up the most space of any table in the database. Jan 5, 2021 at 20:36
  • 1
    That means your system is running well. Your FREE_SPACE is just a minor size. Average row size is 44459MB / 123827118 = ~ 359 bytes. We hope to provide additional analysis services for you soon. Jan 5, 2021 at 22:52

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.