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

En esta charla quiero contar un problema real en producción que nos ha ocurrido en MySQL. Me encontré con la problemática de tener que cambiar un tipo de “string” a “integer” de una columna en una tabla de más de 8 Millones de registros. Te cuento mi experiencia y las diferentes opciones en las que hemos estado trabajando a lo largo de todo el proceso.

Transcript

1. Codemotion 2016 @carlossanchezp MySQL: Cómo migrar una tabla con 8 millones de registros 1
2. Carlos Sánchez Pérez Pragmatic developer, Ruby/Rails choice at the moment, Learning Scala, Elixir/Phoenix & DDD. Currently working as Web Dev at . Let's make it happen Blog (only in spanish): Twitter: @The_Cocktail carlossanchezperez.wordpress.com @carlossanchezp 2
3. Proyecto REAL 3
4. 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 4
5. Arquitectura 5
7. El problema 7
8. Síntomas Un select 10M por índice es lento Full scan de la tabla 8
9. Si ocurre esto es muy extraño, no es normal!! 9
10. Necesitamos saber ¿qué está pasando? 10
11. 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 11
12. Con un "desc tabla" mysql Consola MySQL 12
13. Order tiene una relación con Order Event Consola Rails AR: order.order_event 13
14. 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 14
15. hasta que hacemos: select * from order_events where order_id = 1234567889 y ahora hacemos select * from order_events where order_id = "1234567889" 15
16. t.string "order_id", :null => false 16
17. ¿por qué es ese índice string nuestro problema? 17
18. el problema En Rails fk_id lo interpreta como integer AR: order.order_event select * from order_events where order_id = 1234567889 18
19. 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 19
20. Es una deuda Es una deuda Es una deuda técnica que tengo técnica que tengo técnica que tengo que asumir. El que asumir. El que asumir. El problema está ya problema está ya problema está ya detectado. detectado. 20
21. la solución Cambiar el índice no evolucionado como string a integer 21
23. Trabajar en la Solución 23
24. Migrate de Rails 24
25. ¿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 25
26. Ya tenemos la solución....... 26
27. pero....... en un contexto de tiempo real y dimensiones de 8M 27
28. Espera infinita 28
29. En medio de un deploy a PRO 29
30. HELP!! 30
31. 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 31
32. No se puede comprar!!! 32
33. Buscar una solución pragmática 33
34. 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 34
35. Microservicio en Ruby 35
36. Busco velocidad en la migración de datos 36
37. Tengo un modelo Active Record aprovechar el modelo en lectura 37
38. 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 38
39. 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)}')" 39
40. 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 40
41. Solución con Ruby Un insert con X values a insertar mejor que X inserts con values 41
42. Solución con Ruby Sin incorporar "transaction" no aporta en un único insert con múltiples values por ciclo de batch 42
43. ¿por qué es mejor no utilizar Active Record en INSERT masivos? 43
44. Déjame que te lo explique rápidamente con números 44
45. 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 45
46. 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 46
47. 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 47
48. 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 48
49. Buscamos velocidad y nos encontramos que esta solución es un 70% más rápida... 49
50. ¿ qué pasa cuando ejecutamos nuestra solución? 50
51. Nos descolocan los resultados 51
52. Un problema añadido Siempre a los 100.000 registros, imprevisto!!! Problema: "Hemos perdido conexión con la BBDD" 52
53. Nuestro nuevo reto Buscar solución al Problema: "Hemos perdido conexión con la BBDD" 53
54. Se nos ocurre Capturo la "exception" Reconecto......y seguimos procesando Problema: "mysql server has gone away" 54
55. 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" 55
56. Investigación Sistemas aporta max_allowed_packet y el fichero my.conf de MySQL. Lectura por stackoverflow Vuelta a analizar la situación 56
58. No avanzamos 58
59. Necesitamos más pruebas 59
60. Pruebas Tenemos un campo text Sanitize 60
61. Buscábamos velocidad... de momento lo dejamos en "stand by" 61
62. Pruebas Tamaño del batch size para los Inserts, se va variando, 50.000, 25.000, 1.000........ 62
63. Pruebas Hacemos truncate del campo text 63
64. Sin resultado alguno 64
66. Cambio de tercio en la solución 66
67. Utilizemos un INSERT-SELECT 67
68. 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. 68
69. # 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 69
70. Ventajas Al pasar los campos directamente del Select e insertarlos directamente nos quitamos la problemática del "TEXT" y otros campos null 70
71. Resultado El tiempo de proceso no juega mucho a nuestro favor, funciona y va bien ....... 71
72. 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. 72
74. lecciones aprendidas!!! 74
75. Ahora nuestro campo TEXT, sin problemas!!! 75
76. Una prueba más de concepto 76
77. Quitar del proceso Ruby el campo TEXT 77
78. Todo fue perfecto y rápido 78
79. Es una buena idea... 79
80. Tenemos entre manos el campo TEXT... 80
81. MAX_ALLOWED_PACKT nos quedamos sin conexión en la BBDD 81
82. Volvemos al origen de la solución 82
83. 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" 83
84. Busquemos el número mágico 84
85. 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 85
86. # 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 86
87. 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. 87
88. Resultados después de ajustes, "satisfactorios" 88
89. Conclusiones 89
90. 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. 90
91. Pero sobre todo lo más importante es... 91
92. Compartir experiencias... 92
93. Tú y yo necesitamos ser mejores desarrolladores 93
94. Gracias a todos!! ¿Preguntas? - @carlossanchezp 94