Unable to Modify innodb_dedicated_server variable in Azure Database for MySQL (Flexible Server) - Workarounds or Alternatives?

Niket Kumar Singh 195

I'm trying to optimize performance for my Azure Database for MySQL (Flexible Server) instance. In my research, I found the innodb_dedicated_server parameter, but it's greyed out in the Azure portal, indicating it's non-modifiable,can i change this variable. User's image

I understand this parameter represents a group of pre-configured settings. However, I'd like to explore ways to achieve similar performance improvements.

Are there any alternative server parameters I can adjust within Azure Database for MySQL (Flexible Server) to address specific InnoDB performance bottlenecks?

Are there recommended practices or configurations suitable for optimizing InnoDB tables in this environment?
User's image

Niket Kumar Singh 195 Reputation points

2024-05-09T11:02:03.39+00:00

Hi ShaktiSingh-MSF

Thank you for sharing details.

Please find below the requested details:

Location : Central India
MySQL version : 8.0
Configuration : Business Critical, E20ds_v5, 20 vCores, 160 GiB RAM, 1024 GiB storage

Some parameters' min and max values (ex. max_connections, join_buffer_size, query_cache_size) are determined by the compute tier and before you compute the size of the server.

i've also observed the for the dynamic paramter variable, i'm able to change but for static unable to change the variable any insiught how can i achive this.

i've also read that, If you want to configure a nonmodifiable server parameter at session level, refer to the Azure portal or Azure CLI article for setting the parameter at the connection level using init_connect.
ShaktiSingh-MSFT 13,836 Reputation points Microsoft Employee

2024-05-10T04:09:23.7533333+00:00
Hi Niket Kumar Singh •,

Thanks for providing the details.

The list of supported server parameters is constantly growing.

If you modify a static server parameter using the portal, you need to restart the server for the changes to take effect. In case you are using automation scripts (using tools like ARM templates , Terraform, Azure CLI etc) then your script should have a provision to restart the service for the settings to take effect even if you are changing the configurations as a part of create experience.

If you want to modify a non-modifiable server parameter for your environment, please open a UserVoice item or vote if the feedback already exist which can help us prioritize.

OR

If the server parameter you want to update is nonmodifiable, you can optionally set the parameter at the connection level using init_connect. This sets the server parameters for each client connecting to the server.

Under the SETTINGS section, click Server parameters to open the server parameters page for the Azure Database for MySQL flexible server instance.

Search for init_connect

Add the server parameters in the format: SET parameter_name=YOUR_DESIRED_VALUE in value the value column. For example, you can change the character set of your Azure Database for MySQL flexible server instance by setting init_connect to SET character_set_client=utf8;SET character_set_database=utf8mb4;SET character_set_connection=latin1;SET character_set_results=latin1;

Click Save to save your changes.

Note

init_connect can be used to change parameters that do not require SUPER privilege(s) at the session level. To verify if you can set the parameter using init_connect, execute the set session parameter_name=YOUR_DESIRED_VALUE; command and if it errors out with Access denied; you need SUPER privileges(s) error, then you cannot set the parameter using `init_connect'.

If you need this immediately, I would suggest you to raise support case so that team can facilitate for you.

Thanks
Niket Kumar Singh 195 Reputation points

2024-05-10T05:05:38.46+00:00

Hi ShaktiSingh-MSFT

Thank you for sharing the detailed instrstruction.

If i'm setting the parameter at the connection level using init_connect, is there any impact.

Post setting init_connect to SET character_set_client=utf8;SET character_set_database=utf8mb4;SET character_set_connection=latin1;SET character_set_results=latin1;

do i able to update the nonmodifiable parameter.

As suggested i've also raied the support request with MS but got response :
__It’s not supported to update the configuration innodb_dedicated_server. There are several admin & monitor applications runs with MySQL engine.

Could you please help us in enabling this parameter.__
ShaktiSingh-MSFT 13,836 Reputation points Microsoft Employee

2024-05-13T10:31:17.81+00:00

Hi Niket Kumar Singh •,

Sorry to know that you are stuck because it is not supported.

I think you can ask in support request to assigned Engineer if it is possible to be facilitated.

If not, we recommend you to refer to the "What's New" page of MySQL Flexible Server https://learn.microsoft.com/en-us/azure/mysql/flexible-server/whats-new

to get latest updates in the product.

OR

Also, you can add your feedback over https://feedback.azure.com/d365community/forum/47b1e71d-ee24-ec11-b6e6-000d3a4f0da0

so that team can prioritize feature requests based on the importance and up-votes.

Thanks

1 answer

ShaktiSingh-MSFT 13,836 Reputation points Microsoft Employee

2024-05-09T10:40:10.67+00:00

Hi Niket Kumar Singh •,

Welcome to Microsoft Q&A forum.

As I understand, you want to Modify innodb_dedicated_server variable in Azure Database for MySQL (Flexible Server) for optimization.

Could you please let us know the Pricing tier and other configuration details of your server?

Azure Database for MySQL flexible server supports tuning the values of server parameters. Some parameters' min and max values (ex. max_connections, join_buffer_size, query_cache_size) are determined by the compute tier and before you compute the size of the server. Refer to server parameters for more information about these limits.

Azure Database for MySQL flexible server uses the InnoDB storage engine for all nontemporary tables. With InnoDB, data is stored within a clustered index using a B-Tree structure. The table is physically organized based on primary key values, which means that rows are stored in the primary key order.

Each secondary key entry in an InnoDB table contains a pointer to the primary key value in which the data is stored. In other words, a secondary index entry contains a copy of the primary key value to which the entry is pointing. Therefore, primary key choices have a direct effect on the amount of storage overhead in your tables.

Thanks
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Unable to Modify innodb_dedicated_server variable in Azure Database for MySQL (Flexible Server) - Workarounds or Alternatives?

1 answer