En estas próximas sesiones del módulo SGBD nos centraremos en los aspectos de Monitorización y rendimiento en las consultas (¿donde esta el cuello de botella de la aplicación ? ) y Logging de bases de datos

ESCENARIO: https://data.librebits.info - Nextcloud server -> va muy lento… ¿como diagnosticar posibles cuellos de botella?¿monitorizar el rendimiento en la base de datos?

Diagnóstico de Rendimiento de Base de Datos en Nextcloud

Tabla de Contenidos

1. Habilitar Registro de Consultas
2. Verificar Actividad Actual de la Base de Datos
3. Herramientas Integradas de Nextcloud
4. Identificar Tablas Problemáticas
5. Problemas Comunes y Soluciones Rápidas
6. Monitorización en Tiempo Real
7. Verificar Índices Faltantes
8. Señales de Alerta
9. Escenario Práctico

Antes de empezar a monitorizar la base de datos SQL, siempre es indicativo el realizar un chequeo de la relación de recursos hardware en uso/disponibles. Podemos usar, entre otras muchas herramientas,

# viene por defecto en sistemas debian gnu linux y derivados como ubuntu
$ top 
# htop no viene por defecto en sistemas debian gnu linux y derivados como ubuntu, pero lo instalamos sencillamente (sudo apt install htop), permite ordenar y filtrar 
# Es más visual que top
$ htop 
# btop no viene por defecto en sistemas debian gnu linux y derivados como ubuntu, pero lo instalamos sencillamente (sudo apt install btop), permite ordenar y filtrar. 
# Es más visual que htop
$ btop 

1. Habilitar Registro de Consultas (temporalmente)

MySQL/MariaDB:

# Habilitar log de consultas lentas
mysql -u root -p
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  # consultas > 1 segundo
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-queries.log';

PROBLEMA encontrado :-(

fenix@aldebaran:~$ ls -la /var/log/mysql/
total 8
drwxr-xr-x  2 root root 4096 Jan 15 14:38 .
drwxr-xr-x 17 root root 4096 Jan 15 14:38 ..
-rw-r--r--  1 root root    0 Jan 15 14:38 slow-queries.log
fenix@aldebaran:~$ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
fenix@aldebaran:~$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3356
Server version: 11.8.3-MariaDB-0+deb13u1 from Debian -- Please help get to 10k stars at https://github.com/MariaDB/Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-queries.log';
ERROR 29 (HY000): File '/var/log/mysql/slow-queries.log' not found (Errcode: 13 "Permission denied")
MariaDB [(none)]> 

SOLUCIÓN : asegurarnos de que el usuario ‘mysql’ tiene los permisos necesarios para acceder al fichero donde se vuelca el logging de ‘consultas lentas’.

root@aldebaran:~# chown -R mysql:mysql /var/log/mysql/

2. Verificar Actividad Actual de la Base de Datos

MySQL/MariaDB:

-- Consultas activas
SHOW FULL PROCESSLIST;

-- Bloqueos de tablas
SHOW OPEN TABLES WHERE In_use > 0;

-- Estado de InnoDB
SHOW ENGINE INNODB STATUS\G

3. Herramientas Integradas de Nextcloud

# Estado de la base de datos
sudo -u www-data php occ db:check

# Índices faltantes (¡crítico!)
sudo -u www-data php occ db:add-missing-indices

# Convertir a bigint si es necesario
sudo -u www-data php occ db:convert-filecache-bigint

4. Identificar Tablas Problemáticas

MySQL/MariaDB:

-- Tablas por tamaño de datos
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.TABLES 
WHERE table_schema = 'nextcloud'
ORDER BY (data_length + index_length) DESC LIMIT 10;

-- Tablas con más lecturas/escrituras
SELECT 
    table_name,
    rows_read,
    rows_inserted,
    rows_updated
FROM information_schema.TABLE_STATISTICS
WHERE table_schema = 'nextcloud'
ORDER BY (rows_read + rows_inserted + rows_updated) DESC LIMIT 10;

5. Problemas Comunes y Soluciones Rápidas

oc_filecache (normalmente la más grande):

-- Verificar fragmentación
ANALYZE TABLE oc_filecache;
OPTIMIZE TABLE oc_filecache;

oc_activity (crece indefinidamente):

# Limpiar actividades antiguas (90 días)
sudo -u www-data php occ activity:cleanup 90

Agotamiento del pool de conexiones:

# /etc/mysql/mariadb.conf.d/50-server.cnf
max_connections = 200
wait_timeout = 600
interactive_timeout = 600

6. Monitorización en Tiempo Real

# Instantánea rápida (repetir cada 5s)
watch -n 5 'mysqladmin -u root -p processlist | grep -v Sleep | wc -l'

# O con mytop (instalar: apt install mytop)
mytop -d nextcloud

7. Verificar Índices Faltantes

# La herramienta de Nextcloud detecta la mayoría
sudo -u www-data php occ db:add-missing-indices

# Verificación manual (MySQL)
mysql -u root -p nextcloud -e "
SELECT DISTINCT 
    TABLE_NAME,
    COLUMN_NAME
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'nextcloud' 
  AND COLUMN_KEY = ''
  AND COLUMN_NAME IN ('user_id', 'fileid', 'storage', 'path_hash');"

8. Señales de Alerta

# Alto uso de CPU por MySQL
top -u mysql

# Verificar si los archivos de BD exceden la RAM
du -sh /var/lib/mysql/nextcloud/
free -h

# Uso de swap (mala señal)
vmstat 1 5

Escenario Práctico

Problema: Usuario reporta “La aplicación de Archivos tarda en cargar”

# 1. Verificar consultas activas
mysql -u root -p -e "SHOW FULL PROCESSLIST;"

# 2. Buscar SELECTs de larga duración en oc_filecache
# 3. Verificar si existen índices
sudo -u www-data php occ db:add-missing-indices

# 4. Limpiar
sudo -u www-data php occ files:scan --all  # reconstruir filecache
sudo -u www-data php occ files:cleanup     # eliminar huérfanos

# 5. Optimizar
mysql -u root -p nextcloud -e "OPTIMIZE TABLE oc_filecache, oc_storages;"

Hipótesis/¿Qué podríamos identificar? Índices faltantes, tabla oc_activity sobredimensionada, pool de conexiones insuficiente.

Resumen de Comandos Esenciales

Happy Hacking ! (_)