Codemotion 2016 @carlossanchezp

MySQL: Cómo migrar una tabla con 8 millones de registros

Carlos Sánchez Pérez

Pragmatic developer, Ruby/Rails choice at the moment, Learning Scala, Elixir/Phoenix & DDD. Currently working as Web Dev at @The_Cocktail.

 

Let's make it happen

Blog (only in spanish):

carlossanchezperez.wordpress.com

Twitter:

@carlossanchezp

Proyecto

REAL

E-Commerce

  • 6-7 Millones de usuario al mes
  • 500.000 euros semanales aproximadamente
  • Cientos y cientos de líneas de código
  • 3 años de vida del proyecto
  • Oigan!! No se puede parar, estamos vendiendo

Arquitectura

El problema

Síntomas

  • Un select 10M por índice es lento
  • Full scan de la tabla

Si ocurre esto es muy extraño, no es normal!!

Necesitamos saber ¿qué está pasando?

  create_table "order_events", :force => true do |t|

    t.string   "order_id",    :null => false

    t.string   "origin_type", :null => false
    t.integer  "origin_id"
    t.string   "kind",        :null => false
    t.text     "data"
    t.datetime "created_at",  :null => false
    t.datetime "updated_at",  :null => false
  end



  add_index "order_events", ["order_id", "kind"], 
:name => "index_spree_order_events_on_order_number_and_type"
  add_index "order_events", ["order_id"], 
:name => "index_spree_order_events_on_order_number"

Schema

  • Con un "desc tabla" mysql

Consola MySQL

  • Order tiene una relación con Order Event

Consola Rails

AR: order.order_event

Descubrir del problema

  • Cuidado!! con la obviedad, juega mala pasada, miramos pero no vemos
  • Full scan de la tabla

Un full scan no es un problema, es un síntoma de que necesitas índices

hasta que hacemos:

select * from order_events where order_id = 1234567889

 

y ahora hacemos

 

   select * from order_events where order_id = "1234567889"

t.string   "order_id",    :null => false

¿por qué es ese índice string nuestro problema?

el problema

  • En Rails fk_id lo interpreta como integer

AR: order.order_event

 

select * from order_events where order_id = 1234567889

 

Otros problemas ocultos

  • Una deuda técnica de Código Legacy, debemos responsabilizarnos cuando lo encontremos y dar una solución si es posible acorde con la dedicación.
  • Un índice no evolucionado en el tiempo, dedicación a mantenimiento y optimización.
  • Las prisas no son buenas compañeras
  • Rotación alta en el equipo

  • Por qué vamos a simplificar el stack, pudiendo complicarlo un poco más

Es una deuda técnica que tengo que asumir. El problema está ya detectado.

la solución

  • Cambiar el índice no evolucionado como string a integer

Trabajar en la Solución

Migrate de Rails

¿qué tenemos que hacer?

  • Migrate con el cambio de fk_id de string a integer
  • Utilizar el método "up" del la migrate "change_column" - tabla campo - por integer

 

 

Ya tenemos la solución.......

pero....... en un contexto de tiempo real y dimensiones de 8M

Espera infinita

En medio de un deploy a PRO

HELP!!

Error de velocidad y parar a pensar!!!

  • No tomar datos de partida
  • Envergadura del problema
  • No considerar el tener o no índices
  • Tipo de datos en la tabla
  • Serio problema de deploy de horas

No se puede comprar!!!

Buscar una solución pragmática

Datos de partida

  • Tenemos aproximadamente 8Millones de tuplas
  • Tenemos índices sobre el campo a modificar
  • Migrar todos los datos menos los fk_id con datos innecesarios (datos viejos que no son útiles ya en la nueva data)
  • Existen varios índices sobre la tabla
  • Se necesitan historificar datos en el tiempo

Microservicio en Ruby

Busco velocidad en la migración de datos

Tengo un modelo Active Record

aprovechar el modelo en lectura

Solución con Ruby

  • Utilizar AR para las lecturas en batch




OrderEvent.find_each(batch_size: MAGIC_NUMBER) do |oe|



end

MAGIC_NUMBER = 50.000

Solución con Ruby

  • Los "insert" de los datos los voy almacenando en un array 
inserts_values.push "(#{oe.order_id.to_i}, 
                    '#{oe.origin_type}', #{oe.origin_id.to_i}, 
                    '#{kind}', #{s}, '#{oe.created_at.to_s(:db)}', 
                    '#{oe.updated_at.to_s(:db)}')"

