Surviving the Night

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

Roar: Inserciones Masivas en MongoDB vs SQL Server (IV)

Me alegra comprobar que, al parecer, el pequeño reto que nos traemos Unai y yo está provocando cierto interés; más allá de haber conseguido que el de Monforte y yo nos hayamos tenido que rascar el bolsillo e invitarnos el uno al otro a varias cenas, algo de debate si que se ha generado en twitter, llegando a contar incluso con alguna prueba similar en otra plataforma!(*)

Para quien no haya leído la entrada anterior, o la respuesta de Unai, os recuerdo brevemente las reglas de nuestra improvisada apuesta:

El ganador será aquel de nosotros dos que sea capaz de insertar 500.000 filas (documentos, en su dialecto) compuestas por un GUID y una cadena de 20 caracteres (no unicode). Recordaros que la solución bcp ha sido declarada ilegal, para que Unai siga manteniendo alguna opción de victoria ;)

Lo cierto es que no tenía pensado publicar nada mas avanzada la semana. Sin embargo, me ha surgido un imprevisto viaje a la pérfida Albión este fin de semana para participar en una taskforce de rendimiento (yeah!), y he tenido bastantes ratos libres entre viajes y transbordos. No se me ocurrían muchas maneras mejores de ocupar este tiempo que compartiendo mi siguiente solución al reto, así que ahí vamos!

Descargo de Responsabilidad: Al igual que hice en la entrada anterior, voy a tratar de volver a cubrirme las espaldas frente a conversaciones que deriven en fanatismos. 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.

NOTA: Si alguien esta leyendo mi blog de modo secuencial y nota la ausencia de una entrada llamada ‘Inserciones Masivas en MongoDB vs SQL Server (II)’, esto es porque esta entrada la ha realizado Unai en su blog, y podéis acceder a ella aquí. De hecho, os recomiendo encarecidamente que la leáis incluso en el caso de que no estéis particularmente interesados en MongoDB o bases de datos NoSQL.

(*): El amigo Javier Torrecilla hizo la prueba en Oracle, aunque me niego a dar los resultados! ;) Por otra parte, aun esperamos la versión en WP7 de Yeray!!! En el factor rendimiento no creo que nos gane ni a SQL Server ni a MongoDB, pero lo que es en cool factor nos va a dar una paliza.

¿Por dónde íbamos?

Si recordáis la entrada anterior, mis esfuerzos de inserción ya no eran capaces de aprovechar mejor los procesadores, a pesar de que no se encontraban aún al máximo; mi cuello de botella se había desplazado a otros lugares, y se materializaba en el lado de SQL Server mediante esperas por dos tipos de eventos concretos (WRITELOG y PAGELATCH_EX) como muestra la siguiente captura:

image_thumb58

El tener casi un 50% de las esperas globales en WRITELOG evidencia el uso masivo del log de transacciones por las inserciones. Recordemos que, al contrario que con la escritura de los datos, esta operación es síncrona. A priori poco más podemos hacer de este lado, a no ser que recurriéramos a un escenario de BULK INSERT mínimamente logado, pero lamentablemente esta opción viola las reglas del concurso. Doh… estúpidas reglas!

¡Vamos a ver si la otra espera nos da alguna pista! La espera es por el tipo PAGELATCH_EX, que se trata de una vieja amiga mía. De todos modos, en caso de que os encontréis con alguna espera que no conozcáis, las tenéis casi todas listadas en la entrada relacionada a sys.dm_os_wait_stats de los Books Online… o en vuestro buscador favorito! ;) La respuesta de la documentación para esta espera es la siguiente:

PAGELATCH_EX : Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.

Lo que sucede es que al hacer tantas inserciones de modo concurrente, la ultima pagina del heap sufre de contención, lo que limita seriamente nuestra capacidad para hacer inserciones.

¿Que podemos hacer para reducir la contención de esperas PAGELATCH_EX? Aquí viene el momento genial, pero absolutamente contraintuitivo, de esta entrada del blog:

<drum roll>

Vamos a particionar la tabla sobre la que vamos a hacer las inserciones.

</drum roll>

¡¿Particionar la tabla para las inserts?! Se te pira…

Eso es lo que pensé la primera vez que escuché esta técnica; todos estamos más que habituados al particionado de tablas pensando en las mejoras administrativas que nos proporcionan y, como no, de rendimiento a la hora de realizar búsquedas debido a la eliminación de particiones, la menor altura de los índices, etc…. ¿Pero, mejoras en inserciones?

Os podéis imaginar como me quede cuando leí por primera vez acerca de la idea de particionar una tabla para optimizar la carga de datos en un artículo de Thomas Kejser; sin embargo, tiene sentido. Ahora os estoy escuchando a algunos pensar…

¿¡pero que hardware tiene tu portátil?! Si vas a hacer tantas particiones como procesadores (cuatro), ¿no deberías seguir las buenas recomendaciones y poner cada archivo en un disco independiente?

