Cómo descomprimir una base de datos MySQL a la que se ha accedido demasiadas veces

1. El impacto del hardware del servidor en el rendimiento de MySQL

① Capacidad de búsqueda de disco (E/S de disco), actualmente utilizamos un disco duro SAS de 15000 rpm. MySQL realiza una gran cantidad de operaciones de consulta complejas cada segundo y se puede imaginar la cantidad de lectura y escritura en el disco.

Por lo tanto, la E/S de disco generalmente se considera una de las mayores limitaciones en el rendimiento de MySQL para el foro Discuz! con un volumen de visitas diario promedio de más de 1 millón de PV, debido a las limitaciones de. E/S de disco, ¡el rendimiento de MySQL será muy bajo! Para solucionar esta restricción, considere las siguientes soluciones:

Utilice una matriz de discos RAID1+0. Tenga cuidado de no intentar utilizar RAID-5. MySQL no será tan eficiente como espera en un RAID-. Conjunto de 5 discos tan alto como eso.

②CPU Para aplicaciones MySQL, se recomienda usar DELL R710, E5620 @2.40GHz (4 núcleos)* 2. Ahora prefiero usar DELL R710, que también uso para aplicaciones de virtualización Linuxakg < /; p>

③Memoria física Para servidores de bases de datos que utilizan MySQL, se recomienda utilizar una memoria de no menos de 2,40 GHz (4 núcleos)* 2.

Para servidores de bases de datos que utilizan MySQL, se recomienda que la memoria del servidor no sea inferior a 2 GB y se recomienda utilizar más de 4 GB de memoria física. Sin embargo, la memoria ahora es un problema insignificante para el servidor. Lo encontramos en el trabajo. La mayoría de los servidores de alta gama tienen más de 32 GB de memoria.

Los servidores de bases de datos que utilizamos en el trabajo son HP DL580G5 y DELL R710. Estos dos servidores tienen buena estabilidad y rendimiento, especialmente DELL R710, que es una buena opción para aplicaciones de virtualización Linuxakg. La estabilidad y el rendimiento son buenos; especialmente DELL R710. Descubrí que muchos compañeros lo utilizan como servidor de bases de datos, así que se lo recomiendo a todos.

2. Instale MySQL en línea. El autor recomienda el método de compilación e instalación, que mejorará enormemente el rendimiento. Para el sistema del servidor, el autor recomienda utilizar Centos5.5 de 64 bits. paquete de código fuente. Los parámetros de compilación serán silenciosos

El modo Debgu genera código binario y el modo Debug tiene una pérdida de rendimiento relativamente grande en MySQL, por lo que cuando compilamos el código del producto para la instalación, no lo hagas. olvide deshabilitar el modo de depuración usando el parámetro "-

sin-depuración". Si configura los parámetros de compilación -with-mysqld-ldflags y -with-client-ldflags en -all-static, puede indicarle al compilador que compile estáticamente y maximizar el rendimiento del código resultante. La diferencia de rendimiento entre el código compilado estáticamente y el código compilado dinámicamente

puede llegar al 5%-10%. Me referí a los parámetros de compilación del Sr. Jian Chaoyang, que se enumeran a continuación para su referencia

.

/configure

-prefix=/usr/local/mysql -sin-depuración -sin-banco

-.enable-thread-safe-client -enable-assembler -enable-profiling

-with-mysqld-ldflags=-all-static-with-client-ldflags=-all-static

-with-charset=latin1 -with-extra-charset=utf8 , gbk -con-innodb

-con-motor-de-almacenamiento-csv -con-motor-de-almacenamiento-federado

- con-mysqld-user=mysql -sin-qué-am -Ioing-ded-server

-with-server-suffix=-community

- with-unix-socket-path=/usr/local/mysql/sock/mysql.sock =/usr/local/mysql/sock/mysql.

