Complete Guide to Amazon RDS: Managed Relational Databases#

1
# Basic RDS MySQL instance
2
MySQLDatabase:
3
  Type: AWS::RDS::DBInstance
4
  Properties:
5
    DBInstanceIdentifier: my-mysql-db
6
    DBInstanceClass: db.t3.micro
7
    Engine: mysql
8
    EngineVersion: '8.0.35'
9
    MasterUsername: admin
10
    MasterUserPassword: !Ref DatabasePassword
11
    AllocatedStorage: 20
12
    StorageType: gp2
13
    StorageEncrypted: true
14
    VPCSecurityGroups:
15
      - !Ref DatabaseSecurityGroup
16
    DBSubnetGroupName: !Ref DBSubnetGroup
17
    BackupRetentionPeriod: 7
18
    MultiAZ: false
19
    PubliclyAccessible: false
20
    DeletionProtection: true
21
    Tags:
22
      - Key: Name
23
        Value: MyApplication-MySQL

1
# DB Subnet Group for Multi-AZ deployment
2
DBSubnetGroup:
3
  Type: AWS::RDS::DBSubnetGroup
4
  Properties:
5
    DBSubnetGroupDescription: Subnet group for RDS database
6
    DBSubnetGroupName: my-db-subnet-group
7
    SubnetIds:
8
      - !Ref PrivateSubnet1
9
      - !Ref PrivateSubnet2
10
      - !Ref PrivateSubnet3
11
    Tags:
12
      - Key: Name
13
        Value: Database Subnet Group

1
# Custom parameter group for performance tuning
2
MySQLParameterGroup:
3
  Type: AWS::RDS::DBParameterGroup
4
  Properties:
5
    Family: mysql8.0
6
    Description: Custom MySQL 8.0 parameters
7
    Parameters:
8
      innodb_buffer_pool_size: '{DBInstanceClassMemory*3/4}'
9
      max_connections: 1000
10
      slow_query_log: 1
11
      long_query_time: 2
12
      innodb_file_per_table: 1
13
    Tags:
14
      - Key: Name
15
        Value: MySQL-Custom-Parameters

1
MySQLDatabase:
2
  Type: AWS::RDS::DBInstance
3
  Properties:
4
    Engine: mysql
5
    EngineVersion: '8.0.35'
6
    DBInstanceClass: db.r5.large
7
    AllocatedStorage: 100
8
    StorageType: gp2
9
    StorageEncrypted: true
10
    KmsKeyId: !Ref DatabaseKMSKey
11
    MasterUsername: admin
12
    MasterUserPassword: !Ref DatabasePassword
13
    DBParameterGroupName: !Ref MySQLParameterGroup
14
    BackupRetentionPeriod: 7
15
    PreferredBackupWindow: "03:00-04:00"
16
    PreferredMaintenanceWindow: "sun:04:00-sun:05:00"
17
    MultiAZ: true
18
    PubliclyAccessible: false
19
    VPCSecurityGroups:
20
      - !Ref DatabaseSecurityGroup
21
    DBSubnetGroupName: !Ref DBSubnetGroup

1
PostgreSQLDatabase:
2
  Type: AWS::RDS::DBInstance
3
  Properties:
4
    Engine: postgres
5
    EngineVersion: '15.4'
6
    DBInstanceClass: db.r5.xlarge
7
    AllocatedStorage: 200
8
    StorageType: gp3
9
    Iops: 3000
10
    StorageEncrypted: true
11
    MasterUsername: postgres
12
    MasterUserPassword: !Ref DatabasePassword
13
    DBName: myappdb
14
    DBParameterGroupName: !Ref PostgreSQLParameterGroup
15
    BackupRetentionPeriod: 30
16
    CopyTagsToSnapshot: true
17
    DeletionProtection: true
18
    EnablePerformanceInsights: true
19
    PerformanceInsightsRetentionPeriod: 7

1
AuroraServerlessCluster:
2
  Type: AWS::RDS::DBCluster
3
  Properties:
4
    Engine: aurora-mysql
5
    EngineVersion: '8.0.mysql_aurora.3.02.0'
6
    EngineMode: serverless
