Best practices with Amazon Aurora MySQL (original) (raw)

This topic includes information on best practices and options for using or migrating data to an Amazon Aurora MySQL DB cluster. The information in this topic summarizes and reiterates some of the guidelines and procedures that you can find inManaging an Amazon Aurora DB cluster.

Contents

• Determining which DB instance you are connected to
• Best practices for Aurora MySQL performance and scaling
  • Using T instance classes for development and testing
  • Optimizing Aurora MySQL indexed join queries with asynchronous key prefetch
    * Enabling asynchronous key prefetch
    * Optimizing queries for asynchronous key prefetch
  • Optimizing large Aurora MySQL join queries with hash joins
    * Enabling hash joins
    * Optimizing queries for hash joins
  • Using Amazon Aurora to scale reads for your MySQL database
  • Optimizing timestamp operations
  • Virtual index ID overflow errors
• Best practices for Aurora MySQL high availability
  • Using Amazon Aurora for Disaster Recovery with your MySQL databases
  • Migrating from MySQL to Amazon Aurora MySQL with reduced downtime
  • Avoiding slow performance, automatic restart, and failover for Aurora MySQL DB instances
• Recommendations for MySQL features in Aurora MySQL
  • Using multithreaded replication in Aurora MySQL
  • Invoking AWS Lambda functions using native MySQL functions
  • Avoiding XA transactions with Amazon Aurora MySQL
  • Keeping foreign keys turned on during DML statements
  • Configuring how frequently the log buffer is flushed
  • Minimizing and troubleshooting Aurora MySQL deadlocks
    * Minimizing InnoDB deadlocks
    * Monitoring InnoDB deadlocks
• Evaluating DB instance usage for Aurora MySQL with Amazon CloudWatch metrics

Determining which DB instance you are connected to

To determine which DB instance in an Aurora MySQL DB cluster a connection is connected to, check the innodb_read_only global variable, as shown in the following example.

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only'; 

The innodb_read_only variable is set to ON if you are connected to a reader DB instance. This setting is OFF if you are connected to a writer DB instance, such as primary instance in a provisioned cluster.

This approach can be helpful if you want to add logic to your application code to balance the workload or to ensure that a write operation is using the correct connection.

Aurora MySQL lab mode

Best practices for performance and scaling

Did this page help you? - Yes

Thanks for letting us know we're doing a good job!

If you've got a moment, please tell us what we did right so we can do more of it.

Did this page help you? - No

Thanks for letting us know this page needs work. We're sorry we let you down.

If you've got a moment, please tell us how we can make the documentation better.