Configurar my.cnf

Hola !!

Quien puede ayudarme, tengo un vps con la configuración por defecto en el fichero my.cnf, cual es la configuración adecuada adaptada a la maquina:


CPU Intel Xeon E312xx (Sandy Bridge) (4 core(s))
Versión Plesk v12.5.30 os_CentOS 6
SO CentOS 6.7 (Final)
Memoria: 8 GB

my.cnf

CODE, HTML o PHP Insertado:

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid  

gracias
un saludo

 

Hola buenas,

Pasa un MySQLTunner. No te olvides de hacer un backup del fichero my.cnf antes de pasar el script MySQLTunner con el comando cp.

CODE, HTML o PHP Insertado:

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
perl mysqltuner.pl

Debes de modificar los valores que te recomienda dicho script y al cabo de 24 horas vuelves a realizar el mismo procedimiento comparando los resultados.

Puedes ver la web del script en http://mysqltuner.com/ También puedes probar con el script Tuning Primer: http://www.day32.com/MySQL/

Te recomiendo también que instales algún sistema de caché en tu servidor como xcache y memcached o OPcache.

 

Hola gracias por contestar, bien he realizado los pasos indicados y me aparece esto:

PHP:

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 4m 36s (2 q [0.007 qps], 485 conn, TX: 157B, RX: 116B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 7.5G
[--] Max MySQL memory    : 449.2M
[--] Other process memory: 1.4G
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 56.0M (0.73% of installed RAM)
[OK] Maximum possible memory usage: 449.2M (5.85% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/2)
[OK] Highest usage of available connections: 5% (8/151)
[OK] Aborted connections: 0.21%  (1/485)
[!!] Query cache is disabled
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] No tmp tables created on disk
[!!] Thread cache is disabled
[OK] Table cache hit rate: 100% (64 open / 0 opened)
[OK] Open file limit used: 8% (91/1K)
[OK] Table locks acquired immediately: 100% (58K immediate / 58K locks)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 82.4% (6M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/56.0M
[OK] Read Key buffer hit rate: 99.0% (6M cached / 68K reads)
[OK] Write Key buffer hit rate: 99.9% (268K cached / 215 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 8.0M/90.5M
[OK] InnoDB Used buffer: 100.00% (512 used/ 512 total)
[OK] InnoDB Read buffer efficiency: 99.09% (1086095 hits/ 1096018 total)
[!!] InnoDB Write Log efficiency: 88.46% (46 hits/ 52 total)
[OK] InnoDB log waits: 0.00% (0 waits / 98 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 90M) if possible.
[root@vps125 ~]#


Ahora no se que hacer más... que valores tengo que añadir my.cnf????? soy algo novato en el tema de configuracíon. tengo instalado el opcache.

 

query_cache_size = 64M
thread_cache_size = 16
innodb_buffer_pool_size = 128M

(de acuerdo a las recomendaciones del script + valores que veo en tu configuración)

Lo ideal es que corras ese script cuando el servidor tenga al menos 24 hora de uso real, puede editar el /etc/my.cnf, reinciar mysql y volver a correr el script en 24 horas

Hay mucho más que optimizar en mysql, pero el script puede darte unos valores iniciales decentes

 

Recomendado de lectura obligada si estás empezando:
http://www.fromdual.com/mysql-performance-monitor
http://www.fromdual.com/mysql-performance-tuning-key

Como te han comentado MySQLTunner no debe reemplazar a un DBA. Aunque si buscas por google puedes encontrar configuraciones echas casi a medida para tus necesidades.

 

Gracias Datacenter1 por tu gran ayuda, voy a llevar a cabo las indicaciones.
f.villalba se agradece el tutorial gracias

 

Hola,

Te recomiendo que instales la última versión de mysql que en tu caso ya la tienes y que uses el noatime y nodiratime en las particiones. También debes empezar por optimizar primero las variables key_buffer_size y table_open_cache.

Esta guía es un buen punto de partida: http://dev.mysql.com/doc/refman/5.7/en/server-parameters.html

 

Aporto otro enlace de mucha utilidad. Son configuraciones para servidores de 2GB, 4GB, 8GB, 16GB y 32GB.
http://blog.webeats.it/168/alcuni-my-cnf-per-mysql-per-server-con-2gb-4gb-8gb-16gb-32gb-di-ram/

Aunque debes tener en cuenta que tu vps debe de estar en un nodo donde no haya sobrecarga. Si no de poco te va a servir esto. Es fácil saberlo. Si pagas poco por tu vps probablemente estés en un nodo con overselling. También mirando el parámetro IOWait con el comando top. Y mirando la velocidad del disco.

 

Hola f.villalba,

Ahora me puedo guiar algo mejor con este ejemplo Estoy en ovh VPS CLOUD 3, la version mysql 5.1.73 no se si realmente estoy en un nodo overselling
gracias por la ayuda

 

Hola,

Yo lo que hago para saber si el nodo puede tener indicios de overselling es hacer un test al disco y mirar el iowait. "Carga de escrituras/lecturas en el disco"

Para el iowait basta con un simple top o htop.

Para saber la velocidad de disco yo uso este comando:

CODE, HTML o PHP Insertado:

dd if=/dev/zero of=test bs=64k count=16k conv=fdatasync

Aunque este test es muy sintético pero bueno a mí me sirve para poder ver el panorama general.

Si da buenos resultados empieza a optimizar y si no deberías buscar otro vps. El disco la velocidad de lectura/escritura sobre los 150 y pico está bien. Aunque bueno este también depende de la carga del nodo y de lo bueno que sean los discos.

Luego mide la red y si el ping pasa de 150 rechaza el vps.

Yo para empezar a trabjar hago eso.

En MySQL tienes archivos de configuración por defecto que podrías usar para tener un punto de referencia.

Después deberías instalarte zabbix o algo para monitorizar.

También es importante que instales plugins de caché en tu vps como memcached y xcache o el opcache para mejorar el rendimiento.

MySQL requiere de un buen hardware para funcionar bien y una buena optimización del sistema como el kernel, sistema de ficheros, el stack tcp/ip y el apache. Sin esto por más que pases scripts y demás muy poco vas a hacer.

 

Perfecto tomare nota gracias

 

CODE, HTML o PHP Insertado:

16384+0 records in
16384+0 records out
1073741824 bytes (1.1 GB) copied, 6.1106 s, 176 MB/s

Hola Buenas!!

Esto es lo que aparece al introducir el comando indicado:

PHP:

16384+0 records in
16384+0 records out
1073741824 bytes (1.1 GB) copied, 6.1106 s, 176 MB/s

Pienso que este servidor deja poco que desear, ni aunque se le asigne una buena configuración mysql etc... todo lo que se me ha indicado lo llevare a cabo eso seguro pero en un vps estable.

Desde hace unos días en los ficheros de log /var/log/httpd/error_log observo que aparece estos mensajes:

PHP:

[Wed May 11 11:33:54 2016] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[crit] Memory allocation failed, aborting process.

[Wed May 11 11:43:22 2016] [error] (12)Cannot allocate memory: mod_fcgid: can't run /var/www/cgi-bin/cgi_wrapper/cgi_wrapper

[Wed May 11 11:43:22 2016] [warn] (12)Cannot allocate memory: mod_fcgid: spawn process /var/www/cgi-bin/cgi_wrapper/cgi_wrapper error

Wed May 11 11:43:22 2016 (24975): Fatal Error Unable to allocate shared memory segment of 134217728 bytes: mmap: Cannot allocate memory (12)

mmap() failed: [12] Cannot allocate memory

[Wed May 11 12:02:38 2016] [notice] child pid 30069 exit signal Segmentation fault (11)


/var/log/messages

PHP:

May 11 11:43:49 vps kernel: php-cgi[25122]: segfault at 0 ip 000000000061afe7 sp 00007ffc2df3d970 error 6 in php-cgi[400000+373000]

May 11 11:46:48 vps kernel: php-cgi[25221]: segfault at 7ff5e33d3d68 ip 00007ff5f9fe2410 sp 00007ffd181db3b0 error 4 in opcache.so[7ff5f9fcb000+2b000]

Sucede cuando menos tráfico tengo, cuando pasa la cpu full por las nubes, el sistema queda sordo apache no funciona, pasados unos minutos todo vuelve a la normalidad.

Ya no se que hacer, que me recomiendas??? gracias

 

Hola. 176 MB/s de escritura/lectura es un valor más que correcto. Lo que debes hacer es optimizar. Intenta seguir la guía de MySQL.

Salu2,

 

Hola,

Pasado 24Hrs....

PHP:

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 0h 3m 27s (2 q [0.000 qps], 157K conn, TX: 157B, RX: 116B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 7.5G
[--] Max MySQL memory    : 633.2M
[--] Other process memory: 1.3G
[--] Total buffers: 218.0M global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 363.7M (4.74% of installed RAM)
[OK] Maximum possible memory usage: 633.2M (8.24% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/2)
[OK] Highest usage of available connections: 35% (53/151)
[OK] Aborted connections: 0.28%  (435/157288)
[OK] Query cache efficiency: 100.0% (11M cached / 11M selects)
[!!] Query cache prunes per day: 1898451
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] No tmp tables created on disk
[OK] Thread cache hit rate: 99% (79 created / 157K connections)
[OK] Table cache hit rate: 100% (64 open / 0 opened)
[OK] Open file limit used: 0% (79/65K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 90.6% (7M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/57.2M
[OK] Read Key buffer hit rate: 99.6% (1B cached / 4M reads)
[OK] Write Key buffer hit rate: 99.8% (71M cached / 164K writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/90.7M
[!!] InnoDB Used buffer: 58.51% (4793 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 99.99% (57082064 hits/ 57085536 total)
[!!] InnoDB Write Log efficiency: 49.08% (20807 hits/ 42393 total)
[OK] InnoDB log waits: 0.00% (0 waits / 21586 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
    query_cache_size (> 64M)

indica ajustar a más tamaño o menos? gracias

 

Hola,

Yo casi que desactivaba query_cache_size y lo dejaba a 0 si tienes algún otro sistema de caché en el servidor como memcached etc.

Debes activar también las Slow Query Log
http://www.vichaunter.org/como-se-hace/como-habilitar-log-de-queries-lentas-en-mysql-o-mariadb

 

Sorry pero discrepo profundamente con utilizar configuraciones predefinidas encontradas en Google o incluso configuraciones de acuerdo a la cantidad de memoria de los servidores, de hecho revisé el link indicado mas arriba y podría hacer muchísimas observaciones a las configuraciones que ahí aparecen, pero "por definición" la configuración de los parámetros de MySQL depende básicamente del uso que se esta haciendo de un determinado servidor y eso está directamente relacionado con la cantidad y tipo de tablas, cantidad de registros, cantidad de queries, nivel de optimización, etc, etc y los recursos de hardware disponibles, donde los 2 puntos mas importantes son la velocidad del sistema de discos y la cantidad de RAM disponible.

Solo por mencionar un error "grave" del link ... sugieren 2 Gb para el buffer de InnoDB ( innodb_buffer_pool_size = 2048M ) en un sistema de 32 bits que soporta como máximo 2 Gb de RAM para MySQL ... con esa configuración podrías hasta saturar los 2 Gb disponibles y podrías no tener NINGUNA BD InnoDB !!!!!!!!!!!!!!!!!!!!!!!!!!!
La cantidad de RAM asignada al buffer InnoDB depende en primer lugar de si tienes tablas InnoDB o no ... y si asignas menos RAM que la requerida por las tablas InnoDB, podría ser suficiente para reducir significativamente el rendimiento de todo el sistema e incluso saturarlo al punto de hacerlo inoperante ... independiente de la cantidad de cores, cantidad total de RAM y velocidad de los discos.

Se nota que quien hizo esa página no tiene mucha idea que digamos ... por decirlo "suavemente"

Mirando mas detalladamente es super importante el nivel de indexación y la "calidad" de los índices utilizados, el rendimiento de una BD puede mejorar cientos o miles de veces solo por utilizar un índice mas adecuado.

Absolutamente de acuerdo, usar query_cache puede llegar a ser crítico para el rendimiento de MySQL y no tiene absolutamente nada que ver con memcached, de hecho uno de los puntos negativos de la configuración tiene justamente que ver con query_cache :

Query cache prunes per day: 1898451

Tener casi 1,9 millones de queries en 24 horas que debieron ser "borradas del cache" por no tener capacidad para poder almacenarlas significa que hay un pésimo aprovechamiento de la memoria y eso implica una baja apreciable del rendimiento.
Lo "ideal" sería que "Query cache prunes per day" tienda a cero ... 1,9 millones indica una mala configuración inmediatamente.

Otro punto pésimamente configurado :

Key buffer size / total MyISAM indexes: 8.0M/57.2M

Uno de los puntos mas críticos para optimizar las consultas tal como lo mencioné previamente es justamente el uso de los índices y si las tablas están ocupando 57,2 Mb de índices y solo hay configurados 8 Mb para ello, significa que la mayoría de las busquedas dentro de los índices no se están haciendo en memoria sino en disco y eso automáticamente implica que puede demorar varias veces mas ... teniendo en cuenta ademas que hay casi 7 Gb de RAM que "nunca" van a ser ocupados.

Otro punto que tambien es importante, estás usando MySQL 5.1 una versión que está obsoleta y discontinuada desde hace varios años, de hecho hasta MySQL 5.5 quedó obsoleto a fines del año 2015 ... la versión que deberías estar utilizando es MySQL 5.6 que tiene un ciclo de vida del 2013 al 2018.

 

@neocomp entiendo.

Para un webserver genérico con muchas webs alojadas creo que sería suficiente el MySQLTuner o el MySQL Tuning Primer. Por lo que tengo entendido el MySQL Tuning Primer mejor que el primero. Entiendo que si fuera una web y una sola app tocaría optimizar a pelo partiendo primero del script.

Para server genérico:



Me he estado mirando esta guía:



Salu2,