7
    DatabaseName: myapp
8
    MasterUsername: admin
9
    MasterUserPassword: !Ref DatabasePassword
10
    ScalingConfiguration:
11
      MinCapacity: 1
12
      MaxCapacity: 256
13
      AutoPause: true
14
      SecondsUntilAutoPause: 300
15
    BackupRetentionPeriod: 7
16
    StorageEncrypted: true
17
    VpcSecurityGroupIds:
18
      - !Ref DatabaseSecurityGroup
19
    DBSubnetGroupName: !Ref DBSubnetGroup

1
# Primary database with Multi-AZ
2
PrimaryDatabase:
3
  Type: AWS::RDS::DBInstance
4
  Properties:
5
    DBInstanceIdentifier: primary-db
6
    Engine: postgres
7
    DBInstanceClass: db.r5.2xlarge
8
    MultiAZ: true  # Enables synchronous standby
9
    AllocatedStorage: 500
10
    StorageType: gp3
11
    BackupRetentionPeriod: 7
12
    DeletionProtection: true
13

14
# Monitor failover with CloudWatch
15
FailoverAlarm:
16
  Type: AWS::CloudWatch::Alarm
17
  Properties:
18
    AlarmDescription: Database failover detected
19
    MetricName: DatabaseConnections
20
    Namespace: AWS/RDS
21
    Statistic: Average
22
    Period: 300
23
    EvaluationPeriods: 2
24
    Threshold: 0
25
    ComparisonOperator: LessThanThreshold
26
    Dimensions:
27
      - Name: DBInstanceIdentifier
28
        Value: !Ref PrimaryDatabase

1
# Read replica for scaling reads
2
ReadReplica1:
3
  Type: AWS::RDS::DBInstance
4
  Properties:
5
    DBInstanceIdentifier: read-replica-1
6
    SourceDBInstanceIdentifier: !Ref PrimaryDatabase
7
    DBInstanceClass: db.r5.large
8
    PubliclyAccessible: false
9
    VPCSecurityGroups:
10
      - !Ref ReadReplicaSecurityGroup
11
    Tags:
12
      - Key: Name
13
        Value: Read Replica 1
14

15
# Cross-region read replica
16
CrossRegionReadReplica:
17
  Type: AWS::RDS::DBInstance
18
  Properties:
19
    DBInstanceIdentifier: cross-region-replica
20
    SourceDBInstanceIdentifier: !Sub
21
      - arn:aws:rds:${SourceRegion}:${AWS::AccountId}:db:${SourceDBInstanceIdentifier}
22
      - SourceRegion: us-east-1
23
        SourceDBInstanceIdentifier: !Ref PrimaryDatabase
24
    DBInstanceClass: db.r5.large

1
# Aurora Global Database for disaster recovery
2
AuroraGlobalCluster:
3
  Type: AWS::RDS::GlobalCluster
4
  Properties:
5
    GlobalClusterIdentifier: my-global-cluster
6
    SourceDBClusterIdentifier: !Ref PrimaryCluster
7

8
PrimaryCluster:
9
  Type: AWS::RDS::DBCluster
10
  Properties:
11
    Engine: aurora-postgresql
12
    EngineVersion: '13.7'
13
    DatabaseName: myapp
14
    MasterUsername: postgres
15
    MasterUserPassword: !Ref DatabasePassword
16
    GlobalClusterIdentifier: !Ref AuroraGlobalCluster
17

18
SecondaryCluster:
19
  Type: AWS::RDS::DBCluster
20
  Properties:
21
    Engine: aurora-postgresql
22
    EngineVersion: '13.7'
23
    GlobalClusterIdentifier: !Ref AuroraGlobalCluster
24
    SourceRegion: us-east-1

1
# Encrypted RDS instance with custom KMS key
2
DatabaseKMSKey:
3
  Type: AWS::KMS::Key
4
  Properties:
5
    Description: KMS key for RDS encryption
6
    KeyPolicy:
7
      Statement:
8
        - Effect: Allow
9
          Principal:
10
            AWS: !Sub "arn:aws:iam::${AWS::AccountId}:root"
11
          Action: "kms:*"
