Configurando y Optimizando un servidor. Nuevas mejoras en my.cnf y arrancando en utf8

Siguiendo la optimización del servidor comentada en otros post, por ejemplo el de: Configurando y Optimizando un servidor. my.cnf voy a explicar y añadir una configuración que he ido mejorado.
Adicionalmente incluyo los parámetros necesarios para arrancar el mysql en utf8.
Para ver la configuración con la que arranca, se conecta y devuelve los datos el mysql tendréis que ejecutar la siguiente query :

show variables like '%character%';

La cual os tiene que devolver los datos de la configuración de las variables de codificación, y os tiene que dar algo parecido a lo siguiente:

| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |

La siguiente configuración es la que he llegado a optimizar para un servidor CentOS con un procesador Quad Core de 8 núcleos y 4BG de ram.
No digo que sea la mejor, pero a mi me está funcionando y el rendimiento es bueno.
OJO!! arrancar el servidor mysql en utf8 sin tener toda vuestra aplicación en utf8 puede ser un completo desastre! a si que comprobar toda la codificación de vuestra aplicación antes.

Lo primero un backup

 cp  /etc/my.cnf /etc/my.cnf.backup

abrimos el archivo con vuestro editor favorito.

vi  /etc/my.cnf

Agregar la siguiente configuración del mysql.
Comprobar si los path de instalación y socket coinciden con lo que ya teníais, sino poner los vuestros(Ver las 2 primeras lineas).

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
max_allowed_packet = 128M

# parametros para arrancar el mysql en utf8
# sino esta claro que toda la aplicacion está en utf8 no poner éstos parámetros
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci

skip-character-set-client-handshake

# Puerto y Socket
port = 3306


# Permitir conectar clientes con versiones de protocolos anteriores al 4.1
# http://dev.mysql.com/doc/refman/4.1/en/old-client.html
old_passwords = 1


# Controls the amount of memory allocated to the MyISAM index key cache
# http://dev.mysql.com/doc/refman/4.1/en/myisam-key-cache.html
key_buffer = 32M


# Query Cache
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1


# Limite de conexiones
max_connections = 100
max_user_connections = 70
back_log = 25


# Buffers
sort_buffer_size = 16M
join_buffer_size = 32M
read_buffer_size = 4M


# Deshabilitar las tablas del tipo BDB
skip-bdb
bdb_cache_size = 0
bdb_max_lock = 0


# Numero de tablas abiertas mantener en la cache, para comprobar las tablas abiertas:
# mysql> SHOW STATUS LIKE 'Opened_tables';
# http://dev.mysql.com/doc/refman/5.0/es/table-cache.html
table_cache = 8192


# Logs
#log = /tmp/mysql.log
# grep -v "admin@localhost" /tmp/mysql.log | grep Conn | awk '{ print $5 }' | grep "@" | sort | uniq -c | sort -nr | head -10
# log_slow_queries = /var/log/mysqld.log
# log-queries-not-using-indexes
log_error = /var/log/mysqld.log


# http://dev.mysql.com/doc/refman/5.0/en/error-log.html
# log_warnings = 0 -> se desactivan el logeo de warnings en el error log.
# log_warnings = 1 -> se logean los warnings en el error log.
# log_warnings = 2 -> se logean los warnings + "aborted connections":
# http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html
log_warnings = 0


# Deshabilitar la resolucion DNS en las nuevas conexiones
# http://dev.mysql.com/doc/refman/5.0/en/dns.html
# http://hackmysql.com/dns
skip-name-resolve


# MySQL cerrara aquellas conexiones que llevan mas de X segundos inactivas
# http://jeremy.zawodny.com/blog/archives/000173.html
wait_timeout = 60


# El numero de segundos que espera el servidor para actividad en una conexion
# interactiva antes de cerrarla, en lugar de utilizar wait_timeout. Un cliente
# interactivo se define como un cliente que usa la opcion CLIENT_INTERACTIVE
# para mysql_real_connect().
interactive_timeout = 60


