Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

RDS Modules Bumping - Large Storage Size DB Approach #6370

Open
9 tasks
timckt opened this issue Oct 24, 2024 · 5 comments
Open
9 tasks

RDS Modules Bumping - Large Storage Size DB Approach #6370

timckt opened this issue Oct 24, 2024 · 5 comments
Assignees
@sj-williams

Comments

@timckt
Copy link
Contributor

timckt commented Oct 24, 2024 •
edited
Loading

TL;DR:

When bumping module, users will need to take action and set the db_iops value to be at least 12000 for below 14 + 8 = 22 DBs

  • For 14 postgres/mariadb

    • 6 production
    • 8 non-prod

  • For 8 oracle

    • 2 production
    • 6 non-prod
DBInstanceIdentifier                     AllocatedStorage   Engine       StorageType  Environment     Namespace                                     SlackChannel              InfraSupport
cloud-platform-22ccc42afd500eb2          830                postgres     gp2          production      hmpps-book-secure-move-api-production         N/A                       *
cloud-platform-2d4c157062b20f5d          10000              postgres     gp2          dev             hmpps-activities-management-dev               N/A                       *
cloud-platform-35c188a93c8a060d          500                mariadb      gp2          development     intranet-dev                                  N/A                       *
cloud-platform-43f547bdfa94ce12          550                postgres     gp2          stage           hmpps-delius-alfresco-stage                   ask-probation-webops      *
cloud-platform-59b5cf9e5de6b794          600                postgres     gp2          development     hmpps-dpr-fake-dps-service                    ask_dpr                   *
cloud-platform-5a6f44384e7e06e6          1491               postgres     gp2          preprod         hmpps-activities-management-preprod           N/A                       *
cloud-platform-5b6e8eb8ba215b7b          1500               postgres     gp2          prod            hmpps-manage-adjudications-api-prod           N/A                       *
cloud-platform-5c6377808488916a          512                postgres     gp2          preprod         offender-case-notes-preprod                   public_move-and-improve   *
cloud-platform-6c035586d92ac925          2000               postgres     gp2          preprod         hmpps-manage-adjudications-api-preprod        N/A                       *
cloud-platform-7520cc9334a6f330          16000              postgres     gp2          prod            visit-someone-in-prison-backend-svc-prod      N/A                       *
cloud-platform-78407cd5fbd86ed5          750                postgres     gp2          production      hmpps-workload-prod                           N/A                       *
cloud-platform-a066adb9989d7f7c          1500               postgres     gp2          preprod         hmpps-delius-alfresco-preprod                 ask-probation-hosting     *
cloud-platform-b6244d8c893e1d12          512                postgres     gp2          prod            offender-case-notes-prod                      public_move-and-improve   *
cloud-platform-c6e5432ed45afd19          16000              postgres     gp2          prod            visit-someone-in-prison-backend-svc-prod      N/A                       *

# Below DBs shall be in migration branch.
DBInstanceIdentifier                     AllocatedStorage   Engine       StorageType  Environment     Namespace                                     SlackChannel              InfraSupport
cloud-platform-0ba062271a739a44          300                oracle-se2   gp2          production      laa-crown-court-remuneration-production       laa-crime-higher-billing  *
cloud-platform-22fea73785debcdc          300                oracle-se2   gp2          development     laa-crown-court-litigator-fees-dev            laa-crime-higher-billing  *
cloud-platform-752ad03b27f0d355          300                oracle-se2   gp2          staging         laa-crown-court-litigator-fees-staging        laa-crime-higher-billing  *
cloud-platform-790bee95824fd5d5          300                oracle-se2   gp2          production      laa-crown-court-litigator-fees-production     laa-crime-higher-billing  *
cloud-platform-8eccc874fa0f1708          300                oracle-se2   gp2          staging         laa-crown-court-remuneration-staging          laa-crime-higher-billing  *
cloud-platform-9934ff6a725528ca          300                oracle-se2   gp2          uat             laa-crown-court-remuneration-uat              laa-crime-higher-billing  *
cloud-platform-a6e1cf58f91e81c0          300                oracle-se2   gp2          uat             laa-crown-court-litigator-fees-uat            laa-crime-higher-billing  *
cloud-platform-bb83da2ed8245f4d          300                oracle-se2   gp2          development     laa-crown-court-remuneration-dev              laa-crime-higher-billing  *