12
          Resource: "*"
13

14
EncryptedDatabase:
15
  Type: AWS::RDS::DBInstance
16
  Properties:
17
    StorageEncrypted: true
18
    KmsKeyId: !Ref DatabaseKMSKey
19
    # ... other properties

1
# Database security group with restricted access
2
DatabaseSecurityGroup:
3
  Type: AWS::EC2::SecurityGroup
4
  Properties:
5
    GroupDescription: Security group for RDS database
6
    VpcId: !Ref VPC
7
    SecurityGroupIngress:
8
      - IpProtocol: tcp
9
        FromPort: 5432
10
        ToPort: 5432
11
        SourceSecurityGroupId: !Ref ApplicationSecurityGroup
12
        Description: PostgreSQL access from application servers
13
      - IpProtocol: tcp
14
        FromPort: 5432
15
        ToPort: 5432
16
        SourceSecurityGroupId: !Ref BastionSecurityGroup
17
        Description: PostgreSQL access from bastion host
18
    Tags:
19
      - Key: Name
20
        Value: Database-SG

1
# RDS instance with IAM authentication
2
RDSWithIAMAuth:
3
  Type: AWS::RDS::DBInstance
4
  Properties:
5
    EnableIAMDatabaseAuthentication: true
6
    # ... other properties
7

8
# IAM role for database access
9
DatabaseAccessRole:
10
  Type: AWS::IAM::Role
11
  Properties:
12
    AssumeRolePolicyDocument:
13
      Version: '2012-10-17'
14
      Statement:
15
        - Effect: Allow
16
          Principal:
17
            Service: ec2.amazonaws.com
18
          Action: sts:AssumeRole
19
    Policies:
20
      - PolicyName: DatabaseAccess
21
        PolicyDocument:
22
          Version: '2012-10-17'
23
          Statement:
24
            - Effect: Allow
25
              Action:
26
                - rds-db:connect
27
              Resource: !Sub
28
                - arn:aws:rds-db:${AWS::Region}:${AWS::AccountId}:dbuser:${DBInstanceResourceId}/${DatabaseUser}
29
                - DBInstanceResourceId: !GetAtt RDSWithIAMAuth.DbiResourceId
30
                  DatabaseUser: iamuser

1
import pymysql
2
import boto3
3
import ssl
4
from botocore.exceptions import ClientError
5

6
# Traditional connection with password
7
def connect_with_password():
8
    connection = pymysql.connect(
9
        host='mydb.cluster-xyz.region.rds.amazonaws.com',
10
        user='admin',
11
        password='your-password',
12
        database='myapp',
13
        port=3306,
14
        ssl={'ssl_cert': '/opt/mysql/ssl/server-cert.pem'},
15
        ssl_verify_cert=True,
16
        ssl_verify_identity=True
17
    )
18
    return connection
19

20
# IAM authentication connection
21
def connect_with_iam():
22
    rds_client = boto3.client('rds', region_name='us-east-1')
23

24
    try:
25
        # Generate authentication token
26
        auth_token = rds_client.generate_db_auth_token(
27
            DBHostname='mydb.cluster-xyz.region.rds.amazonaws.com',
28
            Port=3306,
29
            DBUsername='iamuser'
30
        )
31

32
        connection = pymysql.connect(
33
            host='mydb.cluster-xyz.region.rds.amazonaws.com',
34
            user='iamuser',
35
            password=auth_token,
36
            database='myapp',
37
            port=3306,
38
            ssl={'ssl_cert': '/opt/mysql/ssl/server-cert.pem'},
39
            ssl_verify_cert=True
40
        )
41
        return connection
42

43
    except ClientError as e:
44
        print(f"Error generating auth token: {e}")
45
        raise
46

47
# Connection pooling with SQLAlchemy
48
from sqlalchemy import create_engine
49
from sqlalchemy.pool import QueuePool
50

51
def create_connection_pool():
52
    engine = create_engine(
53
        'mysql+pymysql://admin:password@mydb.cluster-xyz.region.rds.amazonaws.com:3306/myapp',
54
        poolclass=QueuePool,
55
        pool_size=20,
56
        max_overflow=30,
57
        pool_pre_ping=True,
58
        pool_recycle=3600
59
    )
