Importar ficheros de gran tamaño en MySQL

Una consulta recurrente de algunos compañeros es referente a los problemas de importación de bases de datos en entornos MySQL, en especial con el uso de MySQL Workbench. Aunque debo reconocer que Workbench me encanta por su simplicidad, comodidad de uso y diseño, los archivos pesados, y con esto, al menos en mi caso, me refiero a un par de gigas de BBDD, se le atragantan.

Vamos a ver unos cuantos métodos para realizar esta importación de bases de datos.

1º El método tradicional: importar datos.

Desde MySQL, vamos a "server", y seleccionamos "Data import". En la pantalla que aparecerá, seleccionamos "Import from Self-Contained File", y le indicamos dónde está el archivo que queremos importar.

Es MUY importante marcar el "Default Target Schema" al que debe importarse el script, porque no siempre el script apunta sobre dónde se deben ejecutar los cambios, así que es mejor indicarlo, adicionalmente, de forma manual. Mejor prevenir que curar.

2º Método secundario: abrir el script y ejecutarlo a mano.

Desde Workbench, vamos a "File", y "Open SQL Script". Una vez abierto, lo ejecutamos, desde el símbolo del rayo.
Este sistema tiene la ventaja sobre el anterior que si falla, vas a ver exactamente en qué punto, algunas veces fallando al inicio, de manera que tienes la seguridad de que no se ha modificado nada. Desde el método de importación, si se bloquea por tamaño, no sabes exactamente en qué punto, y si debes detener el proceso, paso irremediable muchas veces, dejarás la BBDD inestable, debiendo recurrir a backup para restaurarla.

3º Línea de comandos: lo más fiable.

Abriremos una consola y conectamos con MySQL, y directamente, con la BBDD que nos interese. Para ello, ejecutamos mysql, con los parámetros -u y la BBD.
Ejemplo: mysql -u <username> -p <database>
Pongamos que utilizamos el usuario root y la BBDD "miBBDD", por poner un ejemplo. Entonces, escribiríamos lo siguiente: ysql -u root -p MiBBDD
Seguidamente, pedirá poner el password del usuario root. Lo escribimos y pulsamos Enter.
El prompt de la consola cambiará a MySQL.

Lo siguiente sería ejecutar la BBDD a importar. Supongamos que es el archivo "backup.sql", y lo hemos alojado en C:, por comodidad. Pues pondríamos el siguiente comando:"source C:/backup.sql"

(Siempre es preferible dar la ruta completa del archivo a importar).

Con esto, se habrá realizado la importación sin mayores problemas

Test de rendimiento entre disco básico y seccionado

Vamos a realizar una pequeña prueba de rendimiento utilizando Crystal DiskMark para medir el rendimiento de un disco básico de Windows para luego montar un disco dinámico creado a partir de varios volúmenes individuales, pero todos en el mismo datastore.

Para ello vamos a montar unos cuantos discos de 10 GB que luego unificaremos, y uno de 50 Gb. Abrimos el administrador de discos de Windows, inicializamos los discos (boton derecho->inicializar) y seguidamente, sobre cualquiera de los discos, seleccionamos la opción de "Nuevo volumen seccionado"

La razón de hacer esto es una simple prueba de velocidad. Tal y como indica el wizard en la creación del volumen seccionado, lo que ocnseguimos con esto es que los datos se almacenen en secciones de varios discos, obteniendo un acceso más rápido a los datos en comparación con un volumen simple o distribuido.

Esto, cuando los discos son físicos, basicamente lo que consigues es acceder más rápidamente a los datos, por el simple hecho de haber más cabezales en los discos buscando y leyendo. Pero aqui lo que tenemos es un conjunto de discos virtuales que, aunque no están limitados en IOPs, sí que están limitados por las capacidades físicas del datastore que los aloja.

El último disco agregado, aunque es de 50 Gb, finalmente lo dejaremos en 40 GB formateados para que haya similitud de tamaño disponible con los discos seccionados.
¿Cual será el resultado? ¡Vamos a verlo! Lanzaremos 3 test, sobre el disco C: con el sistema operativo, sobre el disco E:, seccionado, y sobre el F:, que es como el C: pero sin lecturas causadas por el sistema operativo. Por cierto, dejo la explicacion que encuentras directamente en la web de Crystal Disk Mark sobre los distintos test que realiza la aplicacion:

• All : All Test (“Seq Q32T1”, “4K Q8T8”, “4K Q32T1”, “4K Q1T1”)
• Seq Q32T1: Sequential (Block Size=128KiB) Read/Write with multi Queues & Threads
• 4K Q8T8: Random 4KiB Read/Write with multi Queues & Threads
• 4K Q32T1: Random 4KiB Read/Write with multi Queues & Threads
• 4K Q1T1: Random 4KiB Read/Write with multi Queues & Threads