3. Los propios factores de MySQL Después de resolver las limitaciones de hardware del servidor anteriores, echemos un vistazo a cómo funciona la optimización propia de MySQL. La optimización propia de MySQL se basa en la optimización de los parámetros en el archivo de configuración my.cnf. A continuación introduciremos algunos parámetros que tienen un mayor impacto en el rendimiento.

A continuación se explica cómo optimizar my.cnf para su configuración de hardware:

#vim /etc/my.cnf

A continuación solo se enumera el archivo my.cnf Sección [mysqld]; otras secciones tienen un pequeño impacto en el rendimiento de MySQL y pueden ignorarse.

[mysqld]

puerto = 3306

serverid = 1

socket = /tmp/mysql.sock

skip-locking

# Evitar el bloqueo externo en MySQL reduce la posibilidad de errores y mejora la estabilidad.

skip-name-resolve

# Deshabilitar la resolución DNS de MySQL para conexiones externas. El uso de esta opción elimina el tiempo necesario para que MySQL realice la resolución DNS. Pero tenga en cuenta que si esta opción está activada, toda autorización de conexión al host remoto debe utilizar el método de dirección IP; de lo contrario, MySQL no podrá manejar la solicitud de conexión correctamente.

back_log = 384

El valor del parámetro #back_log indica cuántas solicitudes pueden aparecer en la pila en un corto período de tiempo antes de que MySQL se detenga temporalmente respondiendo a nuevas solicitudes.

Si el sistema tiene una gran cantidad de conexiones en un corto período de tiempo, es necesario aumentar el valor de este parámetro, que especifica el tamaño de la cola de escucha utilizada para recibir conexiones TCP/IP. Los diferentes sistemas operativos tienen sus propias restricciones en cuanto al tamaño de la cola

. Intentar establecer back_log por encima del límite del sistema operativo no tiene ningún efecto. El valor predeterminado es 50 y para Linux se recomienda establecerlo en un número entero menor que 512.

key_buffer_size = 384M

#key_buffer_size especifica el tamaño del búfer utilizado para la indexación. Aumentar este valor puede mejorar el rendimiento de la indexación. Para servidores con una capacidad de memoria de aproximadamente 4 GB, este parámetro se puede configurar en 256 M o 384 M.

Nota: ¡Establecer este parámetro demasiado grande puede reducir la eficiencia general del servidor!

max_allowed_packet = 4M

thread_stack = 256K

table_cache = 614K

sort_buffer_size = 6M

# Disponible para Tamaño del búfer de clasificación de consultas. Nota: Este parámetro corresponde a la asignación de memoria por conexión, por lo que si hay 100 conexiones, el tamaño total real del búfer de clasificación asignado es 100 × 6 = 600 MB. Por lo tanto, para un servidor con aproximadamente 4 GB de memoria, se recomienda establecer el tamaño del búfer entre 6 y 8 M.

read_buffer_size = 4M

# El tamaño del búfer que se puede utilizar para operaciones de consulta de lectura. El tamaño del búfer que se utilizará. Al igual que sort_buffer_size, este parámetro corresponde a la memoria exclusiva asignada a cada conexión.

join_buffer_size = 8M

# El tamaño del búfer que se puede utilizar para operaciones de consulta de unión. Al igual que sort_buffer_size, este parámetro corresponde a una asignación única por conexión.

myisam_sort_buffer_size = 64M

table_cache = 512

thread_cache_size = 64

query_cache_size = 64M

#Especifique el tamaño del búfer de consultas MySQL. Se puede observar en la consola MySQL que si el valor de Qcache_lowmem_prunes es muy grande, indica que el buffer no se usa con suficiente frecuencia

si el valor de Qcache_hits es grande, indica que el buffer de consulta se usa con frecuencia. Si el valor es muy pequeño, afectará la eficiencia. Puede considerar no usar el búfer de consulta.

flush; si el valor de Qcache_free_blocks es grande, significa que hay muchos fragmentos. en el búfer.

