Resumo : neste tutorial, você aprenderá como configurar o tamanho do buffer pool usando a innodb_buffer_pool_sizeopção de configuração para melhorar o desempenho do MySQL.
Conjunto de buffers
No InnoDB , um buffer pool é uma estrutura na memória que armazena em cache dados e índices acessados com frequência.
Quando você consulta dados de uma tabela , o InnoDB lê os dados do disco para o buffer pool. Se os dados já estiverem no buffer pool, o MySQL pode recuperar rapidamente os dados da memória em vez de realizar um acesso de E/S ao disco mais demorado.
O buffer pool pode melhorar significativamente o desempenho de leitura, especialmente quando você tem altas cargas de trabalho de leitura. Em geral, quanto maior o buffer pool, melhor será o desempenho do servidor MySQL.
Uma carga de trabalho de leitura refere-se a um cenário em que as operações primárias do banco de dados recuperam dados existentes, com foco na consulta em vez de na atualização dos dados.
Para alterar o tamanho do buffer pool, use a innodb_buffer_pool_sizevariável de configuração.
O tamanho ideal do buffer pool depende da memória disponível e da natureza da carga de trabalho.
Uma diretriz geral é alocar cerca de 80% da memória disponível para o buffer pool em um servidor dedicado ao MySQL.
Verificando innodb_buffer_pool_size
Primeiro, conecte-se ao MySQL usando a ferramenta cliente mysql:
mysql -u root -pLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, mostre o valor atual da innodb_buffer_pool_sizeopção:
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.04 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )O valor de innodb_buffer_pool_sizeestá em bytes. Você pode alterá-lo para megabytes usando a seguinte instrução:
SELECT
ROUND(@@innodb_buffer_pool_size / 1024 / 1024,0) "innodb_buffer_pool_size (MB)";Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+------------------------------+
| innodb_buffer_pool_size (MB) |
+------------------------------+
| 128 |
+------------------------------+
1 row in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )O padrão no innodb_buffer_pool_sizeMySQL 8.0 é 128 MB.
Alterando o tamanho do buffer pool usando o parâmetro innodb_buffer_pool_size
Primeiro, altere o valor de innodb_buffer_pool_sizeno arquivo de configuração do MySQL ( my.iniou my.cnf) para um tamanho maior, por exemplo, 1GB, e salve o arquivo:
innodb_buffer_pool_size=1GBLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, reinicie o servidor MySQL .
Terceiro, conecte-se ao servidor MySQL:
mysql -u root -pLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Por fim, mostre o valor da innodb_buffer_pool_sizevariável:
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
1 row in set (0.02 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A saída indica que o valor foi atualizado com sucesso.
Observe que se você tiver um buffer pool na faixa de vários gigabytes, deverá dividir o buffer pool em instâncias separadas para melhorar a simultaneidade. Para fazer isso, ajuste a opção de configuração innodb_buffer_pool_instances .
Configurando innodb_buffer_pool_size on-line
MySQL permite que você defina innodb_buffer_pool_size dinamicamente usando a SETinstrução. Por exemplo:
SET GLOBAL innodb_buffer_pool_size=1073741824;Linguagem de código: PHP ( php )Isso permite redimensionar o buffer pool sem reiniciar o servidor MySQL. Observe que a alteração não persistirá após você reiniciar o servidor MySQL.
Resumo
- Use a
innodb_buffer_pool_sizevariável de configuração para ajustar o tamanho do buffer pool. - Aloque 80% da memória para o buffer pool no servidor dedicado MySQL.
