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

Niket Kumar Singh 300 Reputation points
2024-05-09T09:57:21.24+00:00

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

Azure SQL Database
Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
757 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaktiSingh-MSFT 14,281 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

    0 comments
  2. ShaktiSingh-MSFT 14,281 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.

    1. Under the SETTINGS section, click Server parameters to open the server parameters page for the Azure Database for MySQL flexible server instance.
    2. Search for init_connect
    3. 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;
    4. 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

    0 comments