«O sítio está lento» acaba quase sempre na base de dados. A verificação leva poucos minutos se for feita pela ordem certa: primeiro o que se vê durante o próprio abrandamento, depois o que se foi acumulando durante semanas.

Veja enquanto o problema acontece

A regra principal: a informação mais valiosa está disponível precisamente enquanto tudo está lento. O primeiro comando para o MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

Para o PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

Verá o que está a correr neste momento e há quanto tempo. A resposta costuma ser imediata: uma consulta pesada retém as outras, ou cem consultas iguais esperam pela libertação de um bloqueio.

As ligações

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Se Max_used_connections se aproximar de max_connections, a aplicação recebe de vez em quando a recusa «demasiadas ligações». Subir o limite não é a primeira coisa a fazer: cada ligação ocupa memória, e aumentá-lo num servidor a que a memória já falta apenas acelera o caminho para a swap. Perceba antes porque as ligações não são libertadas: normalmente uma consulta longa ou a ausência de um pool do lado da aplicação.

Vale a pena ver o mesmo pelo outro lado: um aumento brusco de ligações pode vir não de popularidade crescente mas de robôs a martelar uma página pesada: uma pesquisa, um filtro de catálogo, um gerador de relatórios.

As consultas lentas

Sem registo de consultas lentas não se avança. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Um dia depois:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, em postgresql.conf:

log_min_duration_statement = 1000

Depois a extensão pg_stat_statements, que mostra diretamente o tempo acumulado por consulta:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Repare bem: o que importa não é a consulta mais longa mas aquela com maior tempo acumulado. Uma consulta de 50 milissegundos executada mil vezes por minuto faz mais estragos do que um relatório de dez segundos uma vez por dia.

A definição que mais se esquece

No MySQL e no MariaDB é o tamanho do pool de buffers do InnoDB. O valor por omissão são 128 megabytes e não muda há décadas. Num servidor cuja base ocupa vários gigabytes, isso significa leitura constante do disco em vez da memória.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Uma referência razoável para um servidor dedicado a bases de dados é metade da memória; para um servidor onde correm também o servidor web e o PHP, um quarto, com cuidado para não provocar swap. Essa única definição costuma render mais do que uma semana de otimização de consultas.

Duas coisas que devoram o disco em silêncio

Os registos binários do MySQL. Servem para a replicação e a recuperação para um momento no tempo, crescem sem parar e por omissão às vezes nunca são removidos. Para verificar:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

Se não tem replicação e não usa a recuperação para um momento no tempo, defina a retenção em poucos dias razoáveis.

Os ficheiros WAL do PostgreSQL com uma ranhura de replicação esquecida. Caso menos conhecido e mais perigoso: se existir uma ranhura de replicação cujo consumidor já não se liga, o PostgreSQL é obrigado a guardar todos os registos desde a desconexão, e guardá-los-á até encher o disco.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

Uma ranhura inativa de que ninguém precisa tem de ser eliminada. É uma das poucas situações em que uma base de dados leva o disco a zero com segurança.

Tamanho e crescimento

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

Útil não é tanto o número atual como a velocidade a que muda. Uma base que duplicou num mês sem o correspondente aumento de tráfego costuma significar que alguma tabela de registo é escrita e nunca limpa: sessões, histórico de tarefas, registo de eventos de uma extensão.

A segurança em duas linhas

Já que tem a consola da base aberta, valem duas verificações. Primeiro, se a base é alcançável a partir de fora (ss -tulpn | grep 3306): quase nunca deve estar. Segundo, as contas que podem ligar-se a partir de qualquer endereço:

mysql -e "SELECT user, host FROM mysql.user"

As entradas com host = '%' significam «ligações de qualquer parte». Se por cima disso a porta estiver aberta, entre a base e a internet há apenas uma palavra-passe.

As cópias de segurança

Um último ponto. A existência de um ficheiro de cópia não garante nada; só uma reposição testada o garante. Um despejo nunca reposto tem igual probabilidade de sair truncado, feito com um erro de bloqueio, ou de conter a base errada. Isso verifica-se de antemão e numa máquina à parte, não no dia em que a cópia faz falta.

Para a vigilância diária bastam três números: o número de ligações, a quantidade de consultas lentas por dia e o tamanho da base. Os três mudam devagar e de forma previsível, e qualquer movimento brusco de um deles merece um olhar. Como ficam numa página mostra-o a demonstração abaixo.