5.2: Database Activity Streams in action

Database Activity Streams

Database Activity Streams provide a near real-time data stream of the database activity in your relational database. When you integrate Database Activity Streams with third-party monitoring tools, you can monitor and audit database activity.

Database Activity Streams have the following limits and requirements:

  1. For Aurora PostgreSQL, database activity streams are supported for version 2.3 (which is compatible with PostgreSQL version 10.7) or higher and versions 3.0 (which is compatible with PostgreSQL version 11.4) or higher.
  2. They require use of AWS Key Management Service (AWS KMS) because the activity streams are always encrypted.

2.1 Configuring Database Activity Streams

You start an activity stream at the DB cluster level to monitor database activity for all DB instances of the cluster. Any DB instances added to the cluster are also automatically monitored.

You can choose to have the database session handle database activity events either synchronously or asynchronously:

  1. Synchronous mode: In synchronous mode, when a database session generates an activity stream event, the session blocks until the event is made durable. If the event can’t be made durable for some reason, the database session returns to normal activities. However, an RDS event is sent indicating that activity stream records might be lost for some time. A second RDS event is sent after the system is back to a healthy state. The synchronous mode favors the accuracy of the activity stream over database performance.

  2. Asynchronous mode: In asynchronous mode, when a database session generates an activity stream event, the session returns to normal activities immediately. In the background, the activity stream event is made a durable record. If an error occurs in the background task, an RDS event is sent. This event indicates the beginning and end of any time windows where activity stream event records might have been lost. Asynchronous mode favors database performance over the accuracy of the activity stream.

2.2 Start activity stream

  1. Open the Amazon RDS service console Databases section .

  2. Select the Aurora DB cluster that was created automatically for you as a part of the CloudFormation stack or you created manually.

  3. Click Actions menu and choose Start activity stream. 1-das-stream-2

  4. Enter the following settings in the Database Activity Stream window:

    a. For Master key, choose the key that you created in the earlier step. If you don’t see the new key - try to refresh the browser window.

    b. For Database activity stream mode, choose Asynchronous.

    c. Choose Apply immediately.

  5. When you’re done entering settings, click Continue. 1-das-51

  6. The Status column on the RDS-> Database page for the cluster will start showing configuring-activity-stream. 1-das-52

  7. Verify the activity streaming by clicking on the cluster name and clicking on configuration. You will see the Kinesis stream name to which the Database Activity Stream will be published. 1-das-53

  8. Wait till the status on RDS-> Database page for the cluster changes back to Available.

2.3 Generate load on the Aurora cluster

Now, we will generate load on the Database and access the Database activity stream information by executing a python script called das-script.py as shown below.

Open a Cloud9 terminal window and run pgbench:

pgbench --protocol=prepared --progress=60 --time=300 --client=16 --jobs=96 > results1.log

2.4 Sample code to view Database Activity Streams

We already downloaded a sample python script das-script.py to view the activity stream as part of prerequisites section and stored at /home/ec2-user/das-script.py. In this script, you will be required to replace the value for REGION_NAME as per the AWS Region you are running this lab for e.g. us-west-2 and RESOURCE_ID with the Aurora cluster’s Resource id value as highlighted below.

1-das-53

Open a new Cloud9 terminal window and paste the following to edit the Python script:

nano /home/ec2-user/das-script.py

Update the following variables in the script as per your actual settings:

1-das-53

To save file after changes in nano editor, press CTRL-X , enter Y and then Enter.

To view the database activity stream, run the python script as shown below:

python /home/ec2-user/das-script.py

You will see a lot of messages in the terminal output which is in JSON format.

2.5 Sample Output Activity Streaming

To format the Database Activity Streaming output and interpret the results, you can use a free tool like JSON formatter .

Copy a block of the das-script.py script output starting from {"type": and ending with } as shown in the below screenshot and paste it into JSON formatter . Then press the Format / Beautify button. You should see the formatted database activity similar to the following:

1-das-55

{
  "type": "DatabaseActivityMonitoringRecord",
  "clusterId": "cluster-XXXXXXXXXXXXXXXXXXXXXXXXXX",
  "instanceId": "db-XXXXXXXXXXXXXXXXXXXXXXX",
  "databaseActivityEventList": [
    {
      "logTime": "2019-12-26 06:56:09.090054+00",
      "statementId": 3731,
      "substatementId": 1,
      "objectType": "TABLE",
      "command": "INSERT",
      "objectName": "public.pgbench_history",
      "databaseName": "mylab",
      "dbUserName": "masteruser",
      "remoteHost": "10.0.0.204",
      "remotePort": "33948",
      "sessionId": "5e04596c.2383",
      "rowCount": 1,
      "commandText": "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP);",
      "paramList": [
        "424",
        "62",
        "1788678",
        "-2770"
      ],
      "pid": 9091,
      "clientApplication": "pgbench",
      "exitCode": null,
      "class": "WRITE",
      "serverVersion": "2.3.5",
      "serverType": "PostgreSQL",
      "serviceName": "Amazon Aurora PostgreSQL-Compatible edition",
      "serverHost": "10.0.12.39",
      "netProtocol": "TCP",
      "dbProtocol": "Postgres 3.0",
      "type": "record"
    }
  ]
}

2.6 Stop the Database Activity Streaming

  1. In the navigation pane, choose Databases and select the Aurora DB cluster that was created automatically for you as a part of the CloudFormation stack or you created manually.
  2. Click on Action and select Stop activity stream. 1-das-57
  3. Choose Apply immediately and click Continue to stop Database activity streaming on the cluster. 1-das-58
  4. The status column on the RDS Database home page for the cluster will start showing configuring-activity-stream. 1-das-59
  5. After some time, activity streams will be stopped and the status column on the RDS Database home page for the cluster will change back to Available.
  6. If pgbench is still running in our Cloud9 terminal window, press Ctrl+c to stop it at this point.