CODEWITHSUNDEEP
databaseamazon-web-servicescachingamazon-rdsamazon-aurora
Christopher Stevens
Christopher Stevens

Reputation: 1278

How do I increase MySQL Query Cache in an Amazon RDS Aurora database?

New to Amazon RDS, I'm looking for ways to deliver cached SELECT queries to enhance performance of a query heavy website (along with other features leading to interest in RDS). So far I've been able to setup an Amazon Aurora database, migrate an old MySQL database to it via MySQLWorkbench and run a test version of the website successfully. The website is connecting to Aurora remotely, running outside of AWS.

I was reading that I could increase the MySQL Query Cache using queries, e.g. (16MB in this case):

SET GLOBAL query_cache_size = 16777216

This requires higher privileges than the database user that can connect with remotely. The master user seems set for localhost access. If this is the correct route, how would I gain access to make edits via queries?

It also looks like I may be able to edit option groups. If that is the allowed route, I'm guessing I will need to set a specific option or add options (didn't see an add option for the default option group).

Upvotes: 2

Views: 7499

Answers (1)

Anthony Neace
Anthony Neace

Reputation: 26031

On an RDS instance, you will generally manage engine configuration like this through RDS parameter groups.

AWS publishes a list of parameters that are available in Aurora MySQL parameter groups, and it appears that query_cache_size is modifiable as an instance-level parameter.

There are some differences between Aurora cluster and instance level parameter groups that you should be aware of. Per the linked documentation above:

Cluster-level parameters are managed in DB cluster parameter groups. Instance-level parameters are managed in DB parameter groups. Although each DB instance in an Aurora MySQL DB cluster is compatible with the MySQL database engine, some of the MySQL database engine parameters must be applied at the cluster level, and are managed using DB cluster parameter groups. Cluster-level parameters are not found in the DB parameter group for an instance in an Aurora DB cluster and are listed later in this topic.

You can manage both cluster-level and instance-level parameters using the AWS Management Console, the AWS CLI, or the Amazon RDS API. There are separate commands for managing cluster-level parameters and instance-level parameters. For example, you can use the modify-db-cluster-parameter-group AWS CLI command to manage cluster-level parameters in a DB cluster parameter group and use the modify-db-parameter-group AWS CLI command to manage instance-level parameters in a DB parameter group for a DB instance in a DB cluster.

Further Reading

Upvotes: 7

Related Questions