Home / server / Questions / 1094307
In Process
Tim
Asked: 2022-02-22 01:12:26 +0800 CST

GCP log Explorer and slow SQL query log with Cloud SQL

  • 772

Is there a way to analyze SQL Slow query logs easily on log explorer or on some other GCP tool? Now log explorer writes log entries to multiple lines (textPayload divided) and it's hard to find out those slow performing queries.

So far I managed to get this, by help of article here (I think that article is outdated): enter image description here

logging google-cloud-platform google-cloud-sql

2 Answers

  1. To easily analyze query logs, you can create a log-based metrics with the use of Cloud Monitoring.

    Logs-based metrics are Cloud Monitoring metrics that are based on the content of log entries. For example, metrics can record the number of log entries containing particular messages, or they can extract latency information reported in log entries.

    Basically, you would need to do the following:

    1. Start by filtering the Cloud Logging view to match the logs you want to measure

    2. Create the log-based metric

    3. Generate new data and view the new metric

    Note: Metrics only start recording data after they have been created.

    1. Visualize slow query logs with Cloud Monitoring. You can make the metric more useful by parsing the slow SQL statements to be metric labels, which are visible in the dashboard legend in Cloud Monitoring.

    Check this article for a more detailed steps.

    • 0

  2. I reproduced your issue and yes you are correct, Cloud SQL MySQL log (general.log, slow.log) in Log Explorer shows up spitted by newline into multiple records and does not show the long queries as one record.

    Reason : This is because there's a newline character in the input Query that is set to Cloud SQL. If the input query is single-line (does not contain \n), the output will be one log event, showing as one line in Log Explorer. If the input query is multi-line (contains \n), the output will be multiple log events and showing up as multiple logs. the records that they see in the general log are split into multiple records.

    enter image description here

    To let you know, this issue is already known to Google. There are no ETAs for its implementation. All communication/updates from the Cloud SQL Team regarding this feature request will be posted here or here

    • 0