Innodb buffer pool size in Azure Database for MySQL

Question

Innodb buffer pool size in Azure Database for MySQL

Wenjian Feng 41

We have a Azure Database for MySQL instance, detail info as below:

Version: Single Server
Pricing tier: General Purpose
Computer genration: Gen 5
vCores: 8

From below link, for 8 vCores should be 40GB memory as 5GB/vCore.
concepts-pricing-tiers

From below link, for 8 vCores and general purpose pricing tier, both default and max innodb_buffer_pool_size size are 16106127360 (15GB).
concepts-server-parameters

My question: why innodb buffer pool size limit as 15GB? In my opinion, innodb buffer pool size should be 70 to 80 percent of physical memory. For 8 vCores should be 40GB memory as 5GB/vCore, so innodb buffer pool size max size should be 40GB * 70%~80% = 28GB~32GB. But from the document, max value are 15GB. It's very confusing to me.

Accepted answer

0 additional answers

Your answer

Answer 1

Anurag Sharma 17,631

Hi @Wenjian Feng , welcome to Microsoft Q&A forum.

In Azure Database for MySQL, innodb_buffer_pool_size depends on the storage size and vCores both.

For storage size up to 4 TB, the max value is kept at 16106127360 bytes (close to 16 GB).

For storage size up to 16 TB, the max value is kept at 32212254720 bytes (close to 32 GB).

Below is the screenshot of Azure MySQL server with 8 vCores and 16 TB of storage size:

You can refer the same in below link:
innodb_buffer_pool_size

Please let us know if this helps.

----------

If answer helps, please mark it 'Accept Answer'

Wenjian Feng 41 Reputation points

2021-03-25T06:46:10.05+00:00

@AnuragSharma-MSFT, thanks for your help.

As long as my storage space is not larger than 4T, then my MySQL innodb buffer pool size can only configure the maximum size of 16GB, is that correct?

Now, our storage size is less than 4TB and configure as auto-extend, I want to known whether the innodb buffer pool size will change to 32GB automatically or not when the storage size is automatically expanded to more than 4TB? or we should change the size manually?
Anurag Sharma 17,631 Reputation points

2021-03-25T08:15:23.917+00:00
Hi @Wenjian Feng , thanks for replying back.

I think there was some conflict in the document.

If we have 8 vCores configured, we can have the value of the parameter till 32 GB. We can also set less value if we want either through the Azure Portal or through the Azure CLI as mentioned below:

az mysql server configuration set --name innodb_buffer_pool_size --resource-group myrsrcgrp --server myserver --value 16106127360

Once we configured this, we can go to server parameters and check this value.

Please let us know if this helps.
Anurag Sharma 17,631 Reputation points

2021-03-25T08:51:16.217+00:00

I have received additional information from the product group on the same.

As per the recent changes there are few regions that support the storage stack of 16 TB directly where in if we create the new Azure MySQL database with 8 vCores, Innodb buffer pool size will be defaulted to 32 GB irrespective of what storage size we choose. Below is the list of regions:

East US, East US 2, Central US, Brazil South, West US, North Central US, South Central US, North Europe, West Europe, UK South, UK West, Southeast Asia, East Asia, Japan East, Japan West, Korea Central, Korea South, Australia East, Australia South East, West US 2, West Central US, Canada East, and Canada Central.

Any other regions apart from these will have the parameter set based on storage size and vCores.

Azure Database for MySQL pricing tiers
Wenjian Feng 41 Reputation points

2021-03-25T11:12:56.103+00:00

@AnuragSharma-MSFT , thanks for your support, i got it.

Share via

Innodb buffer pool size in Azure Database for MySQL

0 additional answers

Your answer