Hablando en el caso de afinar y optimizar un Blog basado en WordPress ya que seran mas la cantidad de lecturas o visitas que el hit al rendimiento por ingreso de informacion, por este hecho es necesario optimizar nuestro servidor MySQL para la lectura. La optimización del rendimiento de lectura es cuestión de cambiar el tamaño del buffer para hacer uso de la memoria disponible en el servidor. Existen básicamente dos tipos diferentes de buffers: los dedicados a las conexiones y los utilizados a nivel global por todas las conexiones.
Key Buffer
El key buffer almacena los índices de la base de datos en la memoria. Este buffer debe ser lo suficientemente grande como para mantener todos los índices utilizados nuestros servicios. Esto debería estar en el rango de cientos de megabytes. Sitios con grandes cantidades de datos requieren buffers mayores. Para asignar un buffer de 500MB:
key_buffer = 500M key_buffer = 500M
Para encontrar un valor adecuado para key buffer, investigen las variables de estado key_read_requests y Key_reads. El key_read_requests es el número total de solicitudes clave servidas desde el cachí©, mientras que el Key_reads muestra el número de veces que MySQL ha tenido que accesar el sistema de archivos para buscar las llaves.
Cuanto menor sea el número de Key_reads mejor. Entre mas memoria tengamos asignada al key buffer más solicitudes seran servidas desde la memoria cachí©.
Siempre habrá algunas llaves que deben ser leídas desde el disco (por ejemplo, cuando los datos han cambido), por lo que el valor nunca será cero. Al comparar los dos valores veran el ratio de í©xito del key buffer. El key_read_requests debería ser mucho más grande que el Key_reads.
Un 99% de solicitudes en cachí© es un buen número dentro de un ambiente de lectura intensivo.
Cache de Tabla
El table_cache de MySQL dice cuántas tablas se pueden tener abiertos en cualquier momento. En consultas SQL, típicamente varias tablas estan unidas. La regla general es que deben multiplicar el número máximo de conexiones (que se describe a continuación) por el número máximo de tablas utilizadas en joins.
Por ejemplo, si el número máximo de conexiones se establece en 400, el cachí© de la tabla debe ser de al menos 400 * 10. La opción de configuración a continuación muestra una tabla de cachí© de 4000:
table_cache = 4000
Ordenar buffers
MySQL ordena los resultados de una consulta antes de que sean devueltos. El sort buffer es por conexión, por lo que se debe multiplicar el tamaño del sort buffer por el número máximo de conexiones para predecir los requisitos de memoria del servidor.
En nuestro caso usamos un sort buffer de 3MB con 400 conexiones max, el cual puede utilizar un total de 1,2 GB de memoria.
sort_buffer_size = 3M
Conexiones Máximas
MySQL tiene una limitación en el número de conexiones simultáneas que pueden mantener abiertas. Si está usando conexiones persistentes en PHP, en cada proceso de Apache se mantendrá una conexión abierta a MySQL. Esto significa que usted necesita para establecer el número máximo de conexiones en MySQL a igual o mayor que el número de procesos de Apache que pueden conectarse a la base de datos.
En un entorno con clústeres, debe sumar los procesos en cada servidor web a fin de determinar el máximo. Una configuración con suficientes conexiones máximas tambií©n asegura que los usuarios no tendran errores de conexión a la base de datos MySQL.
El establecimiento de 400 conexiones se muestra a continuación.
max_connections = 400 max_connections = 400
Cachí© de consultas
MySQL es capaz almacenar en cachí© los resultados de una consulta. La próxima vez que la misma consulta se ejecuta el resultado es devuelto inmediatamente, ya que se lee del cachí© en lugar de la base de datos. Para un sitio de lectura intensiva, esto puede proporcionar una importante mejora del rendimiento.
Para habilitar el cachí© de consultas, establecer el tipo de “1”:
query_cache_type = 1
Puede configurar el tamaño maximo de cada consulta que puede ser almacenada en cachí©. Si el resultado de la consulta es mayor que el límite de cachí© de consultas, los resultados no se guardaran.
Esto es normalmente establecido a 1M:
query_cache_limit = 1M
La cantidad de memoria disponible globalmente para el cache de consultas se establece con el query_cache_size. Esto debería ser bastante grande, y debe ser aumentado en tamaño para grandes bases de datos.
query_cache_size = 100M
Para ajustar la cachí© de consultas, use el comando SHOW STATUS. Esto puede ser utilizado para determinar los ajustes que necesitan ser modificados y para ver el efecto de alteraciones.
The show status command will show you if the query cache is heavily in use and if you have free memory, which indicates whether the query cache buffer settings should be increased or decreased.
El comando SHOW STATUS le mostrará si la cachí© de consultas está muy en uso y si tiene memoria libre, lo que indica si el buffer del cachí© de consultas debe ser aumentado o disminuido.
+-------------------------+----------+ +-------------------------+----------+
| Variable_name | Value | | Variable_name | Valor |
+-------------------------+----------+ +-------------------------+----------+
| Qcache_free_blocks | 34 | | Qcache_free_blocks | 34 |
| Qcache_free_memory | 16466312 | | Qcache_free_memory | 16466312 |
| Qcache_hits | 1313227 | | Qcache_hits | 1313227 |
| Qcache_inserts | 78096 | | Qcache_inserts | 78096 |
| Qcache_lowmem_prunes | 0 | | Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 3328 | | Qcache_not_cached | 3328 |
| Qcache_queries_in_cache | 140 | | Qcache_queries_in_cache | 140 |
| Qcache_total_blocks | 346 | | Qcache_total_blocks | 346 |
+-------------------------+----------+ +-------------------------+----------+
8 rows in set (0.00 sec) 8 filas en el conjunto (0,00 segundos)
Script para afinar y optimizar el rendimiento de MySQL
Este script toma la información de SHOW STATUS y SHOW VARIABLES para producir recomendaciones para ajustar las variables de servidor.
Es compatible con todas las versiones de MySQL 3.23 y superior (incluyendo 5.1).
Actualmente se ocupa de recomendaciones de optimizacion para:
- Slow Query Log
- Max Connections
- Worker Threads
- Key Buffer
- Query Cache
- Sort Buffer
- Joins
- Temp Tables
- Table (Open & Definition)
- Table Locking Cuadro de bloqueo
- Table Scans (read_buffer)
- Innodb Status
Script optimizacion MySQL | Day32
Via | Webdigity
Yo uso mysqltuner.pl (la direcciond e descarga es la misma) escrito en perl y que permite afinar variables y establece sugerencias de configuracion.
agradecedia si me das el link de favor
El link que indica para “Script para afinar y optimizar el rendimiento de MySQL
parece que esta roto”. ¿Puede volverlo a poner?
excelente, como dice Carlos, MUY UTIL
Muchas gracias, muy util.
Iré optimizando poco a poco
Foros del web – my.cnf para server grande