Configure database monitoring and troubleshooting options

Assessing slow queries, locking, and missing indexes means tracing why reads are slow and whether contention or a missing index is the cause. Slow reads increase input/output (I/O), raise latency, and can overload a primary instance and its replicas. The work flows from latency measurement to query review to lock and storage checks, so each layer either explains the delay or points to the next layer.

1.1Find Missing Indexes From Slow Query Patterns

A missing index is found by comparing the fields used for filtering and sorting in slow queries with the indexes defined on the schema. In Firestore, a query that needs a composite index returns an error with a link to create the required index. A Firestore composite index names a collectionGroup, a queryScope of COLLECTION, and an ordered list of fields with a fieldPath and an order of ASCENDING or DESCENDING.

The order of fields in the definition must match the query filter and sort, including nested paths such as price.currency. For sharded time-series queries, each pattern needs its own index that leads with the shard key and ends with the timestamp, such as shard plus exchange plus timestamp. New definitions are added to the indexes array in the index file and deployed with firebase deploy --only firestore:indexes, after which reads flow from the query to the pre-sorted index entries.

1.2Isolate Latency and Replace Blocking Operations

Isolating latency determines whether delay comes from the database instance, the client, or the network between them. For Memorystore for Valkey (opens in a new tab), the test runs from a Compute Engine virtual machine (VM) in the same region and virtual private cloud (VPC) network as the instance. The valkey-cli tool pings the server for a few minutes, and an average of 1 millisecond or less means the instance is healthy.

Run the isolation test in order:

1. Connect to test VM: Connect to a Compute Engine VM in the same region and VPC network as the instance.
2. Install test tool: Install valkey-tools with apt-get or yum depending on the operating system.
3. Start latency test: Run redis-cli --latency against the endpoint address and port, adding transport layer security (TLS) options when needed.
4. Collect samples: Let the command ping the server for a few minutes.
5. Stop and read results: Stop with Ctrl+C and compare minimum, maximum, and average latency to the 1-millisecond baseline.

If the instance test is fast but the application is still slow, the delay sits outside the instance in cross-region distance, high client central processing unit (CPU) or memory use, exhausted connection pools, or application logic. On the instance itself, replace operations that block the single Valkey main thread, use SCAN instead of KEYS to iterate the keyspace, use read replicas when slightly stale data is acceptable, set expiration times, and check audit logs for changed access patterns.

1.3Resolve Contention With Concurrency Controls and Storage Checks

Data contention in Firestore occurs when a transaction needs documents it read to stay consistent while another operation tries to update the same documents. Firestore resolves contention by delaying or failing one operation and automatically retrying transactions, then returning ABORTED: Too much contention on these documents after a finite number of retries. Firestore guarantees serializable isolation by commit time, so transactions behave as if they ran in series ordered by commit time even while they execute in parallel.

The two concurrency modes differ in how they protect reads:

Server client libraries follow the database setting, with Standard edition defaulting to PESSIMISTIC and Enterprise edition defaulting to OPTIMISTIC. Mobile and web software development kits (SDKs) always emulate optimistic concurrency and do not use built-in transactions, even when the database is set to PESSIMISTIC. Within a transaction, reads must come before writes and reads see the version as of commit time, not the transaction's own prior writes. The mode can be viewed with gcloud firestore databases describe and changed with gcloud firestore databases update --concurrency-mode. When slow behavior remains after indexing and contention checks, confirm underlying disk and memory pressure with slow-log entries, high p99 disk timings, memory use near 80%, and rejected-connection counts.

Monitoring database vitals means tracking compute, memory, storage, and audit signals to keep instances healthy and explainable. Cloud SQL (opens in a new tab) telemetry flows from built-in agents and the Ops Agent into Cloud Monitoring, where Overview, CPU, Memory, Network, and Disk views support dashboards and alerts. Teams use the same signals to right-size instances, grow storage, and answer who changed what and when.

2.1Track CPU and Memory to Find Bottlenecks

CPU Utilization is the percent of virtual CPU used by the instance, and Memory Utilization is the percent of memory used excluding disk caches and, on Linux, kernel memory. Sustained high values mean the workload is pressing against capacity, while consistently low values mean the instance is overprovisioned. Full memory and disk-space signals require the Ops Agent on the virtual machine (VM), because default metrics do not show complete behavior.

High CPU slows queries and should trigger root-cause analysis for poorly written queries, long-running transactions, and many concurrent connections. Query Insights shows which queries consume the most CPU with visual plans and tracing, while pg_stat_activity and per-process tools isolate long sessions and blocking states such as active, idle, and idle in transaction. For memory, a rising Number of Reads served from disk suggests the working set no longer fits in cache, even though managed instances also write to a system table about every second when idle.

