Surviving the Night

El blog de Pablo Doval sobre .NET, SQL, WinDbg...

Hot: Inserciones Masivas en MongoDB vs SQL Server

Esta semana estoy en San Sebastián, impartiendo una pequeña formación sobre optimización de SQL Server. A priori, la semana se presentaba tranquila y con gran carga turística al tratarse de la primera ocasión que visito esta ciudad, y mi predilección general por estas tierras del norte.

Lamentablemente, todos mis planes se torcieron tan pronto como descubrí que Unai estaba en la misma ciudad que yo. Como no podía ser de otro modo, ente pintxo y pintxo nuestros temas técnicos favoritos empezaron a surgir en nuestras conversaciones. Y, como cualquiera que conozca a nuestro Unai podrá intuir, no pasó demasiado tiempo antes de que MongoDB secuestrara todos nuestros hilos conversacionales y se convirtiera en nuestro particular trending topic, por delante de otras apasionantes cuestiones como la entropía de la demografía en el postmoderno barroco, o el peculiar criterio que tengo a la hora de escoger mis guitarras.

He de confesar que no recuerdo muy bien la cadena de acontecimientos que nos condujo a la historia que os voy a contar; quizá fuera mi falta de conocimiento respecto a este producto tan de moda (MongoDB), o el número de zuritos que nos habíamos tomado… No lo acabo de tener muy claro, pero el hecho es que, de algún modo, me metí de lleno en un juego/apuesta que acabaría con el escaso tiempo libre que me quedaba a lo largo de la semana.

La Apuesta

Las reglas eran muy sencillas: el ganador sería aquel de nosotros dos que fuera capaz de insertar una carga de trabajo acordada previamente en menos tiempo. Él con su MongoDB de marras, y yo con mi viejo, fiable, pero no siempre bien ponderado SQL Server 2008 R2, ¡como no!

Tras duras negociaciones de paz, Unai y yo llegamos a un acuerdo para evitar emplear armas de destrucción masiva; por este motivo finalmente decidimos no evaluar la que es, de lejos, la manera más rápida de insertar datos en SQL Server: las Bulk Inserts.

La carga de trabajo que nos pusimos de objetivo fue 500.000 registros, cada uno conteniendo un GUID y 20 caracteres textuales (no unicode).

Descargo de Responsabilidad: Siempre que escribo acerca de una tecnología ajena al ecosistema de Microsoft acabo con problemas de fanatismos y guerras de religión. Por ello, dejadme que deje muy claro que este post solo pretende contar la historia de un pique interesante con un compañero, así como tratar de aprovechar la ocasión para comentaros un par de técnicas no triviales para realizar cargas rápidas de datos en SQL Server. Vaya por delante que MongoDB me resulta un producto más que interesante y con escenarios para los que es más idóneo que una base de datos relacional.

Construcción del Entorno de Prueba

En entorno de prueba inicial consistió simplemente en una base de datos que contiene la tabla sobre la que vamos a insertar nuestros 500.000 registros. La única preparación inicial que he realizado de cara a mejorar un poco el rendimiento ha sido dimensionar de modo apropiado los ficheros (para evitar autocrecimientos por uso del log de transacciones en las inserciones) y establecer el modelo de recuperación a SIMPLE.

A continuación os dejo el script inicial, aunque como veréis más adelante hemos realizado algún que otro cambio:

CREATE DATABASE DemoInserts ON PRIMARY 
( 
    NAME = 'DemoInserts', 
    FILENAME = 'c:\Databases\DemoInserts.MDF', 
    SIZE = 200, 
    FILEGROWTH = 10% 
) 
LOG ON 
( 
    NAME = N'DemoInserts_Log', 
    FILENAME = N'c:\Databases\DemoInserts_Log.LDF', 
    SIZE = 200, 
    FILEGROWTH = 10% 
) 
GO 
ALTER DATABASE DemoInserts SET RECOVERY SIMPLE 
GO 
USE DemoInserts 
GO 
CREATE TABLE [Test] 
( 
    ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), 
    Payload VARCHAR(20) NULL, 
)

 

¡Vamos a Insertar Filas!

La primera solución consistirá en un simple bucle de 500.000 ejecuciones, en el que para cada iteración insertamos una fila. Su única ‘optimización’ fue el empleo de la opción NCOUNT ON para evitar saturar el output buffer con los típicos mensajes de (1 row affected),  tal como se puede ver en el siguiente fragmento de código:

