Mantenimiento de bases de datos en varios sistemas gestores

Hace unos meses estuvimos desarrollando en SaasRadar un artículo que resumía cómo conocer el tamaño de nuestras bases de datos en diferentes sistemas gestores como PostgreSQL, MySQL, SqlServer, entre otros.

Esta información puede resultarte vital cuando tus sistemas presentan problemas constantes de rendimiento.

En este caso la primera acción debería ser discernir si se trata de un crecimiento exagerado de la base de datos o algún otro escenario como ataques de denegación de servicios, cuellos de botella, problemas de carga del servidor, y así una larga lista.

Si el problema está relacionado con crecimiento y optimización, el próximo pasó es diseñar un esquema de mantenimiento que por lo general incluye limpieza de registros marcados para eliminar y la corrección de los índices.

Hoy nos queremos enfocar en este proceso presentando algunas variantes para garantizar el mantenimiento de bases de datos de forma periódica, evitando la acumulación de problemas.

Mantenimiento de bases de datos PostgreSQL

postgresql logo.width 900 Mantenimiento de bases de datos en varios sistemas gestores

De forma similar a los discos duros tradicionales (HDD), cuando en una base de datos en PostgresSQL se realizan operaciones de eliminación, la información permanece en el sistema, solo cambia de estado la fila y se declara reemplazable.

De este modo deja de tenerse en cuenta en los resultados de las consultas, pero sigue ocupando espacio en disco hasta que el sistema la sobreescriba con nueva información.

Estos registros eliminados son las denominadas dead rows (filas muertas).

Puedes consultar las filas muertas de las tablas de tu base de datos con la siguiente consulta:

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup / (n_live_tup
       * current_setting('autovacuum_vacuum_scale_factor')::float8
          + current_setting('autovacuum_vacuum_threshold')::float8)
     DESC
LIMIT 20;

Donde podrás esperar un resultado parecido a este:

Filas muertas en base de datos PostgreSQL sin Vacuum

En este ejemplo se muestra una base de datos con serios problemas de optimización.

La tabla oc_activity tiene 81286 filas vivas y casi 5 veces esa cifra corresponde a filas muertas, con 392282. Algo similar ocurre con oc_filecache.

Como resultado de esta falta de mantenimiento el tamaño de esta base de datos ha aumentado hasta 24 GB, con backups comprimidos de 6.8 GB.

Para resolver este problema con PostgreSQL contamos con tres comandos:

ANALIZE

Se utiliza para analizar el estado de cada una de las tablas de la base de datos y poner esta información en manos del planificador de consulta para que pueda optimizar el rendimiento.

VACUUM

Realiza la limpieza de las tablas. Por defecto elimina las filas muertas que cumplan ciertas condiciones como el umbral xmin. Para recuperar el espacio en disco en estos casos debe usarse VACUUM FULL, pero las operaciones de escritura estarán bloqueadas mientras se ejecuta.

REINDEX

Su función es reconstruir los índices eliminando datos innecesarios y optimizando el espacio en disco.

Es una buena práctica mezclar los VACUUM con ANALYZE para limpiar la base de datos e informar su nuevo estado al planificador de consultas para optimizar las búsquedas.

En el caso que hemos utilizado de ejemplo, la consulta sería:

VACUUM ANALYZE oc_activity;

Una vez concluido este proceso, si volvemos a consultar las filas muertas de esta base de datos obtenemos como resultado una reducción importante en la tabla.

Filas muertas en base de datos PostgreSQL después del Vacuum

Posteriormente debemos utilizar el comando REINDEX para que queden listos los índices de la base de datos y con estos, la base de datos totalmente optimizada.

Mantenimiento de bases de datos MySQL

MySQL Logo Mantenimiento de bases de datos en varios sistemas gestores

Si utilizas MySQL la situación es muy similar.

A medida que vas ejecutando operaciones de inserción y eliminación la base de datos se va fragmentando, hasta llegar a niveles alarmantes con notables efectos sobre el rendimiento de las consultas.

El primer paso que te recomiendo es que compruebes ahora mismo el estado de tus bases de datos en materia de fragmentación con la siguiente consulta:

SELECT ENGINE,
  concat(TABLE_SCHEMA, '.', TABLE_NAME) as Table_Name,
  round(DATA_LENGTH/1024/1024, 2) as Data_Length,
  round(INDEX_LENGTH/1024/1024, 2) as Index_Length,
  round(DATA_FREE/1024/1024, 2) as Data_Free,
  (data_free/(index_length+data_length)) as Frag_Ratio
FROM information_schema.tables
WHERE DATA_FREE > 0
ORDER BY frag_ratio DESC;

Como resultado de la ejecución de esta consulta tendrás la siguiente información:

Resultado de consulta de fragmentación de base de datos MySQL.

La columna Frag_Ratio muestra el porciento de fragmentación de la tabla que especifica dicha fila, en este caso 32%.

Si el porciento de fragmentación es demasiado elevado y notas afectación en el rendimiento debes utilizar el comando OPTIMIZE.

Este comando básicamente reescribe la tabla original para eliminar la fragmentación y luego la sustituye.

