15 dicas úteis de ajuste e otimização de desempenho do MySQL/MariaDB

Este artigo mostrará algumas dicas básicas, mas úteis, sobre como otimizar e ajustar o desempenho do MySQL/MariaDB. Lembre-se de que este artigo pressupõe que você já tenha o MySQL ou MariaDB instalado. Se você ainda está se perguntando como instalá-los em seu sistema, você pode seguir nossos extensos guias aqui:

1. Instalando LAMP no RHEL/CentOS 7
2. Instalando LAMP no Fedora 22
3. Configurando LAMP no Ubuntu 15.04
4. Instalando MariaDB no Debian 8
5. Instale MariaDB no Gentoo Linux
6. Instale MariaDB no Arch Linux

Importante: Antes de começarmos – não aceite estas sugestões cegamente. Cada configuração do MySQL é única e requer reflexão adicional antes de fazer qualquer alteração.

Coisas que você precisa saber:

1. O arquivo de configuração MySQL/MariaDB está localizado em /etc/my.cnf. Cada vez que você modificar este arquivo, você precisará reiniciar o serviço MySQL para que as novas alterações tenham efeito.
2. Para escrever este artigo, a versão MySQL 5.6 foi usada como modelo.

1. Habilite arquivo por tabela do InnoDB

Primeiramente é importante explicar que o InnoDB é um mecanismo de armazenamento. MySQL e MariaDB usam InnoDB como mecanismo de armazenamento padrão. No passado, o MySQL costumava manter tabelas e índices de banco de dados em um espaço de tabela do sistema. Esta abordagem foi destinada a servidores cujo único propósito é o processamento de banco de dados e seu disco de armazenamento não é utilizado para quaisquer outros fins.

O InnoDB oferece uma abordagem mais flexível e cada informação do banco de dados é mantida em um arquivo de dados .ibd. Cada arquivo .ibd representa um espaço de tabela próprio. Dessa forma, operações de banco de dados como “TRUNCATE ” podem ser concluídas mais rapidamente e você também pode recuperar espaço não utilizado ao eliminar ou truncar uma tabela de banco de dados.

Outro benefício dessa configuração é o fato de que você pode manter algumas tabelas do banco de dados em um dispositivo de armazenamento separado. Isso pode melhorar muito a carga de E/S em seus discos.

O innodb_file_per_table está habilitado por padrão no MySQL 5.6 e superior. Você pode ver isso no arquivo /etc/my.cnf. A diretiva fica assim:

innodb_file_per_table=1

2. Armazene dados do banco de dados MySQL em partição separada

Nota: Esta configuração só funciona com MySQL, mas não com MariaDB.

Às vezes, a leitura/gravação do sistema operacional pode diminuir o desempenho do seu servidor MySQL, especialmente se estiver localizado no mesmo disco rígido. Em vez disso, eu recomendaria usar um disco rígido separado (de preferência SSD) para o serviço MySQL.

Para concluir, você precisará conectar a nova unidade ao seu computador/servidor. Para os fins deste artigo, assumirei que a unidade estará em /dev/sdb.

A próxima etapa é preparar a nova unidade:

fdisk /dev/sdb

Agora pressione “n” para criar uma nova partição. Em seguida, pressione “p” para tornar a nova partição primária. Depois disso, defina o número da partição de 1-4. Depois disso você selecionará o tamanho da partição. Pressione Enter aqui. Na próxima etapa você precisará configurar o tamanho da partição.

Se desejar usar o disco inteiro, pressione Enter mais uma vez. Caso contrário, você poderá definir manualmente o tamanho da nova partição. Quando estiver pronto, pressione “w” para escrever as alterações. Agora precisaremos criar um sistema de arquivos para nossa nova partição. Isso pode ser feito facilmente com:

mkfs.ext4 /dev/sdb1

Agora montaremos nossa nova partição em uma pasta. Nomeei minha pasta “ssd” e criei no diretório raiz:

mkdir /ssd/

Estamos prontos para montar a nova partição que acabamos de criar na nova pasta:

mount /dev/sdb1  /ssd/

Você pode realizar a montagem na inicialização adicionando a seguinte linha no arquivo /etc/fstab.

/dev/sdb1 /ssd ext3 defaults 0 0

Agora você está pronto para mover o MySQL para o novo disco. Primeiro pare o serviço MySQL com:

service mysqld stop

Eu recomendaria que você também interrompesse o Apache/nginx para evitar qualquer tentativa de gravação nos bancos de dados:

service httpd stop
service nginx stop

Agora copie todo o diretório MySQL na nova unidade:

cp /var/lib/mysql /ssd/ -Rp

Isso pode demorar um pouco dependendo do local dos seus bancos de dados MySQL. Assim que este processo for concluído, renomeie o diretório MySQL:

mv /var/lib/mysql /var/lib/mysql-backup

A seguir criaremos um link simbólico.

ln -s /ssd/mysql /var/lib/mysql

Agora você está pronto para iniciar seu MySQL e serviço web:

service mysqld start
service httpd start
service nginx start

Neste ponto, seus bancos de dados MySQL serão acessados a partir da nova unidade.