Optimice completamente la base de datos mysql bajo carga alta en Linux

Al mismo tiempo, el número de visitas en línea continúa aumentando. Cuando el servidor con memoria 1G obviamente está muy sobrecargado, incluso fallará todos los días o el servidor se congelará de vez en cuando. Este problema me ha molestado durante más de la mitad. al mes. MySQL utiliza un algoritmo muy escalable, por lo que normalmente se puede ejecutar con menos memoria o darle a MySQL más memoria para obtener un mejor rendimiento.

Después de instalar mysql, los archivos de configuración deben estar en el directorio /usr/local/mysql/share/mysql. Hay varios archivos de configuración, incluido my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf. Los sitios web con diferentes flujos de tráfico y entornos de servidores con diferentes configuraciones, por supuesto, requerirán diferentes archivos de configuración.

En circunstancias normales, el archivo de configuración my-medium.cnf puede satisfacer la mayoría de nuestras necesidades; generalmente copiaremos el archivo de configuración a /etc/my.cnf y solo necesitamos modificar este archivo de configuración, usando las variables mysqladmin extend-status –u. root –p puede ver los parámetros actuales. Hay tres parámetros de configuración que son los más importantes, a saber, key_buffer_size, query_cache_size, table_cache .

key_buffer_size solo funciona para tablas MyISAM,

key_buffer_size especifica el tamaño del búfer de índice, que determina la velocidad de procesamiento del índice, especialmente la velocidad de lectura del índice. Generalmente lo configuramos en 16M. De hecho, este número está lejos de ser suficiente para sitios un poco más grandes. Al verificar los valores de estado Key_read_requests y Key_reads, puede saber si la configuración key_buffer_size es razonable. La proporción key_reads / key_read_requests debe ser lo más baja posible, al menos 1:100, 1:1000 es mejor (el valor de estado anterior se puede obtener usando SHOW STATUS LIKE 'key_read%'). O si ha instalado phpmyadmin, puede verlo a través del estado de ejecución del servidor. El autor recomienda usar phpmyadmin para administrar mysql. Los siguientes valores de estado son el análisis de ejemplo que obtuve a través de phpmyadmin:

Este servidor ha estado funcionando durante 20 días.
key_buffer_size – 128M
solicitudes_lectura_clave – 650759289
lecturas_clave - 79112
La proporción es cercana a 1:8000 y el estado de salud es muy bueno.

Otra forma de estimar key_buffer_size es sumar el espacio ocupado por los índices de cada tabla en la base de datos de su sitio web. Tome este servidor como ejemplo: los índices de varias tablas más grandes suman aproximadamente 125 M. Este número aumentará a medida que la tabla crezca. hacerse más grande.

A partir de 4.0.1, MySQL proporciona un mecanismo de almacenamiento en búfer de consultas. Al utilizar el búfer de consultas, MySQL almacena la declaración SELECT y los resultados de la consulta en el búfer. En el futuro, para la misma declaración SELECT (distingue entre mayúsculas y minúsculas), los resultados se leerán directamente desde el búfer. Según el manual del usuario de MySQL, el uso del almacenamiento en búfer de consultas puede lograr hasta un 238% de eficiencia.

Al ajustar los siguientes parámetros, puede saber si la configuración de query_cache_size es razonable.
Inserciones de Qcache
Visitas de Qcache
Qcache pasas lowmem
Bloques libres de Qcache
Bloques totales de Qcache

Si el valor de Qcache_lowmem_prunes es muy grande, indica que a menudo hay un almacenamiento en búfer insuficiente. Al mismo tiempo, si el valor de Qcache_hits es muy grande, indica que el búfer de consulta se usa con mucha frecuencia. debe aumentarse Si el valor de Qcache_hits es pequeño, indica que la tasa de repetición de consultas es muy alta. En este caso, el uso del búfer de consultas afectará la eficiencia, por lo que puede considerar no utilizar el búfer de consultas. Además, agregar SQL_NO_CACHE a la instrucción SELECT puede indicar claramente que no se utiliza el búfer de consulta.
Qcache_free_blocks, si el valor es muy grande, indica que hay muchos fragmentos en el búfer query_cache_type especifica si se debe utilizar el búfer de consultas

Yo puse:
query_cache_size = 32M
query_cache_type = 1

Obtenga el siguiente valor de estado:
Las consultas de Qcache en el caché 12737 indican la cantidad de elementos actualmente almacenados en caché
Insertos Qcache 20649006
Qcache llega a 79060095 Parece que la tasa de consultas repetidas es bastante alta.
Qcache lowmem prunes 617913 Hay tantas veces que el caché es demasiado bajo.
Qcache no almacenado en caché 189896
Memoria libre de Qcache 18573912 Espacio de caché restante actual
Bloques libres de Qcache 5328 Este número parece un poco grande y fragmentado
Bloques totales de Qcache 30953
Si la memoria permite 32M, deberías agregar más.

