Ontem e hoje o banco travou várias vezes, conseguimos resolver duplicando os valores do buffer_pool (size, instances e chunk size), mas gostaria de saber quais os valores recomendados para o NovoSGA.

Nosso ambiente é Windows com MySQL versão 5.7.19, usamos a triagem e o painel padrão. Em nossa instalação a aplicação e o banco estão no mesmo servidor.

São 7 unidades com 1 painel cada, sendo que em 2 temos 2 balcões de triagem. Temos em média:

1000 senhas emitidas por dia
100 usuários logados simultaneamente

Ontem à tarde o consumo de CPU estava constantemente chegando a 100% e o banco de dados passou a travar o tempo todo.

Nesta madrugada a rotina que zera as senhas de todas as unidades não funcionou, hoje cedo o banco continuou a travar e o arquivo mysql.log cresceu assustadoramente, sempre com a mensagem:

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (4730 search iterations)! 
4730 failed attempts to flush a page! 
Consider increasing the buffer pool size. 
It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. 
Then upgrading to a newer version of your operating system may help. 
Look at the number of fsyncs in diagnostic info below. 
Pending flushes (fsync) 
log: 0; 
buffer pool: 0. 2033 
OS file reads, 2358 
OS file writes, 597 OS fsyncs. 
Starting InnoDB Monitor to print further diagnostics to the standard output.

Variáveis INNODB buffer obtidas com o comando SHOW VARIABLES LIKE '%innodb_buffer%'

Variable_name	Value
innodb_buffer_pool_chunk_size	16777216 ----- alteramos para 2146435072
innodb_buffer_pool_dump_at_shutdown	ON
innodb_buffer_pool_dump_now	OFF
innodb_buffer_pool_dump_pct	25
innodb_buffer_pool_filename	ib_buffer_pool
innodb_buffer_pool_instances	1 ----- alteramos para 2
innodb_buffer_pool_load_abort	OFF
innodb_buffer_pool_load_at_startup	ON
innodb_buffer_pool_load_now	OFF
innodb_buffer_pool_size	16777216 ----- alteramos para 12878610432

Configuração atual das variáveis INNODB no arquivo my.ini

;innodb_data_home_dir = C:/mysql/data/
innodb_data_file_path = ibdata1:12M:autoextend
;innodb_log_group_home_dir = C:/mysql/data/
;innodb_log_arch_dir = C:/mysql/data/
; You can set .._buffer_pool_size up to 50 - 80 % of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 2 
innodb_buffer_pool_chunk_size = 16G
; Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Após a reinicialização do MySQL as únicas mensagens no arquivo mysql.log são:

[Warning] option 'innodb-buffer-pool-chunk-size': unsigned value 17179869184 adjusted to 2146435072
[Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
[Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
[Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
[Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
[Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
[Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
[Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
[Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

Estamos estudando as orientações de dois artigos para entender melhor estes parâmetros:

https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html
https://www.rathishkumar.in/2017/01/how-to-allocate-innodb-buffer-pool-size-in-mysql.html

Agradeço se outras pessoas que tem o sistema em uso em mais de uma unidade puderem informar quais valores estão usando para as variáveis innodb_buffer (SHOW VARIABLES LIKE '%innodb_buffer%'), se é ambiente Linux ou Windows e a quantidade aproximada de usuários e unidades.

Obrigada!