Background

We have cut the major 8.0.0 release for RDS module and we are going to bump the modules across our platform.

For all RDS DB engines except RDS for SQL Server, the baseline storage performance for gp3 volumes increases when the storage size reaches certain thresholds. This means that when user upgrade the module and switch to gp3, they may need to adjust the db_iops value accordingly to ensure optimal performance, especially for RDS instances with large volumes.

gp3 Storage Type

Database Engine Storage Size (GiB) Baseline Storage Performance Range of Provisioned IOPS Range of Provisioned Storage Throughput
Db2, MariaDB, MySQL, PostgreSQL 20–399 3,000 IOPS / 125 MiB/s N/A N/A
Db2, MariaDB, MySQL, PostgreSQL 400–65,536 12,000 IOPS / 500 MiB/s 12,000–64,000 IOPS 500–4,000 MiB/s
Oracle 20–199 3,000 IOPS / 125 MiB/s N/A N/A
Oracle 200–65,536 12,000 IOPS / 500 MiB/s 12,000–64,000 IOPS 500–4,000 MiB/s
SQL Server 20–16,384 3,000 IOPS / 125 MiB/s 3,000–16,000 IOPS 125–1,000 MiB/s

When bumping module, users will need to take action and set the db_iops value to be at least 12000 for :

  • Db2, MariaDB, MySQL, PostgreSQL with larger than 399 allocated_storage and gp2 storage_type (We have 14 DB as of Oct 24 20:00 in this case.)
  • Oracle with larger than 199 allocated_storage and gp2 storage_type (We have 8 DB as of Oct 24 20:00 in this case.)

The complete output are in below comment.

Proposed user journey

Approach

Which part of the user docs does this impact

Communicate changes

  • post for #cloud-platform-update
  • Weeknotes item
  • Show the Thing/P&A All Hands/User CoP
  • Announcements channel

Questions / Assumptions

Definition of done

  • readme has been updated
  • user docs have been updated
  • another team member has reviewed
  • smoke tests are green
  • prepare demo for the team

Reference

How to write good user stories
@timckt
Copy link
Contributor Author

timckt commented Oct 24, 2024 •
edited
Loading

  • For MariaDB, MySQL, PostgreSQL with larger than 399 allocated_storage and gp2 storage type
printf "%-40s %-18s %-12s %-12s %-15s %-45s %-25s %-25s\n" \
"DBInstanceIdentifier" "AllocatedStorage" "Engine" "StorageType" "Environment" "Namespace" "SlackChannel" "InfraSupport"

aws rds describe-db-instances \
    --query 'DBInstances[?AllocatedStorage > `399` && (Engine == `postgres` || Engine == `mysql` || Engine == `mariadb`) && StorageType == `gp2`]' \
    --output json | \
jq -r '.[] | [.DBInstanceIdentifier, .AllocatedStorage, .Engine, .StorageType, .DBInstanceArn] | @tsv' | \
while IFS=$'\t' read -r db_id storage engine storage_type arn; do
    tags=$(aws rds list-tags-for-resource --resource-name "$arn" --output json)
    namespace=$(echo "$tags" | jq -r '.TagList[] | select(.Key=="namespace") | .Value')
    slack_channel=$(echo "$tags" | jq -r '.TagList[] | select(.Key=="slack-channel") | .Value')
    infra_support=$(echo "$tags" | jq -r '.TagList[] | select(.Key=="infrastructure-support") | .Value')
    environment=$(echo "$tags" | jq -r '.TagList[] | select(.Key=="environment-name") | .Value')
    namespace=${namespace:-"N/A"}
    slack_channel=${slack_channel:-"N/A"}
    infra_support=${infra_support:-"N/A"}
    storage_type=${storage_type:-"N/A"}
    environment=${environment:-"N/A"}
    printf "%-40s %-18s %-12s %-12s %-15s %-45s %-25s %-25s\n" \
    "$db_id" "$storage" "$engine" "$storage_type" "$environment" "$namespace" "$slack_channel" "$infra_support"
done

