PCDE Monitor and optimize database performance — All Questions With Answers

gcloud spanner operations describe projects/my-project/instances/test-instance/databases/test-db/operations/_auto_12345

--output:
name: projects/my-project/instances/test-instance/databases/test-db/operations/_auto_12345
metadata:
  '@type': type.googleapis.com/google.spanner.admin.database.v1.OptimizeRestoredDatabaseMetadata
  name: projects/my-project/instances/test-instance/databases/test-db
  progress:
    progress_percent: 45
  start_time: '2023-10-01T10:00:00Z'
  end_time: '2023-10-01T10:15:00Z'
done: false
error: null

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  OrderDate DATE NOT NULL,
  TotalAmount NUMERIC NOT NULL,
) PRIMARY KEY (OrderID, CustomerID),
  INTERLEAVE IN PARENT Customers ON DELETE CASCADE;

Query: SELECT p.*, o.* FROM products p JOIN orders o ON p.product_id = o.product_id WHERE o.order_date BETWEEN '2023-01-01' AND '2023-01-31';
Time: 12.345 sec
Rows examined: 1 million
Rows sent: 5000

gcloud spanner databases execute-sql my-db --sql="SELECT * FROM Users WHERE Email = 'test@example.com'"
---
Output: (empty)

Refer to the exhibit.

```
Query: SELECT * FROM Orders WHERE customer_id = @c1 AND order_date > @d1
Total execution count: 1000
Average CPU time: 50ms
Average latency: 200ms
Rows scanned: 100000
Rows returned: 10
```

Refer to the exhibit.

```
Cloud SQL for MySQL instance configuration:
max_connections: 1000
innodb_buffer_pool_size: 26843545600 (25GB)
query_cache_type: 1
query_cache_size: 1048576 (1MB)
```

Refer to the exhibit.

```
Cloud Monitoring alert policy condition:
metric.type="logging.googleapis.com/user/log-entry-count"
resource.type="cloud_sql_database"
condition_threshold:
  comparison: COMPARISON_GT
  threshold_value: 100
  duration: 60s
```

Refer to the exhibit.

```
Cloud SQL Query Insight output:
Query ID: 1234
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND status = 'SHIPPED';
Average latency: 3.5s
Total executions: 10000
No index used (sequential scan on orders table, size 50 GB)
```

Refer to the exhibit.

```
# Cloud Monitoring alert policy configuration
condition:
  conditionThreshold:
    filter: metric.type="spanner.googleapis.com/instance/node_cpu_utilization"
           resource.type="spanner_instance"
           resource.label.instance_id="my-spanner-instance"
    aggregations:
    - alignmentPeriod: 60s
      perSeriesAligner: ALIGN_MEAN
    - crossSeriesReducer: REDUCE_MEAN
    duration: 300s
    comparison: COMPARISON_GT
    thresholdValue: 0.7
```

Refer to the exhibit.

```
# Bigtable cluster metrics
Cluster: my-cluster
Table: events
CPU utilization: 85%
Storage utilization: 70%
Replication latency (average): 15ms
Current nodes: 30
Recommended nodes: 25
Table load: uniform
```

Refer to the exhibit. Cloud Spanner query statistics:

Query: SELECT customer_id, order_total FROM orders WHERE status = 'PENDING'
Execution time: 4.2 seconds
Rows returned: 500
Rows scanned: 5,000,000
Nodes: 2
Execution plan: Full table scan