Problemas de rendimiento en la restauración de respaldos de bases con FILESTREAM

Por Daniel Torres Garrido

Desde que se introdujo la característica de FILESTREAM en SQL Server esta ha sido bien recibida por nuestros clientes. Muchos de ellos ya utilizan FILESTREAM para guardar documentos, imágenes, archivos, etc. dentro del sistema de archivos de SQL Server. Dado que el FILESTREAM almacena estos archivos directamente en la base no es necesario realizar respaldos alternos de los documentos, con tan solo tomar un respaldo de la base de datos estamos encapsulando toda la información.

Generalmente los archivos almacenados con FILESTREAM suelen ser grandes o bien, se almacenan muchos de ellos de menor tamaño. Como resultado el tamaño de las bases de datos con FILESTREAM suelen ser grandes de varios gigas o incluso varios teras por la cantidad y tamaño de archivos que eventualmente se van guardando con el paso del tiempo.

Recientemente trabajamos en un caso con una base de datos de gran tamaño, aproximadamente 5 TB. Nuestro cliente tomó un respaldo de la base, este proceso tomaba algo así como unas 39 horas. El respaldo se depositaba directamente con un comando T-SQL a una ubicación compartida en otro storage ya que el servidor origen no tenía espacio suficiente para albergar la base y el respaldo al mismo tiempo. Después de obtener el respaldo se realizaba el restore en una instancia diferente a la inicial. Sorprendentemente después de 9 días de dejar el proceso de restauración apenas alcanzábamos un 85% de avance. Lo curioso es que una vez que lanzábamos el RESTORE en tan solo 4 horas y media se tenía un avance del 21%, después de 17 horas alcanzaba el 61% y a partir de este momento el porcentaje de avance se incrementaba cada vez más y más lento. Nuestro cliente dejó 15 días el proceso de restauración y apenas alcanzamos el 90% de avance. Definitivamente algo no estaba bien.

Tomamos contadores de performance en la instancia donde se realizaba el restore. Dado que el respaldo viajaba por la red una de las cosas que pensé es que teníamos retardos en la comunicación y esto afectaba la restauración. En concreto revisé los siguientes contadores:

Bytes Received/sec para todos los adaptadores de red

SQLServer:Databases->Backup/Restore Throughput/sec

Logical Disks->% Disk Read Time and % Disk Write Time.

Una de las cosas que vimos fue que el adaptador que recibía los datos lo hacía por lotes, por un segundo o dos la transferencia de bytes estaba activa y los 9 o 10 segundos siguientes bajaba a cero. Mientras se recibían paquetes la tasa del contador de SQL Restore Throughput también aumentaba. Este comportamiento se repetía siempre, por 1-2 segundos recibíamos datos y después no veíamos actividad por 10 segundos. Esto me hizo sospechar de la presunción inicial que tenía sobre los problemas de red. El cliente argumentó que existía un canal físico de comunicación directo entre el storage donde estaba el respaldo y el servidor de SQL Server. Mi primera petición fue pedirle agregar storage en el servidor destino para poder guardar el respaldo. El cliente muy amablemente accedió y asignó algo así como 11 TB para poder alojar el respaldo y los archivos al mismo tiempo.

Finalmente ya con el espacio asignado en el servidor comenzamos con la restauración. Para mi sorpresa y desencanto la restauración tuvo exactamente el mismo comportamiento, el tema de red no hizo ninguna diferencia. Después de días el porcentaje de progreso incrementaba lentamente e incluso daba la impresión de que conforme más pasaba el tiempo menor era el avance. Podríamos decir que el progreso tenía un comportamiento exponencial, debíamos esperar un X tiempo para aumentar un 1%, y el siguiente 1% tomaba más tiempo que el anterior y así sucesivamente.

Ciertamente nos tomó mucho tiempo continuar con el análisis del caso, habilitamos los trace flags 3004 y 3605 para enviar la salida del restore al error log de SQL. La información obtenida no era concluyente y no teníamos más información.

