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?

Codetails en TCK - 2016

By Carlos Sánchez Pérez

Codetails en TCK - 2016

@carlossanchezp

  • 346
Loading comments...

More from Carlos Sánchez Pérez