3 Dicas para otimizar a sua base de dados e acelerar o seu site

Se você é daqueles que sempre querem ver seus sites com maior desempenho, hoje os nossos engenheiros prepararam um conteúdo mais técnico para dar aquele up na velocidade da sua base de dados, o que irá otimizar otimizar o seu site como um todo.

Ah, se você já hospeda aqui na Infinite, a boa notícia é que suas bases já estão otimizadas! Mas você pode continuar lendo para encontrar algumas dicas mais avançadas sobre como deixar sua base de dados ainda melhor.

A importância de ter bases de dados otimizadas

Sabe quando você vai pagar uma conta em um banco, mas todos os caixas já estão ocupados? Assim como no banco, você é obrigado a aguardar na fila até que um dos caixas libere, o que pode deixar as coisas um pouquinho demoradas.

Quando um site não possui nenhum tipo de otimização na base de dados, é comum que ele comece à consumir recursos computacionais demais e, consequentemente, passe a sofrer quedas ocasionados pelo tão temido ERROR 1040 (08004): Too many connections.

Isso acontece porque o MySQL tem uma variável de configuração que estipula um limite de conexões seguras simultaneamente e, quando esse limite é atingido, o erro é exibido.

A solução então seria incrementar o limite, certo? Depende, por exemplo:

Sites com bastante tráfego, com algo como 800 visitantes simultâneos, no geral não chegam a ocupar nem 100 conexões na base de dados, por exemplo, o que não representa grande perigo.

“Mas então como eu resolvo isso? Esse erro tá acabando com o meu site!” Uma das soluções mais eficazes na maioria das vezes, é otimizar. As dicas abaixo vão te ajudar elevar o seu site para o próximo nível 🙂

1) Altere a engine das tabelas de MyISAM para InnoDB

Se você ainda tem tabelas usando a Storage Engine MyISAM, considere alterá-las para InnoDB. As novas versões do MySQL já trazem essa engine como padrão para criações de novas tabelas devido à sua alta confiabilidade e performance.

Não tem certeza de qual engine é utilizada nas tuas tabelas? Softwares como o phpMyAdmin (normalmente presentes no teu painel de controle) podem exibir isso para você facilmente:

E se você já for mais familiarizado com SQL, utilize a consulta abaixo para listar todas que ainda estão usando MyISAM. Você pode executar a consulta tanto no phpMyAdmin quanto em qualquer outro software que aceite consultas SQL, inclusive no SSH de seu plano.

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE = "MyISAM";

Se você encontrou alguma tabela que ainda usa MyISAM, bora converter para InnoDB!

ATENÇÃO! Embora a conversão seja rápida e geralmente segura, alguns sistemas mais antigos não reconhecem o InnoDB em versões mais novas do MySQL. Recomendamos fazer um backup antes e proceder por sua conta e risco, ok? Se caso algo der errado e seu site começar a apresentar algum erro, é só retornar ao MyISAM ou restaurar seu backup. Lembrando que aqui na Infinite você possui até 15 dias de backups feitos diariamente à sua disposição 🙂

Agora que você já tomou os cuidados devidos, basta utilizar a seguinte consulta:

ALTER TABLE minha_base.minha_tabela ENGINE='InnoDB';

Você vai precisar editar a consulta acima para o nome da sua base e da tabela em questão

Se quiser fazer pelo phpMyAdmin, basta encontrar a tabela e clicar na aba “Operações” para realizar a troca:

Caso você queira retornar para a MyISAM se quiser: é só usar a mesma consulta acima, mas alterando a engine ou restaurando seu backup 🙂

Encontrou tabelas demais? O script abaixo faz o trabalho para você:

Não esqueça de mudar os campos DBNAME, DBUSER e DBPASS para seus dados reais, ok?

Agora que já convertemos nossas tabelas, hora de fazer uma faxina.

2) Tenha uma rotina de limpeza e manutenção

O maior segredo para ter bases de dados rápidas é uma boa rotina de limpeza e manutenção, garantindo que elas continuarão rodando em seu máximo de performance por mais tempo.

Isso porque, com o passar do tempo, sua aplicação vai começar a acumular dados que não precisam mais estar lá, como logs, comentários spam de postagens antigas, configurações de plugins que nem estão mais instalados.

Essas coisas são como aquela sujeirinha que vai se acumulando devagarzinho na ventoinha do seu computador até que chega um dia em que você pensa: “Puxa, meu computador não aquecia tanto!”. Da mesma forma, esse acúmulo de informações desnecessárias pode te dar a impressão que “minha base já foi mais rápida!” ou “meu site nem é tão grande, por que essa base de dados tem 5GB?”

Pois é, o ideal é que a sua base de dados só contenha o que realmente é usado pelo seu site, então estabelecer uma rotina de manutenção é crucial para manter a boa performance do seu sistema. Dados antigos, por exemplo, podem ser enviados para arquivos de log e armazenados num Object Storage.

Aqui vão algumas dicas para te ajudar a identificar o tamanho da sua base e as maiores tabelas, bastando executar as consultas logo abaixo exatamente como estão. Você pode executar as consultas no phpMyAdmin também, na aba “SQL” se preferir:

1. Descubra o tamanho da sua base de dados (em MB):

   SELECT table_schema AS 'Nome da base', ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS 'Tamanho da base em MB' FROM information_schema.tables GROUP BY table_schema;

2. Identifique quais são suas 10 maiores tabelas, ordenando pelo seu tamanho em MB

   SELECT TABLE_SCHEMA, TABLE_NAME, round(((data_length + index_length) / 1024 / 1024), 2) 'Tamanho em MB' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC LIMIT 10;

