Como melhorar o de­sem­pe­nho do MySQL em ser­vi­do­res Linux

O de­sem­pe­nho de um banco de dados fre­quen­te­mente decide sobre a ve­lo­ci­dade e a es­ta­bi­li­dade das apli­ca­ções web. MariaDB e MySQL oferecem várias pos­si­bi­li­da­des de oti­mi­za­ção: desde con­fi­gu­ra­ção, passando por índices, até caching e re­pli­ca­ção. Com medidas es­pe­cí­fi­cas, é possível usar os recursos do banco de dados de forma mais eficiente e reduzir sig­ni­fi­ca­ti­va­mente os tempos de resposta para consultas.

Por que otimizar MySQL e MariaDB

A oti­mi­za­ção de MariaDB e MySQL é sensata porque bancos de dados não oti­mi­za­dos chegam a seus limites ra­pi­da­mente. Um de­sem­pe­nho ruim pode levar a tempos de car­re­ga­mento longos demais, a timeouts ou até mesmo a falhas no sistema.

Além disso, em caso de grandes volumes de dados ou muitos acessos si­mul­tâ­neos, cada consulta pode so­bre­car­re­gar a CPU e a memória RAM. O ar­ma­ze­na­mento de dados re­dun­dan­tes ou a falta de índices também afetam ne­ga­ti­va­mente a ve­lo­ci­dade. Um banco de dados otimizado reduz a carga do sistema, melhora a es­ca­la­bi­li­dade e garante que as apli­ca­ções funcionem de maneira estável, mesmo sob alta carga.

Como otimizar o MySQL e o MariaDB

Existem muitas maneiras de otimizar um banco de dados MariaDB ou MySQL em um servidor Linux. Isso inclui ajustes na con­fi­gu­ra­ção, oti­mi­za­ção de índices, melhorias nas consultas, tuning do InnoDB e uso de caching ou re­pli­ca­ção. A seguir, são apre­sen­ta­das as prin­ci­pais medidas.

Opção 1: iden­ti­fi­car consultas lentas

Parte do processo para otimizar MySQL e MariaDB é verificar consultas lentas ou ine­fi­ci­en­tes. Uma consulta mal es­tru­tu­rada em MySQL ou em MariaDB pode reduzir o de­sem­pe­nho de toda a base de dados.

O MySQL e o MariaDB podem ser con­fi­gu­ra­dos para registrar todas as consultas que demorem mais do que o número es­pe­ci­fi­cado de segundos. Dessa forma, você pode acom­pa­nhar todas as consultas lentas e corrigi-las, se ne­ces­sá­rio.

Para ativar o registro de consultas lentas, acesse o MySQL/MariaDB:

mysql -u root -p

Digite o seguinte comando para ativar o registro:

SET GLOBAL slow_query_log = 'ON';

O limite padrão é de 10 segundos. Use o comando a seguir para ativar o registro para cada consulta que demore mais de 1 segundo:

SET GLOBAL long_query_time = 1;

Consultas que demorarem mais de 1 segundo serão re­gis­tra­das em /var/lib/mysql/hostname-slow.log.

Fer­ra­men­tas de mo­ni­to­ra­mento como mysqltuner ou performance_schema também podem ser uti­li­za­das e fornecem dicas valiosas para re­co­nhe­cer quais consultas podem ser oti­mi­za­das.

Opção 2: ajustar con­fi­gu­ra­ção do InnoDB

A con­fi­gu­ra­ção do MariaDB e do MySQL é um dos métodos mais eficazes para melhorar de­sem­pe­nho de forma sus­ten­tá­vel. Muitas con­fi­gu­ra­ções padrão usam valores genéricos, pro­je­ta­dos para ambientes de teste menores, mas não para sistemas pro­du­ti­vos com muitas consultas. Ajustar os pa­râ­me­tros do InnoDB de forma di­re­ci­o­nada pode permitir que o banco de dados utilize os recursos de maneira mais eficiente.

Pa­râ­me­tros im­por­tan­tes incluem, entre outros:

