↓Skip to main content

Python script to display Aurora Postgres Serverless v2 cloudwatch database insights metrics

·2 mins

When you are using Aurora Serverless V2 , you can get resource utilization metrics from 3 sources, basic cloudwatch metrics, enhanced monitoring metrics and cloudwatch database insights. This python script below queries metrics like CPUUtilization, ACUUtilization, ServerlessDatabaseCapacity at the instance level and shows them side by side in a table.

The script takes the instance id and begin and end times as arguments and displays the following metrics

Cloudwatch Metrics #

CPUUtilization,FreeableMemory,ConnectionAttempts,BufferCacheHitRatio,ReadIOPS,ReadLatency,ReadThroughput

Performance insights Metrics #

CPUUtilization,os.cpuUtilization.total.avg,os.tasks.running.avg,os.general.serverlessDatabaseCapacity.avg,os.general.maxConfiguredAcu.avg,os.general.numVCPUs.avg,db.User.numbackends.avg,db.SQL.queries_started.avg,os.diskIO.auroraStorage.diskQueueDepth.avg,os.memory.free.avg}


    import boto3
    import json
    import argparse
    from datetime import datetime, timedelta,timezone,UTC
    import pandas as pd

    parser = argparse.ArgumentParser()
    parser.add_argument('-i', '--inst', help="Instance Id", type=str,required=True)
    parser.add_argument('-b', '--begintime', help="Begin Time",type=str,required=False,default='26/03/18 00:01')
    parser.add_argument('-e', '--endtime', help="End Time",type=str,required=False,default='26/03/18 23:59')
    args = parser.parse_args()

    pd.set_option('display.float_format', lambda x: '%.2f' % x)
    l_btime=datetime.strptime(args.begintime,"%y/%m/%d %H:%M")
    l_etime=datetime.strptime(args.endtime,"%y/%m/%d %H:%M")

    instance_id = args.inst

    rds = boto3.client("rds")
    desc = rds.describe_db_instances(DBInstanceIdentifier=instance_id)
    dbi_resource_id = desc["DBInstances"][0]["DbiResourceId"]  

    # now = datetime.now(UTC)
    # start = now - timedelta(hours=1)

    now = l_etime
    start = l_btime

    start_cwlog = start - timedelta(hours=4)
    now_cwlog = now - timedelta(hours=4)

    # 1. Cloudwatch metrics

    df_cw_metrics=pd.DataFrame();
    d_cw_col_names={"CPUUtilization":"oscpuutil","FreeableMemory":"freabmem","ConnectionAttempts":"conatt"
                    ,"BufferCacheHitRatio":"bchitrat","ReadIOPS":"riops"
                    ,"ReadLatency":"rlat","ReadThroughput":"rthr"}
    cw = boto3.client('cloudwatch')

    metrics = [
        {'Namespace': 'AWS/RDS', 'MetricName': 'CPUUtilization', 'Dimensions': [{'Name': 'DBInstanceIdentifier', 'Value': instance_id}]},
        {'Namespace': 'AWS/RDS', 'MetricName': 'FreeableMemory', 'Dimensions': [{'Name': 'DBInstanceIdentifier', 'Value': instance_id}]},
        {'Namespace': 'AWS/RDS', 'MetricName': 'BufferCacheHitRatio', 'Dimensions': [{'Name': 'DBInstanceIdentifier', 'Value': instance_id}]},
        {'Namespace': 'AWS/RDS', 'MetricName': 'ReadIOPS', 'Dimensions': [{'Name': 'DBInstanceIdentifier', 'Value': instance_id}]},
        {'Namespace': 'AWS/RDS', 'MetricName': 'ReadLatency', 'Dimensions': [{'Name': 'DBInstanceIdentifier', 'Value': instance_id}]},
        {'Namespace': 'AWS/RDS', 'MetricName': 'ReadThroughput', 'Dimensions': [{'Name': 'DBInstanceIdentifier', 'Value': instance_id}]},
    ]

    for m in metrics:
       resp = cw.get_metric_statistics(
           Namespace=m['Namespace'],
           MetricName=m['MetricName'],
           Dimensions=m['Dimensions'],
           StartTime=start,
           EndTime=now,
           Period=60,
           Statistics=["Average"],
       )
       if df_cw_metrics.empty:
          df_cw_metrics=pd.DataFrame(resp['Datapoints'])
          # if 'Unit' in df_cw_metrics.columns:
          df_cw_metrics.drop(columns=['Unit'], inplace=True)
          df_cw_metrics.columns=['Timestamp',d_cw_col_names[m['MetricName']]]
       else:
          df_cw_metrics_tmp=pd.DataFrame(resp['Datapoints'])
          df_cw_metrics_tmp.drop(columns=['Unit'], inplace=True)
          df_cw_metrics_tmp.columns=['Timestamp',d_cw_col_names[m['MetricName']]]
          df_cw_metrics = pd.merge(df_cw_metrics, df_cw_metrics_tmp, on='Timestamp')

    # 2. Performance Insights CPUUtilization
    df_pi_metrics=pd.DataFrame();
    d_col_names={"CPUUtilization":"cpuutil","os.cpuUtilization.total.avg":"cputot","os.tasks.running.avg":"trun","os.general.serverlessDatabaseCapacity.avg":"svcap","os.general.maxConfiguredAcu.avg":"maxacu","os.general.numVCPUs.avg":"vcpu","db.User.numbackends.avg":"nbkend","db.SQL.queries_started.avg":"qstrt","os.diskIO.auroraStorage.diskQueueDepth.avg":"qdepth","os.memory.free.avg":"fremem"}
    pi = boto3.client("pi")
    resp = pi.get_resource_metrics(
        ServiceType="RDS",
        Identifier=dbi_resource_id,
        MetricQueries=[{"Metric": "os.cpuUtilization.total.avg"},{"Metric": "os.tasks.running.avg"},{"Metric": "os.general.serverlessDatabaseCapacity.avg"}
                       ,{"Metric": "os.general.maxConfiguredAcu.avg"},{"Metric": "os.general.numVCPUs.avg"},{"Metric": "db.User.numbackends.avg"},{"Metric": "db.SQL.queries_started.avg"}
                       ,{"Metric": "os.diskIO.auroraStorage.diskQueueDepth.avg"},{"Metric": "os.memory.free.avg"}
                      ],
        StartTime=start,
        EndTime=now,
        PeriodInSeconds=60,
    )

    for metric in resp["MetricList"]:
        if df_pi_metrics.empty:
           df_pi_metrics=pd.DataFrame(metric['DataPoints'])
           df_pi_metrics.columns=['Timestamp',d_col_names[metric["Key"]["Metric"]]]
        else:
           df_pi_metrics_tmp=pd.DataFrame(metric['DataPoints'])
           df_pi_metrics_tmp.columns=['Timestamp',d_col_names[metric["Key"]["Metric"]]]
           df_pi_metrics = pd.merge(df_pi_metrics, df_pi_metrics_tmp, on='Timestamp')

    df_pi_cw_metrics = pd.merge(df_pi_metrics, df_cw_metrics, on='Timestamp')
    print(df_pi_cw_metrics)