Otimizar o desempenho do MySQL em VMs Azure Linux

Importante

Os VM clássicos serão retirados a 1 de março de 2023.

Se utilizar recursos iaaS da ASM, por favor preencha a sua migração até 1 de março de 2023. Encorajamo-lo a fazer a troca mais cedo para aproveitar as muitas melhorias de funcionalidades em Azure Resource Manager.

Para mais informações, consulte a Migração dos seus recursos iaaS para a Azure Resource Manager até 1 de março de 2023.

Existem muitos fatores que afetam o desempenho do MySQL no Azure, tanto na seleção de hardware virtual como na configuração do software. Este artigo centra-se na otimização do desempenho através de configurações de armazenamento, sistema e base de dados.

Nota

O Azure tem dois modelos de implementação diferentes para criar e trabalhar com recursos: Azure Resource Manager e clássico. Este artigo cobre a utilização do modelo de implementação clássica. A Microsoft recomenda que as implementações mais novas utilizem o modelo Resource Manager. Para obter informações sobre as otimizações do Linux VM com o modelo Resource Manager, consulte o Seu Linux VM no Azure.

A partir de 15 de novembro de 2017, as máquinas virtuais só estarão disponíveis no portal do Azure.

Utilize o RAID numa máquina virtual Azure

O armazenamento é o fator chave que afeta o desempenho da base de dados em ambientes em nuvem. Em comparação com um único disco, o RAID pode fornecer um acesso mais rápido através da concordância. Para mais informações, consulte os níveis standard raid.

O tempo de produção de E/S do disco e o tempo de resposta de I/O em Azure podem ser melhorados através do RAID. Os nossos testes laboratoriais mostram que a produção de E/S pode ser duplicada e o tempo de resposta de I/S pode ser reduzido para metade, em média, quando o número de discos RAID é duplicado (de dois para quatro, quatro para oito, etc.). Consulte o Apêndice A para mais detalhes.

Além do disco I/O, o desempenho do MySQL melhora quando aumenta o nível RAID. Consulte o apêndice B para mais detalhes.

Também pode considerar o tamanho do pedaço. Em geral, quando se tem um tamanho maior, obtém-se uma sobrecarga mais baixa, especialmente para grandes escritos. No entanto, quando o tamanho do pedaço é demasiado grande, pode adicionar sobrecarga adicional que o impede de tirar partido do RAID. O tamanho predefinido atual é de 512 KB, o que se prova ser o ideal para a maioria dos ambientes de produção em geral. Consulte o Apêndice C para mais detalhes.

Existem limites para quantos discos pode adicionar para diferentes tipos de máquinas virtuais. Estes limites são detalhados em tamanhos de máquina virtual e serviço em nuvem para Azure. Necessitará de quatro discos de dados anexados para seguir o exemplo RAID neste artigo, embora possa optar por configurar o RAID com menos discos.

Este artigo pressupõe que já criou uma máquina virtual Linux e que tem o MYSQL instalado e configurado. Para obter mais informações sobre o início, consulte Como instalar o MySQL no Azure.

Configurar RAID em Azure

Os passos seguintes mostram como criar RAID em Azure utilizando o portal do Azure. Também pode configurar o RAID utilizando Windows PowerShell scripts. Neste exemplo, vamos configurar o RAID 0 com quatro discos.

Adicione um disco de dados à sua máquina virtual

No portal do Azure, vá ao painel de instrumentos e selecione a máquina virtual à qual pretende adicionar um disco de dados. Neste exemplo, a máquina virtual é mysqlnode1.

Clique em Discos e clique em Anexar Novo.

Crie um novo disco de 500 GB. Certifique-se de que a Preferência de Cache do anfitrião está definida para Nenhum. Quando terminar, clique em OK.

Isto adiciona um disco vazio na sua máquina virtual. Repita este passo mais três vezes para que tenha quatro discos de dados para RAID.

Pode ver as unidades adicionadas na máquina virtual olhando para o registo de mensagens de kernel. Por exemplo, para ver isto em Ubuntu, use o seguinte comando:

sudo grep SCSI /var/log/dmesg

Criar RAID com os discos adicionais

Os passos seguintes descrevem como configurar o raid de software no Linux.

Nota

Se estiver a utilizar o sistema de ficheiros XFS, execute os seguintes passos após a criação do RAID.

Para instalar o XFS na Debian, Ubuntu ou Linux Mint, utilize o seguinte comando:

apt-get -y install xfsprogs  

Para instalar o XFS no Fedora, CentOS ou RHEL, utilize o seguinte comando:

yum -y install xfsprogs  xfsdump

Criar um novo caminho de armazenamento

Utilize o seguinte comando para configurar uma nova via de armazenamento:

root@mysqlnode1:~# mkdir -p /RAID0/mysql

Copie os dados originais para o novo caminho de armazenamento

Utilize o seguinte comando para copiar dados para a nova trajetória de armazenamento:

root@mysqlnode1:~# cp -rp /var/lib/mysql/* /RAID0/mysql/

Modifique permissões para que o MySQL possa aceder (ler e escrever) ao disco de dados

Utilize o seguinte comando para modificar permissões:

root@mysqlnode1:~# chown -R mysql.mysql /RAID0/mysql && chmod -R 755 /RAID0/mysql

Ajuste o algoritmo de agendamento de E/O do disco

Linux implementa quatro tipos de algoritmos de agendamento de E/O:

• Algoritmo NOOP (Sem Operação)
• Algoritmo de prazo (Prazo)
• Algoritmo de fila completamente justo (CFQ)
• Algoritmo do período orçamental (antecipação)

Pode selecionar diferentes programadores de E/S em diferentes cenários para otimizar o desempenho. Num ambiente de acesso completamente aleatório, não há uma diferença significativa entre os algoritmos de CFQ e Deadline para o desempenho. Recomendamos que desemalte o ambiente de base de dados MySQL para o Prazo de Estabilidade. Se houver muita I/O sequencial, o CFQ pode reduzir o desempenho do disco de E/S.

Para SSD e outros equipamentos, NOOP ou Deadline podem obter um melhor desempenho do que o agendador predefinido.

Antes do núcleo 2.5, o algoritmo de agendamento de I/S predefinido é Deadline. Começando com o núcleo 2.6.18, o CFQ tornou-se o algoritmo de agendamento de I/O padrão. Pode especificar esta definição no tempo de arranque do kernel ou modificar dinamicamente esta definição quando o sistema estiver em funcionamento.

O exemplo a seguir demonstra como verificar e definir o programador padrão para o algoritmo NOOP na família de distribuição Debian.

Ver o atual programador de E/S

Para ver o programador executar o seguinte comando:

root@mysqlnode1:~# cat /sys/block/sda/queue/scheduler

Verá a seguinte saída, o que indica o atual programador:

noop [deadline] cfq

Alterar o dispositivo atual (/dev/sda) do algoritmo de agendamento de E/O

Executar os seguintes comandos para alterar o dispositivo atual:

azureuser@mysqlnode1:~$ sudo su -
root@mysqlnode1:~# echo "noop" >/sys/block/sda/queue/scheduler
root@mysqlnode1:~# sed -i 's/GRUB_CMDLINE_LINUX=""/GRUB_CMDLINE_LINUX_DEFAULT="quiet splash elevator=noop"/g' /etc/default/grub
root@mysqlnode1:~# update-grub

Nota

Definir isto para /dev/sda por si só não é útil. Deve ser definido em todos os discos de dados onde a base de dados reside.

Deve ver a seguinte saída, indicando que a .cfg de comida foi reconstruída com sucesso e que o programador predefinido foi atualizado para a NOOP:

Generating grub configuration file ...
Found linux image: /boot/vmlinuz-3.13.0-34-generic
Found initrd image: /boot/initrd.img-3.13.0-34-generic
Found linux image: /boot/vmlinuz-3.13.0-32-generic
Found initrd image: /boot/initrd.img-3.13.0-32-generic
Found memtest86+ image: /memtest86+.elf
Found memtest86+ image: /memtest86+.bin
done

Para a família de distribuição do Chapéu Vermelho, só precisa do seguinte comando:

echo 'echo noop >/sys/block/sda/queue/scheduler' >> /etc/rc.local

Configurar definições de operações de ficheiros de sistema

Uma das melhores práticas é desativar a funcionalidade de registo a tempo no sistema de ficheiros. A hora é a última hora de acesso ao ficheiro. Sempre que um ficheiro é acedido, o sistema de ficheiros regista o tempo de registo no registo. No entanto, esta informação raramente é usada. Pode desativá-lo se não precisar, o que reduzirá o tempo de acesso ao disco.

Para desativar a registo de uma hora, é necessário modificar o ficheiro de configuração do sistema de ficheiros /etc/fstab e adicionar a opção noatime .

Por exemplo, edite o ficheiro vim /etc/fstab, adicionando o noatime como mostrado na amostra seguinte:

