gcp cloud sql query insight - ghdrako/doc_snipets GitHub Wiki

• https://www.dbi-services.com/blog/google_insights/
• https://cloud.google.com/sql/docs/postgres/using-query-insights
• https://cloud.google.com/blog/topics/developers-practitioners/boost-your-query-performance-troubleshooting-skills-cloud-sql-insights
• https://cloud.google.com/blog/topics/developers-practitioners/introducing-sqlcommenter-open-source-orm-auto-instrumentation-library
• https://google.github.io/sqlcommenter/#support
• https://github.com/google/sqlcommenter
• https://codelabs.developers.google.com/codelabs/cloud-sql-insights-intro#0

There's no additional cost for Query insights. You can access one week of data on the Query insights dashboard. Query insights doesn't occupy any storage space in your Cloud SQL instance storage space. Metrics are stored in Cloud Monitoring.

IAM permissions

To view a query plan or perform end-to-end tracing, you need specific IAM permissions. Create a custom role and add the cloudtrace.traces.get IAM permission to it. Then, add this role to each user account that needs to use Query insights.

gcloud sql instances patch INSTANCE_ID --insights-config-query-insights-enabled

• --insights-config-record-client-address
• --insights-config-record-application-tags
• --insights-config-query-string-length - default 1024 bytes
• --query_plans_per_minut - default 5 per minute

gcloud sql instances patch INSTANCE_ID --no-insights-config-query-insights-enabled

in tf:

query_insights_enabled      = "true"
record_application_tags     = "true"
record_client_address       = "true"

Does this Google Insight use pg_stat_statement? pgSentinel? pg_stat_monitor? pg_stat_plans? or another of the many contributions from the PostgreSQL community?

Actually, those two Google extensions are installed in Cloud SQL PostgreSQL:

postgres=> SELECT * FROM pg_available_extensions where comment like '%Google%';
      name       | default_version | installed_version |                comment
-----------------+-----------------+-------------------+----------------------------------------
 cloudsql_stat   | 1.0             |                   | Google Cloud SQL statistics extension
 google_insights | 1.0             |                   | Google extension for database insights

Query insights doesn't occupy any storage space in your Cloud SQL instance storage space. Metrics are stored in Cloud Monitoring. For API requests, see the Cloud Monitoring Pricing. Cloud Monitoring has a tier that you can use at no additional cost.

The google_insights is the one used for this feature and provides some table functions, like: google_insights_aggregated_stats(), google_insights_consume_query_plans(), google_insights_get_internal_metrics(), google_insights_get_internal_metrics(), google_insights_get_query_string_length_distribution(), google_insights_query_stats(), google_insights_reset_all(), google_insights_statistics(), google_insights_tag_stats() and it reads some of those statistics: rows, shared_blks_hit, shared_blks_read, execution_time, write_time, read_time, wait_time_lwlock, wait_time_lock, wait_time_buffer_pin

Looking at this, there is a hope that shared_blks_hit and shared_blks_read will be exposed in the future. They are the most important metrics when comparing two executions, because time depend on too many other parameters.

Tags

Developers can use tags to associate queries with specific business functions, such as payment, inventory, business analytics, and shipping. Developers can then quickly evaluate the database load contributed by specific microservices or user flows, for example.

Developers simply add tags to their application code:

UPDATE "payment" SET "balance" = ("payment"."balance" + ?) WHERE "payment"."name"::text LIKE ? /* application='rideshare',controller='payment_charge',route='demo/charge'*/

ORMs are used in most applications to simplify database query development. One of the drawbacks of ORMs is that they can generate inefficient queries that are very difficult to diagnose. Insights provides SQL Commenter, an open-source library that auto-instruments your ORMs to help identify which application code is causing problems. Once installed, SQL Commenter automatically tags queries, alleviating the need to make any changes to application code. SQL Commenter supports many popular ORMs such as Hibernate, Spring, Express, Django, Flask, and others.