# Ver o que está rodando agora
mysql -e "SHOW FULL PROCESSLIST\G" | grep -v Sleep | head -60

# Quantas conexões por estado
mysql -e "SELECT command, count(*) FROM information_schema.processlist GROUP BY command;"

# Top queries por tempo acumulado (sem Percona, usa events_statements)
mysql -e "
SELECT LEFT(digest_text,80) AS query, count_star, avg_timer_wait/1e9 AS avg_sec, sum_timer_wait/1e9 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10\G"

# Verificar se está ativo
mysql -e "SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time';"

# Ativar em runtime (sem restart) — threshold 1s
mysql -e "SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';"

# Analisar o log
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Ver últimas entradas raw
tail -50 /var/log/mysql/slow.log

# Formato tabela (básico)
mysql myapp_db -e "EXPLAIN SELECT * FROM users WHERE status = 'ativo' AND entity_id = 1\G"

# Formato JSON (mais detalhado — mostra cost estimates)
mysql myapp_db -e "EXPLAIN FORMAT=JSON SELECT ...\G"

# Com análise real de execução (MySQL 8+)
mysql myapp_db -e "EXPLAIN ANALYZE SELECT ...\G"

# Ver índices existentes
mysql myapp_db -e "SHOW INDEX FROM users\G"

# Índice simples
mysql myapp_db -e "ALTER TABLE users ADD INDEX idx_status_entity (status, entity_id);"

# Índice sem travar tabela (MySQL 8 default, mas explícito)
mysql myapp_db -e "ALTER TABLE users ADD INDEX idx_email (email) ALGORITHM=INPLACE, LOCK=NONE;"

# Ver tamanho das tabelas (priorizar indexação nas grandes)
mysql -e "
SELECT table_schema, table_name,
  ROUND(data_length/1024/1024,2) AS data_mb,
  ROUND(index_length/1024/1024,2) AS index_mb,
  table_rows
FROM information_schema.tables
WHERE table_schema IN ('myapp_db','news_db','work_db')
ORDER BY data_length DESC LIMIT 20;"

# Ativar query log temporário no projeto
cd /home/deploy/myapp
sudo -u deploy php artisan tinker --execute="
DB::listen(fn(\$q) => logger(\$q->sql));
// agora faça a request que está lenta
"

# Ou via Telescope (se instalado)
mysql myapp_db -e "SELECT COUNT(*) FROM telescope_entries WHERE type='query' AND created_at > NOW() - INTERVAL 1 HOUR;"

# N+1 clássico: >50 queries idênticas variando só o ID
tail -200 storage/logs/laravel.log | grep "select \* from" | sort | uniq -c | sort -rn | head -20

# Ver último deadlock
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 40 "LATEST DETECTED DEADLOCK"

# Monitorar deadlocks em tempo real
watch -n 5 "mysql -e \"SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';\""

# Ver configuração atual
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# Ver hit ratio (deve ser >99%)
mysql -e "
SELECT 
  ROUND((1 - (s2.variable_value / s1.variable_value)) * 100, 2) AS hit_ratio_pct
FROM performance_schema.global_status s1
JOIN performance_schema.global_status s2 
  ON s1.variable_name='Innodb_buffer_pool_read_requests'
  AND s2.variable_name='Innodb_buffer_pool_reads';"

# Buffer pool atual: verificar em /etc/mysql/mysql.conf.d/mysqld.cnf
grep -i buffer_pool /etc/mysql/mysql.conf.d/mysqld.cnf

# Ver PID das queries lentas
mysql -e "SELECT id, user, host, db, time, state, LEFT(info,100) FROM information_schema.processlist WHERE time > 5 AND command != 'Sleep' ORDER BY time DESC;"

# Kill (substitui <PID>)
mysql -e "KILL QUERY <PID>;"   # Cancela só a query, mantém conexão
mysql -e "KILL <PID>;"          # Encerra conexão inteira

// Em vez de query direta, usar cache
$stats = Cache::remember("entity_{$id}_stats", 300, function() use ($id) {
    return Entity::withCount(['users', 'eventos'])->find($id);
});

// Invalidar ao salvar
Cache::forget("entity_{$id}_stats");

# Queries lentas no Loki (se Promtail lê /var/log/mysql/slow.log)
{job="mysql-slow"} | regexp `Query_time: (?P<time>[0-9.]+)` | time > 2

# Contar deadlocks por hora
count_over_time({job="mysql"} |= "deadlock" [1h])