table_cache especifica el tamaño de la caché de la tabla. Cada vez que MySQL accede a una tabla, si hay espacio en el búfer de la tabla, la tabla se abre y se coloca en él, lo que permite un acceso más rápido al contenido de la tabla. Al verificar los valores de estado Open_tables y Opened_tables en el momento pico, puede decidir si necesita aumentar el valor de table_cache. Si encuentra que open_tables es igual a table_cache y open_tables está creciendo, entonces necesita aumentar el valor de table_cache (el valor de estado anterior se puede obtener usando SHOW STATUS LIKE 'Open%tables'). Tenga en cuenta que table_cache no se puede establecer ciegamente en un valor grande. Si se establece demasiado alto, puede causar descriptores de archivos insuficientes, lo que resultará en un rendimiento inestable o falla de conexión.

Para máquinas con 1G de memoria, el valor recomendado es 128-256.

Configuración del autor
caché_tabla = 256

Obtenga el siguiente estado:
Mesas abiertas 256
Mesas abiertas 9046
Aunque open_tables ya es igual a table_cache, en relación con el tiempo de ejecución del servidor, ha estado funcionando durante 20 días y el valor de open_tables también es muy bajo. Por lo tanto, aumentar el valor de table_cache debería ser de poca utilidad. Si el valor anterior aparece después de ejecutarse durante 6 horas, entonces debería considerar aumentar table_cache.

Si no necesita registrar registros binarios, desactive esta función. Tenga en cuenta que después de desactivarla, no podrá restaurar los datos antes del problema. Debe realizar una copia de seguridad manual. El registro binario contiene todas las declaraciones que actualizan los datos. El propósito es restaurar la base de datos. Úselo para restaurar los datos a su estado final tanto como sea posible. Además, si realiza una replicación sincrónica (Replicación), también necesita usar el registro binario para transferir modificaciones.

log_bin especifica el archivo de registro. Si no se proporciona ningún nombre de archivo, MySQL generará el nombre de archivo predeterminado. MySQL agregará automáticamente un índice numérico después del nombre del archivo y regenerará un nuevo archivo binario cada vez que se inicie el servicio. Además, use log-bin-index para especificar el archivo de índice; use binlog-do-db para especificar la base de datos para registrar; use binlog-ignore-db para especificar la base de datos que no se registrará. Nota: binlog-do-db y binlog-ignore-db solo especifican una base de datos a la vez. La especificación de varias bases de datos requiere varias declaraciones. Además, MySQL cambiará todos los nombres de las bases de datos a minúsculas. Debe utilizar todos los nombres en minúsculas al especificar la base de datos; de lo contrario, no funcionará.

Para desactivar esta función, simplemente agregue un signo # delante
#log-bin

Activar el registro de consultas lento (registro de consultas lento)

El registro de consultas lento es útil para rastrear consultas problemáticas. Registra todas las consultas que verificaron long_query_time y, si es necesario, las registra sin utilizar un índice. A continuación se muestra un ejemplo de un registro de consultas lento:

Para habilitar registros de consultas lentas, debe configurar los parámetros log_slow_queries, long_query_times y log-queries-not-using-indexes.

log_slow_queries especifica archivos de registro. Si no se proporciona ningún nombre de archivo, MySQL generará un nombre de archivo predeterminado por sí mismo. long_query_times especifica el umbral de consulta lenta; el valor predeterminado es 10 segundos. log-queries-not-using-indexes es un parámetro introducido después de 4.1.0, que indica que las consultas que no utilizan índices se registran. El autor establece long_query_time=10

Configuración del autor:

sort_buffer_size = 1M
conexiones_max=120
tiempo de espera = 120
back_log=100
read_buffer_size = 1M
thread_cache=32
tiempo de espera_interactivo=120
thread_concurrency = 4

Descripción del parámetro:

reserva
El número de conexiones que MySQL debe tener. Esto funciona cuando el hilo principal de MySQL recibe muchas solicitudes de conexión en un corto período de tiempo, y luego el hilo principal toma algún tiempo (aunque sea brevemente) para verificar las conexiones e iniciar un nuevo hilo. El valor back_log indica cuántas solicitudes se pueden almacenar en la pila en un corto período de tiempo antes de que MySQL deje de responder temporalmente a nuevas solicitudes. Sólo si espera muchas conexiones en un corto período de tiempo necesita aumentarlo; en otras palabras, este valor es el tamaño de la cola de escucha para las conexiones TCP/IP entrantes. Su sistema operativo tiene su propio límite en el tamaño de esta cola. La página de manual para la llamada al sistema Unix listening(2) debería tener más detalles. Consulte la documentación de su sistema operativo para conocer el valor máximo de esta variable. Intentar establecer back_log por encima del límite de su sistema operativo no tendrá ningún efecto.
conexiones_max
El número máximo de conexiones simultáneas es 120. Si excede este valor, se recuperará automáticamente y los problemas se resolverán automáticamente.

caché_hilo
No pude encontrar ninguna instrucción específica, pero después de configurarlo en 32, se crearon más de 400 subprocesos en 20 días, mientras que se crearon miles de subprocesos en un día antes, por lo que sigue siendo útil.

hilo_concurrencia
#Establezca el número de su CPU x2, por ejemplo, si solo hay una CPU, entonces thread_concurrency=2
#Hay 2 CPU, entonces thread_concurrency=4

saltar-innodb
#Eliminar el soporte de innodb