Replicação Master-to-Master com MySQL 5.6 e HA + distribuição de carga com GLB + Tunning

mysql

Não tão rapidamente como Percona ou MariaDB, mas o MySQL tem evoluido bastante. Já há muito tempo o MySQL tem como uma de suas engines o InnoDB, mas somente na versão 5.6 ela vem como engine padrão. Claro, não é por não vir por padrão que ela não substitui francamente a antiga e sofrível engine MyISAM; até a versão 5.5 o InnoDB tinha lá suas grande deficiências e ineficiências, mas não sou DBA e não ousarei comparar amplamente os recursos, apenas vou citar o óbvio – MyISAM faz locks nas tabelas que vão receber UPDATES, de forma a enfileirar requisições e esse é o grande gerador de gargalo da engine. Mas mesmo MyISAM recebeu atualizações na nova versão do MySQL, permitindo particionamento de tabelas, o que pode aumentar em muito o desempenho e dar uma sobrevida à sua estrutura de legado. Claro, a utilização desse recurso tem lá seu preço e deve ser ‘tunada’ até atingir o limite do número de processadores disponíveis. Aliás, essa foi mais uma característica incluída, podendo escalar até 64 processadores.

Sobre replicação já escreví outros 2 posts, sendo o primeiro Master-2-Master + HA com Heartbeat e o segundo Master-2-Master com MySQL 5.5. Para o segundo, recomendo a utilização do UCARP para fazer HA, descrito nesse post.
No post do UCARP não citei, mas para subir ou baixar serviços tal qual é feito em heartbeat, apenas edite o respectivo arquivo; /usr/share/ucarp/vip-down para quando o serviço baixar, /usr/share/ucarp/vip-up para quando o serviço subir.

Nessa versão, vamos fazer algo um pouco mais sofisticado, usando um software balanceador para resolver HA e distribuição de carga.

Em relação à engine (que nessa versão é por default a InnoDB), há que se fazer ajustes sim,inclusive com recomendação de fazê-lo ‘from scratch’, conforme descrevem os autores Baron Schwartz , Peter Zaitsev e Vadim Tkachenko no livro High Performance MySQL: Optimization, Backups, and Replication. Logicamente não vou descrever a instalação do banco em si. Apenas devo citar que a versão foi baixada diretamente do site oficial (sim, clique aqui). Tive alguns percalços para rodar a base, tendo como agravante uma instalação anterior (na versão 5.1), sendo que ao término montei todo o arquivo desde o zero, mas não se preocupe tanto com isso – na extração da base vocẽ encontra alguns modelos padrão e basta trabalhar sobre um deles.

Como citei anteriormente, não sou DBA, mas nada me impediu de me atrever a escrever meu arquivo my.cnf do zero. Então, baseado nos meus estudos iniciais vou exemplificar a criação de um, explicando de cada ítem o porquê. Ainda assim posso ter entendido errado e cometido algum engano, então em relação a essa parte – use meu exemplo se quiser, mas fique de olho se está de acordo com suas necessidades e se está se comportando adequadamente. Dito isso, sigamos.

Gerando um my.cnf from scratch

O livro citado anteriormente é excelente e desbravou fronteiras para mim. Dificilmente eu chego a esse grau de satisfação com um livro, não é apenas mais ‘um monte do mesmo’. Porém, não está atualizado para a versão 5.6 do MySQL e algumas coisas da outrora ‘futura versão’ foram citadas, porém nem todas previstas. Por isso tive que ir um pouco além da recomendação, mas tudo estará detalhado a seguir.




Para levantar a base no primeiro momento segui a instrução do livro, onde havia um conjunto inicial de parâmetros citados como fundamentais para um inicio. Francamente, a base nao subiu de imediato com a configuração inicial que eu havia criado, então me pus em busca dos ‘porquês’. Não ter subido de primeira foi uma bela sorte afinal, pois pesquisando a documentação oficial da versão notei haver parâmetros co-relacionados para a versão 5.6, assim uma coisa levou a outra e o resultado final foi um arquivo muito mais elaborado.

Explicando agora.