# CLOUD_IMG: This file was created/modified by the Cloud Image build process
UUID=3cc98c06-d649-432d-81df-6dcd2a584d41       /        ext4   defaults,discard        0 0
#Add the “noatime” option below to disable atime logging
UUID="431b1e78-8226-43ec-9460-514a9adf060e"     /RAID0   xfs   defaults,nobootwait, noatime 0 0
/dev/sdb1       /mnt    auto    defaults,nobootwait,comment=cloudconfig 0       2

Em seguida, remonte o sistema de ficheiros com o seguinte comando:

mount -o remount /RAID0

Teste o resultado modificado. Quando modifica o ficheiro de teste, o tempo de acesso não é atualizado. Os exemplos a seguir mostram como é o código antes e depois da modificação.

Antes:

Depois:

Aumente o número máximo de pegas do sistema para uma elevada concordância

MySQL é uma base de dados de alta concordância. O número predefinido de pegas simultâneas é de 1024 para o Linux, o que nem sempre é suficiente. Utilize os seguintes passos para aumentar as pegas simultâneas máximas do sistema para suportar a elevada concordância do MySQL.

Modifique o ficheiro limites.conf

Para aumentar as pegas simultâneas máximas permitidas, adicione as seguintes quatro linhas no ficheiro /etc/security/limits.conf. Note que 65536 é o número máximo que o sistema pode suportar.

* soft nofile 65536
* hard nofile 65536
* soft nproc 65536
* hard nproc 65536

Atualizar o sistema para os novos limites

Para atualizar o sistema, execute os seguintes comandos:

ulimit -SHn 65536
ulimit -SHu 65536

Certifique-se de que os limites são atualizados na hora do arranque

Coloque os seguintes comandos de arranque no ficheiro /etc/rc.local para que produza efeitos na hora do arranque.

echo “ulimit -SHn 65536” >>/etc/rc.local
echo “ulimit -SHu 65536” >>/etc/rc.local

Otimização da base de dados MySQL

Para configurar o MySQL no Azure, pode utilizar a mesma estratégia de afinação de desempenho que utiliza numa máquina no local.

As principais regras de otimização de E/S são:

• Aumente o tamanho da cache.
• Reduzir o tempo de resposta de E/O.

Para otimizar as definições do servidor MySQL, pode atualizar o ficheiro my.cnf, que é o ficheiro de configuração padrão tanto para computadores servidores como para computadores clientes.

Os seguintes itens de configuração são os principais fatores que afetam o desempenho do MySQL:

• innodb_buffer_pool_size: O conjunto tampão contém dados tampão e o índice. Isto geralmente é definido para 70% da memória física.
• innodb_log_file_size: Este é o tamanho do registo de redo. Utilize registos de redo para garantir que as operações de escrita são rápidas, fiáveis e recuperáveis após um acidente. Isto está definido para 512 MB, o que lhe dará muito espaço para operações de gravação.
• max_connections: Por vezes, as aplicações não fecham corretamente as ligações. Um valor maior dará ao servidor mais tempo para reciclar ligações idled. O número máximo de ligações é de 10.000, mas o máximo recomendado é de 5.000.
• Innodb_file_per_table: Esta definição permite ou desativa a capacidade do InnoDB de armazenar tabelas em ficheiros separados. Ligue a opção de garantir que várias operações de administração avançadas possam ser aplicadas de forma eficiente. Do ponto de vista de desempenho, pode acelerar a transmissão do espaço da tabela e otimizar o desempenho da gestão de detritos. A definição recomendada para esta opção é ON.
  
  Do MySQL 5.6, a definição predefinida é ON, pelo que não é necessária qualquer ação. Para versões anteriores, a definição predefinida é OFF. A definição deve ser alterada antes de os dados serem carregados, porque apenas as tabelas recém-criadas são afetadas.
• innodb_flush_log_at_trx_commit: O valor predefinido é 1, com o âmbito definido para 0~2. O valor predefinido é a opção mais adequada para o MySQL DB autónomo. A definição de 2 permite a maior integridade dos dados e é adequada para Master no Cluster MySQL. A definição de 0 permite a perda de dados, o que pode afetar a fiabilidade (em alguns casos com melhor desempenho), e é adequado para Slave no Cluster MySQL.
• Innodb_log_buffer_size: O tampão de registo permite que as transações sejam executadas sem ter de descarregar o registo para o disco antes que as transações se comprometam. No entanto, se houver um grande objeto binário ou campo de texto, a cache será consumida rapidamente e o disco frequente I/O será acionado. É melhor aumentar o tamanho do tampão se Innodb_log_waits variável do estado não for 0.
• query_cache_size: A melhor opção é desativá-lo desde o início. Defina query_cache_size a 0 (esta é a definição padrão no MySQL 5.6) e utilize outros métodos para acelerar as consultas.