SET NOCOUNT ON

DECLARE @cont INT 
SET @cont = 0

WHILE (@cont < 500000) 
BEGIN 
   INSERT INTO Test(ID, Payload) VALUES (DEFAULT, REPLICATE('X', 20)) 
   SET @cont = @cont + 1 
END

El tiempo de ejecución de la inserción en mi equipo fue de 3 minutos y 8 segundos.

NOTA: Todos los tiempos tomados en este artículo se han calculado realizando el promedio de tres ejecuciones sucesivas.

Evidentemente, desde antes de realizar esta prueba yo era consciente de que este mecanismo no iba a arrojar buenos resultados, pero me serviría para tener una línea base respecto a la que poder evaluar las mejoras posteriores y para hacerme una idea respecto a lo que podría esperar de SQL Server.

Llegados a este punto le comenté a Unai que me daría con un canto en los dientes si era capaz de bajar mi tiempo de inserción por debajo de los 20 segundos. Mi amigo y competidor me corrigió, asegurándome que sería él mismo quien se encargaría de darme con el susodicho canto en toda mi dentadura si conseguía tal hazaña. Creo que en ese momento empecé a tomarme nuestra pequeña competición en serio ;)

Manos a la Obra… ¿Dónde esta mi cuello de botella?

Siempre hay un cuello de botella; ¡siempre! Aunque el rendimiento sea excelente, siempre hay un factor limitante, y solo uno. En este caso, un vistazo rápido al Task Manager me permitió ver que la CPU no eran en este caso el problema:

image

Esto me dio una buena idea para el siguiente intento: realizar una pequeña aplicación en C# que se encargara de lanzar INSERTS modo masivo en paralelo, usando múltiples hilos, para tratar de aprovechar al máximo las cuatro CPUs de las que dispone mi equipo de pruebas.

A continuación os pongo el código exacto que he empleado para esta prueba, aunque casi me da vergüenza ;) No me juzguéis por él, es solo un ejemplo ‘quick and dirty’!:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Diagnostics;

namespace ConsoleApplication5
{
    class Program
    {

        static void Main(string[] args)
        {
            Stopwatch watch = Stopwatch.StartNew();

            var mainTask = new TaskFactory().StartNew(() =>
            {
                new Task(() => DoWork(125000), TaskCreationOptions.AttachedToParent).Start();
                new Task(() => DoWork(125000), TaskCreationOptions.AttachedToParent).Start();
                new Task(() => DoWork(125000), TaskCreationOptions.AttachedToParent).Start();
                new Task(() => DoWork(125000), TaskCreationOptions.AttachedToParent).Start();
            });

            mainTask.ContinueWith((t) =>
            {
                watch.Stop();
                Console.WriteLine(watch.Elapsed);
            }, TaskContinuationOptions.OnlyOnRanToCompletion);

            Console.ReadLine();
        }

