Sugerencias de mysqltuner y cambios en my.cnf

11 minutos de lectura

avatar de usuario
markratledge

Tuve esta pregunta en Serverfault durante unos días sin suerte.

Ejecuté mysqltuner.pl en un VPS y tengo muchas preguntas sobre las sugerencias sobre las variables a cambiar. Estoy seguro de que estas son preguntas generales con respuestas complejas.

No tengo los conocimientos suficientes para escribir consultas y probarlas en el servidor, pero solo estoy tratando de obtener un poco más de rendimiento del servidor que ejecuta cinco sitios de WordPress con más de 200 000 páginas vistas al mes.

He optimizado la base de datos a través de phpmyadmin (y lo hago regularmente), pero el sintonizador todavía dice que hay tablas fragmentadas. Y debido a que esto es WordPress, no puedo cambiar las consultas en el código central.

Pero, ¿cuánto debo aumentar las variables como query_cache_size e innodb_buffer_pool_size? ¿Qué pasa con las otras variables de innodb?

Algunas de las variables sugeridas no existen en my.cnf, como table_cache, y están marcadas en el informe del sintonizador, etc. ¿Puedo agregarlas a my.cnf?

(¿Y por qué este bloque está duplicado en my.cnf? ¿Puedo eliminar el duplicado?)

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

A continuación se muestra my.cnf y la salida de mysqltuner:

Contenido de my.cnf:

query-cache-type = 1
query-cache-size = 8M

set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

old_passwords=1

skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

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

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

Salida de mysqltuner:

------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 133M (Tables: 637)
[--] Data in InnoDB tables: 10M (Tables: 344)
[--] Data in MEMORY tables: 126K (Tables: 2)
[!!] Total fragmented tables: 69

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 6h 24m 13s (2M q [22.135 qps], 116K conn, TX: 4B, RX: 530M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 35.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 303.7M (8% of installed RAM)
[OK] Slow queries: 0% (4/2M)
[OK] Highest usage of available connections: 53% (53/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/46.1M
[OK] Key buffer hit rate: 99.6% (749M cached / 2M reads)
[OK] Query cache efficiency: 32.2% (685K cached / 2M selects)
[!!] Query cache prunes per day: 948863
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 660K sorts)
[!!] Temporary tables created on disk: 46% (400K on disk / 869K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 24K opened)
[OK] Open file limit used: 10% (109/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] InnoDB data size / buffer pool: 10.6M/2.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 8M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 10M)

avatar de usuario
Fuente Tim

Haré todo lo posible para ayudar aquí. El informe de MysqlTuner implica que tiene 4 GB de RAM en este VPS, por lo que mis sugerencias se basan en eso.

tamaño_caché_consulta – Esta es la cantidad de RAM que MySQL puede usar para almacenar en caché los resultados de las consultas de la base de datos. Los resultados almacenados en la caché de consultas se devuelven mucho más rápido que las selecciones normales, por lo que esta variable puede acelerar significativamente las cosas (más que cualquiera de los otros cambios sugeridos).

Exactamente cuál es el valor correcto para usted requerirá algo de experimentación. Actualmente tiene este conjunto en 8M. Si tiene 4GB de RAM en este cuadro, comenzaría con 64M, aumentando a 128M y luego a 256M si es necesario. Después de cada cambio, deje las cosas durante unos días y luego ejecute MysqlTuner nuevamente y compare el porcentaje de ‘Eficiencia de caché de consultas’ con el que tenía antes. Para un servidor que aloja principalmente 5 blogs de WordPress, dudo que vea alguna mejora más allá de 256M, y no recomendaría ir más allá de una octava parte de su RAM total.

Personalmente, encuentro que Munin (una herramienta de monitoreo de servidor gratuita) es bastante útil para vigilar este tipo de cosas, ya que graficará los aciertos de caché frente a otras consultas.

