Solução de 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 servidor MySQL está inativo (enquanto conclui a recuperação de falhas, o que pode levar vários minutos), afetando os usuários e os negócios

• O servidor MySQL leva mais tempo para aquecer completamente os buffers e os caches e alcançar o desempenho máximo
• Possibilidade de corrupção de dados devido a uma falha inesperada

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.

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(*) |

+---------+----------+

| YES     |       70 |

| NO      |      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(*) |

+---------+----------+

| YES     |      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(*) |

+---------+----------+

| YES     |      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%)            |

+-------------------------------------+

| memória_by_host_by_current_bytes     |

| memória_by_thread_by_current_bytes   |

| memória_by_user_by_current_bytes     |

| memória_global_by_current_bytes      |

| memória_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.

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;

+-----------------+-------------------+

| usuário            | current_allocated |

+-----------------+-------------------+

| sbtest          | 60,30 MiB         |

| plano de fundo      | 2,68 MiB          |

| raiz            | 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 | usuário              | 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:

1. Se as tabelas temporárias internas exigirem uma grande quantidade de memória, você poderá limitar o tamanho das tabelas temporárias usando tmp_table_size ou reduzir o innodb_buffer_pool_size para liberar espaço para buffers por sessão.
2. Se um encadeamento específico estiver consumindo muita memória, você poderá filtrar os registros de consulta lentos usando o thread_id para identificar as consultas problemáticas e ajustá-las para reduzir o consumo de memória do encadeamento/sessão.

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:

• Revise a configuração do MySQL com cuidado e confirme se nenhum buffer/cache está alocado em excesso. Este artigo ajudará você a entender como o MySQL aloca memória para diferentes buffers/caches.
• Identifique o recurso/configuração/consulta que está causando o problema de memória e tente reproduzir o problema.
• Revise as notas da versão do MySQL das próximas versões secundárias da mesma série para verificar se algum bug com comportamento semelhante foi corrigido. 
• Atualize o MySQL para a versão secundária mais recente para corrigir o problema de memória.
• Pesquise no banco de dados de bugs do MySQL para verificar se outros problemas semelhantes foram relatados por outros usuários da comunidade.
• Crie um novo bug com um caso de teste reproduzível se não houver nenhum bug para o mesmo problema.