The below output is as of Oct 24 20:00, removed the email address in InfraSupport here for data privacy.
We have 14 DB as of Oct 24 20:00 in this case.

DBInstanceIdentifier                     AllocatedStorage   Engine       StorageType  Environment     Namespace                                     SlackChannel              InfraSupport
cloud-platform-22ccc42afd500eb2          830                postgres     gp2          production      hmpps-book-secure-move-api-production         N/A                       *
cloud-platform-2d4c157062b20f5d          10000              postgres     gp2          dev             hmpps-activities-management-dev               N/A                       *
cloud-platform-35c188a93c8a060d          500                mariadb      gp2          development     intranet-dev                                  N/A                       *
cloud-platform-43f547bdfa94ce12          550                postgres     gp2          stage           hmpps-delius-alfresco-stage                   ask-probation-webops      *
cloud-platform-59b5cf9e5de6b794          600                postgres     gp2          development     hmpps-dpr-fake-dps-service                    ask_dpr                   *
cloud-platform-5a6f44384e7e06e6          1491               postgres     gp2          preprod         hmpps-activities-management-preprod           N/A                       *
cloud-platform-5b6e8eb8ba215b7b          1500               postgres     gp2          prod            hmpps-manage-adjudications-api-prod           N/A                       *
cloud-platform-5c6377808488916a          512                postgres     gp2          preprod         offender-case-notes-preprod                   public_move-and-improve   *
cloud-platform-6c035586d92ac925          2000               postgres     gp2          preprod         hmpps-manage-adjudications-api-preprod        N/A                       *
cloud-platform-7520cc9334a6f330          16000              postgres     gp2          prod            visit-someone-in-prison-backend-svc-prod      N/A                       *
cloud-platform-78407cd5fbd86ed5          750                postgres     gp2          production      hmpps-workload-prod                           N/A                       *
cloud-platform-a066adb9989d7f7c          1500               postgres     gp2          preprod         hmpps-delius-alfresco-preprod                 ask-probation-hosting     *
cloud-platform-b6244d8c893e1d12          512                postgres     gp2          prod            offender-case-notes-prod                      public_move-and-improve   *
cloud-platform-c6e5432ed45afd19          16000              postgres     gp2          prod            visit-someone-in-prison-backend-svc-prod      N/A                       *```

@timckt
Copy link
Contributor Author

timckt commented Oct 24, 2024 •
edited
Loading

  • For Oracle with larger than 199 allocated_storage and gp2 storage_type
printf "%-40s %-18s %-12s %-12s %-15s %-45s %-25s %-25s\n" \
"DBInstanceIdentifier" "AllocatedStorage" "Engine" "StorageType" "Environment" "Namespace" "SlackChannel" "InfraSupport"

aws rds describe-db-instances \
    --query 'DBInstances[?AllocatedStorage > `199` && (Engine == `oracle-se2`) && StorageType == `gp2`]' \
    --output json | \
jq -r '.[] | [.DBInstanceIdentifier, .AllocatedStorage, .Engine, .StorageType, .DBInstanceArn] | @tsv' | \
while IFS=$'\t' read -r db_id storage engine storage_type arn; do
    tags=$(aws rds list-tags-for-resource --resource-name "$arn" --output json)
    namespace=$(echo "$tags" | jq -r '.TagList[] | select(.Key=="namespace") | .Value')
    slack_channel=$(echo "$tags" | jq -r '.TagList[] | select(.Key=="slack-channel") | .Value')
    infra_support=$(echo "$tags" | jq -r '.TagList[] | select(.Key=="infrastructure-support") | .Value')
    environment=$(echo "$tags" | jq -r '.TagList[] | select(.Key=="environment-name") | .Value')
    namespace=${namespace:-"N/A"}
    slack_channel=${slack_channel:-"N/A"}
    infra_support=${infra_support:-"N/A"}
    storage_type=${storage_type:-"N/A"}
    environment=${environment:-"N/A"}
    printf "%-40s %-18s %-12s %-12s %-15s %-45s %-25s %-25s\n" \
    "$db_id" "$storage" "$engine" "$storage_type" "$environment" "$namespace" "$slack_channel" "$infra_support"
done

The below output is as of Oct 24 20:00, removed the email address in InfraSupport here for data privacy
We have 8 DB as of Oct 24 20:00 in this case.
Those DB shall be in migration branch.

DBInstanceIdentifier                     AllocatedStorage   Engine       StorageType  Environment     Namespace                                     SlackChannel              InfraSupport
cloud-platform-0ba062271a739a44          300                oracle-se2   gp2          production      laa-crown-court-remuneration-production       laa-crime-higher-billing  *
cloud-platform-22fea73785debcdc          300                oracle-se2   gp2          development     laa-crown-court-litigator-fees-dev            laa-crime-higher-billing  *
cloud-platform-752ad03b27f0d355          300                oracle-se2   gp2          staging         laa-crown-court-litigator-fees-staging        laa-crime-higher-billing  *
cloud-platform-790bee95824fd5d5          300                oracle-se2   gp2          production      laa-crown-court-litigator-fees-production     laa-crime-higher-billing  *
cloud-platform-8eccc874fa0f1708          300                oracle-se2   gp2          staging         laa-crown-court-remuneration-staging          laa-crime-higher-billing  *
cloud-platform-9934ff6a725528ca          300                oracle-se2   gp2          uat             laa-crown-court-remuneration-uat              laa-crime-higher-billing  *
cloud-platform-a6e1cf58f91e81c0          300                oracle-se2   gp2          uat             laa-crown-court-litigator-fees-uat            laa-crime-higher-billing  *
cloud-platform-bb83da2ed8245f4d          300                oracle-se2   gp2          development     laa-crown-court-remuneration-dev              laa-crime-higher-billing  *```