Solución con Ruby

  • No utilizar ActiveRecord para inserts ya que mi intención es hacer inserciones masivas 
sql_insert = "INSERT INTO #{table_destination} 
            #{columns_origin} 
					  				
            VALUES #{inserts_values.join(", ")}"
					
con_sql.execute sql_insert

Solución con Ruby

  • Un insert con X values a insertar mejor que X inserts con values

Solución con Ruby

  • Sin incorporar "transaction" no aporta en un único insert con múltiples values por ciclo de batch

¿por qué es mejor no utilizar Active Record en INSERT masivos?

Déjame que te lo explique rápidamente con números

TIMES = 10000

def do_inserts
    TIMES.times { User.create(:user_id => 1, :sku => 12, :delta => 1) }
end




Benchmark.measure { ActiveRecord::Base.transaction { do_inserts } }

Benchmark.measure {  do_inserts }

Con Active Record

CONN = ActiveRecord::Base.connection
TIMES = 10000

def raw_sql
    TIMES.times { CONN.execute "INSERT INTO `user` 
(`delta`, `updated_at`, `sku`, `user_id`) 
VALUES(1, '2015-11-21 20:21:13', 12, 1)" }
end


Benchmark.measure { ActiveRecord::Base.transaction { raw_sql } }
Benchmark.measure { raw_sql }

Sin Active Record

Insert + value    x veces

CONN = ActiveRecord::Base.connection
TIMES = 10000


def mass_insert
    inserts = []
    TIMES.times do
        inserts.push "(1, '2015-11-21 20:21:13', 12, 1)"
    end
    sql = "INSERT INTO user (`delta`, `updated_at`, `sku`, 
                            `user_id`) 
           VALUES #{inserts.join(", ")}"
    CONN.execute sql
end

Benchmark.measure { mass_insert }

Sin Active Record

un Insert + x veces value


ActiveRecord without transaction:
 14.930000   0.640000  15.570000 ( 18.898352)
ActiveRecord with transaction:
 13.420000   0.310000  13.730000 ( 14.619136)
  1.29x faster than base

Raw SQL without transaction:
  0.920000   0.170000   1.090000 (  3.731032)
  5.07x faster than base
Raw SQL with transaction:
  0.870000   0.150000   1.020000 (  1.648834)
  11.46x faster than base

Only Mass Insert:
  0.000000   0.000000   0.000000 (  0.268634)
  70.35x faster than base

Resultados

Buscamos velocidad

y nos encontramos que esta solución es un 70% más rápida...

¿ qué pasa cuando ejecutamos nuestra solución?

Nos descolocan los resultados

Un problema añadido

  • Siempre a los 100.000 registros, imprevisto!!!
  • Problema: "Hemos perdido conexión con la BBDD"

Nuestro nuevo reto

  • Buscar solución al Problema: "Hemos perdido conexión con la BBDD"

Se nos ocurre

  • Capturo la "exception"
  • Reconecto......y seguimos procesando
  • Problema: "mysql server has gone away"

Vuelve el problema

  • Reconecto......pero no es la solución, ya no inserta más a partir de los 100.000 registros
  • Problema: "mysql server has gone away"

Investigación

  • Sistemas aporta max_allowed_packet y el fichero my.conf de MySQL.
  • Lectura por stackoverflow
  • Vuelta a analizar la situación

No avanzamos

Necesitamos más pruebas

Pruebas

  • Tenemos un campo text
  • Sanitize

Buscábamos velocidad...

de momento lo dejamos en "stand by"

Pruebas

 

  • Tamaño del batch size para los Inserts, se va variando, 50.000, 25.000, 1.000........

Pruebas

 

  • Hacemos truncate del campo text

Sin resultado alguno

Cambio de tercio en la solución

Utilizemos un INSERT-SELECT

Cambio de concepto

  • OrderEvent.find_each(batch_size: 5000)
  • Preparar estrategia por select los order_id
  • Obtenemos los bloques a insertar
  • Podríamos aplicar un insert de los campos y un select con el where del order_id a tratar en cada ciclo
  • Tendríamos que saber los order_id que hemos insertado en la tabla nueva para no repetirlos
  • Ejecutamos un select previo al insert y verificamos sin utilizar AR ya que no hay modelo.