60
    return engine

1
// MySQL connection with connection pooling
2
const mysql = require('mysql2/promise');
3
const AWS = require('aws-sdk');
4

5
// Traditional password connection
6
const pool = mysql.createPool({
7
    host: 'mydb.cluster-xyz.region.rds.amazonaws.com',
8
    user: 'admin',
9
    password: 'your-password',
10
    database: 'myapp',
11
    port: 3306,
12
    ssl: 'Amazon RDS',
13
    connectionLimit: 20,
14
    acquireTimeout: 60000,
15
    timeout: 60000
16
});
17

18
// IAM authentication connection
19
async function connectWithIAM() {
20
    const rds = new AWS.RDSSigner({
21
        region: 'us-east-1',
22
        hostname: 'mydb.cluster-xyz.region.rds.amazonaws.com',
23
        port: 3306,
24
        username: 'iamuser'
25
    });
26

27
    const token = rds.getAuthToken();
28

29
    const connection = await mysql.createConnection({
30
        host: 'mydb.cluster-xyz.region.rds.amazonaws.com',
31
        user: 'iamuser',
32
        password: token,
33
        database: 'myapp',
34
        port: 3306,
35
        ssl: 'Amazon RDS'
36
    });
37

38
    return connection;
39
}
40

41
// Query with error handling
42
async function executeQuery(query, params = []) {
43
    let connection;
44
    try {
45
        connection = await pool.getConnection();
46
        const [results] = await connection.execute(query, params);
47
        return results;
48
    } catch (error) {
49
        console.error('Database query error:', error);
50
        throw error;
51
    } finally {
52
        if (connection) connection.release();
53
    }
54
}

1
// JDBC connection with HikariCP pooling
2
import com.zaxxer.hikari.HikariConfig;
3
import com.zaxxer.hikari.HikariDataSource;
4
import software.amazon.awssdk.auth.credentials.DefaultCredentialsProvider;
5
import software.amazon.awssdk.regions.Region;
6
import software.amazon.awssdk.services.rds.RdsUtilities;
7

8
public class RDSConnectionManager {
9
    private static final String DB_HOST = "mydb.cluster-xyz.region.rds.amazonaws.com";
10
    private static final int DB_PORT = 3306;
11
    private static final String DB_NAME = "myapp";
12

13
    private HikariDataSource dataSource;
14

15
    public RDSConnectionManager() {
16
        HikariConfig config = new HikariConfig();
17
        config.setJdbcUrl("jdbc:mysql://" + DB_HOST + ":" + DB_PORT + "/" + DB_NAME);
18
        config.setUsername("admin");
19
        config.setPassword("your-password");
20
        config.setMaximumPoolSize(20);
21
        config.setConnectionTimeout(30000);
22
        config.setIdleTimeout(600000);
23
        config.setMaxLifetime(1800000);
24
        config.addDataSourceProperty("useSSL", "true");
25
        config.addDataSourceProperty("serverSslCert", "/opt/mysql/ssl/server-cert.pem");
26

27
        this.dataSource = new HikariDataSource(config);
28
    }
29

30
    // IAM authentication method
31
    public Connection getIAMConnection() throws SQLException {
32
        RdsUtilities rdsUtilities = RdsUtilities.builder()
33
            .credentialsProvider(DefaultCredentialsProvider.create())
34
            .region(Region.US_EAST_1)
35
            .build();
36

37
        String authToken = rdsUtilities.generateAuthenticationToken(builder ->
38
            builder.hostname(DB_HOST)
39
                  .port(DB_PORT)
40
                  .username("iamuser")
41
        );
42

43
        Properties props = new Properties();
44
        props.setProperty("user", "iamuser");
45
        props.setProperty("password", authToken);
46
        props.setProperty("useSSL", "true");
47

48
        return DriverManager.getConnection(
49
            "jdbc:mysql://" + DB_HOST + ":" + DB_PORT + "/" + DB_NAME,
50
            props
51
        );
52
    }
53

54
    public Connection getConnection() throws SQLException {
55
        return dataSource.getConnection();
56
    }
57
}

