Cloud: Database, HPOS and Sessions

-- Size of the tables a store grows, largest first. Run on the writer or a replica, as a read-only user.
SELECT table_name,
       table_rows                                          AS approx_rows,
       ROUND(data_length  / 1024 / 1024, 1)                AS data_mb,
       ROUND(index_length / 1024 / 1024, 1)                AS index_mb,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS total_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND ( table_name REGEXP '^wp_(wc_orders|wc_order_|woocommerce_order|woocommerce_sessions|woocommerce_log|wc_reserved_stock|actionscheduler_|postmeta$|posts$|options$|usermeta$)'
     OR table_name LIKE 'wp\_wc\_%' )
ORDER BY data_length + index_length DESC
LIMIT 20;

-- Bytes of storage per order, and orders per month: the two inputs of the sizing method.
SELECT ROUND(SUM(data_length + index_length)
             / (SELECT COUNT(*) FROM wp_wc_orders WHERE type = 'shop_order')) AS bytes_per_order
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name IN ( 'wp_wc_orders', 'wp_wc_order_addresses', 'wp_wc_order_operational_data', 'wp_wc_orders_meta',
                      'wp_woocommerce_order_items', 'wp_woocommerce_order_itemmeta',
                      'wp_wc_order_stats', 'wp_wc_order_product_lookup', 'wp_wc_order_tax_lookup', 'wp_wc_order_coupon_lookup' );

SELECT DATE_FORMAT(date_created_gmt, '%Y-%m') AS month, COUNT(*) AS orders
FROM wp_wc_orders
WHERE type = 'shop_order'
GROUP BY month
ORDER BY month DESC
LIMIT 12;

# What the store adds to the WordPress database tier: a parameter group with store settings and alarms on the
# numbers that stop checkout first. The instance itself is the one of the managed-database page, named pocketcoffee here.

variable "alarm_topic_arn" {
  type        = string
  description = "SNS topic that receives the database alarms"
}

variable "db_max_connections" {
  type        = number
  description = "Value of max_connections read from the running instance (SHOW GLOBAL VARIABLES)"
}

variable "has_replica" {
  type        = bool
  default     = false
  description = "Create the replica lag alarm when the replica of the managed-database page exists"
}

variable "db_min_free_storage_bytes" {
  type        = number
  description = "Alarm when free storage falls below this many bytes"
}

locals {
  # The parameters of the WordPress page: utf8mb4, 64 MiB packets, slow query log, TLS only.
  wordpress_parameters = {
    character_set_server     = "utf8mb4"
    character_set_client     = "utf8mb4"
    character_set_connection = "utf8mb4"
    character_set_results    = "utf8mb4"
    max_allowed_packet       = "67108864"
    slow_query_log           = "1"
    long_query_time          = "1"
    log_output               = "FILE"
    require_secure_transport = "1"
  }

  # What a store adds: fail a stuck checkout lock fast, and keep every deadlock in the error log.
  store_parameters = {
    innodb_lock_wait_timeout   = "15"
    innodb_print_all_deadlocks = "1"
  }
}

resource "aws_db_parameter_group" "pocketcoffee" {
  name        = "pocketcoffee-mariadb11-8"
  family      = "mariadb11.8"
  description = "Pocket Coffee WooCommerce: WordPress settings plus lock timeout and deadlock logging"

  dynamic "parameter" {
    for_each = merge(local.wordpress_parameters, local.store_parameters)
    content {
      name  = parameter.key
      value = parameter.value
    }
  }

  lifecycle {
    create_before_destroy = true
  }
}

resource "aws_cloudwatch_metric_alarm" "free_storage" {
  alarm_name          = "pocketcoffee-prod-free-storage"
  namespace           = "AWS/RDS"
  metric_name         = "FreeStorageSpace"
  dimensions          = { DBInstanceIdentifier = "pocketcoffee-prod" }
  statistic           = "Minimum"
  period              = 300
  evaluation_periods  = 3
  comparison_operator = "LessThanThreshold"
  threshold           = var.db_min_free_storage_bytes
  treat_missing_data  = "breaching"
  alarm_actions       = [var.alarm_topic_arn]
}

