Recientemente nos hemos encontrado varios casos de backups full, sobre bases de datos en modo de recuperación simple, cuya duración y crecimiento del log excede por mucho lo esperable.

Para plantear el problema necesitamos entender qué incluye un backup completo y cuál es el comportamiento del log en una base de datos en modo de recuperación simple. Un backup completo, incluye todas las páginas en uso de nuestra base de datos, pero además añade la parte del log de transacciones necesaria para asegurar que el backup es consistente.

Normalmente mientras el backup completo se ejecuta existe actividad que pueden modificar páginas ya leídas por el proceso de backup. Si no tuviéramos esta parte del log, no podríamos recuperar la base de datos a un punto consistente ya que unas páginas las recuperaríamos a un punto en el tiempo y otras a otro lo cual rompería la consistencia de base de datos.

Por otro lado, cuando estamos en modo simple, el “truncado del log” (aunque recordemos que únicamente se marcan como inactivos los VLFs) ocurrirá de forma automática tras un checkpoint sin que necesitemos realizar backups del log de forma explícita. Indicar también que si el log se llena a más de un 70% se lanzará un checkpoint de forma automática (aunque no fuese requerido en base al periodo de recuperación). No entraremos a considerar el efecto de una configuración basada en checkpoints indirectos aunque también podría sufrir de la misma problemática que vamos a exponer a continuación. También se lanzará un checkpoint en el momento que iniciemos un backup completo.

Por aclarar lo que ocurre durante un checkpoint básicamente lo que vamos a hacer es volcar todas las páginas sucias (modificadas) en memoria a disco. También se forzaría el flush a disco de aquellos registros del log que no estuvieran ya materializados para asegurarnos que un eventual proceso de recovery funcionará correctamente.

Una vez tenemos claro todo esto, imaginemos que tenemos una base de datos que está en modo simple y tiene una actividad elevada. Esta actividad debido a los “truncados del log” automáticos del modo simple no produce crecimientos del log de transacciones habitualmente lo cual nos puede llevar a engaño respecto a la carga real que sufre dicho log de transacciones. Por decirlo de alguna forma tenemos un log de transacciones de tamaño pequeño pero “caliente”, rebosando actividad de escritura. En este tipo de situaciones es bastante habitual optar por el modelo simple versus el modelo de recuperación completa.

Ahora combinemos este escenario con un destino para backups relativamente lento comparado con el sistema de almacenamiento principal. En este tipo de circunstancias nos podemos encontrar con una situación en la cual el proceso de backup no es capaz de “alcanzar” a respaldar el log a un ritmo igual o superior a la actividad generada por la base de datos. Esto producirá que los tiempos de backup completo se alarguen de forma inesperada ya que normalmente tendemos a estimar o interpolar el tiempo de backup teniendo en cuenta únicamente el tamaño de los datos.

Vamos a ver una demo de esta problemática con una máquina virtual donde uno de los discos lo limitaremos mediante QoS de Hyper-V a únicamente 200 IOPS:

Utilizaremos la base de datos AdventureWorks como base de datos de ejemplo. Realizaremos un primer backup completo sin actividad en la base de datos:

Con la limitación de 200 IOPS el backup en este entorno se ralentiza hasta un ritmo de aproximadamente 1 MB/s ocupando unos 180 MB en total. Como referencia indicar que, sin limitar vía QoS, el backup se realizaba en menos de 5 segundos en el mismo disco:

A continuación, crearemos una tabla temporal y un bucle infinito de inserciones sobre dicha tabla de 1000 en 1000 filas:

Durante este proceso se generan unos 20 MB/s de escritura sobre el log de transacciones y unos 11 MB/s sobre el fichero de datos aproximadamente:

Ahora lanzaremos de nuevo el backup mientras este proceso se encuentra en marcha y observaremos lo que ocurre. Lo primero que detectamos es que el tamaño del log se dispara rápidamente, de los 512 KB que teníamos inicialmente pasamos a 1, 2, 3 GB en muy poco tiempo (no se produce una reutilización del espacio del log). Esto en sí mismo ya podría traernos algún quebradero de cabeza relacionado con alcanzar el límite de espacio en disco disponible o el tamaño máximo del log configurado:

 

