How can I use the statistics of all queries performed in MySQL flexible server ?

Question

How can I use the statistics of all queries performed in MySQL flexible server ?

안예리 41

Hello.

I am using Azure Database for MySQL flexible server.
I would like to monitor the statistics (time of execution, I/O, etc.) of all queries performed in MySQL DBMS.

Installed MySQL Server, the query was saved when the performance_schema server parameter was turned on. However, it does not work in MySQL flexible server, and there is no user permission.

NAME |ENABLED|

--------------------------------
+-------+
events_stages_current |NO |
events_stages_history |NO |
events_stages_history_long |NO |
events_statements_cpu |NO |
events_statements_current |NO |
events_statements_history |NO |
events_statements_history_long |NO |
events_transactions_current |NO |
events_transactions_history |NO |
events_transactions_history_long|NO |
events_waits_current |NO |
events_waits_history |NO |
events_waits_history_long |NO |
global_instrumentation |NO |
thread_instrumentation |NO |
statements_digest |NO |

However, the Azure Database for MySQL - Single Server service has a Query Store.
It is described in the page below.
https://learn.microsoft.com/en-us/azure/mysql/single-server/concepts-performance-recommendations

Question number one:
Is the Query Store function also in MySQL flexible server?

Question number two:
is it possible to update the performance_schema.setup_consumers table so that the query is saved by setting it to performance_schema on?

If there is no both, does it not provide the function to store all query statistics (as well as slow queries) performed?

SSingh-MSFT 16,371 Reputation points Moderator

2022-10-12T10:41:01.823+00:00

Hi

Welcome to Microsoft Q&A platform and thanks for using Azure Services.

In order to use the statistics of all queries performed in MySQL flexible server, here is the detailed standard documentation tutorial-query-performance-insights which might help.

Regarding your questions about Query Store and performance_schema, I am checking and will get back to you.

Thanks.
안예리 41 Reputation points

2022-10-14T02:13:32.363+00:00

related performance_schema sever setting are here.

this is default value in azure mysql flexiable server.

performance_schema=on
performance_schema_consumer_global_instrumentation=yes
performance_schema_consumer_statements_digest=yes
performance_schema_consumer_thread_instrumentation=yes
performance_schema_consumer_events_transactions_history=yes
performance_schema_consumer_events_transactions_current=yes
performance_schema_consumer_events_statements_history=yes
performance_schema_consumer_events_statements_current=yes
performance_schema_max_digest_length=1024

but it does'nt have any data;
--
select * from performance_schema.events_statements_history;
select * from performance_schema.events_statements_current esc;
select * from performance_schema.events_transactions_current etc ;
select * from performance_schema.events_transactions_history eth ;
SSingh-MSFT 16,371 Reputation points Moderator

2022-10-14T15:16:31.433+00:00

Hi @ 72729183 ,

I am yet to get reply from the internal team about this.
I will get back to you.

Thanks for your patience.
SSingh-MSFT 16,371 Reputation points Moderator

2022-11-02T06:05:08.147+00:00

Hi @ 72729183 ,

Following up to see if the suggestion was helpful. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Accepted answer

0 additional answers

Your answer

SSingh-MSFT 16,371 Reputation points Moderator

2022-10-12T10:41:01.823+00:00

Hi

Welcome to Microsoft Q&A platform and thanks for using Azure Services.

In order to use the statistics of all queries performed in MySQL flexible server, here is the detailed standard documentation tutorial-query-performance-insights which might help.

Regarding your questions about Query Store and performance_schema, I am checking and will get back to you.

Thanks.
안예리 41 Reputation points

2022-10-14T02:13:32.363+00:00

related performance_schema sever setting are here.

this is default value in azure mysql flexiable server.

performance_schema=on
performance_schema_consumer_global_instrumentation=yes
performance_schema_consumer_statements_digest=yes
performance_schema_consumer_thread_instrumentation=yes
performance_schema_consumer_events_transactions_history=yes
performance_schema_consumer_events_transactions_current=yes
performance_schema_consumer_events_statements_history=yes
performance_schema_consumer_events_statements_current=yes
performance_schema_max_digest_length=1024