2.2Measure Throughput, IOPS, and Capacity

Storage performance is judged from throughput, input/output operations per second (IOPS), I/O size, queue depth, and disk-space utilization. Disk Throughput is the average bytes read and written, Disk IOPS is the average operations rate, I/O Size Avg is the average operation size, and Queue Length Avg is queued plus running operations. A full disk can halt writes, so disk-space utilization must be watched continuously.

Small 4 to 16 KiB random operations are usually limited by IOPS, while large 256 KiB to 1 MiB sequential operations are usually limited by throughput. To reach disk limits, use high queue depth. For Cloud SQL instances, disk performance depends on disk size and virtual CPU count, while for Hyperdisk volumes it depends on provisioned IOPS, throughput, size, and the attached machine type. Choose solid-state drive (SSD) for most databases where consistent performance matters, and choose hard disk drive (HDD) only for very large 10 TB or larger, infrequently accessed, latency-tolerant datasets.

Capacity work combines automatic growth with cleanup:

• Enable automatic storage increases but keep buffer because workloads grow over time.
• Audit attached disks so they are not over-provisioned.
• Use snapshots only when needed and delete old snapshots.
• Apply retention policies and remove unneeded tables, archiving to Cloud Storage when required.

2.3Use Audit Logs to Explain Changes and Access

Cloud Audit Logs record activity when projects, Cloud SQL instances, and service accounts are created, changed, or accessed. Admin Activity logs record configuration changes and are always written and cannot be disabled. Data Access logs record reads and writes of user data and must be explicitly enabled because of volume. System Event logs record Google-initiated actions such as maintenance, and Policy Denied logs record denials caused by security policy.

An investigation of administrative changes follows an ordered workflow:

1. Select scope: Open Logs Explorer and select the project under investigation.
2. Build query: Filter on log name, resource type, and methods such as SetIamPolicy and service-account creation.
3. Run query: Execute the query to return matching Admin Activity entries.
4. Correlate: Compare actor, timestamp, and resource against the incident window or production change.

Cloud Logging retains logs for a default period, so longer history requires a log sink that exports copies to BigQuery or another destination for aggregate analysis. For Cloud SQL for PostgreSQL, flags for log checkpoints and log temp files extend visibility into I/O causes, while firewall, Virtual Private Cloud (VPC) flow, and Domain Name System (DNS) logging complete the network picture. Centralized dashboards combine these logs with RAM, CPU, storage, and I/O metrics and Query Insights for expensive-query analysis.

Monitoring and updating quotas keeps multi-technology database solutions running by matching limits to real demand. A quota sets the maximum amount of a resource a project, folder, or organization can use, such as virtual machines (VMs) or API calls per minute. When quota runs out, tasks fail, so teams monitor use, optimize efficiency, and justify increases with data.

3.1Compare Allocation, Rate, and Concurrent Quota Types

Allocation, rate, and concurrent quotas restrict different kinds of consumption and recover in different ways. Allocation quotas limit how much of a resource is held at one time, such as 100 VMs, and capacity returns only when unused resources are deleted. Rate quotas limit how many operations occur in a time window, such as 1,000 API requests per day, and refill when the window resets. Concurrent quotas limit simultaneously running long operations, such as Compute Engine inserts that can run up to one hour.

Scope adds a second dimension. Project-level quotas apply to one project, folder-level quotas are shared by child folders and projects, and organization-level quotas are shared across the organization. Location adds a third dimension: global quotas are shared across all regions and zones, regional quotas are shared within one region, and zonal quotas apply within one zone. When a resource has both regional and zonal quotas, use in one zone consumes both. A quota sets a maximum that can be created if capacity exists, but it does not guarantee capacity in that region or zone.

3.2Request Increases With Evidence and Optimization

A quota adjustment request asks to raise a quota for a specific project, folder, or organization. Requests can be made through the Google Cloud console, through a support case, or with an account team or Technical Account Manager (TAM). The console path needs a role such as Project Owner or Editor, Quota Administrator, or a custom role with serviceusage.quotas.update, but no paid Customer Care plan is required.

To request in the console, follow the order:

1. Open Quotas page: Go to Quotas and System Limits and filter for the quota.
2. Select quotas: Select one or more quotas to change.
3. Start edit: Click Edit quotas.
4. Enter contact: Provide name, email, and phone, then continue.
5. Describe need: Enter requested value and justification.
6. Submit: Submit for review.

Most console requests take 24 to 48 hours, while large increases can take 10 business days or more. Automated systems review most requests for availability and history, with some escalated to humans; denials can be pursued via support or the account team. Strong justification shows consumption proof such as quota exceeded errors, future growth and 24-hour patterns, region or zone spread, and related storage or networking needs. Before and after requesting, optimize with exponential backoff, client-side rate limiting, capacity reservations, and steady low-volume patterns instead of sharp bursts.