performance_schema

Esse recurso será utilizado para análise de performance, implicando em até 5% do uso de recursos, portanto desabilite-o após utilizá-lo. Esse recurso será explicado em outro post sobre profiling, utilizado para medir os limites do hardware e de sua aplicação.

innodb_flush_log_at_trx_commit

Isso tornará notavelmente mais rápido a escrita uma vez que a manipulação do buffer não fará o commit imediatamente após o shiffting, mas em consequência 1 segundo de transactions será perdido em caso de falha de energia. Eu prefiro correr o risco com esse 1 segundo, ainda mais considerando uma estrutura de CPD com no-break, replicação e em alguns casos até redundância geográfica, mas se estes dados lhe forem monetariamente custosos, elimine essa opção (ou aponte o valor default 1). Essa é uma variavel global. Para consultá-la, use:

innodb_buffer_pool_size

Essa variável é utilizada para cache de dados e indexes na memória, especificamente para InnoDB. Conforme a documentação, esse cache pode ser tão grande quanto desejado, desde que reste memória para os demais processos do banco, possíveis aplicações e para o sistema operacional. Algumas recomendações são um pouco mais diretas, como por exemplo, deixar algo em torno de 87% da memória total para essa variável; em outros casos, deixar TAMANHO_DO_BANCO+X%. Eu optei pelo segundo caso, mas ainda assim há uma fórmula um pouco mais complexa para se obter um valor absoluto. Não achei tão necessário para o meu caso.

innodb_buffer_pool_instances

Essa variável é utilizada para dividir o cache anterior em blocos distintos. Há ganho de desempenho desde que os tamanhos de cada instância seja maior que 1G.

innodb_log_file_size

Não é uma variável carente de explicações, porém vale citar que o tamanho utilizado é suficiente. Um arquivo gigante gerará processamento extra no append dos dados.

innodb_flush_method

Essa é uma variável importante. O método implementado no MySQL 5.6.4 faz o controle do cache invés de esperar o controle do sistema operacional. Isso garante maior agilidade na liberação de memória e menos I/O uma vez que o kernel não intervirá nessa área de cache sob controle do MySQL.

key_buffer_size

Esse buffer é utilizado para indexação do MyISAM. A documentação diz ser aceitável até 25% da memória total da máquina, mas sinceramente, vi valores bastante pequenos, então nesse primeiro momento optei por 2G e depois com analise verei se há necessidade de aumentar ou diminuir.

myisam_block_size

Na versão 5.6 pode-se setar esse valor para até 4096 (4K), o que deve ajudar a reduzir a fragmentação de escrita, equiparando o valor aos blocos do disco (ou algo muito parecido com isso e que não consegui explicar). Para mim foi conveniente. Procurando pelo google, encontrei inclusive um teste que mostrava claramente a fragmentaçao acontecendo.

thread_cache_size

Essa variável não tem um valor óbvio. Iniciei com 10, porém uma boa medida é tirar dos picos de uma amostragem a partir do seguinte comando:

O ideal é que os valores estejam em 0. Se por exemplo a variação ficar em ~30, o valor da variável deverá então ser incrementada a tanto.

Logging

Nessa sessão 3 variáveis são importantes
long_query_time se refere ao tempo que uma query pode tomar até ser considerada lenta.
slow_query_log ativa o log de queries lentas.
slow_query_log_file especifica o arquivo para gravar as queries lentas.

Antes de querer saber quais são as tais queries lentas, utilize o comando:

E verifique o número apontando em Slow Queries. Isso é fundamental, pois a ferramenta de log pode ser um tiro pela culatra, gerando um bom I/O extra e incrementando seus problemas.

Se você por acaso ainda utiliza MyISAM como engine, saiba que há uma optimização para ela também. Desde alguma versão da série 5.6 é possível utilizar particionamento da tabela, o que ajuda a ‘escapar’ um pouco dos locks – ‘tendão de Aquiles’ dessa engine. Um exemplo de particionamento seria algo como:

Haverá um ganho em relação a concorrência, mas esse recurso não tem efeito sobre InnoDB.