@timckt timckt changed the title RDS Modules Bumping - Large Storage Size DB Approach RDS Modules Bumping - Approach Large Storage Size DB Oct 24, 2024
@timckt timckt changed the title RDS Modules Bumping - Approach Large Storage Size DB RDS Modules Bumping - Large Storage Size DB Approach Oct 25, 2024
@sj-williams sj-williams self-assigned this Nov 6, 2024
@sj-williams
Copy link
Contributor 

DBInstanceIdentifier                     AllocatedStorage   Engine       StorageType  Environment     Namespace
cloud-platform-22ccc42afd500eb2          913                postgres     gp2          production      hmpps-book-secure-move-api-production
cloud-platform-2d4c157062b20f5d          10000              postgres     gp2          dev             hmpps-activities-management-dev
cloud-platform-43f547bdfa94ce12          550                postgres     gp2          stage           hmpps-delius-alfresco-stage
cloud-platform-59b5cf9e5de6b794          600                postgres     gp2          development     hmpps-dpr-fake-dps-service
cloud-platform-5a6f44384e7e06e6          1491               postgres     gp2          preprod         hmpps-activities-management-preprod
cloud-platform-5b6e8eb8ba215b7b          1500               postgres     gp2          prod            hmpps-manage-adjudications-api-prod
cloud-platform-6c035586d92ac925          2000               postgres     gp2          preprod         hmpps-manage-adjudications-api-preprod
cloud-platform-7520cc9334a6f330          16000              postgres     gp2          prod            visit-someone-in-prison-backend-svc-prod
cloud-platform-c6e5432ed45afd19          16000              postgres     gp2          prod            visit-someone-in-prison-backend-svc-prod

@sj-williams
Copy link
Contributor

sj-williams commented Nov 19, 2024 •
edited
Loading

Down to 7:

DBInstanceIdentifier
cloud-platform-22ccc42afd500eb2
cloud-platform-43f547bdfa94ce12
cloud-platform-59b5cf9e5de6b794
cloud-platform-5b6e8eb8ba215b7b
cloud-platform-6c035586d92ac925
cloud-platform-7520cc9334a6f330
cloud-platform-c6e5432ed45afd19

@sj-williams sj-williams moved this from 🏗 In Progress to ⛔ Blocked in Cloud Platform Nov 28, 2024
@timckt
Copy link
Contributor Author

timckt commented Dec 18, 2024

This ticket will be covered by #6606

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@sj-williams sj-williams

Labels
None yet
Projects
Cloud Platform
Status: ⛔ Blocked
Milestone
No milestone
Development

No branches or pull requests
3 participants
@sj-williams @timckt @Matt-Alinosn