1
# High-performance RDS configuration
2
HighPerformanceDB:
3
  Type: AWS::RDS::DBInstance
4
  Properties:
5
    DBInstanceClass: db.r5.8xlarge  # Memory optimized
6
    AllocatedStorage: 1000
7
    StorageType: gp3  # Latest generation SSD
8
    Iops: 12000      # Provisioned IOPS
9
    StorageThroughput: 500  # MB/s throughput
10
    MultiAZ: true
11
    EnablePerformanceInsights: true
12
    PerformanceInsightsRetentionPeriod: 7
13
    MonitoringInterval: 60
14
    MonitoringRoleArn: !GetAtt EnhancedMonitoringRole.Arn

1
# Automatic read replica management
2
import boto3
3
import time
4

5
def manage_read_replicas(primary_db_identifier, target_cpu_threshold=70):
6
    """
7
    Automatically manage read replicas based on CPU utilization
8
    """
9
    rds = boto3.client('rds')
10
    cloudwatch = boto3.client('cloudwatch')
11

12
    # Get current CPU utilization
13
    response = cloudwatch.get_metric_statistics(
14
        Namespace='AWS/RDS',
15
        MetricName='CPUUtilization',
16
        Dimensions=[
17
            {
18
                'Name': 'DBInstanceIdentifier',
19
                'Value': primary_db_identifier
20
            }
21
        ],
22
        StartTime=datetime.utcnow() - timedelta(minutes=30),
23
        EndTime=datetime.utcnow(),
24
        Period=300,
25
        Statistics=['Average']
26
    )
27

28
    if response['Datapoints']:
29
        avg_cpu = sum(point['Average'] for point in response['Datapoints']) / len(response['Datapoints'])
30

31
        # Get current read replicas
32
        replicas = rds.describe_db_instances()['DBInstances']
33
        current_replicas = [
34
            db for db in replicas
35
            if db.get('ReadReplicaSourceDBInstanceIdentifier') == primary_db_identifier
36
        ]
37

38
        if avg_cpu > target_cpu_threshold and len(current_replicas) < 5:
39
            # Create new read replica
40
            replica_id = f"{primary_db_identifier}-replica-{len(current_replicas) + 1}"
41
            rds.create_db_instance_read_replica(
42
                DBInstanceIdentifier=replica_id,
43
                SourceDBInstanceIdentifier=primary_db_identifier,
44
                DBInstanceClass='db.r5.large',
45
                PubliclyAccessible=False
46
            )
47
            print(f"Created read replica: {replica_id}")
48

49
        elif avg_cpu < 30 and len(current_replicas) > 1:
50
            # Remove excess replica
51
            replica_to_remove = current_replicas[-1]['DBInstanceIdentifier']
52
            rds.delete_db_instance(
53
                DBInstanceIdentifier=replica_to_remove,
54
                SkipFinalSnapshot=True
55
            )
56
            print(f"Removed read replica: {replica_to_remove}")
57

58
    return avg_cpu, len(current_replicas)

1
# PostgreSQL performance tuning parameters
2
PostgreSQLParameterGroup:
3
  Type: AWS::RDS::DBParameterGroup
4
  Properties:
5
    Family: postgres15
6
    Description: High-performance PostgreSQL parameters
7
    Parameters:
8
      # Memory settings
9
      shared_buffers: '{DBInstanceClassMemory/4}'
10
      effective_cache_size: '{DBInstanceClassMemory*3/4}'
11
      work_mem: '256MB'
12
      maintenance_work_mem: '2GB'
13

14
      # Checkpoint settings
15
      checkpoint_completion_target: 0.9
16
      wal_buffers: '16MB'
17

18
      # Query planner settings
19
      random_page_cost: 1.1
20
      effective_io_concurrency: 200
21

22
      # Logging for performance analysis
23
      log_min_duration_statement: 1000
24
      log_checkpoints: 1
25
      log_connections: 1
26
      log_disconnections: 1
27
      log_lock_waits: 1
28

29
      # Connection settings
30
      max_connections: 1000

1
# Database with comprehensive backup strategy
2
ProductionDatabase:
3
  Type: AWS::RDS::DBInstance