# Tiempo de espera maximo permitido en las conexiones contra el servidor MySQL
connect_timeout = 15


# Numero de threads en cache para reutilizar, ajustar segun relacion:
# 100 - ((Threads_created / Connections) * 100) ~= 100 %
thread_cache_size = 64


# Tiempo en segundos para considerar una consulta SQL como "slow query"
long_query_time = 10


# Evitar tablas temporales en disco, principalmente afecta a consultas "GROUP BY"
# http://www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/
# Deseable que "mysqladmin ext | grep Created_tmp_disk_tables" sea 0
tmp_table_size = 64M
max_heap_table_size = 32M


# Si se hace mucho uso de MyISAM, el valor deberia ser 2x o 3x de table_cache
open_files_limit = 8192



# Deshabilitar "External Locking"
# http://dev.mysql.com/doc/refman/4.1/en/external-locking.html
skip-external-locking



# Tamano maximo de paquetes de comunicacion, un unica sentencia SQL
# http://dev.mysql.com/doc/refman/5.0/es/packet-too-large.html
max_allowed_packet = 16M



# Tamano inicial del bufer para la comunicacion cliente/servidor, pero puede 
# crecer hasta max_allowed_packet bytes cuando sea necesario
net_buffer_length = 32K



# Maximum number of temporary tables a client can keep open at the same time.
max_tmp_tables = 64



# The stack size for each thread
thread_stack = 128K



# Deshabilitar los "INSERT DELAYED" (inserccion de registros encoladas para
# insertar cuando la tabla no esta en uso).
max_delayed_threads = 0



# myisam_sort_buffer_size <-- PRIO
# thread_concurrency


#==============================================================================
# InnoDB
#==============================================================================

# El tamano del buffer de memoria que InnoDB emplea para el almacenamiento 
# intermedio de los datos e indices de sus tablas. Mientras mas grande sea este
# valor, menores operaciones de E/S en disco seran necesarias para acceder a los
# datos de las tablas. Simil con "key_buffer_size" para tablas MyISAM.
# http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
innodb_buffer_pool_size = 32M



# This variable stores the internal data structure. Make sure it is big enough
# to store data about all your InnoDB tables (you will see warnings in the error
# log if the server is using OS memory instead)
innodb_additional_mem_pool_size = 10M



# The size of the buffer which InnoDB uses to write log to the log files on
# disk. Sensible values range from 1M to half the combined size of log files.
# A big log buffer allows large transactions to run without a need to write the
# log to disk until the transaction commit. Thus, if you have big transactions,
# making the log buffer big will save disk I/O.
innodb_log_buffer_size = 10M



[mysql.server]
user = mysql
basedir =/var/lib


[mysqld_safe]
err-log = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
log-slow-queries=/var/log/mysql-slow-queries.log
skip-bdb

#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

Reinicar el servicio de mysql

/etc/init.d/mysqld restart

Nachín! este va por ti! 😉

Tags de búsquedas:

skip-external-locking my cnf,optimizar servidor mysql,connect_timeout mysql,optimizar servidor centos,optimizar servidor linux,mysql activar puerto 3306 my ini windows,mysql connect_timeout,mysql optmizar parametros my cnf en windows,configuracion de buffer centos,mysql memoria maximo ram,mysql my cnf opciones tablas,optimizar mysql ubuntu,mysql skip-external-locking,query_cache_limit ejemplo my cnf,mysql max connections centos ram

Noticias relacionadas »

