How to get MySQL slow query log in its 'native' format from a Cloud SQL instance?

[Vitaly Karasik]

I used to use pt-query-digest and similar utilities for producing reports based on MySQL slow query log.

But AFAIK all these tools know to process only 'native' MySQL log format.

Is there a way to get a log in such format using some "gcloud logging read"  parameters? Or using other methods, besides writing a script by myself?

Thanks,

Vitaly

[Harmit Rishi (Cloud Platform Support)]

Hi, 

Not entirely sure how you would use the "gcloud logging read" command for retrieving the requested logs. However, I was able to find out that the flag that you are referring to is included in the Cloud SQL database configuration flag list here. Additionally, you can follow the instructions provided here for the log_output flag. You can either do the following: 

1. log_output : FILE  (this is will make the log output of the slow_query_log available in the Logs viewer section of your Cloud Platform Console.)

2. log_output : TABLE (by setting the output to TABLE, the output would be placed in a table inside your database. Note: if the table gets too large, it may cause the instance to lose SLA coverage)

Either way, would you be able to clarify what you mean by "only 'native' MySQL log format"? Our export system of logs currently allows JSON/CSV format at the moment. Would these formats suffice for your use case? 

If the product does not provide the format options you are seeking, you can always feel free to file a Feature Request of the desired export format to the engineering team. Also, if you feel this is a limitation of Cloud SQL, you can always use a Compute Engine instance to run MySQL. This will essentially run the database on a VM instance which in return will allow you more control on the overall configuration of how you want your database to run.   

I hope this helps!

[linux.il]

I already activated slow query log. My issue that I cannot fetch it in its original format. I guess I should open a feature request.

Please see below slow query log example:

# Time: 140905  6:33:11

# User@Host: dbuser[dbname] @ hostname [1.2.3.4]

# Query_time: 0.116250  Lock_time: 0.000035 Rows_sent: 0  Rows_examined: 20878

use dbname;

SET timestamp=1409898791;

...SLOW QUERY HERE...

On Thu, May 23, 2019 at 2:49 AM 'Harmit Rishi (Cloud Platform Support)' via Google Cloud SQL discuss <google-cloud...@googlegroups.com> wrote:

Hi, 

Not entirely sure how you would use the "gcloud logging read" command for retrieving the requested logs. However, I was able to find out that the flag that you are referring to is included in the Cloud SQL database configuration flag list here. Additionally, you can follow the instructions provided here for the log_output flag. You can either do the following: 

1. log_output : FILE  (this is will make the log output of the slow_query_log available in the Logs viewer section of your Cloud Platform Console.)

2. log_output : TABLE (by setting the output to FILE, the output would be placed in a table inside your database. Note: if the table gets too large, it may cause the instance to lose SLA coverage)

Either way, would you be able to clarify what you mean by "only 'native' MySQL log format"? Our export system of logs currently allows JSON/CSV format at the moment. Would these formats suffice for your use case? 

If the product does not provide the format options you are seeking, you can always feel free to file a Feature Request of the desired export format to the engineering team. Also, if you feel this is a limitation of Cloud SQL, you can always use a Compute Engine instance to run MySQL. This will essentially run the database on a VM instance which in return will allow you more control on the overall configuration of how you want your database to run.   

I hope this helps!

On Tuesday, May 21, 2019 at 12:46:09 PM UTC-4, Vitaly Karasik wrote:

I used to use pt-query-digest and similar utilities for producing reports based on MySQL slow query log.

But AFAIK all these tools know to process only 'native' MySQL log format.

Is there a way to get a log in such format using some "gcloud logging read"  parameters? Or using other methods, besides writing a script by myself?

Thanks,

Vitaly

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/1f648b89-ac7b-4f64-8b52-bb3160c7372d%40googlegroups.com.

[Harmit Rishi (Cloud Platform Support)]

It seems you have successfully filed a Feature Request for this here. Additionally, I can see that it has been processed and sent to the Cloud SQL engineering team for investigation. 

All further updates regarding this issue will occur there. Currently, there is no ETA mentioned by the team.

Thanks!