tmp_table_size – para algunas consultas complejas (particularmente aquellas que usan GROUP BY o clasificación compleja), MySQL primero necesita crear una tabla temporal que contenga los datos y luego ejecutar algunas operaciones en ella para crear el conjunto de resultados. Intentará crear estas tablas temporales en la memoria, ya que es mucho más rápido que crearlas en el disco; pero para grandes conjuntos de resultados esto no siempre es posible. tmp_table_size controla este umbral.

No puedo imaginar que WordPress esté haciendo consultas enormemente complejas, así que no me excedería con esta. MysqlTuner sugiere un valor superior a 32 MB, así que comience con 64 MB y vea cómo esto afecta el valor de ‘Tablas temporales creadas en el disco’ después de unos días. Establezca max_heap_table_size mientras lo hace, como sugiere.

thread_cache_size – WordPress no usa conexiones persistentes de forma predeterminada (lo cual es bueno), por lo que cada solicitud realiza una nueva conexión a su base de datos y luego la cierra una vez que se ha generado la página. Esta sobrecarga no es significativa, pero el uso de thread_cache_size le permite a MySQL reutilizar estos hilos de conexión, lo que ayudará un poco.

Iría con el valor sugerido de 4 que imagino que estará bien a menos que obtenga una gran cantidad de usuarios simultáneos.

table_cache – Estoy un poco confuso en esto, parece estar relacionado con el caché de estructura de tablas de MySQL. Iría con 128 para esto.

innodb_buffer_pool_size – esta es la cantidad de memoria que MySQL puede usar para almacenar en caché índices y datos para las tablas de InnoDB. Este me desconcierta un poco, ya que no creo que WordPress use InnoDB en absoluto. ¿Tienes otros sitios en este servidor también?

Para responder a sus otras preguntas, la configuración después de [mysqld_safe] solo se aplica al demonio MySQL en modo seguro, en lugar de MySQL en general, por eso algunas de las variables están duplicadas. Si cambia innodb_buffer_pool_size, querrá cambiar el primero. Las variables que no están en el archivo las puede agregar, sí, pero agréguelas arriba del [mysqld_safe] bloqueo por la misma razón.

Por último, dado que está de humor para optimizar, si aún no está utilizando un caché de código de bytes de PHP como APC entonces vale la pena explorar esto. APC puede brindar algunas mejoras significativas en la velocidad de las aplicaciones PHP sin ningún efecto negativo.

  • Gracias… Repasaré todo esto, probaré esos cambios y probaré Munin. El servidor tiene ioncube instalado.

    – markratledge

    6 oct 2010 a las 19:35


  • Hola… ¿Tengo que poner join_buffer_size >= 128 K en my.cnf? ¿Esto funciona?

    – Raúl Chiarella

    3 sep 2021 a las 20:05

avatar de usuario
cris

Existen más herramientas para ajustar su base de datos mysql: http://www.day32.com/MySQL/
y http://www.maatkit.org/doc/ y http://hackmysql.com/mysqlsla

En la mayoría de los casos, no es necesario escribir consultas y probarlas en el servidor. Simplemente habilite el registro de consultas lentas para identificar sus consultas lentas, agréguelas con mysqlsla y explíquelas con maatkit:

Puede pegar las consultas más lentas de los resultados de mysqla en un archivo de texto y ejecutarlas con maatkit.

mk-visual-explain –host hostname –user username –password passwort –database \
databasename -c query1.sql >> query1_data.txt

mk-query-profiler –host hostname –user username –password passwort –database \
databasename query1_data.txt >> query1_data.txt

A menudo, elegir una versión más nueva de mysql es fundamental para el rendimiento. Experimenté que los planes de ejecución para consultas complejas son muy diferentes cuando comparas, por ejemplo, mysql 5.0.23 con 5.1.4. Se ejecutan en nuestro entorno mucho más rápido con 5.1.4.

Se puede encontrar mucha información útil sobre mysql en http://www.mysqlperformanceblog.com/ y en el libro “MySQL de alto rendimiento”.

Caché de tabulación:
Según el libro, “el caché de la tabla almacena objetos que representan tablas. Cada objeto en el caché contiene el archivo .frm analizado de la tabla asociada más otros datos, según el motor de almacenamiento de la tabla.