14 comentarios en Configurando y Optimizando un servidor. Nuevas mejoras en my.cnf y arrancando en utf8

  1. Rene Soto
    / Responder

    Hola, tengo un servidor windows con 8GB de Ram , procesador intele(R) Core/TM) i7 2.80GHz
    en un sistema operativo de 64bits , si sigo la configuración de este post me servirá o necesito alguna diferente?

  2. / Responder

    magnifico post

  3. / Responder

    Hola gracias por el tuto, yo tengo un server dedicado de 64 Gb en ram,
    he estado siguiendo los pasos que comentas, pero tengo un detalle y es que al lanzar la consulta sql me arroja esto:

    character_set_client
    utf8
    character_set_connection
    utf8
    character_set_database
    latin1
    character_set_filesystem
    binary
    character_set_results
    utf8
    character_set_server
    latin1
    character_set_system
    utf8
    character_sets_dir
    /usr/share/mysql/charsets

    y me yuyo cambiar a que arranque directamente desde utf 8.

    lo otro aunque ya he visto que respondiest e a otro colega,

    como 64 Gb de ram como seria el manejo de estas variables ?

    # Query Cache
    2
    query_cache_limit = 196M
    3
    query_cache_size = 196M
    4
    query_cache_type = 1
    5
    query_cache_min_res_unit=2048
    6

    7
    # Limite de conexiones
    8
    max_connections = 300
    9

    10
    # Buffers
    11
    sort_buffer_size = 32M
    12
    join_buffer_size = 32M
    13
    read_buffer_size = 4M
    14

    15
    table_cache = 11192
    16

    17
    wait_timeout = 20
    18

    19
    long_query_time = 2
    20

    21
    tmp_table_size = 310M
    22
    max_heap_table_size =128M
    23

    24
    open_files_limit = 16384
    25

    26
    thread_stack = 256K

    Saludos y gracias !

  4. Rodrigo
    / Responder

    Se agradece!!!

  5. belen
    / Responder

    MUCHISIMAS GRACIAS. SUPER UTIL

  6. Fernando Real
    / Responder

    Instale Varias instancias de MySQL en Windows puerto 3306 con su schema1 y tabla1 y en la segunda instancia con el puerto 3307 con su schema2 y tabla2. La pregunta es cómo puedo hacer una consulta estando en la instancia del puerto 3306 leer datos de la tabla2 del puerto 3307.

    De antemano muchas gracias por la ayuda.

    Saludos.

  7. Markos
    / Responder

    Hola Pedro!

    Realmente te felicito no sólo por el contenido de esta nota que me he tomado como referencia, sino por tu web completa.

    Si no es demasiada molestia, me gustaria saber que parámetros deberia modificar para una VPS con 3GB de RAM y procesador de 8 nucleos.

    Muchas gracias!

  8. jhonnyjjac
    / Responder

    Gracias nuevamente… en efecto tambien te cuento que tenia mis reservas con ubuntu… pues no me gusta mucho… yo utilizaba hasta hace un mes el ClearOS pero 32 bits… no encontré de 64 Bits… quisiera debian pero no he podido montarlo por el problema ese del grub que al reiniciar despues de instalar….nada de nada…. por los momentos seguire sufriendo con ubuntu server….ya hice los ajustes que me recomendaste… solo esperare un par de dias a ver que tal se comporta con la nueva configuracion… por otro lado tambien tengo la duda porque los sistemas que estan montados algunos tienen bases Myisam y otras son innob…. ademas de que en un principio migramos de windows 2003 server a linux…y en el proceso se nos presentó el problema de las mayusculas y minusculas…. windows no le presta atencion a eso…pero linux si… creo que con – lower_case_table_names=1 – lo solucione…esperemos… Ah, olvide en principio comentarte que mi red local la tengo a 1000 Mbps y son 65 estaciones dandole duro a sistemas de Nomina, Contabilidad, Administrativo, Laboratorios, entre otros procesos adicionales….por lo que las lecturas y escrituras en el servidor son constantemente altas…. Gracias pedro, eres muy amable al contestarme tan pronto….Sigue asi viejo, asi aprendemos todos… seguiré tus posts a ver que aprendo en el camino….YA COMENTARE QUE TAL ME FUE Y CON CUAL SERVER ME QUEDÉ AL FINAL….GRACIAS

    • Pedro Ventura
      / Responder

      Las migración de windows server a linux tiene que haber sido dura. Pero con Linux a la larga todo es mucho mas sencillo, practico y estable. Se necesita tiempo y experiencia para empezar a conocer de adminitracion de sistemas pero ya verás que con linux todo te va mejor.
      Lo que si te digo, es que intentes cuanto antes pasarte a Debian, CentOs o RedHat. Prueba con CentOS, todos los server que actualmente gestiono están en CentOS y esta muy bien. Prueba a instalarlo en algún ordenador en tu tiempo libre si puedes. Es un consejo porque a la larga ubuntu como servidor no se que tal funcionará. Lo que sé que lo que se usa a nivel profesional es lo que te he dicho Debian, CentOs o RedHat y alguna otra distribución tipo FreeBSD, etc.

      ánimo y darle duro!! 🙂

      Un saludo!

  9. jhonnyjjac
    / Responder

    Buen dia pedroventura….te felicito por esta web…. la verdad estaba buscando como optimizar mi servidor mysql….y esto me viene al dedo… De todas maneras quiero comentarte mi caso a ver que me aconsejas…. tengo bajo mi administracion un servidor de base de datos, IBM SYSTEM X3650 M2 CON 16 GB DE MEMORIA – procesador Xeon Y TARJETAS LAN DE 1000… le monté ubuntu server 64 bits porque no pude con debian (me daba un error al instalar el grub y no pase de ahi) bueno, la cosa es que antes lo tenia con ClearOS 5.2 y me iba bien…pero quise probar a 64 bits para ver que tal…. mi sorpresa fue al ponerlo en marcha con 60 usuarios dandole duro a un sistema….se me queda colgado, se puso demasiado lento… y se que debo colocarle algunos parametros al My.cnf pero hasta ahi llego pues no tengo mucha experiencia en linux ni mysql…. LA PREGUNTA FINAL…. CREES QUE ESTA CONFIGURACION QUE EXPLICAS ACA LE VENGA BIEN A ESTE SERVIDORCITO? MUCHISIMAS GRACIAS DE ANTEMANO POR TU COLABORACION Y NUEVAMENTE TE FELICITO… LA VERDAD QUE CON GENTE COMO TU…TODOS APRENDEMOS Y CRECEMOS PARA AYUDAR A OTROS….

    • Pedro Ventura
      / Responder

      Gracias por tu comentario! 🙂 Gente que me lo agradezca como tu hay pocos! gracias!

      Bueno te voy comentando:

      Tienes mucha ram,16GB esta muy bien, tienes que tener un SO de 64bits si o si!!! así que esto esta bien!!
      Luego, ubuntu esta muy bien, pero para nivel usuario, un pc como terminal o un puesto de trabajo pero no lo pondría como un server en producción o aunque sea para desarrollo o pruebas. Metería algo mas estable como un Debian, CentOS o RedHat, sin duda!!

      La configuración que te cuento, esta bien para tu máquina, aunque poco a poco la he ido optimizando para mi servidor y lo que tengo tambien te puede venir bien.
      Usa la configuración que hay en el artículo pero cambia los siguientes parámetros:

      # Query Cache
      query_cache_limit = 196M
      query_cache_size = 196M
      query_cache_type = 1
      query_cache_min_res_unit=2048
      
      # Limite de conexiones
      max_connections = 300
      
      # Buffers
      sort_buffer_size = 32M
      join_buffer_size = 32M
      read_buffer_size = 4M
      
      table_cache = 11192
      
      wait_timeout = 20
      
      long_query_time = 2
      
      tmp_table_size = 310M
      max_heap_table_size =128M
      
      open_files_limit = 16384
      
      thread_stack = 256K
      
      

      En estos parámetros se asigna más memoria ram a ciertos procesos. De hecho yo te diría que asignaras un poquito mas de ram como entre 8-32MB o Kb según corresponda, adicional a cada uno de los valores que te acabo de comentar, ya que tu tienes mas ram de lo que yo tengo en mi server en producción!

      Pruebalo, reinicias mysql, y realizas otra vez el test.

      En la siguiente imagen pudes ver mi gráfica de rendimiento y uso.
      Gráfica Uso Server

      Recuerda que es muy importante tener indices en las tablas de tu base de datos, en este artículo te explico más: http://www.pedroventura.com/blog_programacion/sql/optimizando-consultas-mysql-creando-y-definiendo-indices-manage-index/

      También es super importante tener cacheada tu app y que se hagan el menor numero posibles de consultas a la base de datos. Prueba a usar memcached http://www.pedroventura.com/blog_programacion/tag/memcached/

      Ya me cuentas que tal te fue todo!!

      Un saludo!!

      • Josean
        / Responder

        Hola Pedro,
        Primero agradecerte la información ya que nos sirve de mucho a los que no somos expertos en esto.
        Estoy administrando un servidor con CentOS 64 bits y 12 GB de RAM, me estoy decantando por los valores del servidor de 16 GB de RAM, pero he visto un valor que creo, debe estar equivocado, me refiero a:

        query_cache_limit = 196M,

        yo este valor lo tengo en 4M, ¿no es excesivo 196M?, si esta equivocado, ¿cúal sería el correcto?

        Mis valores para estas variables son:
        1 # Query Cache
        2 query_cache_limit = 4M
        3 query_cache_size = 608M
        4 query_cache_type = 1
        5 query_cache_min_res_unit=16384
        6
        7 # Limite de conexiones
        8 max_connections = 150
        9
        10 # Buffers
        11 sort_buffer_size = 24M
        12 join_buffer_size = 4M
        13 read_buffer_size = 4M
        14
        15 table_cache = 6144
        16
        17 wait_timeout = 40
        18
        19 long_query_time = 5
        20
        21 tmp_table_size = 320M
        22 max_heap_table_size =320M
        23
        24 open_files_limit = 12448
        25
        26 thread_stack = 256K

        Creo que sobre todo tengo un poco disparados los valores referentes a las tablas, ¿que te parece?

        Gracias y un saludo

  10. Lau
    / Responder

    Muchas Gracias!! esta configuración me ha ayudado muchísimo.. eres un sol

  11. / Responder

    Buah chaval, esto está demasiado bien. Nosotros tenemos 4 cosas de configuración, lo básico muy básico.
    En cuanto me deshaga del portal que estamos haciendo me pongo con esto porque vale su precio en oro!
    Un abrazo tio y gracias mil por estos posts

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Invertir en criptomonedas: »

 

