The NeverEnding Story: Full backups en sistemas con alta actividad

The NeverEnding Story: Full backups en sistemas con alta actividad

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.

Buscando las cosquillas al heartbeat de Windows Clustering

Buscando las cosquillas al heartbeat de Windows Clustering

A día de hoy el sistema de alta disponibilidad más utilizado para SQL Server sigue siendo aún Failover Clustering. Uno de los componentes críticos para el buen funcionamiento del clúster es el mantener unas comunicaciones entre los nodos de calidad. Concretamente con calidad nos referimos a que no tengamos cortes, descartes de paquetes o latencias excesivas para el tráfico de heartbeat.

(más…)

Uso indices columnares en SQL2014, ¿me interesa migrar a SQL2016?

Uso indices columnares en SQL2014, ¿me interesa migrar a SQL2016?

SQL Server 2016 realmente es un cambio drástico para mejor en términos de rendimiento general, prueba de ello es que es la primera versión de SQL Server en la que Microsoft activamente evidencia que únicamente por migrar y gracias a los cambios del engine, hay un aumento de rendimiento generalizado. ¿Pero cuánto me puede afectar en mi carga diaria si utilizo un datawarehouse con SQL Server 2014 y hago uso de índices columnares? Realmente con SQL Server 2014 y los índices columnares ya ví una mejora brutal de rendimiento, ¿me merece la pena ir a SQL2016?

(más…)

Prueba gratis la herramienta que analiza el rendimiento de tus consultas

Prueba gratis la herramienta que analiza el rendimiento de tus consultas

En SolidQ estamos a punto de lanzar una nueva herramienta y queremos darte la oportunidad de que la pruebes antes que nadie. Su nombre en clave es TSQL-CSI-DW y es ni mas ni menos que el datawarehouse de análisis de rendimiento de tus consultas. La idea es sencilla: activas una traza de eventos extendidos con el template que te diremos, y nosotros lo procesamos dentro de un Datawarehouse para que lo explotes tú desde un PowerBI que te expondremos para ello.

(más…)

Python en SQL Server 2017

Python en SQL Server 2017

Con la llegada de SQL Server 2017 (aka vNext) junto con el hype del data science Microsoft está “abriendo la puerta” a otros los lenguajes dentro de SQL Server. Si nos remontamos a la época anterior a SQL Server 2005 las opciones disponibles resultaban complejas (código C nativo) y potencialmente peligrosas al basarse en la carga de DLLs sin limitaciones, sin sandboxing, etc. Con la llegada de SQL Server 2005 se abrió la puerta al uso de .NET con la integración CLR que aportaba una mayor sencillez a la hora de programar y mayor seguridad. A día de hoy el uso de la integración con CLR sigue siendo una herramienta muy potente e infravalorada en muchos escenarios.
(más…)

Cache Redis vs SQL Server In-Memory OLTP

Cache Redis vs SQL Server In-Memory OLTP

La llegada de SQL Server 2016 SP1 ha “democratizado” el acceso a la tecnología In-Memory OLTP pasando a estar disponible en la versión Standard y Express. Al facilitarse el acceso normalmente aumenta la cantidad de preguntas respecto a las posibilidades que ofrece una tecnología. Un posible caso de uso para In-Memory OLTP es utilizarla como caché de datos de forma que datos que son pesados de obtener/calcular puedan estar disponibles de forma más rápida. Además existen múltiples casos de éxito asociados a esta tecnología en empresas de renombre como bwin.

(más…)

Crear objetos temporales o no, esa es la cuestión

Crear objetos temporales o no, esa es la cuestión

Es relativamente habitual encontrarnos con cargas SQL que realizan un uso intensivo de objetos temporales. Esto normalmente no supone necesariamente un problema de rendimiento per se, pero debemos tener en cuenta que esta creación y destrucción de objetos no es gratuita. En este post vamos a analizar el rendimiento de distintas configuraciones orientadas a dicho escenario de uso de datos temporales. (más…)

Buenas prácticas de configuración de SQL Server

Cuando hacemos Health Checks a nuestros clientes, nos encontramos situaciones de servidores mal configurados desde su setup. En esta sesión les transmitiremos los problemas más habituales encontrados, por qué son problemas y cómo remediarlos. El objetivo es que esta sesión sirva de guia/check-list para que el asistente pueda asegurarse que su instalación está correctamente configurada.

(más…)