Tengo una base de datos ocupada con sólo tablas InnoDB que tiene un tamaño de unos 5GB. La base de datos se ejecuta en un servidor Debian que utiliza discos SSD y he establecido un máximo de conexiones = 800, que a veces saturan el servidor y lo paran. La media de consultas por segundo es de unos 2,5K. Así que necesito optimizar el uso de la memoria para dejar espacio a las máximas conexiones posibles.
He visto sugerencias de que innodb_buffer_pool_size debería ser hasta el %80 de la memoria total. Por otro lado, recibo esta advertencia del script tuning-primer:
Max Memory Ever Allocated : 91.97 G
Configured Max Per-thread Buffers : 72.02 G
Configured Max Global Buffers : 19.86 G
Configured Max Memory Limit : 91.88 G
Physical Memory : 94.58 G
Aquí están mis variables innodb actuales:
| innodb_adaptive_flushing | ON |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 20971520 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 20971520000 |
| innodb_change_buffering | all |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_io_capacity | 200 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 4194304 |
| innodb_log_file_size | 524288000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 0 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 4 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 1.1.8 |
| innodb_write_io_threads | 4 |
Una nota lateral que podría ser relevante: Veo que cuando intento insertar un post grande (digamos de más de 10KB) desde Drupal (que se encuentra en un servidor web separado) a la base de datos, dura una eternidad y la página no vuelve correctamente.
Con respecto a esto, me pregunto cuál debería ser mi innodb_buffer_pool_size para un rendimiento óptimo. Agradezco sus sugerencias para configurar este y otros parámetros de forma óptima para este escenario.
Su innodb_buffer_pool_size es enorme. Lo tienes configurado en 20971520000
. Eso es 19,5135 GB. Si sólo tienes 5GB de datos e índices InnoDB, entonces sólo deberías tener unos 8GB. Incluso esto puede ser demasiado alto.
Esto es lo que deberías hacer. Primero ejecute 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 recomendado de la reserva de búferes de InnoDB 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, usted establecería lo siguiente en /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=8G
A continuación, service mysql restart
.
Después del reinicio, ejecute mysql durante una o dos semanas. 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á en uso por InnoDB en la reserva de búferes de InnoDB en este momento.
He escrito sobre esto antes : https://dba.stackexchange.com/questions/19164/what-to-set-innodb-buffer-pool-and-why/19181#19181
Podrías ejecutar esta consulta DataGB
ahora mismo en lugar de reconfigurar, reiniciar y esperar una semana.
Este valor DataGB
se asemeja más a lo grande que debería ser el Buffer Pool de InnoDB + (porcentaje especificado en innodb_change_buffer_max_size). Estoy seguro de que esto será mucho menos que los 20000M que tienes reservados ahora mismo. El ahorro de RAM se puede utilizar para afinar otras cosas como
Esto es muy importante para tener en cuenta: A veces, InnoDB puede requerir un 10% adicional sobre el valor de innodb_buffer_pool_size. Esto es lo que dice la Documentación de MySQL sobre esto:
Cuanto mayor sea este valor, menos E/S de disco se necesita para acceder a a los datos de las tablas. En un servidor de base de datos dedicado, puede establecer este valor hasta hasta el 80% del tamaño de la memoria física de la máquina. Esté preparado para reducir este valor si se producen estos otros problemas: La competencia por la memoria física La competencia por la memoria física podría causar la paginación en el sistema operativo sistema operativo.
InnoDB reserva memoria adicional para buffers y estructuras de control, para 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 sistemas Windows con DLLs que se cargan en direcciones específicas.
El tiempo de inicialización de la reserva 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 conjunto de búferes de 10GB toma aproximadamente 6 segundos. Vea la Sección 8.9.1, "La reserva de búferes de InnoDB".
I Vea 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
Por favor, 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 el DBA StackExchange
May 26, 2011
: https://dba.stackexchange.com/questions/2918/about-single-threaded-versus-multithreaded-databases-performance/2948#2948Sep 12, 2011
: https://dba.stackexchange.com/questions/5666/possible-to-make-mysql-use-more-then-one-core/5670#567020 de septiembre de 2011
: https://dba.stackexchange.com/questions/5926/multi-cores-and-mysql-performance/5968#5968Acabo de responder a una pregunta como esta en ServerFault utilizando 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;
Tu título pregunta sobre innodb_buffer_pool_size, pero sospecho que ese no es el verdadero problema. (Rolando comentó por qué lo has puesto bastante grande, incluso demasiado grande).
He configurado conexiones máximas = 800 que a veces saturan y paran el servidor.
Eso no está claro. 800 usuarios en "Sleep" modo tiene prácticamente cero impacto en el sistema. 800 hilos activos sería un desastre. ¿Cuántos hilos están "funcionando"?
¿Los hilos se bloquean entre sí? Ver SHOW ENGINE INNODB STATUS para algunas pistas sobre los bloqueos, etc.
¿Aparece alguna consulta en el slowlog? Optimicémoslas.
¿Qué versión está utilizando? XtraDB (un reemplazo de InnoDB) hace un mejor trabajo al usar múltiples núcleos. La versión 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 la contención Mutex.
¿Estás limitado por la E/S o por la CPU? Las soluciones son radicalmente diferentes, dependiendo de su respuesta.
SSD -- Podría ser mejor si todos los archivos de registro estuvieran en unidades no SSD.
Más memoria es siempre mejor, pero en mi experiencia la mayoría de las veces el tamaño del buffer pool no debería ajustarse al tamaño de los datos. Muchas tablas están inactivas la mayoría de las veces, como las tablas de respaldo que están por ahí, así que el tamaño de la reserva de búferes de innodb debería ajustarse al tamaño de los datos acivos.
El periodo de tiempo que especifique para las páginas activas influye en el rendimiento, pero hay un punto óptimo en el que no obtendrá más rendimiento por un tamaño de búfer mayor. Puedes estimar/calcular/medir eso con show engine innodb status
.