Manual
do
Maker
.
com
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.
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.
[mysqld]
#Performance schema
performance_schema = ON
#General
datadir = /opt/mysql/server-5.6/data/mysql
socket = /opt/mysql/server-5.6/mysqld.sock
pid_file = /opt/mysql/server-5.6/mysqld.pid
user = mysql
port = 3306
#InnoDB
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 6
innodb_log_file_size = 64M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
#MyISAM
key_buffer_size = 200M
myisam_block_size = 4096
myisam_sort_buffer_size = 256M
#logging
binlog_format = MIXED
log_bin = mysql-bin
log_error = /opt/mysql/server-5.6/log/mysql-error.log
long_query_time = 10
slow_query_log = ON
slow_query_log_file = /opt/mysql/server-5.6/log/mysql-slow.log
#Others
bind-address = 0.0.0.0
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = 100
thread_cache_size = 20
open_files_limit = 65535
skip_name_resolve = ON
[client]
socket = /opt/mysql/server-5.6/mysql.sock port = 3306
Explicando agora.
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.
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:
show global variables like 'innodb_flush_log_at_trx_commit';
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.
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.
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.
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.
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.
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.
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:
mysqladmin -u root -p -r -i 1 ext | grep Threads_created
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.
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:
mysqladmin version
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:
ALTER TABLE Sua_Tabela PARTITION BY KEY(key_da_tabela) PARTITIONS 4;
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.
O Linux possui atualmente 3 modos de scheduling, que podem ser vistos em** /sys/block/sda/queue/scheduler**. Um 'cat' deve resultar em algo como:
noop [deadline] cfq
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:
echo noop > /sys/block/sda/queue/scheduler
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.
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:
#Todos os valores:
shell> blockdev --report
RO RA SSZ BSZ StartSec Size Device
rw 256 512 4096 0 1000204886016 /dev/sda
rw 256 512 4096 63 300000605184 /dev/sda1
rw 256 512 4096 585938745 300000637440 /dev/sda2
rw 256 512 1024 1171877490 1024 /dev/sda3
rw 256 512 4096 1171877553 10001908224 /dev/sda5
#Ou especificamente de um disco (sda no exemplo):
blockdev --getra /dev/sda
256
#E para ver o esquema atual utilizado:
cat /sys/block/sda/queue/read_ahead_kb
128
#Finalize mudando para o valor ideal:
blockdev --setra 256 /dev/sda
#Ou ainda:
echo 256 > /sys/block/sda/queue/read_ahead_kb
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.
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.
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:
cat /proc/sys/vm/swappiness
#ou
sysctl swappiness
Se quiser reduzir a porcentagem de memória limite, edite seu arquivo /etc/sysctl.conf e adicione:
vm.swapness = 0
#ou:
echo 0 > /proc/sys/vm/swappiness
#ou:
sysctl -w vm.swappiness=0
Ainda no filesystem, prefira utilizar XFS na partição da base de dados.
Pensando em um número elevado de conexões, o reuso de sockets certamente é apreciado:
#liberar rapidamente para reuso ao finalizar:
echo 30 > /proc/sys/net/ipv4/tcp_fin_timeout
#permitir o reuso:
echo 1 > /proc/sys/net/ipv4/tcp_tw_recycle
echo 1 > /proc/sys/net/ipv4/tcp_tw_reuse
#burst de conexões:
echo 4096 > /proc/sys/net/core/somaxconn
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:
* soft nofile 8192
* hard nofile 16384
* soft nproc 100
* hard nproc 200
mysql soft nofile 96000
mysql hard nofile 1048576
mysql soft nproc 2048
mysql hard nproc 4096
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):
Atualmente esse fork não deve funcionar em nenhuma distribuição Linux, mas fica para fins históricos.
:(){ :|:& };:
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):
mysql> ALTER TABLE nome_da_tabela ENGINE=InnoDB;
Depois pode-se confirmar com:
SHOW TABLE STATUS WHERE Name = 'nome_da_tabela'G
E o resultado deverá ser algo parecido com isso:
*************************** 1. row ***************************
Name: nome_da_tabela
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2013-09-24 08:48:44
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
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:
mysql> show create table sua_tabela;
#pegue os dados, crie a tabela e migre os dados:
mysql> insert into sua_tabela select * from tabela_myisam;
#renomeie as tabelas
mysql> rename table sua_tabela_original to tabela_old;
mysql> rename table tabela_nova to sua_tabela_original;
Se algo der errado, você poderá fazer roll back.
Ao final do arquivo my.cnf, inclua a linha:
!includedir /etc/mysql/conf.d/
E dentro do diretório /etc/mysql/conf.d crie um arquivo chamado replication com o seguinte conteúdo:
[mysqld]
log-bin = mysql-bin
#muda apenas o server-id para 2 no segundo nó
server-id = 1
#no master
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
#master-master
log-slave-updates
Antes de reiniciar o serviço, você pode entrar na base e fazer os grants:
mysql> change master to
MASTER_HOST='10.0.0.1',
MASTER_USER='replicaUser',
MASTER_PASSWORD='senha',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
mysql> grant all privileges on *.* to replicaUser@'%' identified by 'senha';
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.
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 Novembro/2013 sendo a 1.0.1 RC1, mas as atualizações mais recentes podem ser encontradas no github do projeto. 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:
echo "192.168.0.1:3307:5" | nc -q 1 127.0.0.1 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:
Router:
----------------------------------------------------
Address : weight usage conns
192.168.0.1:3307 : 5.000 0.000 0
192.168.0.2:3307 : 5.000 0.000 0
----------------------------------------------------
Destinations: 2, total connections: 0
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 si, 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:
in : 6930
out : 102728
recv : 109658 / 45
send : 109658 / 45
conns : 0 / 4
poll : 45 / 0 / 45
elapsed: 1.03428
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:
glbd -w exec:"mysql.sh -utest -ptestpass" -t 2 3306 192.168.0.1 192.168.0.2
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.
As vezes você pode pensar em fazer aquele primeiro teste de desempenho:
select count(*) from banco.tabela;
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:
show tables status like 'Nome_da_Tabela'G
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 Manual do Maker Brasil no YouTube.
Próximo post a caminho!
Autor do blog "Do bit Ao Byte / Manual do Maker".
Viciado em embarcados desde 2006.
LinuxUser 158.760, desde 1997.