tmp_table_size = 256M

max_connections = 768

# Especifique el número máximo de procesos de conexión permitidos por MySQL. Si el error de demasiadas conexiones ocurre frecuentemente al acceder al foro, es necesario aumentar este parámetro.

max_connect_errors = 1000

wait_timeout = 10

#Especifique el tiempo máximo de conexión para una solicitud, que se puede configurar entre 5 y 10 para servidores con alrededor de 4 GB de RAM.

thread_concurrency = 8

#El valor de este parámetro es el número de CPU lógicas del servidor * 2. En este ejemplo, el servidor tiene 2 CPU físicas, cada una CPU física Todos admiten Hyper-Threading H.T, por lo que el valor real es 4 * 2 = 8; esta es también la configuración actual del servidor principal de doble núcleo.

skip-networking

# Habilitar esta opción puede deshabilitar completamente la conexión TCP/IP de MySQL. Si el servidor WEB accede al servidor de base de datos MySQL a través de una conexión remota, ¡no habilite esta opción! ¡De lo contrario la conexión no funcionará!

table_cache=1024

# Cuanto mayor sea la memoria física, mayor será la configuración.

El valor predeterminado es 2402, preferiblemente 512-1024

innodb_additional_mem_pool_size=4M

# El valor predeterminado es 2M

innodb_flush_log_at_trx_commit=1

# Establecerlo en 0 significa esperar a que la cola innodb_log_buffer_size esté llena antes de almacenarla de manera uniforme. El valor predeterminado es 1

innodb_log_buffer_size=2M

# El valor predeterminado es 1M

innodb_thread_concurrency= 8

# ¿Cuántas CPU hay en su servidor? Hay varias configuraciones de CPU. Se recomienda utilizar el valor predeterminado, que generalmente es 8

key_buffer_size=256M

# El valor predeterminado es 218, ajústelo a 128 para mejorar

. p>

tmp_table_size=64M

# El valor predeterminado es 16 M, ajustado a 64-256 valores más pendientes

read_buffer_size=4M

# El valor predeterminado el valor es 64K

read_rnd_buffer_size=16M

# El valor predeterminado es 256K

sort_buffer_size=32M

# El valor predeterminado es 256K

sort_buffer_size =32M

# El valor predeterminado es 256K. es 256K

thread_cache_size=120

# El valor predeterminado es 60

query_cache_size=32M

※ Vale la pena señalar:

Muchas situaciones requieren un análisis caso por caso

Si Key_reads es demasiado grande, el mío debería cambiarse. Si Key_reads es demasiado grande, debe cambiar Key_buffer_size en my.cnf y mantener Key_reads/Key_read_requests al menos 1/100 o más; cuanto más pequeño, mejor.

En segundo lugar, si Qcache_lowmem_prunes es grande, aumente el valor de Query_cache_size.

Muchas veces, encontraremos que la optimización del rendimiento a través de la configuración de parámetros puede no producir un salto cualitativo como mucha gente imagina, a menos que la configuración anterior sea seriamente irrazonable. No podemos confiar en que el DBA ajuste los parámetros después de que la base de datos esté en línea para completar el ajuste del rendimiento. En lugar de ello, debemos intentar minimizar los problemas de rendimiento durante la etapa de diseño y desarrollo del sistema.

Característica exclusiva de 51CTO Si la optimización de MySQL por sí sola todavía no puede soportar la presión, es hora de considerar la arquitectura de alta disponibilidad de MySQL (a muchos estudiantes también les gusta llamarla un clúster de MySQL. Las soluciones actualmente factibles son). :

Un cluster MySQL

Ventajas: alta disponibilidad y buen rendimiento. muy bien. Se puede almacenar al menos una copia de cada dato en diferentes hosts y las copias de datos redundantes se sincronizan en tiempo real. Pero su mantenimiento es muy complicado, hay algunos errores y no es adecuado para sistemas en línea más básicos, por lo que no lo recomiendo.