3.3Apply Automatic Adjustments and Cloud SQL Rate Rules

The quota adjuster (opens in a new tab) is a Google Cloud system that proactively requests increases when peak use nears the limit. When enabled, the adjuster checks consumption over a duration and typically raises the value by 10 to 20 percent if the increase is possible. The adjuster only increases values, never lowers them, does not act on manually capped quotas, covers only some services, and needs enough history for accurate prediction.

Cloud SQL Admin API rate quotas control Admin API calls from the console, gcloud CLI, service accounts, and connectors, not database queries. The quotas refill over 60-second intervals, and exceeding a limit returns HTTP 429 with rateLimitExceeded. Connect, Get, List, Mutate, and Default per region limits are per minute per user per region, while the Default limit is per minute per user globally. Daily Cloud SQL resource quotas replenish at midnight Pacific Time. Limits differ from quotas because limits are not replenished and are not shown on the Quotas page.

3.4Watch Usage With Dashboards and Quota Alerts

The Cloud Quotas dashboard in IAM and Admin shows current usage, limit, and percent consumed for project-level quotas, with display names and lowercase metric names for monitoring. Values refresh once per day, so changes can take up to 24 hours to appear. Cloud Monitoring extends the view with Consumer Quota metrics, Metrics Explorer charts, and alerting policies that notify email, SMS, or Pub/Sub.

Basic alerts support only project-level quotas. Services such as Compute Engine, Dataflow, Spanner, and Pub/Sub commonly support quota metrics, while App Engine and Cloud SQL commonly do not. Because samples can arrive daily, use stacked bar charts over at least one week for daily metrics, and set thresholds with lead time rather than expecting real-time signals.

Investigating resource contention means finding where concurrent work competes for the same data, connections, CPU, memory, or disk. Contention appears as hotspots, blocking sessions, timeouts, and throughput that stops scaling even when total capacity looks large. Diagnosis flows from load signals to the hot split or blocked session, then to scaling or tuning.

4.1Detect Hotspots in Distributed Data

A distributed hotspot is an unbalanced partition that receives a disproportionate share of reads or writes, so one server task is hot while others stay idle. Sharding splits a database by criteria such as customer or subject to scale reads and writes horizontally, but a poor key choice concentrates work on one shard. In Firestore (opens in a new tab), sequential timestamp indexes can concentrate index writes on one tablet, while a sharded key spreads entries across tablets.

Cloud Spanner distributes compute across zones in its instance configuration, with one server task per zone up to 1000 processing units (PUs) and one task per 1000 PUs above that. Storage follows compute: 1024.0 GiB per 100 PUs below 1 node and 10 TiB per node at 1 node and larger, billed only for storage used. Poor schema choices such as monotonically increasing keys or one heavily updated table concentrate work on a few splits. Detection starts with the Query insights dashboard for total and per-query CPU seconds per minute and the TopN table limited to 100 entries, then uses SPANNER_SYS.SPLIT_STATS_TOP_MINUTE ordered by CPU_USAGE_SCORE to rank the hottest splits.

4.2Diagnose Locking That Blocks Transactions

Locking controls how concurrent transactions share rows and documents without corrupting data. A transaction holds locks until it finishes, so others that need the same records must wait, which appears as blocking sessions, higher latency, and timeouts. The Cloud Healthcare API FHIR server shows the pattern when parallel bundles update the same Patient resource: the first bundle locks the resource, others wait, and the server load-sheds with 429 Resource Exhausted operation_too_costly, throttling all create, read, update, and delete (CRUD) operations in the project.

The contention follows an ordered sequence:

1. Lock acquisition: A transaction bundle locks the target resource until it finishes.
2. Parallel contention: Multiple bundles target the same resource and diagnostics mention the resource type.
3. Load shedding: The server returns 429 throttling that affects the whole project, not just the hot resource.

Throughput under lock contention flattens instead of rising with clients, which distinguishes blocking from CPU, memory, or storage shortage. Diagnosis searches Cloud Logging for operation_too_costly, reviews slow queries and plans for missing indexes and waits, and checks pg_stat_activity by state and user. Lock capacity follows max_locks_per_transaction times max_connections plus max_prepared_transactions, with a default of 64 locks per transaction. Fixes shorten lock hold time with small transaction bundles, separate independent resources, use moderate batching, add supporting indexes, remove unneeded indexes and foreign keys, and perform vacuum, statistics, and reindex maintenance.

4.3Separate CPU, I/O, and Memory Bottlenecks

System bottlenecks share slow responsiveness but differ in metric signature. Google Cloud Monitoring exposes resource metrics while Query Insights shows database load as CPU and CPU Wait, IO Wait, and Lock Wait against CPU capacity. CPU and CPU Wait includes time using the CPU plus time waiting for the Linux scheduler, so load above the capacity line means queries are queued for CPU.