resource "aws_cloudwatch_metric_alarm" "connections" {
  alarm_name          = "pocketcoffee-prod-connections"
  namespace           = "AWS/RDS"
  metric_name         = "DatabaseConnections"
  dimensions          = { DBInstanceIdentifier = "pocketcoffee-prod" }
  statistic           = "Maximum"
  period              = 60
  evaluation_periods  = 5
  comparison_operator = "GreaterThanThreshold"
  threshold           = floor(var.db_max_connections * 0.8)
  alarm_actions       = [var.alarm_topic_arn]
}

resource "aws_cloudwatch_metric_alarm" "replica_lag" {
  count               = var.has_replica ? 1 : 0
  alarm_name          = "pocketcoffee-prod-replica-lag"
  namespace           = "AWS/RDS"
  metric_name         = "ReplicaLag"
  dimensions          = { DBInstanceIdentifier = "pocketcoffee-prod-replica-1" }
  statistic           = "Maximum"
  period              = 60
  evaluation_periods  = 5
  comparison_operator = "GreaterThanThreshold"
  threshold           = 5
  alarm_actions       = [var.alarm_topic_arn]
}

#!/bin/sh
# Read-only look at the order storage of the running store. Safe on production.
set -eu

wp_pod() {
  kubectl -n pocketcoffee exec deploy/pocketcoffee-web -c php-fpm -- wp --path=/var/www/html "$@"
}

wp_pod wc hpos status                  # HPOS enabled?, compatibility mode, unsynced orders, orders subject to cleanup
wp_pod wc hpos count_unmigrated        # orders the backup store has not seen
wp_pod option get woocommerce_custom_orders_table_enabled
wp_pod option get woocommerce_custom_orders_table_data_sync_enabled
wp_pod wc hpos compatibility-info      # active plugins and what they declared about HPOS

#!/bin/sh
# One-time change to "HPOS only". Take a manual RDS snapshot first; the last step cannot be undone.
set -eu

wp_pod() {
  kubectl -n pocketcoffee exec deploy/pocketcoffee-web -c php-fpm -- wp --path=/var/www/html "$@"
}

wp_pod wc hpos sync                    # bring the posts tables level one last time
wp_pod wc hpos verify_data                # the two stores agree
wp_pod wc hpos compatibility-mode disable # new orders are written to the HPOS tables only
# Days later, when nothing reads order data from postmeta any more:
wp_pod wc hpos cleanup all --batch-size=200

-- HPOS state in numbers: orders, the placeholders and what is left in postmeta.
SELECT (SELECT COUNT(*) FROM wp_wc_orders WHERE type = 'shop_order')                        AS hpos_orders,
       (SELECT COUNT(*) FROM wp_posts WHERE post_type = 'shop_order_placehold')             AS placeholders,
       (SELECT COUNT(*) FROM wp_posts WHERE post_type = 'shop_order')                       AS legacy_order_posts,
       (SELECT COUNT(*) FROM wp_postmeta pm JOIN wp_posts p ON p.ID = pm.post_id
         WHERE p.post_type IN ('shop_order', 'shop_order_placehold'))                       AS order_postmeta_rows;

-- Orders by status: pending, failed and checkout-draft rows are the ones that pile up.
SELECT status, COUNT(*) AS orders, MIN(date_created_gmt) AS oldest
FROM wp_wc_orders
WHERE type = 'shop_order'
GROUP BY status
ORDER BY orders DESC;

-- Action Scheduler: rows by status, the oldest pending action and the log table.
SELECT status, COUNT(*) AS actions, MIN(scheduled_date_gmt) AS oldest_scheduled
FROM wp_actionscheduler_actions
GROUP BY status
ORDER BY actions DESC;

SELECT g.slug AS action_group, a.status, COUNT(*) AS actions
FROM wp_actionscheduler_actions a
LEFT JOIN wp_actionscheduler_groups g ON g.group_id = a.group_id
GROUP BY g.slug, a.status
ORDER BY actions DESC
LIMIT 15;