Mas já que estamos falando de tunning, vamos considerar também o que pode ser melhorado no sistema de arquivos, afinal nem tudo é responsabilidade do SGBD.

Scheduling
O Linux possue atualmente 3 modos de scheduling, que podem ser vistos em /sys/block/sda/queue/scheduler. Um ‘cat’ deve resultar em algo como:

No exemplo acima, o sistema está utilizando deadline. Não é a pior opção para banco de dados (maiores detalhes, favor pesquisar no google) mas há um ganho saliente na utilização de noop, que é o scheduler mais simples do Linux e que faz utilização de pipe (consultar também no google, por favor). Infelizmente eu tinha medições do ganho, mas não anotei as referências. Nesse ponto você tem 3 opções; confiar no que estou escrevendo, pesquisar no google ou testar você mesmo. Seja lá qual for sua opção, para mudar os valores utilize o seguinte comando:

Isso mudará a posição dos colchetes para o scheduler escolhido. Não se esqueça de colocar os comandos inseridos manualmente em algum arquivo de inicialização, como o /etc/rc.local por exemplo.

read_ahead_kb

Por padrão o Linux lê 128KB adiante da leitura solicitada para prover um cache. Isso pode ser incrementado para o tamanho suportado pelo seu disco e assim optimizar READ e AHEAD. Para apontar o valor exato, primeiro analise o seu disco:

Esse baixíssimo valor de 256 foi tirado do meu notebook para poder exemplificar. Um dos comandos para setar o valor correto deve ser inserido em algum script qo sistema, no caso, coloquei em /etc/rc.local, onde o servidor que configurei contém o valor de 8192.

noatime

E ainda dá pra fazer um pouco mais, mas leve em consideração que a base de dados deve estar em uma partição separada. Assim sendo, você pode editar o arquivo /etc/fstab e na linha referente à partição do banco, nas opções do filesystem troque o possível ‘default’ por noatime. Se as opções já forem diferentes de ‘default’, então apenas adicione mais essa opção, que guarda informações sobre a criação de arquivos e sua última modificação. Eliminando essas escritas em disco, sim, haverá um ganho de desempenho.

swappiness

Você provavelmente já reparou que em alguns casos muito antes de esgotar a memória já inicia-se a paginação em disco. Para evitar swapping desnecessariamente você pode alterar o valor de tolerância do seu sistema. Para ver o esquema atual, utilize o comando:

Se quiser reduzir a porcentagem de memória limite, edite seu arquivo /etc/sysctl.conf e adicione:

Ainda no filesystem, prefira utilizar XFS na partição da base de dados.

Tunning L3

Pensando em um número elevado de conexões, o reuso de sockets certamente é apreciado:




Segurança

Um pequeno ítem que ajuda muito na prevenção de problemas, é o controle do número de processos por usuário e o número de arquivos abertos por usuário. Arregale para o MySQL e controle os demais usuários:

Claro que o número acima pode não condizer com a realidade do seu hardware. Se puder fazer provas, um duro caminho para isso é utilizando um fork (isso vai congelar sua máquina se os valores acima não forem bons, por isso comece de um valor pequeno e vá subindo):

Replicação com MySQL 5.6

Há muita coisa boa também em relação à replicação, iniciando pela simplicidade. Não é mais necessário inserir uma gama de parâmetros como nas versões anteriores. Ainda assim, separei em um arquivo as poucas linhas referentes à replicação e fiz include a partir do my.cnf.

Quando fiz essa configuração, subi um dump dos dados em uma base recém-instalada. Obviamente a engine padrão dessa base manteve-se como MyISAM, mas eu queria mesmo era apenas 2 tabelas funcionando como InnoDB, apesar de ser pouco recomendado mesclar engines pela consideração da complexidade da manipulação de cada uma delas. Ainda assim pode ser uma vantagem em questão de economia de recursos e desempenho, no caso de tabelas que não recebem muitos updates. Já no caso de tabelas muito manipuladas, InnoDB (na versão inclusa no MySQL 5.6) sim é uma vantagem enorme considerando sua característica de fragmentação e somando a outras características, o desempenho pode ser de até 35x mais rápido que MyISAM, conforme descrito pela Oracle nesse link.