• innodb_flush_log_at_trx_commit: essa con­fi­gu­ra­ção oferece um commit con­si­de­rá­vel em relação a de­sem­pe­nho e con­fi­a­bi­li­dade. Por padrão, o valor é 1, o que significa que cada transação é ime­di­a­ta­mente escrita no disco. Isso garante máxima segurança, mas pode reduzir sig­ni­fi­ca­ti­va­mente o de­sem­pe­nho com alta carga de tran­sa­ções. Um valor de 2 reduz bastante as operações de I/O, enquanto há um risco baixo de perda de dados em caso de falha.
• innodb_log_file_size: o tamanho dos arquivos de log do InnoDB tem impacto direto na ve­lo­ci­dade das operações de gravação. Arquivos de log maiores sig­ni­fi­cam que mais tran­sa­ções podem ser acu­mu­la­das na memória antes de serem escritas no disco.
• innodb_file_per_table: com essa opção, um arquivo de ta­bles­pace separado é criado para cada tabela InnoDB. Isso oferece várias vantagens: facilita o ge­ren­ci­a­mento de tabelas grandes, reduz a frag­men­ta­ção dentro do ta­bles­pace comum e pode melhorar o de­sem­pe­nho durante backups.
• innodb_buffer_pool_size: esse valor deve re­pre­sen­tar entre 50 a 80% da memória RAM dis­po­ní­vel para manter o máximo possível de dados e índices na memória.
• innodb_flush_method: esta opção determina como o InnoDB grava dados e logs no disco. Defina esse valor como O_DIRECT para evitar a dupla me­mo­ri­za­ção em buffer dos dados.

Para alterar as con­fi­gu­ra­ções do InnoDB, abra o arquivo my.cnf para edição. Uma con­fi­gu­ra­ção em my.cnf poderia, por exemplo, ser assim:

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_flush_method = O_DIRECT

Reinicie o MariaDB/MySQL para que as al­te­ra­ções entrem em vigor.

Opção 3: ajustar índices

Os índices são es­sen­ci­ais para melhorar o de­sem­pe­nho do MySQl ou das consultas MariaDB. Em vez de buscar cada linha de uma tabela, o banco de dados pode usar o índice para ir di­re­ta­mente aos registros re­le­van­tes.

No entanto, muitas vezes ou índices in­cor­re­tos podem ser con­tra­pro­du­cen­tes. Cada índice adicional requer espaço de ar­ma­ze­na­mento. Portanto, é im­por­tante indexar apenas as colunas que realmente são usadas com frequên­cia.

Um exemplo simples: Suponha que temos uma tabela users e fre­quen­te­mente buscamos pela coluna email. Nesse caso, um índice pode acelerar con­si­de­ra­vel­mente as consultas:

CREATE INDEX idx_user_email ON users(email);

Com esse índice, consultas como

SELECT * FROM users WHERE email='xyz@example.com';

Podem ser exe­cu­ta­das muito mais ra­pi­da­mente, porque o banco de dados não precisa procurar em cada linha da tabela, mas acessa di­re­ta­mente os registros cor­res­pon­den­tes.

Além disso, índices com­bi­na­dos podem ser úteis quando várias colunas são fre­quen­te­mente usadas juntas em consultas.

Você deve remover re­gu­lar­mente índices que não são mais ne­ces­sá­rios ou raramente uti­li­za­dos para eco­no­mi­zar espaço e melhorar o de­sem­pe­nho de escrita. No exemplo, o índice idx_old_column é excluído:

DROP INDEX idx_old_column ON users;

Opção 4: otimizar consultas

Consultas SQL complexas ou ine­fi­ci­en­tes podem so­bre­car­re­gar o banco de dados e piorar a per­for­mance, es­pe­ci­al­mente em tabelas grandes. Para otimizar consultas, você deve primeiro verificar como o banco de dados executa a consulta. Para isso, o comando EXPLAIN é útil.

EXPLAIN SELECT id, email FROM users WHERE status='active';

Com EXPLAIN, o MySQL e o MariaDB mostram quais índices são usados, quantas linhas precisam ser lidas e em que ordem as tabelas são pro­ces­sa­das. Assim, você pode ver se a consulta é eficiente ou se oti­mi­za­ções adi­ci­o­nais são ne­ces­sá­rias, como adicionar índices ou ajustar joins.