SELECT COUNT(*) AS pending_past_due, MIN(scheduled_date_gmt) AS oldest_due
FROM wp_actionscheduler_actions
WHERE status = 'pending' AND scheduled_date_gmt <= UTC_TIMESTAMP();

SELECT (SELECT COUNT(*) FROM wp_actionscheduler_logs)   AS log_rows,
       (SELECT COUNT(*) FROM wp_actionscheduler_actions) AS action_rows;

# values.yaml of pocketcoffee-woocommerce: the keys this page adds (the rest is as in the overview page).
dbMaintenance:
  enabled: true
  schedule: "30 2 * * 0"
  actionRetentionDays: 14
  failedRetentionDays: 93
  batchSize: 500
  pauseSeconds: 1

{{- if .Values.dbMaintenance.enabled }}
apiVersion: batch/v1
kind: CronJob
metadata:
  name: {{ .Release.Name }}-db-maintenance
  labels:
    {{- include "pc.labels" . | nindent 4 }}
spec:
  schedule: {{ .Values.dbMaintenance.schedule | quote }}
  concurrencyPolicy: Forbid
  startingDeadlineSeconds: 600
  successfulJobsHistoryLimit: 1
  failedJobsHistoryLimit: 3
  jobTemplate:
    spec:
      backoffLimit: 0
      activeDeadlineSeconds: 3000
      ttlSecondsAfterFinished: 86400
      template:
        metadata:
          labels:
            app.kubernetes.io/name: {{ include "pc.name" . }}
            app.kubernetes.io/instance: {{ .Release.Name }}
            app.kubernetes.io/component: db-maintenance
        spec:
          restartPolicy: Never
          serviceAccountName: {{ .Values.serviceAccount.name }}
          automountServiceAccountToken: false
          securityContext:
            fsGroup: 33
            seccompProfile:
              type: RuntimeDefault
          containers:
            - name: maintenance
              image: {{ include "pc.phpImage" . }}
              securityContext:
                {{- include "pc.containerSecurity" . | nindent 16 }}
                runAsUser: 33
                runAsGroup: 33
              command: ["/bin/sh", "-ec"]
              args:
                - |
                  wp --path=/var/www/html action-scheduler clean \
                    --status=complete,canceled \
                    --before='{{ .Values.dbMaintenance.actionRetentionDays }} days ago' \
                    --batch-size={{ .Values.dbMaintenance.batchSize }} \
                    --pause={{ .Values.dbMaintenance.pauseSeconds }}
                  wp --path=/var/www/html action-scheduler clean \
                    --status=failed \
                    --before='{{ .Values.dbMaintenance.failedRetentionDays }} days ago' \
                    --batch-size={{ .Values.dbMaintenance.batchSize }} \
                    --pause={{ .Values.dbMaintenance.pauseSeconds }}
                  wp --path=/var/www/html eval 'wc_cleanup_session_data();'
                  wp --path=/var/www/html transient delete --expired
              envFrom:
                {{- include "pc.envFrom" . | nindent 16 }}
              resources:
                requests: { cpu: 100m, memory: 256Mi }
                limits: { memory: 512Mi }
              volumeMounts:
                - name: tmp
                  mountPath: /tmp
          volumes:
            - name: tmp
              emptyDir: {}
{{- end }}

#!/bin/sh
# After a deployment: do the recurring actions that keep the tables small exist?
set -eu

wp_pod() {
  kubectl -n pocketcoffee exec deploy/pocketcoffee-web -c php-fpm -- wp --path=/var/www/html "$@"
}

for hook in woocommerce_cleanup_sessions action_scheduler_run_actions_cleanup_hook woocommerce_cleanup_logs woocommerce_cleanup_draft_orders; do
  echo "== $hook"
  wp_pod action-scheduler action list --hook="$hook" --status=pending --fields=id,hook,scheduled_date --format=table
done

# Nothing listed? Ask the library to register the recurring actions of WooCommerce now, instead of waiting for its daily action.
# wp_pod eval 'do_action( "action_scheduler_ensure_recurring_actions" );'