RDS

• Amazon RDS (Relational Database Service) is a managed AWS service to simplify relational database setup, operation, and scaling in the cloud.
• Supports database engines like MySQL, PostgreSQL, MariaDB, Oracle, and Microsoft SQL Server.

Key Features of Amazon RDS

• AWS manages administrative tasks like hardware provisioning, patching, backups, and recovery.
• Database capacity is easily scaled vertically (instance size) or horizontally (read replicas).
• Achieves high availability using Multi-AZ deployments with automatic data replication to a standby instance in another Availability Zone.
• Includes automated backups and point-in-time recovery.
• Offers encryption at rest and in transit.
• Integrates with AWS IAM for authentication and authorization.
• Databases can be placed in a Virtual Private Cloud (VPC) for network isolation.
• Retain backups as long as needed by configuring backup retention periods.
• Built-in monitoring tools like Amazon CloudWatch help track database performance and set alarms.
• A pay-as-you-go payment model.
• Read replicas can be created to offload read traffic from the primary database.

Use Cases

• Suited to web and mobile applications, e-commerce platforms, content management systems, and data warehousing.

Security

Network Security

• An RDS instance can be launched in an Amazon VPC, providing network isolation.
• Security Groups act as virtual firewalls, controlling inbound and outbound traffic.
• Placing an instance in a private subnet further restricts access, preventing direct internet accessibility.

Data Encryption

• Encryption at rest uses AWS Key Management Service (KMS) to encrypt data, backups, and snapshots.
• Encryption in transit uses SSL/TLS to protect data transmitted between the database and applications.

Access Control and Authentication

• AWS IAM is used to manage access to RDS resources.
• Some database engines authenticate users via IAM credentials, rather than traditional passwords.
• IAM policies can require MFA for API or console access to AWS resources like EC2 or RDS instances.

Database Activity Monitoring

• Amazon RDS Enhanced Monitoring offers real-time OS metrics for detailed database performance insights.
• AWS CloudTrail records all API calls on AWS resources, including RDS.
• RDS integrates with AWS CloudWatch Logs for monitoring and analyzing database logs.

Automated Backups and Snapshots

• RDS automatically takes daily backups, restorable to any point in time within the backup retention period.
• Manual snapshots of RDS instances can be created and encrypted.
• With encryption enabled on an RDS instance, automated backups and manual snapshots are also encrypted.

Multi-AZ Deployments for High Availability

• RDS provides Multi-AZ deployments, with automatic replication to a standby instance in a different Availability Zone.
• Upon outage or failure, automatic failover to the standby instance occurs.
• Data is synchronously replicated between primary and standby instances, preventing data loss during failover events.

Patching and Maintenance

• Amazon RDS automatically applies security patches to the underlying OS and database software.
• Maintenance windows can be configured for patching and updates.

Audit and Logging

• Receive alerts about RDS instance changes or events by configuring event notifications.
• RDS supports database-level logging to track SQL queries, error messages, and other significant events.

ACID

• ACID compliance in Amazon RDS ensures reliable and predictable database operations through Atomicity, Consistency, Isolation, and Durability.
• Ensures database operations even in the event of system failures.

Atomicity

• Ensures a transaction's operations either all complete successfully, or none at all.
• In the event of any failure, the entire transaction is rolled back.

Consistency

• Guarantees a transaction takes the database from one valid state to another, maintaining data integrity.
• RDS ensures consistency through transaction logs and enforcing data constraints, such as primary and foreign keys.

Isolation

• Defines how transactions are isolated.
• Each executes independently, with intermediate steps invisible to others.
• RDS supports multiple isolation levels (Read Committed, Serializable) configurable based on the engine.

Durability

• Once a transaction is committed, it survives subsequent system failures.
• RDS guarantees durability through transaction logging (write-ahead logs) and automated backups.

Locks

• In Amazon RDS, locks manage concurrent data access to ensure data consistency and integrity.
• These are used to prevent conflicts like dirty reads, non-repeatable reads, and phantom reads.

Lock Types

• Shared locks allow read access but prevent modifications.
• Exclusive locks prevent both read and write access for other transactions.
• Intention locks indicate an intent to acquire a more restrictive lock.
• Auto-increment locks manage auto-increment columns in databases like MySQL.

Deadlocks

• Occur when two or more transactions are waiting for each other to release locks.
• RDS detects deadlocks automatically and rolls back one transaction to resolve the issue.

Lock Granularity

• Locks can be applied at row-level, table-level, or page-level.

Transaction Isolation Levels

• RDS supports multiple isolation levels (e.g., Read Committed, Serializable) that determine how transactions interact regarding locks.
• Higher isolation levels often use more restrictive locks.

Automatic Lock Management

• RDS database engines automatically manage locks.
• Users can monitor lock activity and optimize queries to minimize lock contention.

Best Practices

• Keep transactions short to minimize the time locks are held.
• Use appropriate indexing to reduce the need for table scans that might require heavier locks.
• Implement retry logic in the application to handle deadlocks.

Deadlock Management

• Amazon RDS usually automatically detects and resolves deadlocks by rolling back one of the transactions.

Best Practices for Managing Locks in RDS

• Prevent long-running transactions increase the chance of locking conflicts.
• Keep transactions as short as possible to minimize lock contention.
• Choose the appropriate isolation level based on what the application requires.

Best Practices for Preventing Deadlocks in RDS

• Short transactions reduce the chance of lock contention.
• Ensure that all transactions access the same set of tables or rows in the same order.
• Proper indexing ensures that queries run faster, thus reducing the time locks are held and the chance of deadlocks occurring.
• You can control the degree of isolation between transactions through isolation levels.
• Implement retry logic for deadlock situations.
• Ensure that queries are optimized to minimize the time they hold locks.

Conclusion

• Amazon RDS provides mechanisms to automatically detect and resolve deadlocks.
• Deadlock prevention is often more effective.
• You can minimize the occurrence of deadlocks in your RDS-powered application by following best practices.

RDS Best Practice

• monitor metrics for memory, CPU, replica lag and storage via cloudwatch
• scale databse instances to manage storage capacity efficiently
• enable automatic backups during periods of low write IOPS
• provision sufficient I/O capacity for db workload
• set a time-to-live (TTL) value under 30 seconds
• conduct failover testing regularly

Performance Best Practice

• allocate enough RAM, so that your working set resides almost completely in memory
• check readOPS metric constantly, the value should be small and stable
• Use enhanced monitoring to obtain real time metrics for the OS
• use performance insights for RDS and Aurora simplifies performance monitoring and tuning

Best practices for different engines hosted in RDS

• MySQL: ensure tables don't exceed the 16TiB size limit by partitioning large tables
• Oracle: utilize virtual appliances to run self managed RAC
• PostgreSQL: active autovacuum feature
• SQL: Allocate suffiencient provisioned IOPS to handle your workload during failover