4
  Properties:
5
    BackupRetentionPeriod: 30  # 30 days of backups
6
    PreferredBackupWindow: "03:00-04:00"  # Low-traffic window
7
    CopyTagsToSnapshot: true
8
    DeleteAutomatedBackups: false
9
    DeletionProtection: true
10

11
# Custom backup Lambda function
12
BackupLambda:
13
  Type: AWS::Lambda::Function
14
  Properties:
15
    Runtime: python3.11
16
    Handler: index.lambda_handler
17
    Code:
18
      ZipFile: |
19
        import boto3
20
        import json
21
        from datetime import datetime
22

23
        def lambda_handler(event, context):
24
            rds = boto3.client('rds')
25

26
            # Create manual snapshot
27
            snapshot_id = f"manual-snapshot-{datetime.now().strftime('%Y%m%d-%H%M%S')}"
28

29
            response = rds.create_db_snapshot(
30
                DBSnapshotIdentifier=snapshot_id,
31
                DBInstanceIdentifier=event['db_instance_id']
32
            )
33

34
            return {
35
                'statusCode': 200,
36
                'body': json.dumps(f'Snapshot created: {snapshot_id}')
37
            }

1
def restore_db_to_point_in_time(source_db_identifier, restore_time,
2
                               new_db_identifier, db_instance_class='db.r5.large'):
3
    """
4
    Restore database to a specific point in time
5
    """
6
    rds = boto3.client('rds')
7

8
    try:
9
        response = rds.restore_db_instance_to_point_in_time(
10
            SourceDBInstanceIdentifier=source_db_identifier,
11
            TargetDBInstanceIdentifier=new_db_identifier,
12
            RestoreTime=restore_time,
13
            DBInstanceClass=db_instance_class,
14
            MultiAZ=False,  # Can be enabled after restore
15
            PubliclyAccessible=False,
16
            AutoMinorVersionUpgrade=True,
17
            CopyTagsToSnapshot=True
18
        )
19

20
        print(f"Restore initiated: {response['DBInstance']['DBInstanceArn']}")
21
        return response['DBInstance']
22

23
    except Exception as e:
24
        print(f"Error restoring database: {e}")
25
        raise
26

27
# Automated disaster recovery
28
def setup_disaster_recovery(primary_region, secondary_region, db_identifier):
29
    """
30
    Set up cross-region disaster recovery
31
    """
32
    primary_rds = boto3.client('rds', region_name=primary_region)
33
    secondary_rds = boto3.client('rds', region_name=secondary_region)
34

35
    # Create cross-region read replica
36
    response = secondary_rds.create_db_instance_read_replica(
37
        DBInstanceIdentifier=f"{db_identifier}-dr",
38
        SourceDBInstanceIdentifier=f"arn:aws:rds:{primary_region}:{boto3.client('sts').get_caller_identity()['Account']}:db:{db_identifier}",
39
        DBInstanceClass='db.r5.large'
40
    )
41

42
    print(f"Disaster recovery replica created in {secondary_region}")
43
    return response['DBInstance']

1
# Comprehensive monitoring setup
2
CPUAlarm:
3
  Type: AWS::CloudWatch::Alarm
4
  Properties:
5
    AlarmDescription: RDS CPU utilization is too high
6
    MetricName: CPUUtilization
7
    Namespace: AWS/RDS
8
    Statistic: Average
9
    Period: 300
10
    EvaluationPeriods: 2
11
    Threshold: 80
12
    ComparisonOperator: GreaterThanThreshold
13
    Dimensions:
14
      - Name: DBInstanceIdentifier
15
        Value: !Ref PrimaryDatabase
16
    AlarmActions:
17
      - !Ref SNSAlarmTopic
18

19
DatabaseConnectionsAlarm:
20
  Type: AWS::CloudWatch::Alarm
21
  Properties:
22
    AlarmDescription: Too many database connections
23
    MetricName: DatabaseConnections
24
    Namespace: AWS/RDS
25
    Statistic: Average
26
    Period: 300
27
    EvaluationPeriods: 2
28
    Threshold: 900