2. Programa de duplicación de red de disco DRBD

Ventajas: el software es potente, los datos se pueden duplicar en hosts físicos en dispositivos rápidos subyacentes y se pueden configurar de manera diferente según el rendimiento y la confiabilidad. nivel de sincronización de requisitos. Las operaciones de IO mantienen el orden de la base de datos y cumplen con los estrictos requisitos de coherencia de los datos

. Sin embargo, un entorno de sistema de archivos no distribuido no puede admitir la visibilidad sincronizada de los datos reflejados y existe un conflicto entre el rendimiento y la confiabilidad. No se puede aplicar a entornos con requisitos exigentes de rendimiento y confiabilidad, y el costo de mantenimiento es mayor que el de MySQL.

Copia MySQL.

Además, DRBD también es una de las soluciones de alta disponibilidad de MySQL recomendadas oficialmente, por lo que puede considerar si implementarlo en función de su entorno real.

Tres: replicación de MySQL

En escenarios de aplicaciones reales, la replicación de MySQL

es la forma más efectiva de mejorar la escalabilidad del sistema. de una amplia gama de herramientas de diseño. Muchos usuarios de MySQL han mejorado la escalabilidad de sus sistemas a través de la función de replicación, que les permite aumentar exponencialmente el rendimiento del sistema original

o incluso órdenes de magnitud simplemente agregando dispositivos de hardware baratos.

Existen varias arquitecturas de replicación MySQL tradicionales, que se presentan brevemente aquí

Arquitectura de replicación MySQL I: Arquitectura de replicación convencional: maestro-esclavo es un modo arquitectónico, en el que un maestro replica a uno o más esclavos, se utiliza principalmente para aplicaciones de lectura intensa. Esta es una solución de expansión de bajo costo para aplicaciones grandes en el lado de la base de datos, donde la lectura y la escritura están separadas y la estación maestra es la principal responsable de la presión de escritura.

Arquitectura de replicación MySQL 2: arquitectura de replicación en cascada, es decir, Maestro-Esclavo-Esclavo. Esto también es para evitar una presión de lectura excesiva en los Esclavos. La configuración de una capa de Esclavos secundarios puede resolver fácilmente el problema en el lado Maestro. El riesgo de tener demasiados afiliados Slave y convertirse en un cuello de botella.

Arquitectura de replicación MySQL tres: arquitectura de replicación en cascada dual maestro-esclavo, es decir, Maestro-Maestro-Esclavos. El mayor beneficio es evitar que las operaciones de escritura de la estación maestra maestra se vean afectadas por el clúster esclavo. replicación, asegurando que el Maestro Único punto de falla de la estación.

Las anteriores son soluciones de arquitectura de replicación de MySQL relativamente comunes. Puede diseñarlas de acuerdo con el entorno específico de su empresa. Puede considerar usar LVS o Haproxy para el equilibrio de carga de MySQL. Recomiendo Heartbeat para HA de alta disponibilidad. software.

MySQL

Desventajas de la replicación de MySQL: la cantidad de datos replicados por MySQL es relativamente grande y El método de copia también es más complicado La cantidad de datos copiados es relativamente pequeña La cantidad de datos copiados es relativamente pequeña. La cantidad de datos copiados es relativamente pequeña. La cantidad de datos copiados es relativamente pequeña. Defectos de replicación: si la falla del hardware del host maestro no se puede recuperar, es posible que algunos datos no se transmitan al extremo esclavo y se pierdan. Por lo tanto, debe elegir una arquitectura Mysql razonable basada en su plan de red actual

, comunicarse más con MySQL

DBA y programadores y realizar múltiples copias de seguridad (al menos haré copias de seguridad localmente y fuera del sitio) Hacer copias de seguridad dobles) y realizar múltiples pruebas Los datos son lo más importante, por lo que no se pueden cometer errores

.