MySQL – Variables básicas a configurar

mysql

“Regla de Oro”, si no queremos tener problemas con nuestro MySQL, no dejar el script por defecto de configuración sin editar. Me apunto aquí sólo algunas de las variables que deberíamos editar para evitarnos problemas fáciles de resolver.

El uso de la memoria total que utilizaría potencialmente MySQL es la multiplicación de los buffers x las conexiones totales configuradas (ej, max_connections = 50)

query_cache_size si nuestra aplicación tiene lecturas intensivas y no tenemos cache a nivel de aplicaciones, puede ser de gran ayuda. Valores desde los 32M a los 512M tienen sentido normalmente. Comprueba esto sin embargo después de un rato y mira si está siendo usado. Para ciertos entornos de trabajo el ratio de acierto del cache es bajo y no justifica su activación.

sort_buffer_size buffer usado para ordenar los resultados (alojado por cada thread cuando el “sorting” necesita ser hecho) de la capa SQL, funciona para todos los motores de BD.

read_rnd_buffer_size, read_buffer variables usadas para leer buffer de tablas MyISAM para Full Table Scan (read_buffer) y para leer filas ordenadas con sort (read_rnd_buffer_size). Otros motores como Innodb no usan esta variable.

join_buffer_size bufer usado para joins sin índices y otros casos. Es usado a nivel de capa SQL por lo que se aplica a todos los motores de BD.

table_cache permite evitar la reapertura (a veces costosa para algunos motores de BD)

tmp_table_size esta variable especifíca el tamaño máximo implícito temporal de una tabla (creada durante la ejecución de una query automáticamente) que permitirá alcanzar en MEMORIA antes que sea convertida en disco en una tabla temporal MyISAM . Tamaño máximo permitido para tablas “in-memory” (usado por querys complejas). Pueden llegarse a necesitar más de una tabla por query.

max_heap_table_size

Atención!

buscando información sobre estas variables, me llamó la atención un post en Mysqlperformanceblog.com, donde el gurú Peter hablaba sobre las dudas que le surgían en la implementación y el comportamiento a posterior de estas dos variables. Al parecer, hay algo mal en el comportamiento de tmp_table_size. Según Peter, tmp_table_size no es suficiente porque MySQL también mira en max_heap_table_size y usa un valor menor como límite para tablas temporales en memoria que serán convertidas a MyISAM.

Según Sergei Golubchik, después de examinar el comportamiento a instancias del post de Peter, tmp_table_size no afecta a las tablas temporales creadas en el disco. Es un bug de 2004 y el status es “Won’t Fix”. Según veo en la documentación de variables de sistema de MySQL, tmp_table_size pasa a mejor vida a partir de la versión 5.1.2 (Version Removed: 5.1.2). Tengo que confirmar esto sin embargo. Lo dejo pendiente. El hecho es que, mirando la lista de cosas a tratar, en MySQL hablan de introducir una nueva variable en sustitución de tmp_table_size, llamada memory_tmp_table_size.

thread_cache_size número de threads que MySQL puede cachear y reusar. 32-64 suele ser un buen valor.

innodb_buffer_pool_size es la opción más importante de lejos para el buen funcionamiento de InnoDB. 70-80% de la memoria es una buena apuesta aunque todo depende del tamaño de nuestra BD y los recursos disponibles. Si nuestra BD es pequeña, configurado un buffer algo más grande que nuestra BD sería suficiente.

Links

