Managing Amazon Keyspaces auto scaling with Cassandra Query Language (CQL)

To create and manage auto scaling settings for Amazon Keyspaces tables with Cassandra Query Language (CQL), you can use cqlsh. This topic gives an overview of the auto scaling tasks that you can manage programmatically using CQL.

For more information about the CQL statements described in this topic, see DDL statements (data definition language) in Amazon Keyspaces.

Before you begin

You need to complete the following tasks before you can start.

Configure permissions

If you haven't already done so, you must configure the appropriate permissions for the user to create and manage automatic scaling settings. In AWS Identity and Access Management (IAM), the AWS managed policy AmazonKeyspacesFullAccess is required to manage Amazon Keyspaces scaling policies. For detailed steps, see Before you begin: Granting user permissions for Amazon Keyspaces automatic scaling.

Configure cqlsh

If you haven't already done so, you must install and configure cqlsh. To do this, follow the instructions at Using the cqlsh-expansion to connect to Amazon Keyspaces. You can then use the AWS CloudShell to run the commands in the following sections.

Create a new table with automatic scaling using CQL

When you create a new Amazon Keyspaces table, you can automatically enable auto scaling for the table's write or read capacity in the CREATE TABLE statement. This allows Amazon Keyspaces to contact Application Auto Scaling on your behalf to register the table as a scalable target and adjust the provisioned write or read capacity.

For more information on how to create a multi-Region table and configure different auto scaling settings for table replicas, see Creating a multi-Region table with default settings (CQL).

Note

Amazon Keyspaces auto scaling requires the presence of a service-linked role (AWSServiceRoleForApplicationAutoScaling_CassandraTable) to perform automatic scaling actions on your behalf. This role is created automatically for you. For more information, see Using service-linked roles for Amazon Keyspaces.

To configure auto scaling settings for a table programmatically, you use the AUTOSCALING_SETTINGS statement that contains the parameters for Amazon Keyspaces auto scaling. The parameters define the conditions that direct Amazon Keyspaces to adjust your table's provisioned throughput, and what additional optional actions to take. In this example, you define the auto scaling settings for mytable.

The policy contains the following elements:

• AUTOSCALING_SETTINGS – Specifies if Amazon Keyspaces is allowed to adjust throughput capacity on your behalf. The following values are required:

  • provisioned_write_capacity_autoscaling_update:

    • minimum_units

    • maximum_units

  • provisioned_read_capacity_autoscaling_update:

    • minimum_units

    • maximum_units

  • scaling_policy – Amazon Keyspaces supports the target tracking policy. To define the target tracking policy, you configure the following parameters.

    • target_value – Amazon Keyspaces auto scaling ensures that the ratio of consumed capacity to provisioned capacity stays at or near this value. You define target_value as a percentage.

    • disableScaleIn: (Optional) A boolean that specifies if scale-in is disabled or enabled for the table. This parameter is disabled by default. To turn on scale-in, set the boolean value to FALSE. This means that capacity is automatically scaled down for a table on your behalf.

    • scale_out_cooldown – A scale-out activity increases the provisioned throughput of your table. To add a cooldown period for scale-out activities, specify a value, in seconds, for scale_out_cooldown. If you don't specify a value, the default value is 0. For more information about target tracking and cooldown periods, see Target Tracking Scaling Policies in the Application Auto Scaling User Guide.

    • scale_in_cooldown – A scale-in activity decreases the provisioned throughput of your table. To add a cooldown period for scale-in activities, specify a value, in seconds, for scale_in_cooldown. If you don't specify a value, the default value is 0. For more information about target tracking and cooldown periods, see Target Tracking Scaling Policies in the Application Auto Scaling User Guide.

Note

To further understand how target_value works, suppose that you have a table with a provisioned throughput setting of 200 write capacity units. You decide to create a scaling policy for this table, with a target_value of 70 percent.

Now suppose that you begin driving write traffic to the table so that the actual write throughput is 150 capacity units. The consumed-to-provisioned ratio is now (150 / 200), or 75 percent. This ratio exceeds your target, so auto scaling increases the provisioned write capacity to 215 so that the ratio is (150 / 215), or 69.77 percent—as close to your target_value as possible, but not exceeding it.

For mytable, you set TargetValue for both read and write capacity to 50 percent. Amazon Keyspaces auto scaling adjusts the table's provisioned throughput within the range of 5–10 capacity units so that the consumed-to-provisioned ratio remains at or near 50 percent. For read capacity, you set the values for ScaleOutCooldown and ScaleInCooldown to 60 seconds.

You can use the following statement to create a new Amazon Keyspaces table with auto scaling enabled.

