Diez parámetros de ajuste de rendimiento de MySQL después de la instalación

Nota: ¡Este blog ha sido actualizado a MySQL 5.7 aquí!

En este blog, analizaremos los diez principales parámetros de ajuste de rendimiento de MySQL que puede implementar después de una instalación.

Cuando nos asociamos para una auditoría de rendimiento de MySQL, planeamos revisar la configuración de MySQL y sugerir mejoras. Muchas personas se sorprenden porque, en la mayoría de los casos, solo sugerimos cambiar algunas configuraciones de ajuste de rendimiento de MySQL después de la instalación, incluso si hay cientos de opciones disponibles. El propósito de esta publicación es brindarle una lista de algunos de los parámetros más críticos.

Ya hicimos tales sugerencias en el pasado aquí en este blog hace unos años, ¡pero las cosas han cambiado mucho en el mundo de MySQL desde entonces!

Antes de comenzar a ajustar el rendimiento de MySQL:

Incluso las personas con experiencia pueden cometer errores que pueden causar muchos problemas. Entonces, antes de aplicar ciegamente los consejos de esta publicación, tenga en cuenta lo siguiente:

  • ¡Cambie una configuración a la vez! Esta es la única manera de estimar si un cambio es beneficioso.
  • La mayoría de los parámetros se pueden cambiar en tiempo de ejecución SET GLOBAL. Es muy útil y le permite revertir rápidamente el cambio si crea algún problema. Pero al final, desea que el parámetro se agregue permanentemente al archivo de configuración.
  • ¿Un cambio de configuración no es visible incluso después de reiniciar MySQL? ¿Utilizó el archivo de configuración correcto? ¿Pusiste el parámetro en la sección correcta? (Todos los parámetros en esta publicación pertenecen al [mysqld] sección)
  • El servidor se negó a iniciarse después de un cambio: ¿usó la unidad correcta? Por ejemplo, innodb_buffer_pool_size debe establecerse en bytes mientras max_connection es adimensional.
  • No permita parámetros duplicados en el archivo de configuración. Si desea realizar un seguimiento de los cambios, utilice el control de versiones.
  • No haga matemáticas ingenuas, como «mi nuevo servidor tiene 2x RAM, solo haré todos los valores 2x anteriores».

Parametros basicos

Aquí hay 3 parámetros de ajuste de rendimiento de MySQL que siempre debe tener en cuenta. Si no lo hace, es muy probable que se meta en problemas muy rápidamente.

innodb_buffer_pool_size: Esta es la configuración número 1 que debe buscar para cualquier instalación con InnoDB. El grupo de búfer es donde se almacenan en caché los datos y los índices: tener la mayor cantidad posible garantizará que utilice la memoria y no los discos para la mayoría de las operaciones de lectura. Los valores típicos son 5-6 GB (8 GB de RAM), 20-25 GB (32 GB de RAM), 100-120 GB (128 GB de RAM).

innodb_log_file_size: este es el tamaño de los registros de rehacer. Los registros de rehacer se utilizan para garantizar que los scripts sean rápidos y duraderos e incluso durante la recuperación de errores. Hasta MySQL 5.1, era difícil de ajustar, ya que desea tanto registros de rehacer grandes para un buen rendimiento como registros de rehacer pequeños para una recuperación rápida de fallas. Afortunadamente, el rendimiento de la recuperación de fallas ha mejorado mucho desde MySQL 5.5, por lo que ahora puede tener un buen rendimiento de escritura y una rápida recuperación de fallas. Hasta MySQL 5.5, el tamaño del registro de red total estaba limitado a 4 GB (el valor predeterminado es tener 2 archivos de registro). Esto ha sido actualizado a MySQL 5.6.

Empezando con innodb_log_file_size = 512M (Da 1 GB de registros de rehacer) debería dar mucho espacio para escribir. Si sabe que su aplicación utiliza muchas secuencias de comandos y utiliza MySQL 5.6, puede comenzar innodb_log_file_size = 4G.

max_connections: Si a menudo experimenta el error «Demasiadas conexiones», max_connections es demasiado bajo. Es muy común que debido a que la aplicación no cierra correctamente las conexiones a la base de datos, necesite más de 151 conexiones predeterminadas. El principal inconveniente de los valores altos para max_connections (como 1000 o más) es que el servidor dejará de responder si por cualquier motivo ejecutará 1000 o más transacciones activas. El uso de un grupo de conexiones a nivel de aplicación o un grupo de cables a nivel de MySQL puede ayudar.

Parámetros de InnoDB

InnoDB ha sido el motor de almacenamiento predeterminado de MySQL 5.5 y es mucho más común que cualquier otro motor de almacenamiento. Es por eso que debe configurarse con cuidado.

innodb_file_per_table: Este parámetro le dirá a InnoDB si almacenar datos e índices en el espacio de tabla compartido (innodb_file_per_table = OFF) o en un archivo .ibd separado para cada tabla (innodb_file_per_table= ON). Tener un archivo por tabla le permite recuperar espacio al dejar, truncar o reconstruir una tabla. También es necesario para algunas funciones avanzadas, como la compresión. Sin embargo, no proporciona beneficios de rendimiento. El escenario principal cuando no desea un archivo por tabla es cuando tiene una gran cantidad de tablas (digamos 10k +).