23 thoughts on “MySQL – Variables básicas a configurar

  1. Eduardo says:

    Muy buena recopilación sobre las variables de configuración, pero recomiendas alguna configuración a modo de ejemplo, yo veo que tanto apache como mysql consume muchos recursos de nuestro servidor…

    Un saludo

  2. Rubén Ortiz says:

    Hola Eduardo

    la configuración dependerá de los recursos disponibles. Por eso no es posible darte ningún ejemplo, hace falta saber, cpu, ram, etc. Pero si quieres ejemplos, dentro de tu sistema linux donde hayas instalado MySQL tienes ejemplos. Haz un

    # find / -name “my-*”

    Deberías tener archivos de ejemplo con el nombre my-huge.cnf, my-large.cnf, etc. Todos archivos de ejemplo para nuestro servidor MySQL.

    Saludos

  3. JeskaDominaria says:

    Tengo dudas tengo un server que tiene dos procesadores sin embargo mysql se traga mucho de lo que ellos dan y me cuelgan en algunas consultas que se generan.
    sera psoible que me diga como puedo hacer para solvertar esto?

  4. Carlos says:

    Rubén , por favor necesito tu ayuda , en que archivo o archivos encuentro las siguientes variables :

    Variables to adjust:
    query_cache_size (> 16M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 2M)

    Perdona la ignorancia pero en my.cnf solo encontre 2 , query_cache_size y innodb_buffer_pool_size

    Corri el tuner y me dio esas recomendaciones.

    Lo siento pero soy un neofito en esto , muchas gracias desde ya .

  5. Rubén Ortiz says:

    Hola

    no has de encontrarlas, el archivo my.cnf es como un libro en blanco, eres tú el que lo editas. De esa forma, tu simplemente rellena el my.cnf con esas variables y reinicia el mysql. Con eso aplicarás los cambios.

    Acuerdate de poner las variables dentro de [mysqld].

    Saludos

  6. Carlos says:

    Waoo Ruben ! gracias por la gran respuesta estaba super perdido ! , sabes no se si puedo contactarme contigo , podrías darme tu telefono para llamarte o una direccion de MSN ? Muchas gracias nuevamente.

    Carlos

  7. jmgelectronika says:

    hola ruben, arto de dar vueltas por foros he decidido para aquí que tiene buena pinta haber si me puedees sacer de la duda.
    he realizado un programa en linux que recoge una vble de un puerto, y la necesito guardar, bien como entero que es lo más inmediato, o bien como float.
    este es el código:
    mysql_real_connect(conn,”localhost”,”root”,”or2009″,”mibdd”,0,NULL,0);
    mysql_query(conn,”CREATE TABLE escribir (valor VARCHAR(25), vol INT)”);
    vl=int2char(Val);
    vl=’d’;
    printf(“valor %d\n”,Val);
    mysql_query(conn,”INSERT INTO escribir VALUES(‘nombre 1’, ‘%i, *Val’)”);

    como se puede observar queiero guardar con nombre 1 el valor de Val.
    esto es lo que consigo guardar:
    mysql> select * from escribir;
    +———-+——+
    | valor | vol |
    +———-+——+
    | nombre 1 | 0 |

    espero que me saques de dudas.

    un saludo desde huelva

  8. Rubén Ortiz says:

    Hola

    no entiendo bien tu problema, si te explicas un poco mejor el que NO consigues hacer a lo mejor podemos hacer algo 🙂 ¿De dónde sacas la variable del puerto en cuestión?

  9. Santiago says:

    Hola Rubén,

    mi nombre es santiago, soy de Argentina.

    Te escribo porque estoy teniendo un problema con una configuración de MySQL. Te cuento que estoy en una cátedra de la Universidad Tecnológica Nacional de Argentina y estamos instalando MySQL para dar la parte práctica de la materia Bases de Datos. El problema que nos surge es que algunos alumnos, cuando están rindiendo exámen efectúan consultas que nos matan el servidor. Por ejemplo, si se le pide a un alumno que realice una consulta que involucra varias tablas, si pone un producto cartesiano (SELECT * FROM table1,table2,table3) en vez de un join, el servidor no puede atender otras peticiones tan facilmente.

    Quería saber si hay alguna forma de limitar el tiempo de una consulta, o tal vez la memoria de una conexion. En el caso de detectar que excede cierto limite finalizar esa consulta (o la conexión).

    Muchas gracias por todo.

    Santiago Basulto.-

  10. Rubén Ortiz says:

    Hola

    si algunas consultas os tiran el servidor, deberiais mirar otras soluciones pero en cuanto al timeout de una conexión puedes mirar el valor de wait_timeout y interactive_timeout. El valor por defecto de wait_timeout es de 28800 segundos.

    También te recomiendo configurar el query_cache.

    Saludos

  11. Santiago says:

    Gracias Rubén!

    Con lo de la cátedra ya lo pudimos solucionar.

    Después estuve ayudando a unos amigos que tienen un tracker de bittorrent llamado trackerx.com.ar (obviamente estas invitado) Teniamos en un principio 15000 peers, y el servidor se caia mucho. Es una Athlon X2 5400. Así que me pidieron ayuda para mejorar la configuración. Incrementé los buffers de cache (tanto de consultas, como de indices) y arranco al pelo, la mejora fue sustancial. Antes d los cambios los dos nucleos del servidor estaban todo el dia al 100%, ahora bajaron a 50/50. Los tiempos de respuesta disminuyeron drasticamente tambien. Otra cosa que toque fue la cache de tablas. Ahora voy a seguir probando, y obviamente usando estos tips que dejaste aca arriba.
    Si te interesa te sigo contando como evoluciona el tema. Muchos saludos, gracias nuevamente por el excelente contenido!

  12. Pingback: Blogs 11/04 | Carballada

  13. Edith says:

    Hola Rubén, desconozco si aún sigas atendiendo este blog, tengo algunas dudas respecto al max_user_connections, espero pudieras ayudarme, aunque primero quiero saber si sigues activo 🙂

  14. Alyta says:

    Hola Ruben, muchas gracias por tus consejos, me orientan bastante, solo que tengo un problema, tenemos una nueva instalación del mysql 5.1.63 y tenemos 4 procesadores, pero solo esta tomando recursos de 1, como le hago para que ocupe los demás procesadores, nos preocupa porque le hemos agregado estas variables y unas mas, pero no ha sulucionado el asunto. Gracias

  15. Carlos Sevilla says:

    Hola, Ruben, expongo este caso a ver si puedes ayudarme.
    Tengo mysql 4.1.22 en un servidor HP ML350 con 2 procesadores, 10 GB de memoria ram y discos en raid 5, por momentos al realizar consultas se queda trabada la consulta por mucho tiempo hasta que al final muestra el resultado, especialmente en el modulo de ventas donde el momento de realizar la consulta se calcula el stock disponible de cada articulo leyendo toda la tabla de movimientos y haciendo la operacion algebraica correspondiente.
    Estos son los parametros del mi.ini:
    port = 3306
    datadir=F:/mysql/Data/
    skip-locking

    table_cache=4500
    sort_buffer_size=16M
    read_buffer_size=8M
    thread_cache_size=8
    thread_stack=192KB
    query_cache_size=256M
    join_buffer_size=4M
    innodb_buffer_pool_size=512M
    innodb_additional_mem_pool_size=200M
    innodb_log_file_size = 128M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit=1
    innodb_lock_wait_timeout=50

    #[mysqldump]
    max_connect_errors=40
    max_connections=400

    [mysql]
    no-auto-rehash

    [mysqlhotcopy]
    interactive-timeout
    [WinMySQLAdmin]
    Server=F:/mysql/bin/mysqld-nt.exe
    Agradeciendo de antemano tu ayuda.
    Atentamente:
    Carlos Sevilla.

  16. bruce.wayne says:

    Hola

    es difícil hacer un diagnóstico, para mi y creo que para cualquiera. Hay que tener en cuenta el hardware, el tamaño de la bbdd, etc. No creo que te pueda ayudar Carlos :-/ desde mi posición.

    Suerte!

  17. Milton says:

    Estimado Ruben
    Soy de Ecuador, me gustaría me ayudes con mi problema, tengo un servidor HP Proliant ML110 G9 con 18gb ram. base mysql aprox de 50gb . y a veces se queda y no responde. Necesito solucionar este problema urgente, espero tu gentil ayuda, muchas gracias

Leave a Reply

Your email address will not be published. Required fields are marked *