O Cloud SQL para MySQL do Google Cloud é um serviço de banco de dados totalmente gerenciado que ajuda a configurar, manter, gerenciar e administrar seu banco de dados MySQL, PostgreSQL ou SQL Server no Google Cloud Platform. O Cloud SQL executa um grande número de bancos de dados MySQL na própria frota. É comum que algumas instâncias do MySQL consumam muita memória e falhem por causa do erro de falta de memória (OOM, na sigla em inglês). Neste artigo, vamos ajudar você a resolver problemas de memória do MySQL.
Uma condição de falta de memória (OOM, na sigla em inglês) ocorre quando um processo tenta alocar memória e falha porque o servidor não tem memória disponível. Isso geralmente resulta no Linux OOM Killer, um processo que o kernel do Linux emprega quando o sistema está criticamente com pouca memória, encerrando o processo do banco de dados.
Uma falha no banco de dados devido a uma condição de OOM não é ideal porque o processo do banco de dados é encerrado usando a diretiva SIGKILL, que não dá ao aplicativo a chance de encerrar normalmente o MySQL. Ele causa os seguintes problemas:
O MySQL adicionou instrumentação de memória em performance_schema a partir da versão 5.7, o que ajuda a entender a alocação de memória do MySQL. Neste artigo, vamos ajudar você a resolver problemas de memória do MySQL usando performance_schema.
Cada instância do Cloud SQL usa uma máquina virtual (VM, na sigla em inglês) em execução em um servidor host do Google Cloud. Cada VM opera o programa do banco de dados, como o MySQL Server e agentes de serviço que fornecem serviços de suporte, como geração de registros e monitoramento. O Cloud SQL reserva uma parte da memória para o sistema operacional (SO), agentes de serviço e aloca a memória restante para programas de banco de dados como o mysqld.
A tabela a seguir mostra a alocação de memória para o processo mysqld e a configuração padrão do pool de buffers do InnoDB para cada tipo de máquina.
Tipo de máquina | RAM da instância GIB (bytes) | memória mysqld (GB) | GiB do pool de buffer do InnoDB (bytes) | Pool de buffers (% de RAM da instância) | Pool de buffer (% de memória mysqld) |
db-g1-small | 1.7(1825361101) | 1.11 | 0.625(671088640) | 37% | 56% |
db-custom-1-3840 | 3.75(4026531840) | 2,97 | 1.375(1476395008) | 37% | 46% |
db-custom-2-7680 | 7.5(8053063680) | 6.72 | 4(4294967296) | 53% | 60% |
db-custom-2-13312 | 13(13958643712) | 12 | 9(9663676416) | 69% | 75% |
db-custom-4-15360 | 15(16106127360) | 14 | 10.5(11274289152) | 70% | 76% |
db-custom-4-26624 | 26(27917287424) | 24 | 19(20401094656) | 73% | 79% |
db-custom-8-30720 | 30(32212254720) | 28 | 22(23622320128) | 73% | 80% |
db-custom-8-53248 | 52(55834574848) | 48 | 38(40802189312) | 73% | 79% |
db-custom-16-61440 | 60(64424509440) | 55 | 44(47244640256) | 73% | 80% |
db-custom-16-106496 | 104(111669149696) | 96 | 75(80530636800) | 72% | 78% |
db-custom-32-122880 | 120(128849018880) | 110 | 87(93415538688) | 73% | 79% |
db-custom-32-212992 | 208(223338299392) | 191 | 150(161061273600) | 72% | 78% |
db-custom-64-245760 | 240(257698037760) | 221 | 173(185757335552) | 72% | 78% |
db-custom-96-368640 | 360(386547056640) | 331 | 260(279172874240) | 72% | 79% |
db-custom-64-425984 | 416(446676598784) | 383 | 300(322122547200) | 72% | 78% |
db-custom-96-638976 | 624(670014898176) | 574 | 450(483183820800) | 72% | 78% |
Tipo de máquina
RAM da instância GIB (bytes)
memória mysqld (GB)
GiB do pool de buffer do InnoDB (bytes)
Pool de buffers (% de RAM da instância)
Pool de buffer (% de memória mysqld)
db-g1-small
1.7(1825361101)
1.11
0.625(671088640)
37%
56%
db-custom-1-3840
3.75(4026531840)
2,97
1.375(1476395008)
37%
46%
db-custom-2-7680
7.5(8053063680)
6.72
4(4294967296)
53%
60%
db-custom-2-13312
13(13958643712)
12
9(9663676416)
69%
75%
db-custom-4-15360
15(16106127360)
14
10.5(11274289152)
70%
76%
db-custom-4-26624
26(27917287424)
24
19(20401094656)
73%
79%
db-custom-8-30720
30(32212254720)
28
22(23622320128)
73%
80%
db-custom-8-53248
52(55834574848)
48
38(40802189312)
73%
79%
db-custom-16-61440
60(64424509440)
55
44(47244640256)
73%
80%
db-custom-16-106496
104(111669149696)
96
75(80530636800)
72%
78%
db-custom-32-122880
120(128849018880)
110
87(93415538688)
73%
79%
db-custom-32-212992
208(223338299392)
191
150(161061273600)
72%
78%
db-custom-64-245760
240(257698037760)
221
173(185757335552)
72%
78%
db-custom-96-368640
360(386547056640)
331
260(279172874240)
72%
79%
db-custom-64-425984
416(446676598784)
383
300(322122547200)
72%
78%
db-custom-96-638976
624(670014898176)
574
450(483183820800)
72%
78%
Para instâncias com 10 GB de RAM ou mais, o processo mysqld é limitado a usar 92% da RAM da instância e o pool de buffer do InnoDB é configurado no intervalo de 75% a 80% da memória disponível para o processo mysqld. Além do pool de buffers do InnoDB, o MySQL aloca vários buffers e caches para melhorar o desempenho. Consulte este artigo para entender a alocação de memória no MySQL.
Quando o uso da memória mysqld cresce além do limite configurado do processo mysqld, o Linux OOM killer mata o processo mysqld, já que ele é o maior consumidor de memória na instância.
performance_schema é um recurso do MySQL para monitorar a execução do servidor em um nível baixo. Com o Cloud SQL para MySQL, é possível ativar performance_schema nas instâncias com 3 GB de RAM ou mais. Ele é ativado por padrão em instâncias com pelo menos 15 GB de RAM, começando com o MySQL 8.0.
O Performance_schema é desativado por padrão nas versões 5.6 e 5.7 do MySQL. Ele pode ser ativado usando sinalizações do banco de dados.
A ativação de performance_schema envolve algumas sobrecargas de desempenho e também implicações na memória. Consulte O modelo de alocação de memória do esquema de desempenho para entender a alocação de memória.
A instrumentação de memória performance_schema ajuda a monitorar o uso de memória no MySQL. Os instrumentos de memória têm nomes no formato memory/code_area/instrument_name, em que code_area é um valor como sql ou Innodb e instrument_name é o detalhe do instrumento.
O MySQL 5.7 ativa alguns instrumentos de memória por padrão, mas não todos. É possível ativá-los atualizando a coluna "ATIVADO" da tabela performance_schema.setup_instruments.
mysql> select version();
+-------------------+
| version() |
+-------------------+
| 5.7.39-google-log |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;
+---------+----------+
| enabled | count(*) |
+---------+----------+
| SIM | 70 |
| NÃO | 306 |
+---------+----------+
2 rows in set (0.00 sec)
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
Query OK, 306 rows affected (0.00 sec)
Linhas correspondentes: 376 Alteradas: 306 Avisos: 0
mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;
+---------+----------+
| enabled | count(*) |
+---------+----------+
| SIM | 376 |
+---------+----------+
1 row in set (0.00 sec)
Ativar instrumentos de memória no ambiente de execução conta apenas os objetos de memória criados depois de ativar a instrumentação. Portanto, recomendamos que você espere o servidor coletar dados suficientes para solucionar problemas.
O MySQL 8.0 ativa todos os instrumentos de memória por padrão.
mysql> select version();
+---------------+
| version() |
+---------------+
| 8.0.28-google |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;
+---------+----------+
| enabled | count(*) |
+---------+----------+
| SIM | 490 |
+---------+----------+
1 row in set (0.00 sec)
O esquema do sistema contém muitas visualizações que resumem os dados performance_schema em formulários facilmente compreensíveis. A maioria dessas visualizações vem em pares. Elas compartilham o mesmo nome e uma delas tem um prefixo x$. As visualizações a seguir estão disponíveis no MySQL 5.7 e 8.0 para exibir a alocação de memória em diferentes níveis.
mysql> use sys;
Banco de dados alterado
O mysql> mostra tabelas como '%memory%';
+-------------------------------------+
| Tables_in_sys (%memory%) |
+-------------------------------------+
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes |
| memory_global_by_current_bytes |
| memory_global_total |
| x$memory_by_host_by_current_bytes |
| x$memory_by_thread_by_current_bytes |
| x$memory_by_user_by_current_bytes |
| x$memory_global_by_current_bytes |
| x$memory_global_total |
+-------------------------------------+
10 rows in set (0.02 sec)
A visualização sys.memory_global_total resume o uso total de memória em um formato legível.
mysql> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 12,43 GiB |
+-----------------+
1 linha no conjunto (0,44 s)
O sys.x$memory_global_total exibe as mesmas informações, mas em bytes.
mysql> select * from sys.x$memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 13351482992 |
+-----------------+
1 linha no conjunto (1,39 s)
A visualização sem o prefixo x$ visa fornecer uma saída mais intuitiva e fácil de ler. A visualização com o prefixo x$ que mostra os mesmos valores em formato bruto é mais usada com outras ferramentas que realizam o próprio processamento nos dados.
A visualização sys.memory_global_by_current_bytes mostra o uso de memória atual agrupado pelo tipo de alocação (event_name). Por exemplo, Memorystore/innodb/buf_buf_pool (pool de buffer de InnoDB). Por padrão, as linhas são classificadas pela quantidade decrescente de memória usada.
Antes de detalhar cada tipo de alocação, é bom ter uma visão geral do uso de memória para cada área de código, como Innodb, sql, performance_schema e muito mais.
A consulta a seguir agrega a memória alocada atualmente pela área de código.
SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,
FORMAT_BYTES(SUM(atual_alloc)) AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(nome_do_evento,'/',2)
ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/innodb | 11.28 GiB |
| memory/performance_schema | 520.02 MiB |
| memory/temptable | 514.00 MiB |
| memory/sql | 106.12 MiB |
| memory/mysys | 43.25 MiB |
| memory/vio | 372.15 KiB |
| memory/myisam | 696 bytes |
| memory/csv | 88 bytes |
| memory/blackhole | 88 bytes |
+---------------------------+---------------+
9 rows in set (1.02 sec)
A saída mostra que o innodb é o maior consumidor de memória, seguido pelo performance_schema e pelas tabelas temporárias.
Agora, a consulta de sys.memory_global_by_current_bytes mostra diretamente o uso da memória para estruturas internas do MySQL, conforme descrito na coluna event_name. Se você estiver solucionando o aumento anormal de memória, poderá identificar a origem observando a saída dessa visualização.
mysql> select event_name,current_alloc,high_alloc from sys.memory_global_by_current_bytes limit 5;
+-------------------------------+---------------+------------+
| event_name | current_alloc | high_alloc |
+-------------------------------+---------------+------------+
| memory/innodb/buf_buf_pool | 10,72 GiB | 10,72 GiB |
| memory/temptable/physical_ram | 514,00 MiB | 515,00 MiB |
| memory/innodb/hash0hash | 292,69 MiB | 292,69 MiB |
| memory/innodb/memory | 191,77 MiB | 197,94 MiB |
| memory/sql/TABLE | 60,39 MiB | 62,35 MiB |
+-------------------------------+---------------+------------+
5 rows in set (0.45 sec)
Veja abaixo os detalhes de cada tipo de alocação.
Tipo de alocação | Detalhes |
memory/innodb/buf_buf_pool | Memória usada pelo pool de buffer do InnoDB |
memory/temptable/physical_ram | Memória usada por tabelas temporárias internas |
memory/innodb/hash0hash | Memória usada pela tabela de hash do InnoDB |
memory/innodb/memory | Memória usada por vários buffers do InnoDB |
memory/sql/TABLE | Memória usada por objetos TABLE e a raiz de mem deles |
Tipo de alocação
Detalhes
memory/innodb/buf_buf_pool
Memória usada pelo pool de buffer do InnoDB
memory/temptable/physical_ram
Memória usada por tabelas temporárias internas
memory/innodb/hash0hash
Memória usada pela tabela de hash do InnoDB
memory/innodb/memory
Memória usada por vários buffers do InnoDB
memory/sql/TABLE
Memória usada por objetos TABLE e a raiz de mem deles
Essa visualização resume o uso da memória agrupado pelo usuário. Se um usuário tiver várias conexões, ela soma o uso da memória de todas as instâncias do usuário.
mysql> select user,current_allocated from memory_by_user_by_current_bytes;
+-----------------+-------------------+
| user | current_allocated |
+-----------------+-------------------+
| sbtest | 60,30 MiB |
| background | 2,68 MiB |
| root | 1,52 MiB |
| event_scheduler | 16,38 KiB |
+-----------------+-------------------+
4 rows in set (1.16 sec)
Se você usa um usuário diferente para cada aplicativo/job (recomendado), essa visualização pode ajudar a identificar se um aplicativo ou job específico está consumindo muita memória.
Esta visualização resume o uso da memória agrupado pelo nome do host.
mysql> select host,current_allocated from memory_by_host_by_current_bytes;
+------------+-------------------+
| host | current_allocated |
+------------+-------------------+
| 10.128.0.8 | 62.10 MiB |
| background | 2.70 MiB |
| 127.0.0.1 | 311.01 KiB |
| localhost | 16.38 KiB |
+------------+-------------------+
4 rows in set (1.58 sec)
Essa visualização é útil para identificar o uso de memória por host do cliente. É muito comum ter vários clientes/servidores de aplicativos atualmente. Essa visualização ajuda a identificar se algum cliente ou carga de trabalho em particular está causando alto uso de memória. Também é possível implantar a alteração primeiro em um servidor cliente/de aplicativo, monitorar o impacto dela na memória (comparando o uso de memória com outros hosts do cliente) e tomar decisões informadas.
Essa visualização resume o uso da memória, agrupada por linha de execução, ajuda a encontrar as linhas de execução que ocupam a maior parte da memória.
mysql> select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 5;
+-----------+-------------------+-------------------+
| thread_id | user | current_allocated |
+-----------+-------------------+-------------------+
| 12999 | sbtest@10.128.0.8 | 3.80 MiB |
| 12888 | sbtest@10.128.0.8 | 3,75 MiB |
| 12760 | sbtest@10.128.0.8 | 3,68 MiB |
| 13128 | sbtest@10.128.0.8 | 3,67 MiB |
| 13221 | sbtest@10.128.0.8 | 3,47 MiB |
+-----------+-------------------+-------------------+
5 rows in set (2.29 sec)
Se você descobrir que uma linha de execução específica está consumindo muita memória, filtre os registros de consulta lentas ou os registros gerais do MySQL usando o thread_id e identifique as sessões/consultas que levaram ao aumento da memória.
Resolver problemas de alto uso de memória é uma tarefa desafiadora. Felizmente, a instrumentação de memória performance_schema ajuda os usuários a entender como a memória é usada por estruturas internas do MySQL. As visualizações do esquema sys facilitam a interpretação das informações. Se você tiver problemas relacionados à memória, esse recurso o ajudará a encontrar a origem do problema e você pode corrigi-lo fazendo alterações na configuração/consulta.
Exemplo:
Se você se deparar com uma situação em que uma instância do MySQL está usando uma memória mais alta do que deveria, e o uso de memória aumenta continuamente até que o servidor fique sem memória, e o performance_schema mostra uma lacuna na memória total em comparação com a memória instrumentada ou não informa onde isso pode ser um sintoma de um vazamento de memória. Se você suspeitar de um vazamento de memória, siga estas etapas:
Comece a criar no Google Cloud com US$ 300 em créditos e mais de 20 produtos do programa Sempre gratuito.