CREATE TABLE mykeyspace.mytable(pk int, ck int, PRIMARY KEY (pk, ck))
WITH CUSTOM_PROPERTIES = {  
    'capacity_mode': {  
        'throughput_mode': 'PROVISIONED',  
        'read_capacity_units': 1,  
        'write_capacity_units': 1  
    }
} AND AUTOSCALING_SETTINGS = {
    'provisioned_write_capacity_autoscaling_update': {
        'maximum_units': 10,  
        'minimum_units': 5,  
        'scaling_policy': {
            'target_tracking_scaling_policy_configuration': {
                'target_value': 50
            }  
        }  
    },  
    'provisioned_read_capacity_autoscaling_update': {  
        'maximum_units': 10,  
        'minimum_units': 5,  
        'scaling_policy': {  
            'target_tracking_scaling_policy_configuration': {  
                'target_value': 50,
                'scale_in_cooldown': 60,  
                'scale_out_cooldown': 60
            }  
        }  
    }
};

Enable automatic scaling on an existing table using CQL

For an existing Amazon Keyspaces table, you can turn on auto scaling for the table's write or read capacity using the ALTER TABLE statement. If you're updating a table that is currently in on-demand capacity mode, than capacity_mode is required. If your table is already in provisioned capacity mode, this field can be omitted.

Note

Amazon Keyspaces automatic scaling requires the presence of a service-linked role (AWSServiceRoleForApplicationAutoScaling_CassandraTable) that performs automatic scaling actions on your behalf. This role is created automatically for you. For more information, see Using service-linked roles for Amazon Keyspaces.

In the following example, the statement updates the table mytable, which is in on-demand capacity mode. The statement changes the capacity mode of the table to provisioned mode with auto scaling enabled.

The write capacity is configured within the range of 5–10 capacity units with a target value of 50%. The read capacity is also configured within the range of 5–10 capacity units with a target value of 50%. For read capacity, you set the values for scale_out_cooldown and scale_in_cooldown to 60 seconds.

ALTER TABLE mykeyspace.mytable
WITH CUSTOM_PROPERTIES = {  
    'capacity_mode': {  
        'throughput_mode': 'PROVISIONED',  
        'read_capacity_units': 1,  
        'write_capacity_units': 1  
    }
} AND AUTOSCALING_SETTINGS = {
    'provisioned_write_capacity_autoscaling_update': {
        'maximum_units': 10,  
        'minimum_units': 5,  
        'scaling_policy': {
            'target_tracking_scaling_policy_configuration': {
                'target_value': 50
            }  
        }  
    },
    'provisioned_read_capacity_autoscaling_update': {  
        'maximum_units': 10,  
        'minimum_units': 5,  
        'scaling_policy': {  
            'target_tracking_scaling_policy_configuration': {  
                'target_value': 50,
                'scale_in_cooldown': 60,  
                'scale_out_cooldown': 60
            }  
        }  
    }
};

View your table's Amazon Keyspaces auto scaling configuration using CQL

To view details of the auto scaling configuration of a table, use the following command.

SELECT * FROM system_schema_mcs.autoscaling WHERE keyspace_name = 'mykeyspace' AND table_name = 'mytable';

The output for this command looks like this.

 keyspace_name | table_name | provisioned_read_capacity_autoscaling_update                                                                                                                                                                      | provisioned_write_capacity_autoscaling_update
---------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 mykeyspace    | mytable    | {'minimum_units': 5, 'maximum_units': 10, 'scaling_policy': {'target_tracking_scaling_policy_configuration': {'scale_out_cooldown': 60, 'disable_scale_in': false, 'target_value': 50, 'scale_in_cooldown': 60}}} | {'minimum_units': 5, 'maximum_units': 10, 'scaling_policy': {'target_tracking_scaling_policy_configuration': {'scale_out_cooldown': 0, 'disable_scale_in': false, 'target_value': 50, 'scale_in_cooldown': 0}}}

Turn off Amazon Keyspaces auto scaling for a table using CQL

You can turn off Amazon Keyspaces auto scaling for your table at any time. If you no longer need to scale your table's read or write capacity, you should consider turning off auto scaling so that Amazon Keyspaces doesn't continue modifying your table’s read or write capacity settings. You can update the table with an ALTER TABLE statement.

The following statement turns off auto scaling for write capacity of the table mytable. It also deletes the CloudWatch alarms that were created on your behalf.

ALTER TABLE mykeyspace.mytable
WITH AUTOSCALING_SETTINGS = {
    'provisioned_write_capacity_autoscaling_update': {
        'autoscaling_disabled': true
    }
};

Note

To delete the service-linked role that Application Auto Scaling uses, you must disable automatic scaling on all tables in the account across all AWS Regions.