but it does'nt have any data;
--
select * from performance_schema.events_statements_history;
select * from performance_schema.events_statements_current esc;
select * from performance_schema.events_transactions_current etc ;
select * from performance_schema.events_transactions_history eth ;
SSingh-MSFT 16,371 Reputation points Moderator

2022-10-14T15:16:31.433+00:00

Hi @ 72729183 ,

I am yet to get reply from the internal team about this.
I will get back to you.

Thanks for your patience.
SSingh-MSFT 16,371 Reputation points Moderator

2022-11-02T06:05:08.147+00:00

Hi @ 72729183 ,

Following up to see if the suggestion was helpful. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Answer 1

SSingh-MSFT 16,371 Moderator

Hi @ 72729183 ,

Thanks for your patience.

I have checked with the internal team and got below reply on the questions asked:

Question 1:
Is the Query Store function also in MySQL flexible server?

Currently Query Store function is not available in Flexible Server and not planned very soon

Question 2:
is it possible to update the performance_schema.setup_consumers table so that the query is saved by setting it to performance_schema on?
If there is no both, does it not provide the function to store all query statistics (as well as slow queries) performed?

Updating performance_schema.setup_instruments is not enabled, we can use Slow query log for query performance details.

tutorial-query-performance-insights

how-to-server-logs-portal

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you.
Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification

안예리 41 Reputation points

2022-10-21T07:18:13.733+00:00

Hi,

Thanks for your answer.

I hava a questions in connection with the answer.

The server parameter menu has the following setting values.

performance_schema=on
performance_schema_consumer_global_instrumentation=yes
performance_schema_consumer_statements_digest=yes
performance_schema_consumer_thread_instrumentation=yes
performance_schema_consumer_events_transactions_history=yes
performance_schema_consumer_events_transactions_current=yes
performance_schema_consumer_events_statements_history=yes
performance_schema_consumer_events_statements_current=yes

These are the values that are originally set in the performance_schema.setup_instruments table. Azure seems to provide it as a server parameter.
However, even if the parameters are set, the data in the table below cannot be retrieved.

select from performance_schema.events_statements_history;
select from performance_schema.events_statements_current esc;
select from performance_schema.events_transactions_current etc ;
select from performance_schema.events_transactions_history eth ;

Are the set values of the server parameters meaningless?
Or are these values only in the items in advance because they are scheduled to be serviced in the future?
SSingh-MSFT 16,371 Reputation points Moderator

2022-10-25T07:07:22.85+00:00

Hi @ 72729183 ,

Thanks for your patience.

I am checking on your ask and will update you.
SSingh-MSFT 16,371 Reputation points Moderator

2022-10-26T12:40:59.923+00:00

Hi @ 72729183 ,

Thanks for your patience.

I have checked with the internal team regarding this question. They recommend to raise support ticket to get this enabled from backend for existing server.

For newly created servers, it should work. For existing servers, team is working to get this issue fixed in upcoming releases.

Please let us know if you are able to raise support ticket else, I will assist you.
안예리 41 Reputation points

2022-10-31T04:00:09.253+00:00

Hi,
Thank you for your kind reply.

You told me that the issue will be fixed in the next release.
When is the next release schedule?

Thank you for your reply in advance.
SSingh-MSFT 16,371 Reputation points Moderator

2022-10-31T05:19:49.85+00:00

Hi @ 72729183 ,

I will check and let you know.

Thanks,
Shakti
SSingh-MSFT 16,371 Reputation points Moderator

2022-11-01T04:05:26.067+00:00

Hi @ 72729183 ,

Could you please toggle performance_schema server parameter from Azure portal and restart MySQL servers below at the time which works for you and performance schema will work for them going forward.

If you have explicitly set the value to "YES" or "NO", please reset them to "ON" or "OFF" before restarting.

Hope this helps. Please let us know it this works for you now.
안예리 41 Reputation points

2022-11-03T00:13:33.227+00:00

Hi, Shakti.

The server parameter value has been changed and restarted.
It works really well.
Thank you for applying it to our server for me.

Have a nice day^^

Share via

How can I use the statistics of all queries performed in MySQL flexible server ?

0 additional answers

Your answer