Memory risk in Cloud SQL for PostgreSQL uses database/memory/components.usage with a 90% alert threshold; cache plus free below 10% combined signals high out-of-memory risk. A common pattern is high work_mem with many active connections. Connection exhaustion itself can exhaust CPU and memory, hide metrics, and cause timeouts, so bounded pooling matters. Pinpointing follows an order: confirm elevated load, establish duration over a week, identify the dominant wait category, filter by database or user or tag, then open Top queries where normalized digests group similar statements. Immediate relief often comes from scaling up disk throughput, virtual CPU, and RAM, while long-term fixes add read replicas, partitioning, archiving, or sharding.

Setting up alerts connects database signals to people and tools so issues trigger timely response. Log-based alerts catch error patterns in logs, metric-based policies catch numeric exhaustion, and notification channels and incident workflows carry the alert to ownership and closure. Data flows from Cloud SQL (opens in a new tab) instances and Cloud Logging into Cloud Monitoring incidents and notifications.

5.1Catch Failures With Log-Based Alerting

Log-based alerting watches the log stream for a matching filter and opens an alert when a database error, failed execution, or system failure appears. Log data flows from Cloud Logging to a Cloud Monitoring alerting policy, which forwards notifications to channels such as email, SMS, or PagerDuty. Multiple channels should be configured so one channel failure does not block a critical notice.

The policy evaluates only included logs at the Google Cloud project level and cannot filter by log buckets or other resources such as billing accounts or organizations. Filters must use correct Logging query-language field names and regular expressions. Validate the query in Logs Explorer or with Preview logs before saving, tighten the query if it creates noise, and check the Alerts page by policy name if it creates nothing. A policy is limited to 2 new alerts per minute and 20 alerts and 20 notifications per day, with the daily limit resetting at a random time. SQL-based alerting with Observability Analytics instead queries a linked BigQuery dataset from an upgraded log bucket, without a WHERE timestamp filter, using the Monitoring Service Account with Monitoring Editor, Logging SqlAlertWriter, Monitoring Service Agent, and BigQuery Data Viewer roles.

5.2Define Metric Thresholds and Alert Conditions

Metric-based policies watch numeric Cloud SQL signals and open an incident when a signal crosses a threshold for a duration. Core signals cover CPU utilization, memory usage and total_usage, disk utilization and available space, and disk throughput and IOPS. The usage metric shows database-process memory while total_usage adds operating-system cache, so comparing them shows reclaimable cache.

A condition combines metric, threshold comparison, and duration so brief spikes do not page on-call staff while sustained breaches signal overload or tuning needs such as missing indexes or stale statistics. The policy also defines auto-close behavior when metric data stops. Finalizing adds Documentation with runbook steps, a unique Alert name naming database, metric, and threshold, and Create Policy to start evaluation. Changing memory size requires a restart, and an instance at maximum memory must be sharded.

5.3Route Notifications Through Incident Lifecycle

Notification and incident management connects detection to triage, investigation, and resolution. Procedures assign incident commanders, investigators, communicators, and experts, define communication and escalation paths, and document steps, tools, and contacts in a runbook that is regularly reviewed. A centralized system consolidates incident data for visibility, coordination, and clear ownership.

Investigation starts on the Incident details page, where the Logs pane shows matching entries, Labels identify the monitored resource, Documentation shows mitigation tips, and Related incidents reveal shared causes. Do not modify the alerting policy while working with support because precomputed incident data is discarded. Incidents move in order:

1. Open: Condition met and incident still open; repeat matches do not open a new incident.
2. Acknowledged: Open incident manually marked as under investigation.
3. Closed: Manually closed or automatically closed after the auto-close period.

Snoozing stops new incidents and notifications during the period but leaves open incidents open until auto-close, which defaults to 7 days with a 30-minute minimum.

Key Facts

• A missing Firestore composite index returns an error with a creation link, and new indexes are deployed with firebase deploy --only firestore:indexes.
• Memorystore for Valkey is healthy when latency from a same-region, same-VPC Compute Engine VM averages 1 millisecond or less, and SCAN must be used instead of KEYS to avoid blocking the main thread.
• Admin Activity audit logs are always written and cannot be disabled, while Data Access audit logs must be explicitly enabled.
• A quota sets a maximum that can be created if capacity exists but does not guarantee capacity, with per-day quotas resetting at midnight Pacific Time and Cloud SQL Admin API rate quotas refilling over 60-second intervals.
• Cloud Monitoring incidents move in order through Open, Acknowledged, and Closed, with log-based policies limited to 2 alerts per minute and 20 alerts and notifications per day.