¿Cómo optimizar su base de datos MySQL?

Actualizado el 15 de agosto, 2016. Por BlueHosting.

La ejecución de MySQL en condiciones y ajustes óptimos para recursos específicos ayuda a manejar las cargas del servidor y a prevenir cualquier ralentización en el servidor. En general, después de optimizar su servidor web Apache para que maneje grandes cargas, es beneficioso optimizar MySQL para soportar conexiones adicionales. La optimización de bases de datos es un tema muy extenso, y esta guía cubre únicamente los conceptos básicos para editar la configuración de su MySQL.

Nota
Los pasos de este tutorial requieren que el usuario posea privilegios root en el servidor virtual privado. Asegúrese de ejecutar los pasos a continuación como root o usando el prefijo sudo.

Herramientas

Con el fin de determinar si su base de datos MySQL necesita ser reconfigurada, lo mejor es mirar cómo se están desempeñando sus recursos actualmente. Esto puede ser realizado con el comando top. Como mínimo, debe familiarizarse con el uso del CPU y de la memoria RAM de su servidor, los cuales pueden ser averiguados con estos comandos:

echo [PID]  [MEM]  [PATH] &&  ps aux | awk '{print , , }' | sort -k2rn | head -n 20
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -20

MySQLTuner

El script MySQLTuner evalúa su instalación de MySQL, y luego imprime sugerencias para incrementar el desempeño y estabilidad de su servidor.

  1. Para descargar MySQLTuner ejecute:

    wget http://mysqltuner.pl/ -O mysqltuner.pl

  2. Para ejecutar el programa ingrese el siguiente comando:

    perl mysqltuner.pl

  3. Se imprimirán sus resultados, a continuación la salida de nuestro ejemplo (su salida tendrá una estructura similar):

    >>  MySQLTuner 1.6.15 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: 
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 2K (Tables: 1)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 27d 8h 38m 36s (231 q [0.000 qps], 41 conn, TX: 59K, RX: 18K)
[--] Reads / Writes: 84% / 16%
[--] Binary logging is disabled
[--] Physical Memory     : 490.0M
[--] Max MySQL memory    : 449.2M
[--] Other process memory: 120.8M
[--] 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: 45.0M (9.18% of installed RAM)
[!!] Maximum possible memory usage: 449.2M (91.67% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/231)
[OK] Highest usage of available connections: 2% (4/151)
[!!] Aborted connections: 9.76%  (4/41)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 11 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 21% (16 on disk / 75 total)
[OK] Table cache hit rate: 44% (11 open / 25 opened)
[OK] Open file limit used: 2% (21/1K)
[OK] Table locks acquired immediately: 100% (92 immediate / 92 locks)

...

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (>= 8M)

Se observan recomendaciones muy útiles y específicas sobre nuestro MySQL. MySQLTuner ofrece sugerencias con respecto a cómo mejorar la base de datos. Si usted está pensando en actualizar su base de datos por su cuenta, explorar las sugerencias de MySQLTuner es una de las maneras más seguras de mejorar el desempaño de su base de datos.

Optimizar MySQL

Cuando altere la configuración MySQL, tenga cuidado de los cambios y de cómo pueden afectar su base de datos. Incluso al seguir las instrucciones de programas como MySQLTuner, lo mejor es tener alguna comprensión del proceso.

El archivo que debe cambiar suele estar ubicado en /etc/mysql/my.cnf o en /etc/my.cnf (también puede estar en otra ubicación bajo /etc).

Buenas prácticas

  1. Antes de actualizar u optimizar la configuración de MySQL, cree un respaldo del archivo my.cnf:

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

  2. Las buenas prácticas sugieren hacer pequeños cambios uno a la vez, y supervisar el servidor después de cada cambio. Después de cada cambio reinicie MySQL.
    Para Debian/Ubuntu ejecute:

    service mysql restart

    Para CentOS/Fedora:

    /etc/init.d/mysqld restart

    Cuando cambie los valores en el archivo my.cnf asegúrese de que la línea que está cambiando no esté comentada con el prefijo numeral -o almohadilla- (#).

Variables importantes a considerar

key_buffer

Cambiar el valor de key_buffer asigna más memoria a MySQL, lo que puede acelerar sus bases de datos, asumiendo que tiene memoria libre. El tamaño de key_buffer, en general, no debe tomar más del 25% de la memoria del sistema cuando se usa el motor de tablas MyISAM (mecanismo por defecto de MySQL), y hasta 70% cuando se usa el motor InnoDB. Si se establece un valor demasiado alto, se gastan recursos. De acuerdo con la documentación de MySQL, para servidores con 256 MB de memoria RAM (o más) con muchas tablas, se recomienda una configuración de 64M. Mientras que para servidores con 128MB de memoria RAM, o menos, las tablas pueden ser configuradas a 16M, el valor por defecto. Los sitios web que tengan aun menos recursos y tablas, pueden establecer este valor más bajo.

max_allowed_packet

Es el tamaño máximo permitido para el envío de un paquete. Un paquete es un único estado SQL, una única fila siendo enviada a un cliente, o un registro (log) desde un maestro a un esclavo. Si usted sabe que su servidor MySQL va a estar procesando grandes paquetes, lo mejor es aumentar este valor al tamaño de su paquete más grande. En caso de que este valor sea demasiado bajo, usted recibiría un error en su registro de errores (logs).

thread_stack

Este valor contiene el tamaño de la pila para cada hilo. MySQL considera que el valor de la variable thread_stack variable es suficiente para los usos normales; sin embargo, en caso de obtener errores en sus logs que se relacionen con el thread_stack entonces puede aumentar su valor.

thread_cache_size

Si thread_cache_size está "apagado" (configurado en 0), entonces todas las nuevas conexiones que se estén haciendo requieren que se cree un nuevo hilo para ellas, y cuando las conexiones se desconectan, el hilo es destruido. De otro modo, este valor establece el número de hilos sin uso para almacenar en caché hasta que necesiten ser utilizados para una conexión. Generalmente esta configuración tiene poco efecto en el desempeño, a menos que reciba cientos de conexiones por minuto, en cuyo caso este valor debe ser aumentado para que la mayoría de las conexiones sean hechas en hilos en caché.

max_connections

Este valor configura la cantidad máxima de conexiones concurrentes. Lo mejor es considerar la cantidad máxima de conexiones que ha tenido en el pasado y luego configurar este valor de manera que haya algún búfer entre ese número y el valor de max_connections. Note que esto no representa la cantidad máxima de usuarios en su sitio web a la vez, sino la cantidad máxima de usuarios que hacen solicitudes concurrentemente.

table_cache

Debe ser mantenido por encima del valor de su open_tables. Para determinar este valor puede utilizar:

SHOW STATUS LIKE 'open%';

¡Ya tiene los conocimientos básicos para comenzar la optimización de su servidor MySQL!, recuerde seguir las buenas prácticas para evitar inconvenientes.

Recursos adicionales

Puede consultar los siguientes recursos en busca de información adicional con respecto a este tema. Aunque este material es provisto esperando que sea útil, tome en cuenta que no podemos dar fe de la actualidad o precisión de los contenidos externos.

¿QUÉ DESEAS SABER?

Intentaremos leer tu mente...