Então, após subir o dump bastou executar a primeira query (que serve para as versões anteriores do MySQL também):

Depois pode-se confirmar com:

E o resultado deverá ser algo parecido com isso:

Porém se estiver migrande uma tabela MyISAM muito grande, a melhor opção é migrar os dados invés de convertê-los. Crie uma segunda tabela com a estrutura da tabela MyISAM, trocando apens Engine=MyISAM por Engine=InnoDB:

Se algo der errado, você poderá fazer roll back.

Ao final do arquivo my.cnf, inclua a linha:

E dentro do diretório /etc/mysql/conf.d crie um arquivo chamado replication com o seguinte conteúdo:

Antes de reiniciar o serviço, você pode entrar na base e fazer os grants:

Algumas informações estão um pouco diferentes na saída de show slave statusG, dê uma observada por lá. Se precisar de mais alguma informação a respeito dessa saída ou comandos para testar a replicação, veja os posts anteriores citados acima.

GLB – Galera Load Balancer

Galera Load Balancer é um projeto baseado no Pen, que cria um proxy TCP no user-space. O projeto está hospedado nesse link. Baixe-o e proceda com a instalação conforme descrito dentro do pacote.

É bastante simples manipular conexões com ele e alguns exemplos estão descritos também dentro do diretório de instalação. Esse balanceador é parte de um outro projeto chamado Galera Cluster, uma ferramenta de clusterização muito interessante da qual escreverei posteriormente seu uso em um post exclusivo.

A grande diferença do GLB e do Pen é que o GLB faz balanceamento apenas de conexões TCP com objetivo focado em performance e escalabilidade. Sua versão atual é de março/2013 sendo a 1.0.0 RC1. Entre suas características estão destacados:

– lista de servidores de backend configurável em tempo de execução

– Suporta o modo “draining” – quer dizer, não aloca novas conexões para o servidor, mas não mata as existentes, aguardando que finalizem gentilmente.

– É multithread, podendo utilizar multi-cores. A recomendação é de que se seu kernel não suporta a API epoll, você deve considerar o uso de multithreads em uma máquina mesmo que seja single core.




– Opcionalmente o módulo watchdog pode monitorar os servidores alvo e ajustar automaticamente sua tabela de rotas – boa pedida para evitar o direcionamento de conexões para um servidor cuja aplicação esteja indisponível.

O GLB oferece 5 possíveis políticas de balanceamento:
1 – Least Connected – Novas conexões serão direcionadas ao servidor que tiver menos conexões, considerando o peso. Essa é a política padrão.
2 –round-robin – Cada nova conexão é roteada para o próximo servidor em uma lista de ordem circular. Isso quer dizer, servidor A, servidor B, servidor N, repetida e ordenadamente.
3 – single – Todas as conexões são enviadas ao servidor de maior peso até que ele falhe e a partir de então salta para o próximo nó considerando sempre o peso.
4 – random – Distribuição randômica entre os servidores
5 – source tracking – Conexões originadas de um dado endereço se destinam ao mesmo servidor. Extremamente necessário por exemplo quando uma conexão HTTP faz chunck.

O limite máximo de arquivos abertos é de 1024, tendo por default 493 – isso ainda respeita a limitação imposta pelo sistema operacional, então se você faz a limitação pelo limits.conf, não esqueça de adequar essa condição. O limite de 4096 resultará em 2029 conexões, incremente como necessário.

Uma característica interessantíssima é o gerenciamento em tempo de execução. Esse recurso está disponível em dois modos; via pipe de sistema e via conexão TCP. Para utilizar esse recurso com TCP, porta e endereço devem ser especificados com a opção ‘-c’.

As operações de inclusão, deleção e modificação são feitas enviando uma mensagem ao daemon no formato :[:weight]. Como notado, o peso é opcional sendo 1 o valor default. Se quiser fazer draining, basta enviar 0 como peso.

Um exemplo de envio para um servidor escutando localmente na porta 4444:

No exemplo, o comando foi enviado via NetCat no format citado anteriormente. Enviando “getinfo” invés de servidor:porta:peso, você obterá o status como no formato de exemplo:

O campo usage tem como máximo 1.0. Mas outro recurso muito interessante é a possibilidade de recolher dados estatísticos através do daemon. Isso é muito útil não só pela informação em sí, mas também pelo trabalho poupado em recolher a informação diretamente nos nós. Para tal, utilize o comando “getstat” que retornará algo no formato de exemplo a seguir:

Eu quebrei linha, mas eram apenas espaços. O formato original é proposital para facilitar a manipulação dos dados via script. No arquivo README estão disponíveis detalhes a respeito.

O script de watchdog é experimental ainda, mas acredito que valha experimentar. Sem o watchdog a verificação de disponibilidade de serviço se limita ao nível de rede. A flag de linha de comando para watchdog é ‘-w’. Exemplificando o uso:

Os scripts de watchdog dos serviços pretendidos devem estar contidos no diretório “files”. Intervalos podem ser especificados com a opção -i em segundos fracionados. Mais informações podem ser encontradas no próprio README e acredite, são suficientes.

Para rodá-lo como serviço do sistema, basta copiar o script glbd.sh para /etc/init.d/glbd e o script gldb.conf para /etc/default/glbd. Neste último, edite-o e habilite os parâmetros pretendidos.

A utilização desse serviço se dá bem considerando 2 servidores à frente e 2 bancos por trás. Os servidores da frente são os direcionadores, sendo que estes podem conter a aplicação web, por exemplo e a partir deles as conexões são redirecionadas ao banco. O balanceamento dos direcionadores pode ser feito através de switches ou roteadores na borda, ou ainda através de um IPV com UCARP, por exemplo.

Não cometa esse erro

As vezes você pode pensar em fazer aquele primeiro teste de desempenho:

Até em uma tabela grande o retorno deve vir em torno de 0.02 na tabela MyISAM. Isso acontece porque o MyISAM guarda o número exato de rows na memória. Aí o susto pode ser grande se você tentar fazer o mesmo com InnoDB, porque devido a sua atomicidade não faz sentido guardar o número de rows na memória, uma vez que esse valor pode ser impreciso. Então, se quiser um valor aproximado (e na grande maioria dos casos é mais que suficiente), utilize esse comando:

E leia o número de rows. Se você for fazer select count(*) from banco.tabela, saiba que isso será buscado em disco e haverá uma ligeira trava; ao menos ligeira na configuração acima, que dimensionei para um grande cliente. Tão grande que o número de dispositivos que acessam a base passa de 2 milhões, podendo acessar a base N vezes ao dia; cada requisição ao serviço pode gerar até 5 queries no banco, sem contar as transactions. No momento, o servidor em questão está com 920qps rodando inclusive o serviço web, sem slow queries, com reserva de memória de 14GB, com lvg de ~6.00 e I/O em disco em torno de 12% de sua capacidade.

Se você precisava de um case para ter incentivo a essa configuração, aí está!

Enfim, essa é uma configuração bastante flexível onde outros parâmetros podem ser ajustados tanto no sistema quanto na base. Tem experiência nisso? Que tal comentar possibilidades? ;-)

Como esse post já ficou um bocado grande vou deixar para falar de profiling em um outro post, talvez no mesmo que falarei sobre cluster com Galera. Gostou? Compartilhe!

Se gostou, não deixe de compartilhar; dê seu like no video e inscreva-se no nosso canal Do bit Ao Byte Brasil no YouTube.

Prefere seguir-nos pelo facebook? Basta curtir aqui.

Prefere twitter? @DobitAoByte.

Próximo post a caminho!

Comments

comments

Djames Suhanko

Djames Suhanko é Perito Forense Digital. Já atuou com deployer em sistemas de missão critica em diversos países pelo mundão. Programador Shell, Python, C, C++ e Qt, tendo contato com embarcados ( ora profissionalmente, ora por lazer ) desde 2009.

Deixe uma resposta