Evite consultas do tipo SELECT *, pois isso carrega muitas colunas que não são ne­ces­sá­rias. Em vez disso, consulte apenas as colunas que realmente precisa. Isso reduz a quan­ti­dade de dados trans­fe­ri­dos e melhora a ve­lo­ci­dade da consulta. Em joins complexos, vale a pena formular as condições na cláusula WHERE de forma precisa, para evitar var­re­du­ras des­ne­ces­sá­rias de toda a tabela.

Opção 5: re­pli­ca­ção e caching

A re­pli­ca­ção, ou seja, a dis­tri­bui­ção da carga em vários ser­vi­do­res, bem como o caching, para reduzir o número de acessos diretos ao banco de dados, podem ajudar a otimizar o de­sem­pe­nho do MariaDB e do MySQL.

Na re­pli­ca­ção, ge­ral­mente é utilizado o chamado princípio master-slave: o servidor master processa todas as operações de escrita, enquanto um ou mais ser­vi­do­res slave replicam os dados e assumem consultas de leitura. Assim, o banco de dados pode lidar com alta carga de forma mais eficiente, sem so­bre­car­re­gar o servidor master. A con­fi­gu­ra­ção e a im­ple­men­ta­ção da re­pli­ca­ção requerem um esforço inicial, mas trazem uma melhoria de de­sem­pe­nho, es­pe­ci­al­mente em apli­ca­ções com alta uti­li­za­ção.

Além disso, o Caching pode melhorar sig­ni­fi­ca­ti­va­mente os tempos de resposta. O MySQL/MariaDB oferece o Query Cache, que armazena em cache os re­sul­ta­dos de consultas fre­quen­te­mente repetidas. Dessa forma, as mesmas consultas não precisam ser exe­cu­ta­das novamente. Com as con­fi­gu­ra­ções a seguir, você pode definir o tamanho do cache e ativar o Query Cache:

SET GLOBAL query_cache_size = 64*1024*1024;
SET GLOBAL query_cache_type = 1;

Para apli­ca­ções modernas, também é acon­se­lhá­vel utilizar soluções de caching externas, como o Redis, que podem acessar dados fre­quen­te­mente ne­ces­sá­rios com ainda mais rapidez.

Opção 6: par­ti­ci­o­na­mento de tabelas

Em tabelas muito grandes, o pro­ces­sa­mento de consultas pode demorar mais, pois o banco de dados precisa pesquisar cada linha. Com o par­ti­ci­o­na­mento, as tabelas podem ser divididas em partes menores e lo­gi­ca­mente separadas, como por data, intervalo de ID ou outros critérios. Cada partição é tratada in­ter­na­mente como uma tabela separada, per­mi­tindo que consultas que envolvem apenas certas partições sejam exe­cu­ta­das mais ra­pi­da­mente.

Um exemplo de par­ti­ci­o­na­mento por ano em uma tabela de pedidos poderia ser assim:

CREATE TABLE orders (
id INT, 
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);

Nesse caso, todos os pedidos de 2023 serão ar­ma­ze­na­dos na partição p2023 e todos os pedidos de 2024 em p2024.

Opção 7: utilizar pooling de conexões

Cada nova conexão com MySQL/MariaDB requer tempo e recursos. Se a sua aplicação es­ta­be­lece e encerra uma conexão a cada so­li­ci­ta­ção, isso gera uma carga des­ne­ces­sá­ria no servidor. O chamado pooling de conexões pode ser utilizado para otimizar MySQL e MariaDB, re­sol­vendo esse problema ao manter um número es­pe­cí­fico de conexões de banco de dados per­ma­nen­te­mente abertas. As apli­ca­ções então reu­ti­li­zam essas conexões exis­ten­tes, em vez de criar novas.

Um exemplo em PHP com mysqli é assim:

$mysqli = new mysqli('localhost', 'user', 'password', 'db');
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);

Em vez de abrir uma nova conexão para cada re­qui­si­ção, o pool utiliza as conexões já exis­ten­tes. Isso leva a tempos de resposta mais rápidos e, ao mesmo tempo, alivia o servidor de banco de dados.