[SOLUCIONADO] Mejorar rendimiento MySQL

[JairLizcano]

Me gustaría saber qué alternativas hay para mejorar el rendimiento de MySQL en función de la memoria RAM y recursos que utilice para los procesos y no en orden a las consultas y demás.

Debo importar una hoja de Excel con aprox. 50.000~70.000 filas, tomar dicha información y llenar las respectivas tablas, evidentemente debo tener parámetros de validación a la base de datos, múltiples insert y algunos update. Todo lo anterior ya ha sido debidamente actualizado pero el proceso suele tardar más de 40 minutos y busco reducir ese tiempo (no hay limitantes en los recursos del servidor).

Desde ya, agradezco sus comentarios.

[blanxiii]

primero, entiendo que haces insert uno a uno porque no intentas hacer un insert masivo? cada 50k no.. pero puedes hacer cada 1k insertar así solo se lanzan 50k por ejemplo.


Eje:

INSERT INTO table (campo1, campo2, campo3) VALUES (1,2,3),(4,5,6)

es decir acumular 1000 de (1,2,3),(4,5,6) me explico?

Si haces selects a la vez que insertas eso relantiza ya que un insert bloquea la tabla.

En cuanto a optimización un MySQL Server se caracteriza por tener RAM y HD solido

tienes el my.cnf original con los mismos valores? Si me dices tu servidor y que tablas usas InnoDB o MyIsam puedo mirar de hacerte una configuración.


Yo creo que con esas dos cosas notarías montón la performance.

También depende de lo que estas insertando, podrías poner el schema de tu base de datos y un valor para que me haga una idea?

Que cargas en un array del csv 50k?


Yo haría leo del archivo mientras mi array sea inferior a 1000 cuando es 1.000 inserto en base de datos, elimino esas 1000 del array y sigo leyendo 1.000 más.

si comentas el insert cuanto te tarda en leer el csv? Es para descartar ya que creo que en leer un csv de esas cantidades ya tardará lo suyo.

Añado:

Trabajando con una base de datos de 136GB usamos 24GB de RAM y dos HD sólidos y la performence se nota un monton, eso si tienes que saber tocar tu my.cnf pasame especificaciones de la maquina en privado y te lo miro. Importante saber el % de InnoDB y MyISAM que tienes saca un excel y analiza las grandes...

SELECT table_schema, table_name, engine FROM INFORMATION_SCHEMA.TABLES;

[JairLizcano]

Buena tarde blanxii y gracias por tu atención.

Ya había pensado en registrar los datos en una sola consulta, pero necesitaba los id para relacionar una de las dos tablas restantes, que ahora bien, mientras escribo el problema creo que encontré una solución... intentaré revisar ello ahora mismo. Con respecto a lo otro:

1. Sí tengo el my.ini con los valos por defecto.
2. InnoDB en todas las tablas.

En este momento estoy haciendo pruebas sobre un equipo i5 (2.5 GHz) de 4 GB de RAM y 750 GB de disco duro.

Hace poco generé un log con un tabla de Excel completa (42.933 registros en el documento) y el tiempo de carga, proceso (recorrer filas y columnas) y registro en las respectivas tablas de la DB que tal vez te pueda asustar un tanto como a mí ahora:

Tiempo de tratamiento del archivo y consultas MySQL: 5543.64982509613 s

[gnzsoloyo]

Normalmente acudir al Manual de Referencia suele ser la mejor opción para ver cómo se optimiza el rendimiento de MySQL.
¿Lo miraste?

Chapter 8. Optimization
8.9.2. Tuning Server Parameters
8.9.4. How MySQL Uses Memory
8.9.5. Disk Issues
8.9.7. Enabling Large Page Support

Muchos de los parámetros los puedes administrar directamente por medio de algunas interfases, entre las que te recomiendo usar MySQL Workbench y las MySQL Utilities. Las puedes descargar la la pagina oficial.
Tendrás que acostumbrarte a leer inglés técnico, porque la mayor parte de la bibliografía útil y necesaria la encontrarás en ese idioma.

[blanxiii]

Lo primero que haría es.


Ejectura el archivo y mira que proceso es el que consume más (en windows puedes ver el rendimiento) que es lo que tienes a tope acceso al disco o memoria RAM?

¿Podrías darnos el valor de tu memory_limit de php? en tu caso como el PHP tiene que realziar una gran carga probaría a meterlo a 1GB ( llamadme loco!!! ), sin duda lo que haría es ejecuto una vez con 1GB, ¿como va? mejora el rendimiento si o no.

Luego el mysql... yo te recomiendo (ojo, es cosa tuya acabar de pulir-lo)

[mysqld_safe]
lower_case_table_names = 1 #Importante ya que en linux tenemos problemas de key sensitive.

[mysqld]
skip-external-locking
skip-name-resolve
lower_case_table_names = 1

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 8
myisam-recover = BACKUP

query_cache_limit = 1M
query_cache_size = 512M
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size = 3GB
table_open_cache = 1280
join_buffer_size = 6MB
max_heap_table_size = 512MB

tmp_table_size = 124MB
query_cache_min_res_unit = 1K
max_connections = 10 #Ojo no se si tienes muchas conexiones si aumentas esto se puede ver afectado el rendimiento
wait_timeout = 28800

Luego ya es ir puliendo estos valores, puedes mirar por internet scripts de performance que te analizan tus logs y te dicen para tu uso de base de datos deberías aumentar tal valor a x... (yo uso el primero de estos http://www.askapache.com/mysql/performance-tuning-mysql.html) son el linux...


Aún así, sin olvidar lo que comentabas como puedes mejorar esa maquina, te recomiendo que si puedes subirlo a 8GB mínimo lo vas a notar y bastante... si quieres lo optimo para los valores que me comentas con una maquina de 16GB estarías bien, a todo esto le metes un solido y ¡Boom! piensa una cosa mi experiencia en MySQL (y no es poca) es que lo que más necesitas es RAM y acceso a disco.

Por último destacar que si todo esto lo puedes meter a un linux la performance se nota.. una query que usaba que cargaba un servidor en windows misma maquina que linux (copias exactas mismo hardware) windows tardaba 13 segundos linux 3-4 segundos. La distribución te recomiendo centos sin entorno grafico servidor 24 horas metiendo caña... son unos monstruos...


Suerte!

[JairLizcano]

Gracias por su atención, aún así... edité el respectivo archivo de configuración de MySQL y no tuve una mejora considerable.

Así que opté por cambiar el motor de almacenamiento (probando con MyISAM) y es la hora que no dejo de sorprenderme por el rendimiento, pasé de un tiempo con una muestra de 1000 filas que con el motor InnoDB tomaba ~94.01105809211731 s y ahora con los cambios a MyISAM bajaron a 5.649657964706421 s.

Evidentemente reconozco las "habilidades" de las que carece MyISAM en cuanto a atomicidad, pero... haciendo una revisión de la integridad de los datos en BD no he encontrado anomalía alguna.

Estoy debatiendo qué tan viable sería implementar este motor en mi proyecto... y muy seriamente. Agradezco cualquier opinión respecto a esto, también.