Configurar Servidor MySQL / MariaDB (para alto rendimiento)

NUESTRO SITIO necesita la publicidad para costear hosting y el dominio. Por favor considera deshabilitar tu AdBlock en nuestro sitio. También puedes hacernos una donación entrando en linuxparty.es, en la columna de la derecha.

Configurar Servidor MySQL / MariaDB (para alto rendimiento)

Publicado: 28 Noviembre 2016 | Escrito por Javier Orovengua | Correo electrónico | Visto: 15308

Ratio: 5 / 5

Para empezar, se puede comparar el archivo my.cnf contra la versión Q&A que está disponible de forma gratuita a través de Percona. Es ésta una solución ideal? No, pero le permitirá tomar una nueva mirada acerca de su archivo de configuración después de que responda a todas sus preguntas, a través de su asistente de configuración.

https://tools.percona.com/wizard

innodb_buffer_pool_size –

select @@innodb_buffer_pool_size;

Ajustar innodb_buffer_pool_size es, con mucho, uno de los lugares más importantes para un MySQL InnoDB database. Algunos buenas artículos sobre este tema es el siguiente:

Con respecto al fichero /etc/my.cnf, aquí te presento dos ejemplos, pero recuerda que esto es configurable según se requiera.

[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

innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=4M
innodb_additional_mem_pool_size=1000K
innodb_log_buffer_size=1000K
innodb_thread_concurrency=4
log=/var/log/mysql/mysql.log

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet   =20M
query_cache_size     =32M
# skip-character-set-client-handshake
# innodb_log_file_size = 268435456

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

# Currently, there are mariadb and community-mysql packages in Fedora.
# This particular config file is included in respective RPMs of both of them,
# so the following settings are general and will be also used by both of them.
# Otherwise the RPMs would be in conflict.
# Settings for particular implementations like MariaDB are then
# defined in appropriate sections; for MariaDB server in [mariadb] section in
# /etc/my.cnf.d/server.cnf (part of mariadb-server).
# It doesn't matter that we set these settings only for [mysqld] here,
# because they will be read and used in mysqld_safe as well.
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld_safe]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Es necesario tener en cuenta que no existe una "talla única de consulta" para el ajuste del innodb_buffer_pool_size. Tenemos algunas pautas y sugerencias que se han surgido en los últimos años (ver a continuación) y también tenemos varias teorías diferentes acerca de las mejores opciones.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A; SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables WHERE engine='InnoDB') A, (SELECT 3 PowerOf1024) B;

Es seguro decir que si usted tiene la serie 8M defecto como la variable innodb_buffer_pool_size entonces este debe ser uno de los primeros parámetros que ajustar.

Básicamente, permitir que esto pueda usar toda la memoria disponible, es un lujo que tal vez se pueda dar. Esto por supuesto tiene en cuenta varios factores, como ¿Cuánta memoria más necesita asignar en el servidor? Ten en cuenta que el disco I / O es importante para el rendimiento y a más memoria permite que esta variable use menos el disco I / O que se debería exigir para el acceso a la tabla.

Consideremos también esto ....
Como una recomendación general es establecer la innodb_log_file_size a 25% del tamaño del buffer que también debemos evaluar para innodb_log_file_size por conceptos del barón y luego mirar para ver cómo se relaciona con el innodb_buffer_pool_size.

Por ejemplo, siguiendo la lógica de De Barón:

####

 > show engine innodb status\G select sleep(60); show engine innodb status\G
 Log sequence number 3982683217
 1 row in set (0.01 sec)
 1 row in set (59.99 sec)

 Log sequence number 3991367755
 1 row in set (0.01 sec)
 > SET @sequence1=3982683217;
 Query OK, 0 rows affected (0.00 sec)
 > SET @sequence2=3991367755;
 Query OK, 0 rows affected (0.00 sec)
 > select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;

+----------------+
 | MB_per_min |
+----------------+
 | 8.28222084  |
+----------------+

1 row in set (0.00 sec)
> select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;

+-------------------+
 | MB_per_hour  |
+-------------------+
 | 496.93325043 |
+-------------------+

1 row in set (0.00 sec)
> select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;

+-----------------------+
| estimated_buffer_pool |
+-----------------------+
|         1987.73300171 |
+-----------------------+
1 row in set (0.00 sec)
> select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;
+--------------------------+
| estimated_buffer_pool_GB |
+--------------------------+
|           1.941145509481 |
+--------------------------+
1 row in set (0.00 sec)

####

Ahora, cuando se comparan estos resultados con las "directrices"

####

