Solución:
Solución 1:
Esto es difícil sin saber mucho sobre la base de datos en sí. Hay algunas herramientas que debe conocer;
- mysqltuner.pl Github
- MySQL Tuning Primer Github
Acerca del almacenamiento de toda la base de datos en la memoria; Cualquier consulta que esté realizando cambios en la base de datos permanecerá abierta hasta que se realice la escritura en el disco. Lo único que puede evitar que el disco sea un cuello de botella es un controlador de disco con caché de escritura.
Comenzaría con los siguientes cambios de los valores predeterminados:
key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 8
table_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M
innodb_buffer_pool_size = 4G
# This is crucial to avoid checkpointing all the time:
innodb_log_file_size = 512M
# If you have control on who consumes the DB, and you don't use hostnames when you've set up permissions - this can help as well.
skip_name_resolve
Luego vería cómo van las cosas y probaría cosas diferentes según (entre otras cosas) el resultado de las herramientas mencionadas anteriormente. También me aseguraría de graficar las tendencias con una herramienta de monitoreo, como Munin o Cacti, para ver con qué tipo de carga de trabajo estoy lidiando realmente. Personalmente, tengo una gran experiencia con los complementos de MySQL proporcionados con Munin.
Solucion 2:
En mi humilde opinión, deberías poder ir con
innodb_buffer_pool_size=5G
Eso sería el 62.5% de RAM con una cantidad suficiente de RAM para el SO del servidor más memoria para conexiones de base de datos
@kvisle recomendó usar mysqltuner.pl. Ese script es excelente para juzgar la cantidad de RAM para dedicar a join_buffer_size, sort_buffer_size, read_buffer_size y read_rnd_buffer_size. Esos 4 búferes sumados se multiplican por max_connections. Esa respuesta se agrega a los búferes estáticos (innodb_buffer_pool_size + key_buffer_size). Se informan las sumas combinadas. Si esa suma combinada supera el 80% de la RAM, es entonces cuando debe reducir esos tamaños de búfer. mysqltuner.pl será muy útil en este sentido.
Dado que todos sus datos son InnoDB, puede hacer que key_buffer_size (búfer de caché de claves para índices MyISAM) sea muy bajo (recomiendo 64M).
Aquí hay una publicación que hice en DBA StackExchange para calcular un tamaño recomendado de innodb_buffer_pool_size.
ACTUALIZACIÓN 2011-10-15 19:55 EDT
Si sabe que tendrá 5 GB de datos, entonces mi primera recomendación fue correcta. Sin embargo, olvidé agregar una cosa:
[mysqld]
innodb_buffer_pool_size=5G
innodb_log_file_size=1280M
El tamaño del archivo de registro debe ser el 25% del grupo de búfer de InnoDB
ACTUALIZACIÓN 2011-10-16 13:36 EDT
La regla del 25% se basa estrictamente en el uso de dos archivos de registro. Si bien es posible utilizar varios archivos de registro innodb, normalmente dos funcionan mejor.
Otros han expresado usando 25%
- http://lists.mysql.com/mysql/203879
- http://teaching-2.cs.uml.edu/MySQLdocs/MySQL_Manual/InnoDB_start.html
- http://www.issociate.de/board/post/395589/innodb_buffer_pool_size._How_to_setup_with_more_than_5G_?.html
Sin embargo, para ser justos, alguien de la compañía InnoBase Oy original expresó que no usaba la regla del 25% debido a que tenía un grupo de búferes InnoDB más grande.
Naturalmente, la regla del 25% no puede funcionar cuando se tiene una gran cantidad de RAM. De hecho, el tamaño de archivo innodb_log_file_size más grande permitido usando solo 2 archivos de registro es 2047M, ya que el tamaño combinado del archivo de registro debe ser menor que 4G (4096M)
CASO EN PUNTO: Uno de los clientes de mi empleador tiene un servidor de base de datos con 192 GB de RAM. No hay forma de tener archivos de registro de 48G. Simplemente uso el tamaño máximo de archivo para un archivo de registro innodb, 2047M. El comentario de @Kvisle sobre mi respuesta simplemente proporciona un enlace que indica que no tiene que limitarse a dos archivos de registro. Si tiene N archivos de registro, no pueden sumar 4G. Mi regla del 25% está en un mundo perfecto (DB Server con 8GB o menos).