Got it, let's break this down for you—monitoring Cloud SQL programmatically is totally doable, you just need to use the right Google Cloud services and APIs. Here's exactly how to tackle both your metrics and logging needs:

All the metrics you see in the Cloud SQL console (CPU utilization, storage usage, active connections, etc.) are exposed through Cloud Monitoring (formerly Stackdriver Monitoring). Here's how to access them programmatically:

• Use the Cloud Monitoring API to query these metrics. Key metric types to know:
  • CPU utilization: cloudsql.googleapis.com/database/cpu/utilization
  • Used storage: cloudsql.googleapis.com/database/storage/used
  • Active connections: cloudsql.googleapis.com/database/connections/active
• Test first with the gcloud CLI to get familiar with available metrics:

  gcloud monitoring metrics list --filter="metric.type:cloudsql.googleapis.com/*"
  

• For code implementations, Google provides official client libraries for Python, Go, Java, and more. For example, with Python's google-cloud-monitoring library, you can build a time-series query targeting your specific SQL instance, define a time range, and pull raw metric data for analysis.
• Don't forget permissions: Your service account needs the Monitoring Viewer or Monitoring Editor IAM role to call this API.

Cloud SQL routes all logs to Cloud Logging (formerly Stackdriver Logging), which you can access via its API or client libraries.

Error Logs #

• Error logs for your instance are automatically sent to Cloud Logging. Use a filter like this to target them (adjust based on your database engine):
  • MySQL: resource.type="cloudsql_database" AND logName="projects/[PROJECT_ID]/logs/cloudsql.googleapis.com%2Fmysql.err"
  • PostgreSQL: resource.type="cloudsql_database" AND logName="projects/[PROJECT_ID]/logs/cloudsql.googleapis.com%2Fpostgresql.log"
• Programmatically, use the Cloud Logging API or client libraries (e.g., Python's google-cloud-logging) to fetch log entries, filter by severity, or set up alerts for critical errors like connection failures or query crashes.

Query Logs (Slow/General Queries) #

First, you need to enable these logs in your Cloud SQL instance:

1. Go to your instance's edit page in the Cloud Console.
2. Under "Database flags", add the relevant flags based on your engine:
   • MySQL: Set slow_query_log=1 (for slow queries) and/or general_log=1 (for all queries), plus log_output=FILE (Cloud SQL syncs these files to Logging automatically).
   • PostgreSQL: Enable log_statement (e.g., log_statement=all for all queries) or log_min_duration_statement (to capture queries exceeding a specific time threshold).

• Once enabled, these logs appear in Cloud Logging with specific log names:
  • MySQL slow queries: cloudsql.googleapis.com%2Fmysql-slow.log
  • MySQL general queries: cloudsql.googleapis.com%2Fmysql.general.log
• Use the Cloud Logging API to pull these logs, or set up logging sinks to export them to BigQuery for deeper analysis (e.g., identifying frequent slow queries that impact performance).

• Alerting Policies: Skip writing custom code and use Cloud Monitoring's built-in alerting. Set rules for CPU spikes, low storage, or critical errors, and get notifications via email, Slack, or SMS directly from Google Cloud.
• Logging Sinks: Export logs to BigQuery, Cloud Storage, or Pub/Sub. For example, export slow queries to BigQuery and run scheduled queries to generate weekly performance reports.
• gcloud CLI Scripts: For simple monitoring workflows, use the gcloud CLI in shell scripts. Example to fetch recent error logs:

  gcloud logging read 'resource.type="cloudsql_database" AND severity>=ERROR' --limit=20 --format=json
  

内容的提问来源于stack exchange，提问作者Saksham Ahuja