        private static void DoWork(int rows)
        {
            string connStr = "Data Source=.;Initial Catalog=DemoInserts
;Integrated Security=true;Application Name=TestInserts"; int countIterations = 0; while (countIterations < rows) { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = "INSERT INTO Test(Id,Payload) VALUES (NEWID(), REPLICATE('X',20))"; cmd.ExecuteNonQuery(); countIterations++; } } } } } }

Como podéis ver, el código simplemente instancia 4 tareas (una por cara núcleo disponible en mi máquina). Estas tareas reciben el número de filas a insertar, y proceden a realizar estas inserciones como comandos SQL dentro de un bucle.

La inserción de las dichosas 500.000 filas por este mecanismo se produjo en 1 minuto y 7 segundos; si bien el valor no está nada mal en términos de mejora, sabía que aún me quedaba mucho para tener un tiempo competitivo. Una nueva visita al Task Manager me hizo darme cuenta rápidamente de que, si bien había aumentado mucho mi consumo de CPU, aparentemente aún disponía de mucho margen adicional:

image

A la vista de esto, decidí probar a aumentar el número de hilos empleados para lanzar las sentencias INSERT, y monitorizar tras cada prueba tanto la duración de la misma como la utilización de los procesadores durante el transcurso de las inserciones. De este modo pude lograr grandes usos del procesador, pero es importante tener presente una cuestión: el incremento de la actividad de CPU no implica necesariamente un mejor rendimiento en las inserciones.

Para demostrar este punto, os adjunto una pequeña gráfica que muestra los tiempos obtenidos en función del número de hilos empleados:

image

(Atiende que graficazaaa… ¿verdad que una de estas ayuda a darle un look más profesional a cualquier artículo? ;))

Como se puede apreciar, el sweet spot de este proceso de inserción en mi equipo se encuentra en los 8 hilos, con un tiempo de ejecución de 43 segundos. A continuación os muestro la gráfica de uso de CPU de esta ejecución, para que podáis contrastarla con la primera de todas y apreciéis el notable incremento en la utilización que hemos logrado:

image

 

¿Por donde seguimos ahora?

Bien, llegados a este punto ya tenía mas o menos claro el punto más idóneo de paralelismo desde el lado de la aplicación C# de inserciones, pero no conocía lo que estaba sucediendo por parte de SQL Server. Por esto, decidí estudiar las esperas del servidor; borre las estadísticas de esperas, lancé el proceso y a su finalización, use el celebérrimo script de Glenn Berry, personalizado por Paul Randal para la agregación de información de esperas por recursos en SQL Server.

El resultado de la prueba, si bien era de esperar, resulta revelador:

image

Como se puede ver, un 99.22% del tiempo total de la prueba se destino a realizar entradas en el log de transacciones; esto se debe al hecho de que las inserciones, salvo casos muy puntuales en escenarios de BULK INSERT, son operaciones completamente logeadas. Dicho a lo bestia: por cada inserción, a parte del dato en si, hay que escribir la intención de realizar la inserción previamente en el log de transacciones. Esta operación es síncrona, y es la que esta provocando una gran cantidad de esperas.

Para tratar de remediarlo, se me ocurrió tratar de reducir el número de cláusulas INSERT que enviamos al servidor mediante las sentencias de inserción múltiples de SQL Server 2008, que nos permiten insertar en el mismo statement hasta mil filas(*). Ya sabéis, algo así:

INSERT INTO Test(ID, Payload) VALUES
(NEWID(), 'AAA'),
(NEWID(), 'BBB'),
...
(NEWID(), 'XXX')

Modifique un poco la aplicación para realizar estas sentencias, quedando el código del siguiente modo:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Diagnostics;

namespace ConsoleApplication5
{
    class Program
    {

        static void Main(string[] args)
        {
            Stopwatch watch = Stopwatch.StartNew();

            var mainTask = new TaskFactory().StartNew(() =>
            {
                new Task(() => DoWork(), TaskCreationOptions.AttachedToParent).Start();
                new Task(() => DoWork(), TaskCreationOptions.AttachedToParent).Start();
                new Task(() => DoWork(), TaskCreationOptions.AttachedToParent).Start();
                new Task(() => DoWork(), TaskCreationOptions.AttachedToParent).Start();
                new Task(() => DoWork(), TaskCreationOptions.AttachedToParent).Start();
                new Task(() => DoWork(), TaskCreationOptions.AttachedToParent).Start();
                new Task(() => DoWork(), TaskCreationOptions.AttachedToParent).Start();
                new Task(() => DoWork(), TaskCreationOptions.AttachedToParent).Start();
            });

            mainTask.ContinueWith((t) =>
            {
                watch.Stop();
                Console.WriteLine(watch.Elapsed);
            }, TaskContinuationOptions.OnlyOnRanToCompletion);

            Console.ReadLine();
        }