# SOLUCIÓN 1

				
sql = "select order_id from order_events_new_origin where order_id = #{oe.order_id}"
result = con_sql.execute sql
if !inserts.include? oe.order_id
    puts "CREAMOS/MIGRAMOS en NUEVA TABLA Order ID #{oe.order_id} y el CONT #{cont}" 
    sql = "INSERT INTO order_events_new_origin
           (order_id,origin_type,origin_id,kind,data,created_at,updated_at) 

    SELECT order_id,origin_type,origin_id,kind,data,created_at,updated_at 
    FROM order_events where order_id = '#{oe.order_id}'"
    con_sql.execute sql
    cont = cont + 1
    inserts.push oe.order_id
end

Ventajas 

  • Al pasar los campos directamente del Select e insertarlos directamente nos quitamos la problemática del "TEXT" y otros campos null

Resultado 

  • El tiempo de proceso no juega mucho a nuestro favor, funciona y va bien .......

Resultado 

  • ...... no es demasiado rápido para como nos gustaría. Nos hemos alejado de nuestra idea inicial de la velocidad de migración y hemos complicado la solución y necesitamos comprobar demasiado. Ralentiza el todo.

lecciones aprendidas!!!

Ahora nuestro campo TEXT, sin problemas!!!

Una prueba más de concepto

Quitar del proceso Ruby el campo TEXT

Todo fue perfecto y rápido

Es una buena idea...

Tenemos entre manos el campo TEXT...

 MAX_ALLOWED_PACKT  nos quedamos sin conexión en la BBDD

Volvemos al origen de la solución

Experiencia aprendia

  • Seguimos aplicando "Find each" con nuestro batch_size (número mágico) a jugar con el dato primero 5.000
  • Aparece problema conocido de "mysql server has gone away"

Busquemos el número mágico

Experiencia aprendia

 

  • Vamos ajustando hasta llegar a 50
  • Vamos haciendo "Sanitize" no nos sirve los métodos de Rails de sanitize.
  • Sin "transaction" esto ya lo aplicamos en inicio en esta estrategia
 	# SOLUCIÓN 2
	# Vamos a sanitizar los campos antes de llevarlos al insert
	kind = oe.kind.nil? ? '' : oe.kind
	s = oe.data.nil? ? ''.inspect : oe.data.inspect

        # almacen de los values con sus datos ya sanitizados
	if oe.origin_id.nil?
		inserts_values.push "(#{oe.order_id.to_i}, 
                    '#{oe.origin_type}', null, 
                    '#{kind}', #{s}, '#{oe.created_at.to_s(:db)}', 
                    '#{oe.updated_at.to_s(:db)}')"
	else
		inserts_values.push "(#{oe.order_id.to_i}, 
                    '#{oe.origin_type}', #{oe.origin_id.to_i}, 
                    '#{kind}', #{s}, '#{oe.created_at.to_s(:db)}', 
                    '#{oe.updated_at.to_s(:db)}')"
	end


	if inserts_values.size >= size_values
	    sql_insert = "INSERT INTO #{table_destination} #{columns_origin} 
		  				VALUES #{inserts_values.join(", ")}"
	    begin	
		con_sql.execute sql_insert
	    rescue Exception => e
	        puts "No lo guardamos ERROR #{e}" 
	    end
	    inserts_values = []
	end

Claves del proceso

  • Dada la ingesta de datos en los campos "Text" ajustamos el batch_size de AR a 50
  • Ajustamos los insert y values a 50 también ya que si no están ambos acompasados los resultados varian en 15 minutos adicionales en el proceso
  • Eliminamos los index antes de la migración, ralentiza la carga en 15 minutos aprox.

Resultados después de ajustes, "satisfactorios"

Conclusiones

El mejor resultado

  • Equivocarse es una buena idea, tenemos experiencia
  • Trabajar con Código Legacy es una responsabilidad y una mina de aprendizaje y entrenamiento.
  • Creamos la tabla a partir de la original
  • Modificamos el campo de string a entero
  • Eliminamos los index creados al copiar tabla y optimizamos el tiempo y evitamos que el árbol B-TREE del índice no sea dispar y mejor distribución.
  • Procesamos con un insert y 50 values en cada ciclo
  • Sin transaction por ser una única sentencia
  • Creamos los índices nuevamente con los nuevos datos, esto lleva 5 minutos en 2 índices.

Pero sobre todo lo más importante es...

Compartir experiencias...

Tú y yo necesitamos ser mejores desarrolladores

Gracias a todos!!

¿Preguntas? - @carlossanchezp

Cómo migrar una tabla con 8 millones de registros - Codemotion2016

By Carlos Sánchez Pérez

Made with Slides.com

Cómo migrar una tabla con 8 millones de registros - Codemotion2016

@carlossanchezp

  • 1,558

More from Carlos Sánchez Pérez