Como Funciona o MySQL

Como Funciona o MySQL | Neste post descrevemos as conexões MySQL, os tópicos do usuário e o escalonamento. Esperamos que um maior entendimento de como o MySQL funciona ajude os desenvolvedores de aplicações e administradores de sistemas a fazer boas escolhas e trocas.

Descrevemos como as conexões funcionam em um servidor de comunidade simples e não cobrimos tópicos relacionados, tais como agrupamento de threads, grupos de recursos ou multiplexação de conexões neste post. Confira mais detalhes que o nosso site trouxe.

Como Funciona o MySQL

O servidor MySQL (mysqld) executa como um único processo de SO, com múltiplos threads executando atividades simultâneas.

O MySQL não tem sua própria implementação de threads, mas se baseia na implementação de threads do SO subjacente.

Quando um usuário se conecta ao banco de dados, uma thread do usuário é criada dentro do mysqld e esta thread do usuário executa consultas do usuário, enviando os resultados de volta para o usuário, até que o usuário se desconecte.

Quando mais e mais usuários se conectam ao banco de dados, mais e mais threads de usuário executam em paralelo.

Desde que todos os threads do usuário executem como se estivessem sozinhos, podemos dizer que o sistema (MySQL) é bem dimensionado.

Mas em algum momento chegamos a um limite e a adição de mais threads de usuários não será útil ou eficiente.

Conexão e Desconexão

As conexões correspondem às Sessões em terminologia padrão SQL. Um cliente se conecta ao MySQL Server e permanece conectado até que ele faça uma desconexão.

Clientes

Um Cliente MySQL é uma ferramenta de linha de comando ou uma aplicação que fala com o MySQL Server sobre o protocolo Cliente-Servidor MySQL usando a biblioteca libmysqlclient ou alguns dos muitos conectores MySQL.

Um único cliente multi-tarefa pode abrir muitas conexões com o servidor, mas para simplificar dizemos aqui que um cliente abre uma conexão com o servidor.

Solicitações de Conexão

Os clientes MySQL enviam solicitações de conexão para o servidor MySQL. Um pedido de conexão é simplesmente uma mensagem de conexão TCP-IP enviada à porta 3306 na máquina host do servidor.

Linha do Receptor

As solicitações de conexão recebidas são enfileiradas e depois processadas pelo thread receptor um a um. A única tarefa da thread do receptor é criar uma thread do usuário, o processamento posterior é feito pela thread do usuário.

Cache

A linha do receptor criará uma nova linha do sistema operacional ou reutilizará uma linha do sistema operacional existente “livre” se encontrada no cache da linha. O cache costumava ser importante para a velocidade de conexão quando as roscas do sistema operacional eram dispendiosas de criar.

Hoje em dia, o valor padrão do tamanho do cache é calculado como 8 + (max_connections / 100) e raramente é alterado.

Pode fazer sentido tentar aumentar o cache de thread nos casos em que o número de conexões flutua entre ter muito poucas conexões e ter muitas conexões.

Thread do Usuário

É o thread do usuário que manipula o protocolo cliente-servidor, por exemplo, envia de volta o pacote de aperto de mão inicial. Este thread do usuário irá alocar e inicializar o THD correspondente, e então continuará com a negociação e autenticação da capacidade.

Neste processo, as credenciais do usuário são armazenadas no contexto de segurança do THD. Se tudo correr bem na fase de conexão, a thread do usuário entrará na fase de comando.

THD

A conexão é representada por uma estrutura de dados chamada THD que é criada quando a conexão é estabelecida e apagada quando a conexão é abandonada. Há sempre uma correspondência um-a-um entre uma conexão de usuário e uma THD, ou seja, as THDs não são reutilizadas através das conexões.

O tamanho da THD é de ~10K e sua definição é encontrada em sql_class.h. A THD é uma grande estrutura de dados que é usada para manter o controle de vários aspectos do estado de execução.

A memória na THD crescerá significativamente durante a execução da consulta, mas exatamente o quanto ela cresce dependerá da consulta. Para fins de planejamento de memória, recomendamos planejar para ~10MB por conexão, em média.

Papel dos Desenvolvedores de Aplicações

Em alguns casos, os desenvolvedores de aplicações estão no controle da arquitetura geral do sistema, do esquema do banco de dados e das consultas ao banco de dados. Mas talvez na maioria das vezes, os desenvolvedores de aplicações serão obrigados a desenvolver uma aplicação em cima de um banco de dados existente.

Em ambos os casos, o desenvolvedor do aplicativo precisará prestar atenção às consultas enviadas para a camada de banco de dados.

O caso clássico da maneira de como funciona o MYSQL é o Processamento de Transações Online (OLTP), que normalmente tem requisitos de tempo de resposta exigentes. Os tempos de resposta aceitáveis do banco de dados são frequentemente especificados em milissegundos e isso, naturalmente, limitará o tipo de consultas que podem ser esperadas (talvez combinadas com limitações no volume de dados e na estrutura do esquema do banco de dados).

Isto é frequentemente contrastado com o Processamento Analítico Online (OLAP), onde há consultas mais complexas, mas a frequência da consulta é menor e os requisitos de tempo de resposta podem ser mais relaxados.

Especialmente para OLTP, o desenvolvedor do aplicativo deve ter cuidado ao projetar consultas que possam ser executadas dentro de determinado SLA de tempo de resposta e que possam ser executadas em paralelo.

Não é muito difícil produzir uma carga de trabalho que não seja escalonável, por exemplo, muitos clientes paralelos não fazem nada além de atualizar exatamente a mesma linha na mesma tabela (veja projetos alternativos aqui).

Conclusão

• O MySQL é muito bom em lidar com muitos clientes conectando e desconectando ao banco de dados em alta frequência, até 80 mil conexões e desconexões por segundo
• O MySQL se dimensiona bem em CPUs multi-core e pode fornecer até 2 milhões de chaves primárias de acesso por segundo em 48 núcleos de CPU.
• Regra de ouro: Número máximo de conexões = 4 vezes os núcleos de CPU disponíveis
• O uso eficiente das conexões dependerá da carga do usuário, o número útil de conexões do usuário pode até ser inferior ao número de núcleos de CPU quando o gargalo do gargalo estiver em outro lugar que não seja o rosqueamento
• Verifique sua própria carga duplicando o número de conexões até que o TPS não aumente mais e a latência comece a aumentar

Agora que você sabe como funciona o MYSQL, deixe seu comentário!