Consulte o Apêndice D para uma comparação de desempenho antes e depois da otimização.

Ligue o registo de consulta lenta MySQL para analisar o estrangulamento de desempenho

O registo de consulta lenta MySQL pode ajudá-lo a identificar as consultas lentas para o MySQL. Depois de ativar o registo de consulta lenta MySQL, pode utilizar ferramentas MySQL como mysqldumpslow para identificar o estrangulamento de desempenho.

Por padrão, isto não está ativado. Ligar o registo de consulta lenta pode consumir alguns recursos da CPU. Recomendamos que o ative temporariamente para resolver problemas de desempenho. Para ligar o registo de consulta lenta:

1. Modifique o ficheiro my.cnf adicionando as seguintes linhas até ao fim:

    long_query_time = 2
    slow_query_log = 1
    slow_query_log_file = /RAID0/mysql/mysql-slow.log
   

2. Reinicie o servidor MySQL.

    service  mysql  restart
   

3. Verifique se a definição está a produzir efeitos utilizando o comando de exibição .

Neste exemplo, pode ver-se que a função de consulta lenta foi ligada. Em seguida, pode utilizar a ferramenta mysqldumpslow para determinar estrangulamentos de desempenho e otimizar o desempenho, como adicionar índices.

Apêndices

Seguem-se os dados de teste de desempenho da amostra produzidos num ambiente de laboratório direcionado. Eles fornecem o fundo geral sobre a tendência de dados de desempenho com diferentes abordagens de afinação de desempenho. Os resultados podem variar em diferentes ambientes ou versões de produtos.

Anexo A

Desempenho do disco (IOPS) com diferentes níveis raid

Comandos de teste

fio -filename=/path/test -iodepth=64 -ioengine=libaio -direct=1 -rw=randwrite -bs=4k -size=5G -numjobs=64 -runtime=30 -group_reporting -name=test-randwrite

Nota

A carga de trabalho deste teste utiliza 64 fios, tentando atingir o limite superior do RAID.

Apêndice B

Comparação de desempenho mySQL (produção) com diferentes níveis de RAID
(sistema de ficheiros XFS)

Comandos de teste

mysqlslap -p0ps.123 --concurrency=2 --iterations=1 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-guid-primary --number-of-queries=10000 --auto-generate-sql-load-type=write –engine=innodb

Comparação do desempenho mySQL (OLTP) com diferentes níveis raid

Comandos de teste

time sysbench --test=oltp --db-driver=mysql --mysql-user=root --mysql-password=0ps.123  --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-socket=/var/run/mysqld/mysqld.sock --mysql-db=test --oltp-table-size=1000000 prepare

Apêndice C

Comparação do desempenho do disco (IOPS) para diferentes tamanhos de pedaços
(sistema de ficheiros XFS)

Comandos de teste

fio -filename=/path/test -iodepth=64 -ioengine=libaio -direct=1 -rw=randwrite -bs=4k -size=30G -numjobs=64 -runtime=30 -group_reporting -name=test-randwrite
fio -filename=/path/test -iodepth=64 -ioengine=libaio -direct=1 -rw=randwrite -bs=4k -size=1G -numjobs=64 -runtime=30 -group_reporting -name=test-randwrite  

Os tamanhos de ficheiro utilizados para este teste são de 30 GB e 1 GB, respectivamente, com sistema de ficheiros XFS RAID 0 (4 discos).

Apêndice D

Comparação do desempenho do MySQL (produção) antes e depois da otimização
(Sistema de ficheiros XFS)

Comandos de teste

mysqlslap -p0ps.123 --concurrency=2 --iterations=1 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-guid-primary --number-of-queries=10000 --auto-generate-sql-load-type=write –engine=innodb,misam

A definição de configuração para predefinição e otimização é a seguinte:

Parâmetros	Predefinição	Otimização
innodb_buffer_pool_size	Nenhuma	7 GB
innodb_log_file_size	5 MB	512 MB
max_connections	100	5000
innodb_file_per_table	0	1
innodb_flush_log_at_trx_commit	1	2
innodb_log_buffer_size	8 MB	128 MB
query_cache_size	16 MB	0

Para obter parâmetros de configuração de otimização mais detalhados, consulte as instruções oficiais do MySQL.

Ambiente de teste

Hardware	Detalhes
CPU	Processador AMD Opteron(tm) 4171 HE/4
Memória	14 GB
Disco	10 GB/disco
SO	Ubuntu 14.04.1 LTS