        private static void DoWork()
        {
            string connStr = "Data Source=.;Initial Catalog=DemoInserts;Integrated Security=true;
Application Name=DemoInserts"; int countIterations = 0; while (countIterations < 63) // 63 { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; int countValues = 0; var builder = new StringBuilder(); builder.Append("INSERT INTO Test(Id,Payload) VALUES "); while (countValues < 1000) { builder.Append("(NEWID(), REPLICATE('X',20)) "); if (countValues != 999) builder.Append(","); countValues++; } cmd.CommandText = builder.ToString(); cmd.ExecuteNonQuery(); countIterations++; } } } } } }

Con esta nueva versión, usando ocho hilos, baje el tiempo a… (redoble de tambor, por favor) 4 segundos escasos!!!!

Llegados a este punto me sorprendí yo mismo; no creí que pudiera bajar tanto el tiempo de estas inserciones, y por primera vez vi peligrar mi dentadura ;) Como me encanta tener datos y números de cada mejora que voy logrando, volví a lanzar la prueba y consulté las estadísticas de esperas del servidor durante el tiempo de ejecución de la misma:

image

Como podéis ver, el hecho de agrupar las inserciones en batches ha hecho milagros! ahora mismo el servidor esta dedicando aproximadamente el mismo tiempo a escribir en el log de transacciones que en en resolver bloqueos de latches, una situación más normal, y que ha repercutido de manera espectacular en el rendimiento de estas inserciones.

(*): Por si alguien se lo pregunta, yo tampoco tengo ni puñetera idea de por qué el hard-limit de mil filas.

Y por hoy ha estado bien, ¿no? ;)

Con esta entrada he buscado abriros un poco el apetito y, con un poco de suerte, picar a alguien más para que entre al pequeño juego que tenemos Unai y yo. He explicado mis aproximaciones iniciales, pero me reservo para la siguiente entrada la solución ‘buena, buena’ desde el lado del SQL Server.

Para los mas ansiosos os iré adelantando que se basa en particionar la tabla para evitar contención sobre la misma; los detalles de implementación los pondré en mi siguiente post, pero como mis actualizaciones son como los despertares del lazy_writer (ya sabéis, puede ser dentro de 3 milisegundos, o dentro de 3 años!), os he querido adelantar la técnica por si queréis investigarla por vosotros mismos.

A modo de resumen, voy a finalizar con otra gráfica de estas elegantes y vistosas, que hoy tengo el día ejecutivo ;)

image

Por cierto, si os preguntáis que tal lo hizo MongoDb… vais a tener que esperar a que Unai lo publique en su blog. No obstante, os puedo adelantar que fue capaz de bajar de estos 4 segundos….

...

..

.

¡¡¡¡Pero yo también!!!! Muahahahaha!!! ;)

Keep Rockin’!

Rock Tip:

Hacía mucho tiempo que no compartía con vosotros uno de mis rock tips… o mejor dicho, hacía mucho que no compartía nada con vosotros! Tengo el blog muy dejado, pero un servidor no da para más :(

En esta ocasión me traigo a uno de mis descubrimientos del año, los geniales Reckless Love. Banda finlandesa liderada por el increíble Olly Herman, quien fuera vocalista de Crashdiet por una breve temporada y una de las personas con mas carisma y buen rollo que he visto nunca sobre un escenario. Herederos de las mejores raíces del hard rock melódico ochentero, pero con un increíble toque dance y discotequero, puedo afirmar que verles en directo es la experiencia mas cercana a ver al David Lee Roth sin recurrir a un De Lorean con condensador de fluzo.

El tema que he escogido es el single de su segundo (y último hasta la fecha) disco: Hot. No tiene gran relación con la temática de la entrada, pero realmente me apetece compartir este tema con el mundo: si a uno solo de vosotros le hace sentir tan solo una decima parte de lo bien que me sienta a mi escuchar este temazo, ya habrá justificado saltarme mi costumbre de poner temas relacionados.

Posted: 17/11/2011 3:09 por Pablo Alvarez | con 11 comment(s) |
Comparte este post:

Comentarios

Juan Carlos González Martín ha opinado:

Joder,

Buenísimo el post...ahora falta ver la réplica de Unai :P

Un abrazo crack

# November 17, 2011 7:49 AM

Juan Irigoyen ha opinado:

Excelente!!!!, creo que muchos esperan que Unai muerda el polvo... :)

# November 17, 2011 2:02 PM

El Bruno ha opinado:

Genial !!! ^^

# November 17, 2011 8:25 PM

Juanma ha opinado:

Muy bueno el artículo.

Viendo que eres todo un experto en la materia, te planteo una duda, ¿hay alguna forma de que el paralelismo lo introduzca el sql server en lugar de hacerlo desde la aplicación cliente?

Por ejemplo, no sé si enviando los inserts como un batch (en lugar de usar distintas conexiones) el sql es lo bastante listo como para aprovechar sus fibers y paralelizarlos.

# November 17, 2011 8:57 PM

Pablo Alvarez ha opinado:

¡Gracias a todos! Estos días voy a estar muy liado, pero ya estoy deseando sacar un rato para escribir la siguiente parte del artículo, en la que os voy a tratar de explicar un truco muy interesante :)