29
    ComparisonOperator: GreaterThanThreshold
30
    Dimensions:
31
      - Name: DBInstanceIdentifier
32
        Value: !Ref PrimaryDatabase
33

34
FreeableMemoryAlarm:
35
  Type: AWS::CloudWatch::Alarm
36
  Properties:
37
    AlarmDescription: Low freeable memory
38
    MetricName: FreeableMemory
39
    Namespace: AWS/RDS
40
    Statistic: Average
41
    Period: 300
42
    EvaluationPeriods: 2
43
    Threshold: 1073741824  # 1 GB in bytes
44
    ComparisonOperator: LessThanThreshold
45
    Dimensions:
46
      - Name: DBInstanceIdentifier
47
        Value: !Ref PrimaryDatabase

1
# Custom RDS monitoring dashboard
2
import boto3
3
import json
4
from datetime import datetime, timedelta
5

6
def create_rds_monitoring_dashboard(db_instance_id, region='us-east-1'):
7
    """
8
    Create comprehensive RDS monitoring dashboard
9
    """
10
    cloudwatch = boto3.client('cloudwatch', region_name=region)
11

12
    dashboard_body = {
13
        "widgets": [
14
            {
15
                "type": "metric",
16
                "properties": {
17
                    "metrics": [
18
                        ["AWS/RDS", "CPUUtilization", "DBInstanceIdentifier", db_instance_id],
19
                        ["AWS/RDS", "DatabaseConnections", "DBInstanceIdentifier", db_instance_id],
20
                        ["AWS/RDS", "FreeableMemory", "DBInstanceIdentifier", db_instance_id],
21
                        ["AWS/RDS", "ReadLatency", "DBInstanceIdentifier", db_instance_id],
22
                        ["AWS/RDS", "WriteLatency", "DBInstanceIdentifier", db_instance_id]
23
                    ],
24
                    "period": 300,
25
                    "stat": "Average",
26
                    "region": region,
27
                    "title": "RDS Performance Metrics"
28
                }
29
            },
30
            {
31
                "type": "metric",
32
                "properties": {
33
                    "metrics": [
34
                        ["AWS/RDS", "FreeStorageSpace", "DBInstanceIdentifier", db_instance_id],
35
                        ["AWS/RDS", "ReadIOPS", "DBInstanceIdentifier", db_instance_id],
36
                        ["AWS/RDS", "WriteIOPS", "DBInstanceIdentifier", db_instance_id]
37
                    ],
38
                    "period": 300,
39
                    "stat": "Average",
40
                    "region": region,
41
                    "title": "RDS Storage and I/O"
42
                }
43
            }
44
        ]
45
    }
46

47
    response = cloudwatch.put_dashboard(
48
        DashboardName=f'RDS-{db_instance_id}',
49
        DashboardBody=json.dumps(dashboard_body)
50
    )
51

52
    return response
53

54
# Performance Insights analysis
55
def analyze_performance_insights(db_resource_id, start_time, end_time):
56
    """
57
    Analyze Performance Insights data
58
    """
59
    pi = boto3.client('pi')
60

61
    # Get top SQL statements
62
    response = pi.get_resource_metrics(
63
        ServiceType='RDS',
64
        Identifier=db_resource_id,
65
        StartTime=start_time,
66
        EndTime=end_time,
67
        PeriodInSeconds=300,
68
        MetricQueries=[
69
            {
70
                'Metric': 'db.SQL.Innodb_redo_log_writes.avg',
71
                'GroupBy': {
72
                    'Group': 'db.sql_tokenized.statement'
73
                }
74
            },
75
            {
76
                'Metric': 'db.wait_event.io/file/innodb/innodb_data_file.avg'
77
            }
78
        ]
79
    )
80

81
    return response

1
def analyze_rds_utilization(db_instance_id, days=30):
2
    """
3
    Analyze RDS utilization for right-sizing recommendations
4
    """
5
    cloudwatch = boto3.client('cloudwatch')
6
    end_time = datetime.utcnow()
7
    start_time = end_time - timedelta(days=days)
8

9
    # Get CPU utilization