Con MySQL 5.6, el valor predeterminado es ON, por lo que no tiene nada que hacer en la mayoría de los casos. Para versiones anteriores, debe activarlo antes de cargar los datos, ya que solo tiene efecto en las tablas recién creadas.

innodb_flush_log_at_trx_commit: La configuración predeterminada de 1 significa que InnoDB es completamente ACID. Es mejor cuando su principal preocupación es la seguridad de los datos, por ejemplo, sobre un maestro. Sin embargo, puede tener una sobrecarga significativa en los sistemas con discos lentos debido a las sincronizaciones adicionales que se necesitan para eliminar cualquier cambio en los registros de rehacer. Poner 2 es un poco menos confiable, porque las transacciones ocupadas estarán expuestas a registros de rehacer solo una vez por segundo, pero eso puede ser aceptable en algunas situaciones para un maestro y definitivamente es un buen valor para una réplica. 0 es aún más rápido, pero es más probable que pierda algunos datos en caso de un bloqueo: es simplemente un buen valor para una réplica.

innodb_flush_método: este parámetro controla cómo se muestran los datos y los registros en el disco. Los valores populares son O_DIRECT cuando tiene un controlador RAID de hardware con caché de escritura protegido por batería y fdatasync (predeterminado) para la mayoría de los otros escenarios. sysbench es una buena herramienta para ayudarlo a elegir entre 2 valores.

innodb_log_buffer_size: este es el tamaño del búfer para las transacciones que aún no han estado ocupadas. El valor predeterminado (1 MB) suele estar bien, pero tan pronto como realice transacciones con campos de blob/texto grandes, el búfer puede llenarse muy rápidamente y desencadenar una carga de E/S adicional. Mire la variable de estado Innodb_log_waits y si no es 0, auméntelo innodb_log_buffer_size.

Otros parámetros

query_cache_size: La caché de consultas es un cuello de botella bien conocido que se puede ver incluso cuando la competencia es moderada. La mejor opción es deshabilitarlo desde el día 1 para la configuración query_cache_size = 0 (ahora el predeterminado en MySQL 5.6) y usar otras formas de acelerar las solicitudes de lectura: buena indexación, agregar réplicas para distribuir la carga de lectura o usar un caché externo (memcache o redis, por ejemplo). Si ya ha creado su aplicación MySQL con el caché de consultas habilitado y nunca ha tenido ningún problema, el caché de consultas puede ser beneficioso para usted. Entonces tienes que tener cuidado si decides apagarlo.

log_bin: Se requiere la activación del registro binario si desea que el servidor actúe como maestro de replicación. Si es así, no olvides armarlo. server_id en un solo valor. También es útil para un solo servidor cuando desea poder realizar una recuperación oportuna: restaurar su última copia de seguridad y aplicar registros binarios. Una vez creados, los archivos de registro binarios se guardan para siempre. Entonces, si no desea quedarse sin espacio en disco, debe limpiar los archivos antiguos con él. PURGAR REGISTROS BINARIOS o establecer expire_logs_days para especificar después de cuántos días los registros se eliminarán automáticamente.

El registro binario, sin embargo, no es gratuito, por lo que si no necesitas, por ejemplo, una réplica que no sea maestra, es recomendable seguir desactivándola.

skip_name_resolve: cuando un cliente se conecta, el servidor ejecutará la resolución del nombre de host, y cuando el DNS es lento, el establecimiento de la conexión también será lento. Por lo tanto, es recomendable iniciar el servidor con skip-name-resolve para deshabilitar todas las búsquedas de DNS. La única limitación es que el GRANT Las declaraciones solo deben usar direcciones IP, así que tenga cuidado al agregar este parámetro a un sistema existente.

Conclusiones

Por supuesto, existen otros parámetros que pueden marcar la diferencia según su carga de trabajo o su hardware: poca memoria y discos rápidos, alta concurrencia, cargas de trabajo intensivas de escritura, por ejemplo, son casi cuando necesita un ajuste específico. Sin embargo, el objetivo aquí es brindarle algunos ajustes de rendimiento de MySQL para permitirle obtener rápidamente una configuración completa de MySQL sin perder demasiado tiempo cambiando parámetros de MySQL no esenciales o leyendo la documentación para comprender qué parámetros son importantes para usted.

Más recursos:

Correo

seminarios web

Presentaciones

Libros electrónicos gratuitos

Instrumentos


Descargue nuestro nuevo documento técnico hoy para conocer el costo real del tiempo de inactividad y cómo evitar pérdidas potenciales causadas por una configuración de infraestructura y base de datos mal configurada.

Descargar PDF

Author: Ing. Luis

A lo largo de conocer Windows y otros sistemas operativos me eh encontrado con diversos tipos de error, ahora brindo soluciones según mi experiencia-

Deja un comentario