@Juanma: ¡Genial tu pregunta! Voy a ver si me puedo explicar en poco espacio y en poco tiempo :) SQL Server es capaz de forzar el paralelismo intra-query (es decir, que una única consulta utilize mútliples procesadores) pero solo lo hace a partir de un coste, que podemos definir con la opcion de servidor 'cost threshold for parallelism'.

El asunto está en que ésta consulta es tan sencilla (una insert sin más) que por mucho que quiera, el coste es mínimo y, por tanto, no involucrará el paralelismo intra-query.

En este caso concreto he usado paralelismo desde el servidor para aumentar la carga de trabajo. ¿Por que? Pues porque como habíamos visto en las esperas, el cuello de botella NO esta en la CPU. Cada conexión está lanzando un batch de unas mil filas, y vemos que las esperas están en el evento de tipo LOGWRITER (escritura en el log de transacciones) y en PAGELATCH_EX.

Sabiendo que tengo mas 'CPU' disponible, jugue a meterle mas sentencias... de este modo aumento el paralelismo por concurrencia (que no el paralelismo intra-query) y trato de aprovechar un poco mas los procesadores.

A modo de resumen:

- Una sentencia SQL (o un batch) es una tarea unica que, a no ser que involure paralelismo intra-query, esta atada a la CPU (scheduler) donde se inicio.

- Estos batches empeizan a ejecutarse n el CPU pero cada poco salen de la CPU y se ponen a esperar por disco. Mientras, la CPU esta desocupada.

- Si tengo 4 CPUs y solo 4 hilos, es posible que este en algun momento las CPUs paradas, mientras los hilos estan esperando por disco. Si tuviera mas hilos, seguramente podria maximizarse el uso de CPU.

- Por este ultimo punto decidi ir aumentando las conexiones (Tareas en SQL Server) para que haya mas tareas en cada scheduler (CPU) y, por tanto, minimizar el tiempo ocioso de las CPUs.

El como resolver la contencion de disco, o reducirla, será de lo que hablaremos en la próxima entrada :)

Espero haberme explicado! un saludo!!

# November 17, 2011 10:22 PM

Juanma ha opinado:

"- Una sentencia SQL (o un batch) es una tarea unica que, a no ser que involure paralelismo intra-query, esta atada a la CPU (scheduler) donde se inicio."

Vale, eso me aclara todo. Pensé que a lo mejor enviando 100 inserts del tirón en lugar de 1 insert con 100 values SQL hacía algo, pero tiene sentido que no lo haga. Analizar el batch para saber si hay dependencia en el orden de ejecución sería bastante complicado para SQL (y para cualquiera), sobre todo si tiene que tener en cuenta la posibilidad de triggers y otros efecto colaterales.

Otra idea, si mantuvieras la inserción que realiza cada hebra en una misma transacción, ¿se ahorraría tiempo al acceder sólo una vez al transaction log? Entiendo que al hacer las inserciones de 1000 en 1000 se reduce mucho el acceso, pero no sé si todavía sigue pesando (lo siento pero no tengo mucha idea de esto y no sé interpretar bien las capturas de pantallas que hay en el post).

# November 18, 2011 9:29 AM

O bruxo mobile ha opinado:

DESCARGO DE RESPONSABILIDAD Antes de empezar la entrada me gustaría dejar claro que esto es un pasatiempos

# November 18, 2011 11:36 AM

Juan Irigoyen ha opinado:

Un par de ideas para mejorar, la primera la utilización de las common table expresión para optimizar la sentencia insert y evitar bloqueos,

msdn.microsoft.com/.../ms175972(v=sql.90).aspx, otra podría ser la utilización de un store procedure en lugar de enviar la sentencia directamente, me gustaria saber la razón del porque llegasteis al acuerdo de no utilizar las bulk insert. ¿ se supone que en MongoDB tampoco se han realizado concesiones, o si... ?

Un saludo.

# November 19, 2011 1:05 AM

Surviving the Night ha opinado:

Me alegra comprobar que, al parecer, el pequeño reto que nos traemos Unai y yo está provocando cierto

# November 22, 2011 8:23 PM

Josue Yeray ha opinado:

Hola a todos! Desde hace unas semanas, mis compañeros Pablo Doval y Unai Zorrilla están

# December 8, 2011 1:16 PM

Chalalo Land ha opinado:

Hola, acá les dejo los documentos y demos sobre ASP.NET API y MongoDB. Aspnetwebapi mongo from Gonzalo

# August 31, 2012 3:02 PM