El diseño de la caché de tablas está un poco centrado en MyISAM: esta es una de las áreas donde la separación entre el servidor y el motor de almacenamiento no está completamente limpia, por razones históricas. La caché de tablas es un poco menos importante para InnoDB, porque InnoDB no depende de ella para muchos propósitos (como almacenar descriptores de archivos; tiene su propia versión de caché de tablas para este propósito). Sin embargo, incluso InnoDB se beneficia del almacenamiento en caché de los archivos .frm analizados”.

Si eleva el caché de la tabla, puede haber errores con el límite de archivos abiertos. También necesita aumentar la variable open_files_limit en el servidor y quizás el límite de archivos abiertos del sistema operativo: http://www.cyberciti.biz/faq/linux-aumenta-el-número-máximo-de-archivos-abiertos/.

Caché de subprocesos:

La memoria caché de subprocesos contiene los subprocesos que no están asociados actualmente con una conexión pero que están listos para atender nuevas conexiones. Mientras MySQL tenga un subproceso libre en el caché, puede responder muy rápidamente a las solicitudes de conexión, porque no tiene que crear un nuevo subproceso para cada conexión.

[!!] Tablas temporales creadas en disco: 46% (400K en disco / 869K total)
Si tmp_table_size y max_heap_table_size aún no están configurados, auméntelos. Las operaciones de disco son muy lentas en comparación con las operaciones de RAM. ¿Wordpress usa muchas columnas de blob/texto? entonces no verá muchos beneficios, porque las columnas BLOB y Text no están permitidas en las tablas de memoria.

[OK] Mayor uso de conexiones disponibles: 53% (53/100)
Para ahorrar RAM, puede disminuir las conexiones máximas permitidas. Por otro lado, es posible que te quedes sin conexiones en las horas punta.

¡Usar un caché de código de operación para PHP es una muy buena idea!

Mis recomendaciones para optimizar MySQL para WP:

Las tablas de la base de datos deben optimizarse periódicamente (y repararse si es necesario) para un rendimiento óptimo.

recomiendo usar Administrador de WP-DB complemento que proporciona esta funcionalidad, así como la copia de seguridad de la base de datos, todo crucial para la instalación de cualquier blog.

WP-DBManager le permite programar y olvidar, y se encargará de todo el trabajo automáticamente.

Otra alternativa es optimizar y reparar manualmente su tabla a través de una herramienta como phpmyadmin.

MySQL Query Cache guarda los resultados de las consultas en caso de que la consulta vuelva a aparecer. Sin embargo, solo sabe cómo guardar el texto de bytes de las consultas, no sus versiones compiladas, por lo que pequeños cambios en la consulta crearán diferentes entradas de caché. Actívelo si no tiene identificadores únicos en cada consulta. Puede habilitarlo agregando lo siguiente a /etc/my.cnf:

query_cache_type = 1
query_cache_size = 26214400

  • Menciono que optimizo/reparo con phpmydmin, y las copias de seguridad no son un problema aquí. Mi pregunta principal tiene que ver con las variables que no están actualmente en my.cnf pero que el informe recomienda.

    – markratledge

    29 de septiembre de 2010 a las 21:28

Esta no es una respuesta directa a su pregunta, pero en mi experiencia wordpress puede ser muy bien optimizado utilizando el almacenamiento en caché en los servidores frontend. También, en su mayoría, wordpress parece estar vinculado a la CPU en las máquinas frontend, no en la base de datos. (Es posible que desee verificar que, de hecho, está optimizando el cuello de botella).

Eche un vistazo a “caché total w3”, por ejemplo. Usarlo con APC debería ser el enfoque más simple. Asegúrese de echar un vistazo al tamaño de apc.shm_size (en php.ini) y la proporción de aciertos de caché (se debe proporcionar alguna utilidad de información de apc con w3tc).

He visto instancias de wordpress funcionando sin problemas en un solo servidor con esa configuración y mucho más de 200,000 páginas vistas por mes.

¿Ha sido útil esta solución?