Para mi fortuna y durante la investigación encontré un artículo de Paul Randall:

FILESTREAM Storage in SQL Server 2008 https://msdn.microsoft.com/library/hh461480

El artículo por sí solo da la impresión que solo incluye algunas recomendaciones para mejorar el rendimiento e incluye in ejemplo de cómo habilitar y configurar el FILESTREAM sin embargo una sección me llamó la atención: NTFS Configuration.

En esta sección se indica que el rendimiento del subsistema de IO puede no rendir correctamente si no lo configuramos de la forma correcta. Para optimizar el rendimiento el artículo indica que el sistema NTFS no está configurado para manejar una carga de trabajo pesada de decenas o miles de archivos en un sistema de directorios individual, justo como el que maneja el FILESTREAM. Una configuración que impacta directamente el rendimiento del NTFS es una característica de compatibilidad con plataformas de 16 bits que generan nombres en formato 8.3.

Investigando más sobre el tema encontré el siguiente KB que lo explica:

How Windows Generates 8.3 File Names from Long File Names https://support.microsoft.com/kb/142982

Windows soporta longitudes de archivos de hasta 255 caracteres sin embargo a su vez genera un nombre compatible con MS-DOS o aplicaciones de 16 bits para reducir la longitud del nombre del archivo y estos puedan accederlos. Si desea mayor información puede revisar el KB con detalle.

El algoritmo que genera los nombres en formato 8.3 se vuelve extremadamente lento y consume muchos recursos ya que debe escanear los nombres de los archivos que generó anteriormente para asegurarse de que el nuevo nombre es único y no está repetido. Si el número de archivos en el directorio es mayor a 300,000 el proceso de generación de nombres en formato 8.3 tomará cada vez más y más tiempo en crear 1 archivo hasta que el rendimiento del equipo se degrade por completo. En este caso para revisar cuántos archivos existían en los directorios del FILESTREAM en la base de datos revisé directamente en la instancia origen y encontré que existían 2 directorios con un total de más de 3 millones de archivos. Dado que el restore va leyendo el respaldo y tiene que crear archivo por archivo evidentemente el proceso de generación de nombres en formato 8.3 comenzaba a tomar más y más tiempo en crearlos. Lo que el artículo recomienda es deshabilitar esta característica a través del siguiente comando aunque a su vez nos alerta que si existen aplicaciones de 16 bits que accedan a los volúmenes NFTS evidentemente se presentarán problemas:

fsutil behavior set disable8dot3 1

El artículo nos alerta también de otra característica que debemos deshabilitar en el sistema NTFS que se encarga de actualizar la hora en que se accedió por última vez a un archivo. Si tenemos una carga de trabajo intensa que está accediendo a los archivos entonces se utilizará un tiempo significativo actualizando la hora de último acceso. Para deshabilitarlo se sugiere el uso del siguiente comando:

          fsutil behavior set disablelastaccess 1

Vale la pena señalar que para que estos cambios tomen efecto se necesita reiniciar le servidor. En nuestro caso implementamos este plan de acción. Lanzamos de nuevo el restore y ciertamente el porcentaje de avance en un inicio no mejoró drásticamente pero sí observamos que siempre se mantuvo constante. Después de cerca de 26 horas el restore finalizó con éxito y el problema se resolvió.

Definitivamente aunque la solución al problema por si sola es sencilla llegar a ella no lo fue. Esto nos hace confirmar que SQL Server confía plenamente en el rendimiento del subsistema de IO con el cual interactúa. Este componente es vital para entender que no todos los casos de performance tienen como causa raíz un problema relacionado con los métodos de acceso de SQL Server, el sistema de discos por sí solo tiene un impacto significativo en el rendimiento. Siempre que vayan a implementar una característica de SQL Server que dependa del sistema de IO revise a detalle las mejores prácticas, recomendaciones y configuraciones del producto.