Solución:
Tu innodb_buffer_pool_size es enorme. Lo tienes fijado en 20971520000. Eso es 19,5135 GB. Si solo tiene 5GB de datos e índices InnoDB, entonces solo debería tener alrededor de 8GB. Incluso esto puede ser demasiado alto.
Esto es lo que deberías hacer. Primero ejecuta esta consulta
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine="InnoDB") A;
Esto le dará el RIBPS, el tamaño de grupo de búfer de InnoDB recomendado, basado en todos los datos e índices de InnoDB, con un 60% adicional.
Por ejemplo
mysql> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
-> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
-> FROM information_schema.tables WHERE engine="InnoDB") A;
+-------+
| RIBPS |
+-------+
| 8 |
+-------+
1 row in set (4.31 sec)
mysql>
Con esta salida, establecería lo siguiente en /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=8G
Próximo, service mysql restart
Después del reinicio, ejecute MySQL durante una semana o dos. Luego, ejecute esta consulta:
SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name="Innodb_buffer_pool_pages_data") A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name="Innodb_page_size") B;
Esto le dará cuántos GB reales de memoria está utilizando InnoDB Data en el grupo de búfer de InnoDB en este momento.
Ya he escrito sobre esto antes: ¿Qué configurar innodb_buffer_pool y por qué …?
Podrías ejecutar esto DataGB consulta ahora mismo en lugar de reconfigurar, reiniciar y esperar una semana.
Este valor DataGB se parece más a lo grande que debería ser el grupo de búfer InnoDB + (porcentaje especificado en innodb_change_buffer_max_size). Estoy seguro de que será mucho menor que los 20000M que ha reservado en este momento. Los ahorros en RAM se pueden usar para ajustar otras cosas como
- join_buffer_size
- sort_buffer_size
- read_buffer_size
- read_rnd_buffer_size
- max_connection
AVISO # 1
Es muy importante tener en cuenta: en ocasiones, InnoDB puede requerir un 10% adicional sobre el valor de la innodb_buffer_pool_size. Esto es lo que dice la documentación de MySQL sobre esto:
Cuanto mayor sea este valor, se necesitará menos E / S de disco para acceder a los datos de las tablas. En un servidor de base de datos dedicado, puede configurarlo hasta en un 80% del tamaño de la memoria física de la máquina. Esté preparado para reducir este valor si ocurren estos otros problemas:
La competencia por la memoria física puede causar paginación en el sistema operativo.
InnoDB reserva memoria adicional para búferes y estructuras de control, de modo que el espacio total asignado sea aproximadamente un 10% mayor que el tamaño especificado.
El espacio de direcciones debe ser contiguo, lo que puede ser un problema en los sistemas Windows con archivos DLL que se cargan en direcciones específicas.
El tiempo para inicializar el grupo de búferes es aproximadamente proporcional a su tamaño. En instalaciones grandes, este tiempo de inicialización puede ser significativo. Por ejemplo, en un servidor Linux x86_64 moderno, la inicialización de un grupo de búfer de 10 GB tarda aproximadamente 6 segundos. Consulte la Sección 8.9.1, “El conjunto de búfer de InnoDB”.
AVISO # 2
Veo los siguientes valores en su my.cnf
| innodb_io_capacity | 200 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 4 |
| innodb_write_io_threads | 4 |
Este número impedirá que InnoDB acceda a múltiples núcleos
Establezca lo siguiente:
[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
He escrito sobre esto antes en DBA StackExchange
- 26 de mayo de 2011: Acerca del rendimiento de las bases de datos de un solo subproceso frente al de múltiples subprocesos
- 12 de septiembre de 2011: ¿Es posible hacer que MySQL use más de un núcleo?
- 20 de septiembre de 2011: múltiples núcleos y rendimiento de MySQL
Acabo de responder una pregunta como esta en Server Fault usando una fórmula más concisa:
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1.25 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;
¿Algo como esto? Utilizando SHOW VARIABLES y SHOW GLOBAL STATUS:
Expresión: innodb_buffer_pool_size / _ram
Sentido: % de RAM utilizada para InnoDB buffer_pool
Rango recomendado: 60 ~ 80%
Expresión: Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
Sentido: Leer solicitudes que tuvieron que llegar al disco
Rango recomendado: 0-2%
Qué hacer si está fuera de rango: Aumente innodb_buffer_pool_size si tiene suficiente RAM.
Expresión: Innodb_pages_read / Innodb_buffer_pool_read_requests
Sentido: Leer solicitudes que tuvieron que llegar al disco
Rango recomendado: 0-2%
Qué hacer si está fuera de rango: Aumente innodb_buffer_pool_size si tiene suficiente RAM.
Expresión: Innodb_pages_written / Innodb_buffer_pool_write_requests
Sentido: Escribir solicitudes que tuvieron que llegar al disco
Rango recomendado: 0-15%
Qué hacer si está fuera de rango: Compruebe innodb_buffer_pool_size
Expresión: Innodb_buffer_pool_reads / Uptime
Sentido: Lee
Rango recomendado: 0-100 / seg.
Qué hacer si está fuera de rango: ¿Aumentar innodb_buffer_pool_size?
Expresión: (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) / Uptime
Sentido: E / S InnoDB
Rango recomendado: 0-100 / seg.
Qué hacer si está fuera de rango: ¿Aumentar innodb_buffer_pool_size?
Expresión: Innodb_buffer_pool_pages_flushed / Uptime
Sentido: Escribe (se ruboriza)
Rango recomendado: 0-100 / seg.
Qué hacer si está fuera de rango: ¿Aumentar innodb_buffer_pool_size?
Expresión: Innodb_buffer_pool_wait_free / Uptime
Sentido: Contador para cuando no hay páginas libres en buffer_pool. Es decir, todas las páginas están sucias.
Rango recomendado: 0-1 / seg.
Qué hacer si está fuera de rango: Primero asegúrese de que innodb_buffer_pool_size esté configurado de manera razonable; si sigue teniendo problemas, disminuya innodb_max_dirty_pages_pct
Su título pregunta sobre innodb_buffer_pool_size, pero sospecho que ese no es el problema real. (Rolando comentó por qué lo ha establecido lo suficientemente grande, incluso demasiado grande).
Establecí conexiones máximas = 800 que a veces saturan y hacen que el servidor se detenga.
Eso no está claro. 800 usuarios en modo de “suspensión” prácticamente no tienen ningún impacto en el sistema. 800 hilos activos serían un desastre. ¿Cuántos subprocesos se están “ejecutando”?
¿Los hilos se bloquean entre sí? Consulte MOSTRAR ESTADO INNODB DEL MOTOR para obtener algunas pistas sobre interbloqueos, etc.
¿Aparecen consultas en el registro lento? Optimicémoslos.
Qué versión estás usando? XtraDB (un reemplazo directo de InnoDB) hace un mejor trabajo al usar múltiples núcleos. 5.6.7 hace un trabajo aún mejor.
innodb_buffer_pool_instances – cámbielo a 8 (asumiendo un buffer_pool de 20G); reducirá ligeramente el argumento de Mutex.
¿Está vinculado a E / S o está vinculado a la CPU? Las soluciones son radicalmente diferentes, según su respuesta.
SSD: sería mejor si todos los archivos de registro estuvieran en unidades que no sean SSD.