10
    cpu_response = cloudwatch.get_metric_statistics(
11
        Namespace='AWS/RDS',
12
        MetricName='CPUUtilization',
13
        Dimensions=[
14
            {'Name': 'DBInstanceIdentifier', 'Value': db_instance_id}
15
        ],
16
        StartTime=start_time,
17
        EndTime=end_time,
18
        Period=3600,  # 1 hour periods
19
        Statistics=['Average', 'Maximum']
20
    )
21

22
    # Get database connections
23
    conn_response = cloudwatch.get_metric_statistics(
24
        Namespace='AWS/RDS',
25
        MetricName='DatabaseConnections',
26
        Dimensions=[
27
            {'Name': 'DBInstanceIdentifier', 'Value': db_instance_id}
28
        ],
29
        StartTime=start_time,
30
        EndTime=end_time,
31
        Period=3600,
32
        Statistics=['Average', 'Maximum']
33
    )
34

35
    # Calculate averages
36
    avg_cpu = sum(point['Average'] for point in cpu_response['Datapoints']) / len(cpu_response['Datapoints']) if cpu_response['Datapoints'] else 0
37
    max_cpu = max(point['Maximum'] for point in cpu_response['Datapoints']) if cpu_response['Datapoints'] else 0
38
    avg_connections = sum(point['Average'] for point in conn_response['Datapoints']) / len(conn_response['Datapoints']) if conn_response['Datapoints'] else 0
39

40
    # Recommendations
41
    recommendations = []
42

43
    if avg_cpu < 20 and max_cpu < 40:
44
        recommendations.append("Consider downsizing instance - low CPU utilization")
45
    elif avg_cpu > 70:
46
        recommendations.append("Consider upgrading instance - high CPU utilization")
47

48
    if avg_connections < 10:
49
        recommendations.append("Consider serverless Aurora for low connection usage")
50

51
    return {
52
        'average_cpu': avg_cpu,
53
        'max_cpu': max_cpu,
54
        'average_connections': avg_connections,
55
        'recommendations': recommendations
56
    }

1
# GP3 storage for cost optimization
2
OptimizedStorageDB:
3
  Type: AWS::RDS::DBInstance
4
  Properties:
5
    StorageType: gp3
6
    AllocatedStorage: 100
7
    Iops: 3000        # Baseline IOPS
8
    StorageThroughput: 125  # MB/s (cost-effective baseline)
9
    # Enable storage autoscaling
10
    MaxAllocatedStorage: 1000

1
def diagnose_connection_issues(db_endpoint, port=3306):
2
    """
3
    Diagnose common RDS connection issues
4
    """
5
    import socket
6
    import dns.resolver
7

8
    issues = []
9

10
    # Test DNS resolution
11
    try:
12
        dns.resolver.resolve(db_endpoint, 'A')
13
        print(f"✓ DNS resolution successful for {db_endpoint}")
14
    except Exception as e:
15
        issues.append(f"DNS resolution failed: {e}")
16

17
    # Test port connectivity
18
    try:
19
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
20
        sock.settimeout(10)
21
        result = sock.connect_ex((db_endpoint, port))
22
        sock.close()
23

24
        if result == 0:
25
            print(f"✓ Port {port} is accessible")
26
        else:
27
            issues.append(f"Cannot connect to port {port}")
28
    except Exception as e:
29
        issues.append(f"Socket connection failed: {e}")
30

31
    # Check security groups
32
    ec2 = boto3.client('ec2')
33
    try:
34
        # This would require additional logic to get security group IDs
35
        # and check rules - simplified for brevity
36
        pass
37
    except Exception as e:
38
        issues.append(f"Security group check failed: {e}")
39

40
    return issues

1
-- MySQL slow query analysis
2
SELECT
3
    query_time,
4
    lock_time,
5
    rows_sent,
6
    rows_examined,
7
    sql_text
8
FROM mysql.slow_log
9
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
10
ORDER BY query_time DESC
11
LIMIT 10;
12

13
-- PostgreSQL active queries
14
SELECT
15
    pid,
16
    now() - pg_stat_activity.query_start AS duration,
17
    query,
18
    state
19
FROM pg_stat_activity
20
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
21
  AND state = 'active';