...¡Y vamos alla!

Aqui tenemos el resultado del disco C:. Unos resultados normalitos, para un datastore de discos SAS, con los resultados habituales, como son una alta lectura secuencial, pero con pérdidas en el resto de lecturas.

La sorpresa llega con el disco seccionado. Como se ve rápidamente, el acceso secuencial a disco se pierde, implicando esto que la copia de, por ejemplo, archivos de gran tamaño, va a ir curiosamente más lenta. Pero a cambio el acceso de datos aleatorio se dispara, llegando a casi 20.000 IOPs, que no está nada mal!

Vamos con el último disco de nuestro test, la unidad F: a partir de un volumen simple MBR.

Pues los resultados tampoco distan mucho del disco seccionado! Lo extraño es la caida de lectura-escritura frente al disco C: La posible explicación del rendimiento de C: frente a F: puede ser al trabajo de caché del disco de sistema, que influye directamente en las lecturas/escrituras del test. Quizá si trabajasemos con un archivo de pruebas de tamaño mucho más grande, los resultados serian distintos. El problema es que tambien se falsearían las lecturas de datos no secuenciales.

De manera que creo que podemos entender que los discos seccionados permiten una ganancia de IOPs sobre todo en escritura y posiblemente no tanto en lectura, en base a las distintas funciones de cacheo, paginacion, ballooning memory y demas con los que pueda trabajar VMware y el propio Windows con el fin de mejorar tiempos de respuesta.

Para cualquier duda, consulta aclaracion o corrección, ¡no dudeis en comentar!

Solventar error 1067 en MySQL

Este error es bastante común en MySQL, asi que aquí va un pequeño post cubriendo las distintas soluciones a aplicar. Porque el error es el mismo, pero las causas difieren.

Siempre comienza con que no podemos acceder a la BBDD de SQL, bien con un HeidiSQL o el Workbench (mi preferido). La razón es que la BBDD no ha iniciado, y esto sucede porque el servicio MySQL no ha arrancado. Cuando intentamos arrancarlo nos encontramos con el siguiente error:

No se puede iniciar el servicio MySQL en Equipo local.

Error 1067: El proceso ha terminado de forma inesperada.

Vamos a desglosar causas y soluciones:

- Tras la instalacion hemos movido la BBDD a otra ubicación

Es de suponer que hemos modificado la ruta de la nueva BBDD en el archivo My.ini, ubicado normalmente en c:/ProgramData/MySQL/MySQL Server 5.6 (este ultimo punto del path varia segun la version de MySQL que utiliceis, logicamente).

También en este punto es importante tratar de poner la nueva ruta sin espacios. A veces no toma bien la ruta con espacios.
Eliminamos tambien los archivos ib_logfile0 e ib_logfile1 ubicados en la misma carpeta que my.ini. (por si acaso, haz copia de los ficheros de datos antes. No suele pasar nada, pero, por si pasa...). Tras eliminar los ficheros, reinicia el servicio. Ya deberia ir correctamente.

- Da el error, y hemos realizado la instalacion básica. No hemos tocado absolutamente nada.

Por defecto, Al instalar MySQL se crea el servicio MySQL con la cuenta de "servicios de red" del sistema (network services). Suele ser un problema de permisos sobre la carpeta de la BBDD y la cuenta de servicio asociada. Prueba a dar permisos de lectura y escritura a servicios de red en la ubicacion de la BBDD. Y reinicia el servicio, claro. Si sigue sin ir, crea una cuenta de administrador local en la maquina, y asignas esa cuenta al servicio.

Si a estas alturas el error no está solventado, solo queda una posible causa.

- Los recursos del sistema son escasos.

Esto no suele ser una causa habitual, porque por defecto las instalaciones de MySQL traen en el archivo my.ini una cantidad de memoria asignada para la BBDD de chiste. Para mejorar el rendimiento, normalmente se efectua un ajuste que en las instrucciones indica debe ser de al menos el 80% de la memoria de la maquina. Yo prefiero ajustarlo al 50%, pero son preferencias.

Si esta cifra se ha ajustado y luego se han reducido las características de hardware de la máquina (vamos, lo normal si estamos jugando con VM´s), podemos encontrarnos que los recursos que queremos asignar para la BBDD superan lo que el sistema puede dar al programa. Y entonces, sale el error 1067 igualmente. Lo ajustamos como procede, e iniciamos de nuevo el servicio.

Si no funciona ninguna de estas soluciones, ve desinstalando y limpiando todo rastro de MySQL en ese equipo, incluido el registro, y reinstalando.

¿Dudas? Deja un comentario, lo mismo puedo ayudar en algo...