Score:1

GCP log Explorer and slow SQL query log with Cloud SQL

kp flag
Tim

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

Score:0
in flag

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

Score:0
de flag

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.

Tim avatar
kp flag
Tim
I tried that article, but it was outdated. The metric part was changed on google after that article has been published and could not solve problems on that... I try it again, but more detailed instructions would be beneficial.
Mabel A. avatar
de flag
@Timo can you let me know which step specifically has been changed? I'll try to help you with that.
Tim avatar
kp flag
Tim
I have slow queries enabled and logs are on log explorer. The article instructions are working only if queries are short as they are on example. As soon as I filter with 'textPayload:("SELECT" OR "INSERT" OR "UPDATE" OR "CREATE" OR "DELETE")' I lose all long queries and see only the tip of my queries on log explorer. Most of slow queries we have are quite big queries and they are multiple lines on log explorer.
Tim avatar
kp flag
Tim
And if I continue with that article, and try to visualize (using regular expressions) it, then it goes wrong... At this point the instructions are outdated, you can see differences in screenshots.
Mabel A. avatar
de flag
@Timo losing long queries in the log explorer is an odd behavior. I suggest you open a [support case](https://cloud.google.com/support-hub) for further investigation.
Tim avatar
kp flag
Tim
I am actually not losing those. Those are wrapped to multiple lines. And because those are on multiple lines I can not use the article shown here. Do you know that long queries should not be divided into multiple lines at all and I should raise a support ticket?
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.