SELECT CONCAT(ROUND(KBS/POWER(1024,
    -> IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
    -> SUBSTR(' KMG',IF(PowerOf1024<0,0,
    -> IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
    -> FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
    -> WHERE engine='InnoDB') A,
    -> (SELECT 3 PowerOf1024) B;

+-------------------------------------+
| recommended_innodb_buffer_pool_size |
+-------------------------------------+
| 1G                                  |
+-------------------------------------+

####

Obtendrá un resultado diferente. Mientras que comprueba que Baron depende del período de tiempo que se ejecuta dentro (es por eso que usted debe comprobar esto durante picos de tráfico) se le puede dar una visión más realista de su tráfico y uso. Me gustaría ver en la creación del innodb_buffer_pool_size a 2G no 1G en este sencillo ejemplo.

 SET @sequence1=3982683217;
 SET @sequence2=3991367755;
 select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;
 select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;
 select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;
 select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;

innodb_log_file_size–

 select @@innodb_log_file_size;

He descubierto que me gustan los conceptos del barón sobre cómo configurar el innodb_log_file_size. Recuerde que debe ejecutar sus sugerencias durante las horas punta para obtener lecturas reales. Cuanto mayor sea el tamaño de estos archivos, el rendimiento mejora con grandes conjuntos de datos, pero nada es gratis, aumentará los tiempos de recuperación. El aumento de los tiempos de recuperación no puede sonar como una gran preocupación para algunos, hasta que sea una base de datos de la que dependen sus ingresos y estará esperando y vigilando siempre el proceso de observación. Una recomendación general es situarlo en el 25% del tamaño del buffer.

select ( @@innodb_buffer_pool_size * .25 )

innodb_log_buffer_size  –

select @@innodb_log_buffer_size;

Recuerdo que una vez tuve este conjunto en un servidor para innodb_log_buffer_size = 128M.¿Fue una buena elección? ¿Significaba que quería más memoria cuando probablemente no tenía que hacerlo?. Era una base de datos muy pesada de escribir, pero esta opción es muy probable que sea muy alta. Centrarse en los valores predeterminados primero y luego duplicarlo (8 MB - 16 MB) max.

innodb_additional_mem_pool_size–

select @@innodb_additional_mem_pool_size;

Pedro se dirige a esta configuración en su blog . Usted puede tratar y evaluar las opciones de configuración después de que otros se han tratado en mi opinión.

innodb_flush_log_at_trx_commit -

select @@innodb_flush_log_at_trx_commit;


> Set GLOBAL innodb_flush_log_at_trx_commit = 2;

Pedro se dirige a esta configuración en su blog también. Se trata de una mejora en el rendimiento de pruebas variables y el valor comúnmente pasado por alto por el ajuste 0-2.Tenga en cuenta los riesgos.

thread_cache -

select @@thread_cache_size;
+---------------------+
| @@thread_cache_size |
+---------------------+
|                  50 |
+---------------------+

http://dev.mysql.com/doc/refman/5.5/en/connection-threads.html

>show status like 'threads_created';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created |     4 |
+-----------------+-------+
1 row in set (0.00 sec)

> show status like 'connections';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 962   |
+---------------+-------+
1 row in set (0.00 sec)

> SELECT 100 - ((4 / 962) * 100);

+-------------------------+
| 100 - ((4 / 962) * 100) |
+-------------------------+
|                 99.5842 |
+-------------------------+

Mantenga un ojo en la misma situación: mostrar el estado como "Threads_created ';

Si sube, entonces tengo configurado demasiado bajo y tiene que ser elevado.

query_cache_size -

select ((@@query_cache_size / 1024) / 1024);

Los valores permitidos son múltiplos de 1.024.

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html # sysvar_query_cache_size

Preste atención a este ajuste, leer más depende de lo que haga la aplicación. Yo solía correr con los ajustes key_buffer = 16M y probablemente debería haberla doblado.

key_buffer_size -

select @@key_buffer_size;

Con el cambio a MySQL InnoDB como el motor de almacenamiento por defecto pudimos ver cada vez menos las tablas creadas como MyISAM. Eso no es un hecho. El key_buffer_size será muy importante para usted si utilizas tablas MyISAM.

Revisión de Shlomi Noaj mensaje para ayudarle a encontrar lo que fácilmente las tablas que tipo de motor.
"Ver al tamaño de la tabla (casi exactamente como se presenta en INFORMATION_SCHEMA)"- Shlomi Noaj.

####

> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name   |    Value |
+-----------------+----------+
| key_buffer_size | 15728640 |
+-----------------+----------+ 

####
table_cache –
show variables like '%table%cache%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_definition_cache     | 4096  |
| table_open_cache           | 4096  |
| table_open_cache_instances | 1     |
+----------------------------+-------+
####

Según el manual es una buena fórmula para ayudar a determinar latable_definition_cache tamaño.

SELECT 400 + (@@table_open_cache / 2); 
> SHOW status like '%Opened_tables%';

table_open_cache_instances
Un valor de 8 o 16 se recomienda en sistemas que utilizan habitualmente 16 o más núcleos, el valor predeterminado es 1.

Esperemos que la exposición de estas variables y los ajustes le ayuden a obtener lo mejor rendimiento de su base de datos MySQL / MariaDB.