Painkiller:¿cómo llevarse 8 Millones de una tabla 

 a otra sin sufrir, en Ruby  y cambiando types?

Codetails The Cocktail @carlossanchezp

Objetivo Codetails

Objetivos

• ¿cómo detectar problemas?
• ¿cómo resolverlos?
• Ganar tiempo si te lo encuentras

El problema

Síntomas

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

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

Descubrir del problema

• Cuidado!! con la obviedad, juega mala pasada, miramos pero no vemos
• Con un "desc tabla" mysql
• Un índice mal entendido como string
• En Rails fk_id lo interpreta como integer
• Full scan de la tabla

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

Herramienta de detección full scan

Explain extended

• Posible Keys: lista de índices o NULL si no hay para buscar
• Chosen Keys: nombre del índice a utilizar después de verificar todos los posibles y haber seleccionado el mejor
• Rows scaned: el esfuerzo que necesita para cumplir la consulta

Explain extended

• EXTRA column nos aporta más información
• Using WHERE - FILESORT - TEMPORARY TABLE - INDEX

FILESORT: WHERE ID = 123 ORDER BY NAME

Using INDEX: COUNT SUM AVG

Explain extended

mysql> explain extended select * from users order by idusers desc limit 20 ;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered  | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------+-------+
|  1 | SIMPLE      | users | index | NULL          | PRIMARY | 4       | NULL |   20 | 396545.00 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------+-------+

 Filtered = 396.545 and total rows = 79.309

 The explain shows rows = 20 

 Filtered calculate = (Total Rows Table / 20)*100 = 396.545

¿Qué hace especial este codetails?

Importante

• Solución en equipo
• Todos hemos aportado

Trabajar en la Solución

Solución MIGRATE

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

Ya tenemos la solución....... ¿de verdad lo crees así?

Espera infinita

Error de concepto

• No tomar datos de partida
• Envergadura del problema
• Serio problema de deploy de horas

Todo lo probamos y medimos en local, después en muchos casos en Staging también

Analí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
• Existen varios índices sobre la tabla

Solución Tarea

• Utilizar AR para las lecturas en batch
• Los insert de los datos en array
• No utilizar AR para inserts
• Un insert con X values a insertar mejor que X inserts con values
• Sin incorporar "transaction" no aporta en un único insert con múltiples values por ciclo de batch

Primera decadencia

Problema

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

Solución

• Capturo la "exception"
• 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

Pruebas

• Tenemos un campo text
• Sanitize
• Tamaño del batch size para los Inserts, se va variando, 50.000, 25.000, 1.000........
• Hacemos truncate del campo text sin resultado

Fracaso

Cambio de tercio en la solución

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.

# SOLICIÓN 1
con_sql.execute "START TRANSACTION" if cont == 0
				
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 pero no es demasiado rápido para como nos gustaría.

Volvemos al origen de la solución

Experiencia aprendia

• Seguimos aplicando "Find each" con nuestro batch_size a jugar con el dato primero 5.000
• Aparece problema conocido de "mysql server has gone away"
• 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

Resultados

• 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

• 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.

Gracias a todos!!

¿Preguntas?