¡Buena pregunta! (y tanto… ¡como que me la he hecho yo a mi mismo! ;)) Pero daros cuenta de un detalle: la contención NO está en el disco, sino en las estructuras internas de SQL Server de la tabla. Lo que pretendo con esto no es optimizar el acceso a disco, sino la sobrecarga que tiene SQL Server cuando tiene varios hilos tratando de insertar en la misma tabla. Al particionarla, estas esperas se reducen, y ese es nuestro objetivo (PAGELATCH_EX).

imageDe cara a realizar este particionado, vamos a realizar un pequeño cambio a mi tabla que, evidentemente, consensué con Unai previamente. De hecho, a priori, este cambio me pone en una situación mas comprometida, ya que implica agregar un campo nuevo (de tipo TINYINT y al que llamaré Hash aunque realmente no es tal…), así como un índice clustered sobre el ID y este nuevo campo.

La siguiente pregunta es: ¿cuantas particiones crear sobre la tabla? En este punto no he sido muy profesional, para ser sinceros: solo he hecho una prueba por el momento, con 8 particiones. Escogí este valor un poco por intuición; mi maquina tiene 4 núcleos reales, y consideré que 8 particiones deberían ser un numero apropiado para poder jugar un poco con la ociosidad de las tareas y minimizar el signal_wait_time. De todos modos, si encuentro algo más de tiempo, trataré de hacer al menos un par de escenarios mas con diferente número de particiones.

Como ya comentamos antes, todas estas particiones apuntaran al mismo filegroup y fichero, por lo que en realidad no se trata de paralelizar las escrituras en ficheros o unidades diferentes; una vez mas recuerdo que solo pretendemos reducir la contención en las estructuras internas de SQL Server.

Así pues, nuestro particionado quedaría tal y como muestra el siguiente diagrama:

image

A continuación os muestro el script de creación de mi nueva tabla, así como la función y esquema de particionado que he empleado, y el nuevo índice clustered:

CREATE PARTITION FUNCTION pf_hash (TINYINT) 
AS RANGE LEFT FOR VALUES 
(0x00, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07)

CREATE PARTITION SCHEME ps_hash
AS PARTITION pf_hash TO ([ALL])
GO

CREATE TABLE dbo.Test
(
    ID uniqueidentifier NOT NULLDEFAULT NEWID(),
    Payload varchar(20) NULL,
    Hash tinyint NOT NULL
) ON ps_hash(Hash)

CREATE UNIQUE CLUSTERED INDEX CIX_Hash 
ON dbo.Test (ID ASC, Hash ASC) ON ps_hash(Hash)

Además del particionado, voy a adelantarme a un potencial problema de contención de bloqueos en la tabla asegurándome que mi índice no permita bloqueos de pagina ni fila, y estableciendo la política de escalado de bloqueos de la tabla a Auto:

ALTER INDEX CIX_Hash ON dbo.Test
SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = OFF)

ALTER TABLE dbo.Test SET (LOCK_ESCALATION = AUTO)

El código de la prueba es similar a la versión anterior, pero agregando el código hash a cada una de los Tasks, de modo que cada Task enviará sus inserts a una partición diferente:

        private static void DoWork(int hash)
        {

            int countIterations = 0;
            while (countIterations < 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, Hash) VALUES ");

                        while (countValues < 1000)
                        {
                            builder.Append("(NEWID(), REPLICATE('X',20), ");
                            builder.Append(hash.ToString());


                            if (countValues != 999)
                                builder.Append("),");
                            else
                                builder.Append(")");

                            countValues++;
                        }

                        cmd.CommandText = builder.ToString();
                        cmd.ExecuteNonQuery();
                        countIterations++;
                    }
                }
            }
        }

Y todo esto.. ¿para que?

Solo hay una respuesta a esta pregunta: para derrotar, y humillar si cabe, a Unai y su MongoDB :P Vamos a ver si lo hemos conseguido!

Bueno, en este caso no me entretendré demasiado con los resultados; simplemente os diré que después de los cambios consigo la inserción de los 500.000 registros en 1,8 segundos estables.

Esta solución esta ligeramente por detrás de los 1,6 segundos del MongoDB de Unai, pero aun así me siento muy satisfecho porque es muy estable en tiempos y, bueno… si miro atrás al primer día del reto, no creía que pudiera bajar de los 20 segundos!

Aun así, ya estoy trabajando en la siguiente idea… No se si será mas rápida, pero os garantizo que es bastante espectacular! ;)

Conclusiones:

A pesar de que aún falta lo más importante, el cara a cara final sobre el mismo metal, parece que ya podemos ir obteniendo algunas conclusiones buenas:

  • Para empezar, y contra todo pronóstico (al menos por mi parte), parece que en esté escenario concreto SQL Server puede plantar cara a MongoDB. Aún tengo un as en la manga, pero incluso en el caso de que finalmente SQL Server salga derrotado en este pequeño juego, ya me resulta increíble estar en el mismo orden de magnitud de tiempos, así que no digamos estar en una situación tan reñida y con posiciones de victoria.
  • Aun no llegamos al limite de nuestras CPUs en este escenario; estoy convencido de que algo más podremos hacer… y aquí entra en juego mi as en la manga.
  • En este caso hemos tratado de mejorar las esperas que no se corresponden con las entradas en el log de transacciones; ¿podríamos mejorar este otro frente, que supone el otro 50% del tiempo en nuestro escenario original? Esta parte es mas difícil, por no decir imposible si no podemos realizar el proceso con operaciones mínimamente logadas (BULK), así que no creo que podamos ganar nada por ahí sin hacer trampas a Unai.

