Alta Carga en Mysql – Configuración
Hace tiempo quería hacer este tema, no va a ser un tema de configuración / optimización de mysql, hay muchos por ahí, simplemente voy a contar algunas vivencias que he tenido al respecto.
Algunos puntos básicos si voy a comentar como lo es: ¿ Mysql – Configuración por defecto un pecado ?
Esto puede ser un dilema muy grande en mysql, yo no soy un experto ni conozco al detalle toda la configuración simplemente por que es muy grande, aunque si he configurado servidores mysql bastante pesados pero no me convierte en un experto, simplemente soy una persona con cabeza y que a leído la documentación, este punto es sumamente importante, leer la documentación, si no la has leído no pienses que puedes configurar un servidor de bases de datos solo leyendo lo que hay en blogs o copiando my.cnfs a lo loco.
Esto que acaba de escribir es el punto fuerte de mis vivencias, he configurado muchos servidores he visto como funcionan muchos de ellos, no diré cuantos exactamente por que no lo se, pero bien he podido revisar 1000 o 2000 o los que sean, tampoco te convierte en mejor sysadmin haber estado en 10 000 servidores y haber configurado cientas de veces un mysql la cantidad no hace la calidad, puede que hayas configurado 1000 servidores y los hayas configurado mal todos ( cometiendo los mismos errores ) eso ya te da de que pensar, por eso siempre se trata de mejorar, sin duda alguna con cada servidor se aprender algo nuevo aunque hayas configurado muchos servidores siempre aprenderás algo nuevo y algo que hacías bien puedes hacerlo mejor y algo que creías que hacías bien puede que lo estés haciendo mal y de eso te das cuentas cuando haces pruebas, configuras, reconfiguras relees la documentación.
Ahora bien, volviendo al tema de mysql y su configuración por defecto desde mi punto de visto y lo que he visto en muchos servidores es que la configuración por defecto casi no hay que tocarla esta diseñada para trabajar bien en la mayoría de los casos, hablemos de wordpress, joomla, drupal etc gestores de contenido, blogs etc con tráfico regular, esto sin duda es lo que más abunda en internet ,aparte de esto pensemos en aplicaciones hechas a medida.
Primero los gestores de contenido, normalmente aplicaciones como wordpress con tantos desarrolladores están muy bien optimizadas, los que fallan son debido a que has metido plugins o themes de un tio que los hizo los probo sin casi tráfico y los saco a producción y tu con tu blog con 1000 visitas online has puesto el plugin y a petado… si eso pasa aunque esto es otro tema, volvamos al mysql y su configuración.
– He configurado bastantes servidores con gente que tiene 1000 / 2000 visitas online en wordpress en los cuales la configuración de mysql no se a tocado apenas y este funciona perfectamente bien, dirán por que ? la respuesta es sencilla, tener la configuración de mysql por defecto no quiere decir que no tengamos nada configurado, el mysql ya viene configurado para dar un uso normal cada valor opción de mysql ya viene configurada ( si no esta desactivada por defecto ) y en muchos casos no tiene sentido alguno cambiarla simplemente por que así funciona bien, al menos claro que tengamos un tráfico descomunal y ya tengamos mysql replication debido a la cantidad de consultas etc etc el otro motivo por el que necesitemos cambiar la configuración puede ser que se nos quede corto un valor, tal vez el key_buffer o bien el max_allowd_packet, o los joins sean una animalada y tengamos que hacer ajustes, es completamente normal, re configuramos los valores a nuestro gusto cuando lo necesitemos.
Ahora comienza lo bueno ( con esto último que he dicho ) y lo repito, cambiamos los valores cuando lo necesitemos para que demonios cambiamos algo en el mysql si no lo necesitamos ? esto lo digo por el simple hecho de que mucha gente coge un servidor, no a montado aún su aplicación y ya dice algo así:
«Tengo 32 gigas de ram, vamos a hacer re-configurar esta movida» key-bufer 2048 ( que no falte ) innodb_buffer_pool_size = 6 gigas » que no falte que leí por ahí que entre más ram mejor y así que esto tiene volar » y así el colega le va metiendo valores a lo tonto que luego en sus bases de datos usará solo myisam y no pesará más de 200kb en total y tendrá un desperdicio de recursos marciano…
Con esto tengo que decir que el desconocimiento es el que peor configura un mysql.
En este caso anterior estoy 100% seguro que mysql con su configuración por defecto funcionará 100 veces mejor que con valores anormales y sin sentido alguno.
Así que es tiempo de contar una historia que me a pasado varias veces pero que contaré la mejor de todas a mi manera de verlo.
Este es un cliente que me contacta desesperado tiene un script a medida, no recuerdo que hacia muy bien la aplicación pero guardaba muchos datos, tipo conteo de visitas o de clics o de publicidad o alguna cosa similar.
Me dice algo así » Estoy desesperado he contratado a 3 sysadmin para que me configuren mysql y la aplicación va igual o peor que antes »
Bien el problema era este, la web tenia algo de tráfico tampoco era exagero, la aplicación cargaba datos al hacer login en el site, pues bien cuando hacías login tenias que esperar entre 30 segundos y 120 segundos para que cargara, el problema claramente estaba en el mysql, un uso era descomunal, en momentos buevos 500% de cpu.
Mysql 600% de CPU
En momentos malos ni idea…
Problemas con Mysql
Pues bien acepto el reto y lo primero que hago es revisar la configuración intento entrar a mysql como siempre dando a tabular luego de /etc/my.cnf y no entraba, el motivo era que habían por lo menos 10 archivos my.cnf* my.cnf-bak my.cnF y un largo etc, reviso la configuración y veo cosas como estas:
max_connectios = 200
Eso es una cosa normal, el tema es que en los otros archivos de configuración veías que el que intento «configurar» mysql puso:
max_conections = 500
Otro
max_connectios = 1000
Y solo pongo el max_connections para no llegar entrada de código no sea que alguien intenta meterlos en el my.cnf XD
El tema es que al igual que en el max_connections los valores tenían una variaciones de un my.cnf a otro que ya habían usado que eran brutales uno ya lo había configurado el mysql para usar 256 gigas de ram, supongo que le peto en la cara y murió.
Yo siempre he querido hacer esta pregunta:
A ver, tienes max_connections en 200 lo cual te esta generando un uso de CPU de 600%, tu crees que si con 200 conexiones ( que no las esta usando ni en broma, ya que no te has molestado en mirar el show status de mysql que no llega ni a 20 ) con 500 o 1000 va ir mejor ? XD
O sea, supongamos…. mysql tiene ese consumo descomunal por la cantidad de conexiones que tiene, tienes asignadas 200 y las esta usando todas la mayor parte del tiempo, no siempre, aveces 150, aveces 200 pero es indiferente, si con 200 te genera un uso de CPU de 500 % si subes la cantidad de conexiones a 1000 y llegan a realizarse las 1000 simultaneas no tendrás un uso de 500% de cpu efectivamente, posiblemente no veas ni puedas ver cuanto CPU consumas ya que posiblemente se vaya a quedar frito antes de que puedas intentar mirarlo xD ya que cada conexión te consume mucho CPU por el tipo de consultas que estas haciendo ( pesadas a lo bestia ) entre más conexiones permitas a mysql más conexiones pesadas tendrá y más CPU consumirá lo ideal para reducir el uso de cpu sería bajar la cantidad de conexiones posibles a ese usuario para que no afecte a los demás, en caso de tener más bases de datos y usuarios sería lo ideal, configuras max_users_connectios, evitas que ese usuario se conecte mucho a su bd listo que revise por que consume tanto la carga bajará y los demás usuarios trabajarán mejor, esta claro que si solo es una bd y un usuario esto da igual la aplicación petará.
Todos cometemos errores, cuando comenzaba en esto he hecho algo similar alguna vez pero vamos, no tan salvaje.
Como ya dije al igual que el max_connections habían decenas de valores que habían probado los otros sysadmin, subían y bajaban para ver si el problema se resolvía, lo mas curioso es que ninguno se conecto a la consola de mysql para ver que pasaba en verdad, este es otro punto a conectar, como configuras el mysql si no sabes lo que pasa por dentro ? usa la consola de mysql, show status, show variables etc etc todo te ayudará a determinar el problema.
¿Como resolví el problema?
Esto tengo que contárselo si no menuda gracia no ?
Primero deje la configuración de mysql por defecto por si luego tenias que hacer cambios desde 0, aunque no me hizo falta nada…
Luego entre a ver que pasaba en el mysql, basta con hacer un SHOW PROCESSLIST vi alguna que otra consulta ( demasiadas )
El problema no era la cantidad de consultas, normalmente esto no suele ser un problema , si más bien el problema era lo que hacían las consultas, en este caso la enorme lentitud era debida a esto:
select count(Id) from TB_LD_Campaigns where (Targeted_Mem_Types like "%\"Default\"%") and ((Targeted_Members_Gru="") and ((Targeted_coun="") or (Targeted_coun like "%ES%")) and ((Targeted_Languages="") or (Targeted_Languages like "%s%")) ) and (Id not in (select PTC_Id from TB_LD_Stats where MemberId=3196 and Event="eventosk" and New_Completion_Time_Interval is null)) and (Id not in (select PTC_Id from TB_LD_Stats where MemberId=3196 and Event="eventosk" and now()
Oh yeah, la consulta hace un select extra planetario que escanea 533 080 filas una bestialidad de filas para mostrar un misero resultado, usuario,id, fecha o lo que sea 10 o 20 de estas consecutivas o al mismo tiempo hacen que tu mysql este usando 500% de cpu.
La solución es muy sencilla, le dices al programador que haga un select más decente o que añada indices, en este caso como no era gran cosa, le añadí indices a un par de tablas / columnas etc y el resultado en el mismo servidor:
No fueron más de 15 minutos de trabajo, el resulto fue:
- Cliente contento.
- Servidor dejo de estar cargado.
- Aplicación funcionaba como una bala.
- Cliente muy contento.
La base de datos no pesaba mucho, tenia muchas entradas,pero no eran más de 20mb en disco, por lo tanto viendo las consultas y demás deje el my.cnf por defecto excepto algún cache y poco más.
Como conclusión tenemos que, la mayoría de las veces no hace falta hacer grandes o complejos cambios en la configuración de mysql, una base de datos mal diseñada puede colapsar tu servidor, la configuración no hace milagros, resuelve problemas de caches, tiempos de conexión, tamaño de paquetes etc etc, si tu base de datos, su estructura, las consultas que hace la aplicación no esta en armonía todo puede ser un verdadero desastre y traerte muchos dolores de cabeza.
Muchos wordpress tienen desastres así pero no por el wordpress, si no por el theme, algún plugin, por ejemplo un plugin que meta información de cada visitante en la base de datos y no lo haga del todo bien puede hacer que la base de datos pese mucho, tenga millones de entradas y cuando le hagas un select si no esta optimizado verás como el uso de CPU de mysql se dispara
Si creemos que nuestro mysql necesita una mejora ya que nuestra base de datos es muy grande o bien a crecido y no sabemos que valores tocar siempre podemos optar por la configuración por defecto superior o configuración que recomienda mysql para bases de datos más grande, normalmente mysql viene con varios archivos de configuración my.cnf, my.cnf-small, my.cnf-medium, my.cnf-large etc que podemos usar sin ningún problema -> configuración por defecto de my.cnf