3. Identifique quais tabelas possuem mais linhas:

   SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_ROWS > 10000 ORDER BY TABLE_ROWS DESC;

   No exemplo acima, estamos listando todas as tabelas que possuem mais de 10 mil linhas, mas você pode adaptar a quantidade de linhas para sua necessidade 😉

Agora vamos vasculhar tudo que podemos limpar dessa listagem.

• Logs de transações antigas;
• Comentários spam de 300 anos atrás;
• Informações de plugins que nem estão mais instalados;
• E coisa do tipo...

Se você conseguir fazer uma boa limpeza ao ponto de que todos os teus dados caibam no pool do InnoDB -- uma área de memória reservada para as tabelas InnoDB -- você provavelmente terá um bom ganho de performance na base. Caso não tenha na base, o ganho vai ser sentido na redução de tempo de espera de disco e CPU 🙂

O que é o pool do InnoDB?

Ok! Agora que você fez uma faxina, é importante que sua base de dados tenha um tamanho compatível com a memória RAM do seu plano também.

O consumo de RAM do MySQL é a soma dos buffers, conexões e processamento das consultas

Uma das grandes vantagens do InnoDB é que ele reserva uma área da memória do teu plano para colocar os dados das tabelas. Dessa forma, toda vez que algo requisita ou manda uma informação para uma tabela InnoDB e ela estiver nesse buffer de memória, a operação vai reduzir o tempo de espera de disco e da CPU.

Explicando com mais detalhes, o MySQL tem uma configuração chamada innodb_buffer_pool_size. Esse buffer serve para salvar em cache as tabelas InnoDB, evitando que toda e qualquer consulta fique consumindo CPU e Disk IO. A ideia aqui é ganhar performance, e, como já podemos imaginar, esse buffer fica na RAM.

E não, você não pode mudar isso. Só quem pode fazer esse tipo de alteração é sua empresa de hospedagem -- a não ser que você tenha um VPS ou Cloud Dedicado. (À propósito, você sabe a diferença dessas hospedagens?)

Não é recomendado que esse buffer ocupe mais de 60% da RAM, visto que temos ainda processos PHP e de sistema que também consomem memória. Para um plano com 32GB de RAM, por exemplo, o innodb_buffer_pool_size não deveria ter mais de 19,2GB (32 * 0.6).

Vale mencionar também que você não precisa ter um buffer muito maior que tua base de dados mesmo que tenha RAM disponível, pois isso seria um desperdício de RAM.

Quer saber qual é o tamanho do innodb_buffer_pool_size no teu plano atual?

MariaDB [(none)]> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                          20.000000000000 |
+------------------------------------------+
1 row in set (0.000 sec)

20GB no exemplo acima, muchacho!

Agora se estamos falando de uma nuvem exclusiva para tua base de dados, em que não há processamento de PHP, servidor web e outros tipos de serviços, então esse buffer poderia ocupar até 80% da RAM disponível.

3) Faça bom uso do innodb_buffer_pool_size

O cenário ideal é que o tamanho da tua base caiba no buffer. Se você já passou disso e não consegue limpar mais nada, talvez esteja chegando a hora de considerar um upgrade para um plano maior, a fim de contar com mais RAM.

Quanto mais dados no MySQL, maior deve ser o innodb_buffer_pool_size para que as consultas fiquem rápidas e o número de conexões simultâneas diminua por consequência.

Em contrapartida, quanto menor o innodb_buffer_pool_size, mais lento será o processamento de suas consultas no MySQL, maior será o número de conexões (pois estão esperando resposta), e também maiores serão as chances de um processo mais pesadinho travar o site todo. OMG!

Concluindo

Essas dicas são eficazes para melhorar a performance da sua base de dados, mas para deixar tudo ainda melhor você também precisa montar consultas SQL com qualidade. Confira esse artigo pois ele tem boas práticas que valem a pena conhecer.

O ideal não é forçar os limites do banco, servidor, ou mesmo trocar o framework de desenvolvimento, mas sim otimizar a arquitetura e as consultas que são feitas no MySQL.

Também recomendamos a leitura do ebook High Performance MySQL da Editora O'Reilly para entender exatamente como montar uma arquitetura e consultas que não sobrecarregam o banco de dados. A parte específica sobre otimização de consultas MySQL está disponível de graça nesse link. (Embora continue sendo uma boa ideia você ler o livro todo!)

Outra coisa que vai te ajudar muito, é confirmar que o site esteja usando uma ferramenta de caching como por exemplo o PhpFastCache com ou sem a ajuda de um Redis/Memcached.

Quando uma aplicação faz uso de um bom sistema de cache, isso evita que cada acesso tenha que ir lá na base de dados executar a mesma consulta vez após vez.

Aliás, sabia que clientes Infinite já possuem Redis/Memcached disponível nos seus planos gratuitamente? Migre pra cá!

Caso sua aplicação ainda não use um ORM como Eloquent, Doctrine e Medoo, vale a pena fazer uma pesquisa no assunto.

Um ORM geralmente entrega uma camada de abstração para base de dados que é mais otimizada que os drivers padrões do PHP como o PDO ou MySQLi. Não é nada mágico que irá resolver todos os problemas, mas pode evitar a necessidade de debugs constantes e agilizar o desenvolvimento.

Muitos sistemas usam mecanismos de cache e consultas bem otimizadas, então isso ajuda muito na performance, desafoga a base de dados e faz o site voar 😀

Num artigo futuro, vamos te ensinar a fazer uma análise ainda mais interessante, para ver se o consumo do site é realmente gerado por consultas SELECT, outro tipo de consulta ou mesmo algo na arquitetura com a ajuda de profilers para PHP. Tenho certeza que você vai gostar! 😉