Igual que hice en la ocasión anterior, os voy a dejar una pista de lo que será mi siguiente intento para mejorar el rendimiento en las inserciones; solo os diré que voy a tratar de reducir las esperas por SOS_SCHEDULER_YIELD :)

Cuando publiquemos los resultados definitivos, publicaremos también el código de ambas implementaciones; me encantaría que alguien mas se animara a realizar las pruebas y extraer conclusiones!

Por último, quizá sea interesante reflexionar si realmente es necesario tanto esfuerzo para ganar, en este caso, simplemente dos segundos.

Keep Rockin’!

Rock Tip:

“Ready to Roar, ready to rock, ready to give it all I’ve got” reza el estribillo de ‘Roar’, uno de os temas más espectaculares del increíble ‘Coup de Grace’, el último disco de la ya venerable banda de Hard Rock melódico sueca Treat.

Y eso es precisamente lo que me dije a mi mismo cuando Unai y su MongoDB empezaron a apretarme las tuercas! Ready to give it all I’ve got! De hecho, este disco me estuvo acompañando durante gran parte de las pruebas que iba realizando; debería poner al final un grupo de los artistas, discos y temas involucrados en todo el reto ;)

Posted: 22/11/2011 16:24 por Pablo Alvarez | con 6 comment(s) |
Comparte este post:

Comentarios

Juan Irigoyen ha opinado:

Impresionante, particionar la tabla, nunca lo hubiera pensado, quizás una prueba más lógica pase por aumentar los registros 1,8 segundos es tan poco que seguramente los tiempos de conexión y desconexión sumados a los de la preparación de la sentencia se lleven la mayoría. Una opción que no has comentado y que en excenarios así puede tener sentido es la activación de boost SQL Server priority y probar a habilitar y deshabilitar processor affinity e I/O affinity, quizas nos llevemos alguna sorpresa, algunas veces los programas que hacen uso de varios hilos pueden llegar a ser mas lentos.

Un saludo.

# November 22, 2011 9:41 PM

Pablo Alvarez ha opinado:

¿¡Que tal Juan?! Tranquilo, yo tampoco lo hubiera pensado :) Coincidio que poco antes del reto leí un par de entradas del señor Kejser, asi como un par de videos suyos del SQLBits, y eso fue lo que me dio la idea.

La verdad es que tanto los tiempos de preparacion de sentencia, como de gestion de conexiones, son aun minimos. Ahora nos encontramos con dos esperas principales del lado de SQL Server: SOS_SCHEDULER_YIELD y WRITELOG. La segunda sabeis que no puedo combatirla, ya que solo podria mejorarlo con BULK INSERTS. La primera... estate atento a la siguiente entrada, pero no vas desencaminado con el tema de la afinidad ;)

Respecto al priority boost, esto solo incrementa la prioridad del proceso de SQL Server; puede ayudar a evitar que otros procesos nos 'roben' memoria, etc.. pero no debiera hacer que esto fuera mas rapido.

Un abrazote!!

# November 23, 2011 12:11 AM

Javier Torrecilla ha opinado:

Simplemente increible los resultado que habeis obtenido con SQL y MONGO.

Yo hice una pequeña prueba con Oracle, y "mas o menos" conociendo las diferencias de hardware entre vuestras maquinas y la mia. Los resultados de Oracle dejaban mucho que desear.

Un saludo y a la espera de ver los últimos post!

# November 23, 2011 8:36 AM

Eduard Tomàs i Avellana ha opinado:

Estoy siguiendo la serie con mucho interés. Primero he de decir que no tenía claro que Sql Server sin Bulk insert pudiese competir tan de cara con MongoDb en este escenario.

Dicho esto, extraigo una conclusión: para conseguir 1,6 segundos en MongoDb Unai no ha tenido que hacer nada extraordinario, cualquiera con un poco de experiencia en MongoDb puede llegar a la solución de Unai (otra cosa es que se le ocurra :p). Por otro lado para conseguir 1,8 segundos en SQL Server se necesita... bueno, se necesita ser Pablo :P

Al menos me da a mi la sensación de que Pablo está manejando temas de SQL Server realmente avanzados! :D

Javier, estaría bien que publicases tus resultados así como el proceso que has seguido con Oracle, para poder comparar!

A menos, a primera vista,

# November 23, 2011 9:38 AM

Luis M. González ha opinado:

Estoy siguiendo muy de cerca este pique y lo único que puedo decir es que ... me encanta :)

Y la verdad no me imaginaba que SQL Server pudiese plantar cara a MongoDB sin los BULK INSERTS, así que con los números actuales creo que es más que suficiente.

Saludos!

# November 23, 2011 2:56 PM

Josue Yeray ha opinado:

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

# December 11, 2011 11:50 AM