Cuando lo utilizas la tabla queda inaccesible por lo que puede afectar a los sistemas que utilicen la información que contiene.

Debes ser especialmente cuidadoso con este aspecto y ejecutar OPTIMIZE en mantenimientos planificados.

La sintaxis de este comando para ejecutar el mantenimiento de bases de datos es la siguiente:

OPTIMIZE TABLE nombretabla;

También puedes optimizar una base de datos completa o todas las bases de datos de un servidor.

Este comando funciona de modo diferente en cada uno de los motores de MySQL pero en todos cumple con su propósito.

Al igual que en PostgreSQL, el mantenimiento de bases de datos con MySQL cuenta con ANALIZE, que le brinda información sobre el estado de la base de datos para optimizar las consultas.

El uso de este comando también afectará la lectura de los datos por lo que utilizar en horarios de baja carga.

En casos críticos, cuando una base de datos se corrompe, tenemos a nuestra disposición un último aliento, CHECK.

Este comando cuenta con las herramientas necesarias para solucionar la mayoría de los problemas de este tipo y devolverte el control de tu base de datos.

Mantenimiento de bases de datos SQL Server

sqlserver Mantenimiento de bases de datos en varios sistemas gestores

El mantenimiento de bases de datos con SQLServer es más sencillo para aquellas personas que odian los comandos.

Al contar con SQLServer Management Studio todos los planes de mantenimiento pueden programarse a base de clic. Veamos el proceso paso a paso.

Una vez dentro de SQLServer Management Studio y autenticado en el servidor, obtendremos una interfaz como esta:

Programar Plan de Mantenimiento en SQLServer. Paso 1

Como se puede observar, haciendo clic derecho sobre la carpeta Maintenance Plans obtendremos un menú contextual en el que podremos seleccionar el proceso asistido Maintenance Plan Wizard. Una vez seleccionado se nos muestra la siguiente ventana:

Programar Plan de Mantenimiento en SQLServer. Paso 2.

En este paso se nos pide el nombre y la programación de la ejecución de la tarea. Una vez completados los datos hacemos clic en siguiente.

Programar Plan de Mantenimiento en SQLServer. Paso 3.

Tendremos acceso en esta ventana a todas las tareas relacionadas de algún modo con el mantenimiento de bases de datos en este servidor.

Opciones como el chequeo de integridad de los datos, reducir el espacio ocupado por las tablas (Shrink), reorganizar y reconstruir índices, así como actualizar estadísticas son fundamentales para optimizar bases de datos.

En casi todos los casos anteriores el nombre es lo suficientemente descriptivo para entender su objetivo, por lo que me voy a centrar en aclarar el que puede ser más confuso.

Update Statistics es quien mantiene actualizada la información sobre el estado de la base de datos para tomar las mejores opciones para que se generen consultas óptimas.

Viene siendo muy similar a los ANALYZE de PostgreSQL y MySQL.

De forma predeterminada, el optimizador de consultas ya actualiza las estadísticas según sea necesario para mejorar el plan de consulta, pero en algunos casos puede que necesites realizar esta tarea manualmente.

Una vez seleccionada la opción que necesitemos procedemos a pasar al siguiente paso, donde seleccionaremos las bases de datos a las que le aplicaremos la operación y establecemos parámetros de configuración en dependencia de nuestras necesidades.

Programar Plan de Mantenimiento en SQLServer. Paso 4.

Una vez concluida esta configuración procedemos a establecer la ruta del archivo de registro que generará la operación.

Programar Plan de Mantenimiento en SQLServer. Paso 5.

Por último, revisamos el resumen de todo el proceso y hacemos clic en Finish.

Programar Plan de Mantenimiento en SQLServer. Paso 6.

Si necesitamos reducir el tamaño de las bases de datos con Shrink debemos ser muy cuidadosos en SQLServer, ya que generalmente estarás cambiando optimización de tamaño en disco con un aumento considerable en la fragmentación.

Esto ocurre porque la reducción de archivos de datos recupera espacio al mover páginas de datos desde el final del archivo al espacio desocupado más cerca del frente del archivo.

Cuando se crea suficiente espacio libre al final del archivo, las páginas de datos al final del archivo se pueden desasignar y devolver al sistema de archivos.

Estos constantes movimientos sin un orden minucioso son los que crean la fragmentación que luego nos afecta el rendimiento, y que solo se puede solucionar volviendo a ocupar el espacio en disco que teníamos inicialmente.

Este tema queda ilustrado perfectamente con un ejemplo en este artículo.

Hasta aquí el tema de hoy, donde intentamos resumir y centralizar en un único sitio la información principal de un tema crítico como el mantenimiento de bases de datos en diferentes sistemas gestores.

Espero que puedas comenzar a utilizar a partir de hoy los procedimientos que ponemos a tu disposición y que mejore el rendimiento de tus sistemas informáticos.

Si has llegado hasta este punto, creo que puede ser de tu interés quedarte con nosotros revisando nuestro artículo sobre las mejores herramientas para gestionar bases de datos.

Deja un comentario

Tu dirección de correo electrónico no será publicada.

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.