Cómo comprar bitcoin

Invertir y comprar Bitcoin y otras altcoins es más fácil de lo que crees.

1) Si sólo quieres comprar Bitcoin, Ethereum o Litecoin como inversión, tu opción es Coinbase

2) Si quieres probar con otras criptomonedas tu opción es Binance

3) Cualquier duda escríbeme con total libertad al formulario de contacto para resolver dudas: https://www.pedroventura.com/contacto/

¡Comparte este artículo! »

  • Pedro (3 días)
    Buenos días Margherita El plugin está obsoleto y ya no…
  • Juan Carlos Díaz (4 días)
    Este grupo de Telegram me ha dado muy buenas señales…
  • Margherita (4 días)
    Hola, tengo un problema con el plugin. Tanto en la…
  • Pablo H (2 semanas)
    Hola Pedro, Muchas gracias por el post, me fue muy…
  • Pedro Ventura (3 semanas)
    Hola Margat Muchísimas gracias por tu comentario, me alegro de…
  • Pedro Ventura (3 semanas)
    Hola Rebeca, Tan sólo se permite 1 dominio por cuenta.…

Suscríbete al newsletter »

Proporciona tu correo electrónico a continuación y recibe las últimas noticias! Funciona con Feedburner de Google

Archivo »

Leer entrada anterior
script backup mysqldump en linux mediante crontab

Si queréis hacer un shell script automático para que genere backups de la base de datos de vuestros proyectos, voy...

Cerrar