Lo siguiente que observaremos es que el backup indicará que el 100% se ha procesado, pero no ha acabado en realidad debido precisamente a la cantidad de datos en el log de transacciones que debe respaldar aún:

Dejamos transcurrir los minutos, pero el proceso de backup nunca termina y el tamaño tanto del log como del backup destino sigue incrementandose sustancialmente:

Decidimos parar la carga tras 6 minutos de inserciones, para que no se escriba más en el log y dejar todos los recursos de la máquina disponibles para el backup:

Sin ya actividad sobre la base de datos el backup necesitó de 2602 segundos (~43 minutos) para poder finalizar. Podemos ver también como el tamaño del backup es mucho mayor que el tamaño “previsible” que es habitualmente “poco más” que el tamaño total de los datos:

Tened en cuenta que hablamos de una base de datos muy pequeña, imaginad el impacto que podríamos tener en una base de datos de mayor tamaño cuyo backup sin carga se alargue varias horas. También es posible que no tengamos tanta paciencia como para esperar la finalización del backup pero en ese caso debemos considerar que el proceso de rollback del backup puede ser también bastante lento.

En casos extremos donde el log de transacciones haya crecido durante mucho tiempo y el disco donde se ubique tenga poco rendimiento accediendo a partes “lejanas” del log (falta de acierto en caché, discos con rendimiento aleatorio muy pobre, etc.) podríamos llegar a necesitar forzar el recovery de la base de datos, bien reiniciando la instancia o bien forzando el offline/online de la base de datos afectada.

Entendemos que “escenario demo” puede parecer forzado, pero como administradores de base de datos es algo con lo que nos encontraremos con mayor probabilidad cuanto más rápido sea el sistema de entrada/salida de la base de datos respecto al usado para el backup.

Por ejemplo, un caso típico es que cambiamos a una cabina o array SSDs pero seguimos manteniendo un disco de backup lento, en una NAS o en cinta. También puede convertirse en habitual cuando se utilicen almacenamiento cloud de bajo rendimiento para el backup (archive disks, blob storage, etc.) y tengamos discos Premium/SSD para nuestros ficheros de transacciones. De hecho, la razón principal por la que decidimos escribir este post ha sido precisamente la de encontrarnos estos últimos meses con este problema en entornos reales (virtuales y cloud) con bastante más frecuencia que en todos los años anteriores.

Si nos encontramos con este tipo de problemas podemos tomar varias medidas paliativas:

  • Mejorar el rendimiento del sistema de backup para equilibrarlo respecto al resto del sistema.
  • Mover la planificación del backup completo a una ventana de tiempo con menor actividad en el sistema.
  • Realizar el backup completo a un disco rápido y posteriormente moverlo al medio más lento/económico donde deba residir finalmente.
  • Realizar los backups completos mediante técnicas de snapshot (VSS, snapshots a nivel de disco, etc.)

En definitiva, no debemos de olvidarnos de nuestros sistemas de backup cuando se realice algún upgrade que implique un aumento de rendimiento en la base de datos. Es habitual que cuando se plantea un futuro aumento de carga se dimensionen nuevos servidores con mejores discos, más memoria, más CPUs, etc. Sin embargo, es muy poco habitual que para el sistema de backup se considere que vamos a necesitar “rendimiento extra”, limitándose la discusión a únicamente el espacio extra que pueda llegarse a necesitar en base a las previsiones de volumetría futuras.

Rubén Garrigós

Rubén Garrigós is an expert in high-availability enterprise solutions based on SQL Server design, tuning, and troubleshooting. Over the past ten years, he has worked with Microsoft data access technologies in leading companies around the world. He currently is a Microsoft SQL Server and .NET applications architect with SolidQ. Ruben is certified by Microsoft as a Solution Expert on the Microsoft Data Platform (MSCE: Data Platform) and as a Solution Expert on the Microsoft Private Cloud (MSCE: Private Cloud). As a Microsoft Certified Trainer (MCT), Ruben has taught multiple official Microsoft courses as well as other courses specializing in SQL Server. He has also presented sessions at official events for various Microsoft technologies user groups.