What control over RDS Aurora Serverless V2 buffer cache/cache do we have? It is causing issues when users make GET requests before we have written to the DB, returning emtpy sets for some time.

Hello there,

In Aurora MySQL, 3/4th of the instance memory is used for the innodb_buffer_pool_size which is a native MySQL concept which allows caching data and indexes in memory. The data is stored in the buffer as pages and their retention is managed using the Least Recently Used (LRU) algorithm. You can read more about the MySQL InnoDB buffer pool in [1] and how to optimize it's usage in [2].

Each database page is 16KB in Aurora MySQL [3] and each page will only contain one table's information so updating another table should not invalidate the existing pages in the cache unless it requires the space used by these pages or they have any references that causes these pages to be updated as well.

innodb_buffer_pool_size: Recommended setting: Default (variable value), as it is preconfigured in Aurora to 75 percent of instance memory size. You can see buffer pool use in the output of SHOW ENGINE INNODB STATUS.

Impact: A larger buffer pool improves overall performance by allowing less disk I/O when accessing the same table data repeatedly. The actual amount of allocated memory might be slightly higher than the actual configured value due to InnoDB engine overhead.

I would recommend you to set the required parameters according to the best practices[3].

I also recommend that you reach out to AWS technical support[4]. Provide them with all the information about the concerned aurora cluster to enable a deeper investigation into this issue.

References:

[1] Buffer Pool - https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html [2] InnoDB Buffer Pool Optimization - https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-optimization.html [3] What are IOs in Amazon Aurora and how are they calculated? - https://aws.amazon.com/rds/aurora/faqs/ [4]https://support.console.aws.amazon.com/support/home#/case/create?issueType=customer-service

Hello, good day.

To expire cache or make it smaller for Aurora serverless, you could try couple of things -

• Explicitly expire cached queries using SET statement_timeout = 0, after writing data. do this before queries. Does re-execution of query rather than caching it

• Set the aurora_db_cluster_parameter_group_name to a group with parameter aurora_read_buffer_size set to a small value like 1MB. This reduces the size of the buffer cache.

• Increase keep_alive_timeout to a smaller value like 1 min, to reopen connections more frequently, thus skipping cache.

• Include a random parameter in the SELECT query to explicitly make it non-identical each run, skipping cache e.g. SELECT * FROM table WHERE id = 123 AND rand() > 0.85

• Use the DB instance endpoint directly instead of the reader endpoint to avoid reader caching.

Maybe possible to disable query caching. set in parameters query_cache_type = 0 (OFF) to custom parameter group and apply it to aurora serverless. Hope it helps