{"id":541,"date":"2014-10-09T10:22:12","date_gmt":"2014-10-09T08:22:12","guid":{"rendered":"http:\/\/dbahire.com\/?p=541"},"modified":"2022-03-10T12:49:51","modified_gmt":"2022-03-10T11:49:51","slug":"probando-la-manera-mas-rapida-de-importar-una-tabla-en-mysql-y-unos-resultados-interesantes-del-rendimiento-de-5-7","status":"publish","type":"post","link":"https:\/\/jynus.com\/dbahire\/probando-la-manera-mas-rapida-de-importar-una-tabla-en-mysql-y-unos-resultados-interesantes-del-rendimiento-de-5-7\/","title":{"rendered":"Probando la manera m\u00e1s r\u00e1pida de importar una tabla en MySQL (y unos resultados interesantes del rendimiento de 5.7)"},"content":{"rendered":"

Como mencion\u00e9 en mi anterior entrada, donde comparaba las opciones de configuraci\u00f3n por defecto de 5.6 y 5.7<\/a>, he estado haciendo algunos tests para un tipo particular de carga en varias versiones de MySQL. Lo que he estado comprobando es las diferentes maneras de cargar un archivo CSV (el mismo fichero que us\u00e9 para comprobar las herramientas de compresi\u00f3n<\/a>) en MySQL. Aquellos administradores de bases de datos y programadores MySQL con experiencia probablemente ya conozc\u00e1is la respuesta, as\u00ed que pod\u00e9is saltar a los resultados de 5.7 respecto de 5.6. Sin embargo, la primera parte de este post est\u00e1 dedicado a los desarrolladores\/aquellos que dan sus primeros pasos con MySQL que desean saber la respesta a la pregunta del t\u00edtulo, paso a paso. Debo decir que yo tambi\u00e9n he aprendido algo, ya que sobrestim\u00e9 e infravalor\u00e9 algunos efectos de ciertas opciones de configuraci\u00f3n para este tipo de carga.<\/p>\n

Aviso: no pretendo realizar benchmarks formales, la mayor\u00eda de los resultados obtenidos aqu\u00ed se han creado ejecut\u00e1ndose un par de veces, y muchos de ellos con la configuraci\u00f3n por defecto. Esto ha sido por dise\u00f1o, ya que intento mostrar alguna de las “malas pr\u00e1cticas”<\/strong> para la gente que acaba de empezar a trabajar con MySQL, y lo que deber\u00edan evitar hacer. Tan s\u00f3lo la comparativa entre 5.6 y 5.7 me ha llamado la antenci\u00f3n. Aviso adicional<\/strong>: No me llamo a m\u00ed mismo programador, y mucho menos un programador de Python, as\u00ed que me disculpo por adelantado por mi c\u00f3digo- despu\u00e9s de todo, esto va de MySQL<\/em>. El enlace de descarga de los scripts est\u00e1 al final del post.<\/p>\n

Las reglas<\/h3>\n

Empiezo con un archivo CSV<\/a> (recordad que realmente es un archivo de valores separado por comas, TSV<\/em>) que tiene 3.700.635.579 bytes de tama\u00f1o, y 46.741.126 filas y tiene la siguiente pinta:<\/p>\n

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd\n171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250    j3m\n171775  38.6067166      -0.0498342      2       2008-01-19 10:23:21     666916  9250    j3m\n171776  38.6028122      -0.0497136      5       2012-08-25 00:26:40     12850816        472193  rubensd\n171933  40.4200658      -3.7016652      6       2011-11-29 05:37:42     9984625 33103   sergionaranja \n<\/pre>\n
Quiero cargarlo en la siguiente tabla, con la estructura que muestro a continuaci\u00f3n:<\/p>\n
CREATE TABLE `nodes` (\n  `id` bigint PRIMARY KEY,\n  `lat` decimal(9,7),\n  `lon` decimal(10,7),\n  `version` int,\n  `timestamp` timestamp,\n  `changeset` bigint,\n  `uid` bigint,\n  `user` varchar(255)\n);\n<\/pre>\n
El tiempo de importaci\u00f3n termina en el momento que la tabla es crash safe<\/em> (resistente a fallos) (aunque quede E\/S pendiente). Eso quiere decir que para InnoDB, el \u00faltimo COMMIT<\/code> realizado tiene que haberse hecho de manera satisfactoria y flush_log_at_trx_commit<\/code> debe valer 1, de manera que aunque queden operaciones de escritura por hacerse, es completamente consistente con disco. Para MyISAM, eso quiere decir que fuerzo la ejecuci\u00f3n de un FLUSH TABLES<\/code> antes de terminar el test. Estos, por supuesto, no son equivalentes, pero al menos es una manera de asegurarse de que todo est\u00e1 m\u00e1s o menos sincronizado con disco. \u00c9sta es la parte final de todos mis scripts:<\/p>\n
# finishing\nif (options.engine == 'MyISAM'):\n    cursor.execute('FLUSH TABLES')\nelse:\n    db.commit()\n\ncursor.close()\ndb.close()\n<\/pre>\n
Para el hardware y sistema operativo, comprobad los detalles de m\u00e1quina de este post antiguo<\/a>– us\u00e9 el mismo entorno que el mencionado aqu\u00ed, con la excepci\u00f3n de usar CentOS7 en vez de 6.5.<\/p>\n
Empezando con un m\u00e9todo muy ingenuo<\/h3>\n
Digamos que soy un desarrollador al que le han asignado cargar de manera regular un archivo en MySQL, \u00bfc\u00f3mo lo har\u00eda? Probablemente estar\u00eda tentado de usar una biblioteca de parseo de CSV, el conector de MySQL, y los unir\u00eda a ambos juntos en un bucle. \u00bfEso funcionar\u00eda, no? La parte principal del c\u00f3digo ser\u00eda algo como esto (load_data_01.py<\/code>):<\/p>\n
# import\ninsert_node = \"INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)\"\n\nwith open(CSV_FILE, 'rb') as csv_file:\n    csv_reader = csv.reader(csv_file, delimiter='\\t')\n    for node in csv_reader:\n        cursor.execute(insert_node, node)\n<\/pre>\n
Como estoy interpretando el papel de un desarrollador sin experiencia, tambi\u00e9n usar\u00eda la configuraci\u00f3n por defecto. Veamos qu\u00e9 resultados obtenemos (de nuevo, esta es la raz\u00f3n por la cual llamo a esto “pruebas”, y no benchmarks). Menos es mejor:
\n\"load_data_01\"<\/a><\/p>\n\n\n\n\n\n
MySQL Version<\/em><\/td>\n5.1.72<\/td>\n5.5.39<\/td>\n5.6.20<\/td>\n5.7.4<\/td>\n<\/tr>\n<\/thead>\n
Load time (seconds)<\/em><\/td>\n4708.594<\/td>\n6274.304<\/td>\n6499.033<\/td>\n6939.722<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Guau, \u00bf5.1 es un 50% m\u00e1s r\u00e1pido que el resto de versiones? Incorrecto<\/strong>, recordad que 5.5 fue la primera versi\u00f3n en introducir InnoDB como el motor por defecto, e InnoDB tiene una sobrecarga debido a las transacciones, y normalmente no tiene una muy buena configuraci\u00f3n por defecto (al contrario que MyISAM, que estan simple que las opciones por defecto pueden funcionar en muchos casos). Normalicemos los resultados por motor de almacenamiento:
\n\"load_data_01_by_engine\"<\/a><\/p>\n\n\n\n\n\n\n
MySQL Version<\/em><\/td>\n5.1.72<\/td>\n5.5.39<\/td>\n5.6.20<\/td>\n5.7.4<\/td>\n<\/tr>\n<\/thead>\n
MyISAM<\/em><\/td>\n4708.594<\/td>\n5010.655<\/td>\n5149.791<\/td>\n5365.005<\/td>\n<\/tr>\n
InnoDB<\/em><\/td>\n6238.503<\/td>\n6274.304<\/td>\n6499.033<\/td>\n6939.722<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Esto ya parece m\u00e1s razonable, \u00bfno? Sin embargo, en este caso, parece haber una ligero empeoramiento en el rendimiento de un s\u00f3lo hilo a la vez que las versiones van progresando, especialemnte en MySQL 5.7. Por supuesto, es pronto para sacar conclusiones, porque este m\u00e9todo de carga de un archivo CSV, fila a fila, es uno de los m\u00e1s lentos, y adem\u00e1s estamos usando unas opciones de configuraci\u00f3n muy pobres (las que vienen por defecto) -las cuales var\u00edan de versi\u00f3n a versi\u00f3n- y que no deber\u00edan usarse para llegar a ninguna conclusi\u00f3n<\/strong>.<\/p>\n
Lo que podemos decir es que MyISAM parece funcionar mejor para este escenario muy espec\u00edfico por las razones que comentaba antes, pero a\u00fan as\u00ed tardar\u00edamos de 1 a 2 horas en cargar un archivo tan sencillo.<\/p>\n
Otro m\u00e9todo incluso m\u00e1s ingenuo<\/h3>\n
La siguiente pregunta no es: \u00bfpodemos hacerlo mejor? sino \u00bfpodemos hacerlo incluso m\u00e1s lento? Un texto en particular me llam\u00f3 la atenci\u00f3n mientras miraba la documentaci\u00f3n del conector de MySQL:<\/p>\n
Since by default Connector\/Python does not autocommit, it is important to call this method after every transaction that modifies data for tables that use transactional storage engines.<\/p><\/blockquote>\n
que es algo as\u00ed como:<\/p>\n
Debido a que por defecto Connector\/Python no realiza autocommit, es importante llamar a este m\u00e9todo despu\u00e9s de cualquier transacci\u00f3n que modifique datos en tablas de motores transaccionales.<\/p><\/blockquote>\n
-de la documentaci\u00f3n de connector\/python<\/a><\/p>\n
Pens\u00e9- \u00a1ah!, entonces podemos reducir el tiempo de importaci\u00f3n realizando commit<\/em> para cada fila de la base de datos, una a una, \u00bfno? Despu\u00e9s de todo, estamos insertando en la tabla en una \u00fanica transacci\u00f3n enorme. \u00a1Seguro que crear un enorme n\u00famero de transacciones ser\u00e1 mejor! \ud83d\ude42 Este es el c\u00f3digo ligeramente modificado (load_data_02.py<\/code>):<\/p>\n
insert_node = \"INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)\"\n\nwith open('\/tmp\/nodes.csv', 'rb') as csv_file:\n    csv_reader = csv.reader(csv_file, delimiter='\\t')\n    for node in csv_reader:\n        cursor.execute(insert_node, node)\n        db.commit()\n<\/pre>\n
Y en este caso no tengo ni siquiera un bonito gr\u00e1fico que ense\u00f1aros, porque despu\u00e9s de 2 horas, 19 minutos y 39.405 segundos, cancel\u00e9 la importaci\u00f3n porque s\u00f3lo se hab\u00edan insertado 111533 nodos<\/strong> en MySQL 5.1.72 con InnoDB con la configuraci\u00f3n por defecto (innodb_flush_log_at_trx_commit = 1<\/code>). Obviamente, millones de fsyinc<\/code>s no har\u00e1n que la carga sea m\u00e1s r\u00e1pida, considerad esto como una lecci\u00f3n aprendida.<\/p>\n
Haciendo progresos: multi-inserts<\/h3>\n
El siguiente paso que quer\u00eda probar es c\u00f3mo de efectivo es agrupar consultas en inserciones m\u00faltiples. Este m\u00e9todo lo usa mysqldump<\/code>, y supuesta mente minimiza la carga extra de SQL por tener que gestionar cada consulta por separado (parseo, comprobaci\u00f3n de permisos, plan de la consulta, etc.). \u00c9ste es el c\u00f3digo principal (load_data_03.py<\/code>):<\/p>\n
concurrent_insertions = 100\n[...]\nwith open(CSV_FILE, 'rb') as csv_file:\n    csv_reader = csv.reader(csv_file, delimiter='\\t')\n    i = 0\n    node_list = []\n    for node in csv_reader:\n        i += 1\n\tnode_list += node\n        if (i == concurrent_insertions):\n            cursor.execute(insert_node, node_list)\n            i = 0\n            node_list = []\n    csv_file.close()\n\n# insert the reminder nodes\nif i > 0:\n    insert_node = \"INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)\"\n    for j in xrange(i - 1):\n        insert_node += ', (%s, %s, %s, %s, %s, %s, %s, %s)'\n    cursor.execute(insert_node, node_list)\n<\/pre>\n
Lo probamos con, por ejemplo, 100 filas insertadas en cada consulta. \u00bfCu\u00e1les son los resultados? Menos es mejor:
\n\"load_data_03.py<\/a><\/p>\n\n\n\n\n\n\n
MySQL Version<\/em><\/td>\n5.1.72<\/td>\n5.5.39<\/td>\n5.6.20<\/td>\n5.7.4<\/td>\n<\/tr>\n<\/thead>\n
MyISAM<\/em><\/td>\n1794.693<\/td>\n1822.081<\/td>\n1861.341<\/td>\n1888.283<\/td>\n<\/tr>\n
InnoDB<\/em><\/td>\n3645.454<\/td>\n3455.800<\/td>\n2849.299<\/td>\n3032.496<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Con este m\u00e9todo observamos una mejora en el tiempo de importaci\u00f3n de 262-284% respecto del tiempo original para MyISAM y de 171-229% respecto del tiempo original de InnoDB<\/strong>. Recordad que este m\u00e9todo no escalar\u00e1 indefinidamente, ya que encontraremos un l\u00edmite en el tama\u00f1o de paquete si intentamos insertar demasiadas filas a la vez. Sin embargo, se trata de una clara ventaja sobre el insertado fila a fila.<\/p>\n
Los tiempos para MyISAM son esencialmente los mismos entre versiones, mientras que InnoDB muestra una mejora a lo largo del tiempo (que podr\u00edan deberse a los cambios en c\u00f3digo y la optimizaci\u00f3n, pero tambi\u00e9n a la configuraci\u00f3n por defecto del tama\u00f1o del log de transacciones), excepto de nuevo entre 5.6 y 5.7.<\/p>\n
El m\u00e9todo correcto para importar datos: Load Data<\/h3>\n
Si se tiene un m\u00ednimo de experiencia con MySQL, conocer\u00e9is la existencia de una palabra clave especializada para importaciones, y \u00e9sta es LOAD DATA<\/code>. Veamos como quedar\u00eda el c\u00f3digo usando esta opci\u00f3n (load_data_04.py<\/code>):<\/p>\n
# data import\nload_data = \"LOAD DATA INFILE '\" + CSV_FILE + \"' INTO TABLE nodes\"\n\ncursor.execute(load_data)\n<\/pre>\n
\u00bfSimple, verdad? Con esto estamos minimizando la sobrecarga de SQL, y ejecutando el bucle en c\u00f3digo C compilado de MySQL. Echemos un vistazo a los resultados (menos es mejor):
\n\"load_data_04.py<\/a><\/p>\n\n\n\n\n\n\n\n
MySQL Version<\/em><\/td>\n5.1.72<\/td>\n5.5.39<\/td>\n5.6.20<\/td>\n5.7.4<\/td>\n<\/tr>\n<\/thead>\n
MyISAM<\/em><\/td>\n141.414<\/td>\n149.612<\/td>\n155.181<\/td>\n166.836<\/td>\n<\/tr>\n<\/tbody>\n
InnoDB<\/em><\/td>\n2091.617<\/td>\n1890.972<\/td>\n920.615<\/td>\n1041.702<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
En este caso, MyISAM tiene una mejora dram\u00e1tica –LOAD DATA<\/code> convierte la importaci\u00f3n en 12 veces m\u00e1s r\u00e1pida. InnoDB, de nuevo todav\u00eda con los par\u00e1metros por defecto mejora en 3 veces, y m\u00e1s significativamente en las versiones m\u00e1s recientes (5.6, 5.7) que en las anteriores (5.1, 5.5).<\/strong> Mi predicci\u00f3n es que esto tiene mucho m\u00e1s que ver con la diferente configuraci\u00f3n de los archivos de log que con cambios en el c\u00f3digo.<\/p>\n
Intentado mejorar Load Data para MyISAM<\/h3>\n
\u00bfPodemos mejorar los tiempos de carga para MyISAM? Hay dos cosas que podemos hacer -aumentar el tama\u00f1o de key_cache_size<\/code> y deshabilitar Performance Schema para 5.6 y 5.7. Establec\u00ed el tama\u00f1o de la cach\u00e9 de \u00edndices (key_cache_size<\/code>) en 600M<\/code> (intentado que cupiera la clave primaria en memoria) y cambi\u00e9 performance_schema = 0<\/code>, probando las 3 combinaciones restantes. Menos es mejor:
\n\"load_data_04.py<\/a><\/p>\n\n\n\n\n\n\n\n\n
MySQL Version<\/em><\/td>\n5.1.72<\/td>\n5.5.39<\/td>\n5.6.20<\/td>\n5.7.4<\/td>\n<\/tr>\n<\/thead>\n
default<\/em><\/td>\n141.414<\/td>\n149.612<\/td>\n155.181<\/td>\n166.836<\/td>\n<\/tr>\n
key_buffer_size=600M<\/em><\/td>\n136.649<\/td>\n170.622<\/td>\n182.698<\/td>\n191.228<\/td>\n<\/tr>\n
key_buffer_size=600M, P_S = OFF<\/em><\/td>\n133.967<\/td>\n170.677<\/td>\n177.724<\/td>\n186.171<\/td>\n<\/tr>\n
P_S = OFF<\/em><\/td>\n142.592<\/td>\n145.679<\/td>\n150.684<\/td>\n159.702<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Hay varias cosas que destacar aqu\u00ed:<\/p>\n