MySQL 8.4.6 Версия сообщества вешает с ожиданием Commit Handler ⇐ MySql

[Anonymous]

Мы используем MySQL 8.4.6 Community Edition. Когда мы используем наше приложение, которое имеет около 1000 транзакций в секунду с Select, вставить, обновить, удалять запросы. MySQL Service висит с ожиданием коммита Handler. < /P>
Диск данных поддерживает 2000 IOPS. Проблемы с диском не выявлено < /p>
Запуск с помощью конфигураций MySQL в my.cnf file < /p>
max_connections = 500
key_buffer_size = 32M
join_buffer_size = 16M
sort_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
max_allowed_packet = 16M
thread_cache_size= 64
tmp_table_size = 128M
max_heap_table_size = 512M
bulk_insert_buffer_size = 256M
table_definition_cache = 10000
table_open_cache = 512
secure_file_priv = ""

innodb_data_home_dir=/data/mysql-84
innodb_buffer_pool_size=8G
innodb_file_per_table
innodb_data_file_path=ibdata1:4G;ibdata2:2G:autoextend
innodb_fast_shutdown = 0
innodb_log_file_size=2G
innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
long_query_time =0.4
innodb_buffer_pool_instances =4
innodb_doublewrite = 0
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_sort_buffer_size = 4M

log_error=/hms/logs/mysql-84/mysql-error.log
slow-query-log-file=/hms/logs/mysql-84/mysql-slow-query.log
log_queries_not_using_indexes

slow-query-log = 1

server_id =1
log_bin =/data/mysql-84/mysql-bin.log
log_bin_index =/data/mysql-84/mysql-bin.log.index
binlog_format =ROW
relay_log =/data/mysql-84/mysql-relay-bin
relay_log_index =/data/mysql-84/mysql-relay-bin.index
binlog_expire_logs_seconds =604800
log_slave_updates = 0
auto_increment_increment =2
auto_increment_offset =1
replicate-do-db = radius_v3
binlog_cache_size = 65536
local_infile=1

slave-skip-errors=1062,1053,1032

skip_name_resolve

symbolic-links =0

explicit_defaults_for_timestamp = 1

sql_mode ='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE'

mysql-native-password=ON
character_set_server=latin1
collation_server=latin1_swedish_ci
event_scheduler=OFF
innodb_autoinc_lock_mode=1
innodb_flush_method=fsync
innodb_flush_neighbors=1
innodb_max_dirty_pages_pct=75
innodb_max_dirty_pages_pct_lwm=0
innodb_undo_log_truncate=OFF
log_error_verbosity=3
max_error_count=1024 # Previous default was 64. But this can be increased to 1024
optimizer_trace_max_mem_size=1MB # Previous default was 16KB but can be increased to new default of 1MB
performance_schema_consumer_events_transactions_current=OFF
performance_schema_consumer_events_transactions_history=OFF

plugin-load=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
validate_password_length=8
validate_password_mixed_case_count=1
validate_password_number_count=1
validate_password_special_char_count=1
validate_password_policy=MEDIUM

#skip-grant-tables

require_secure_transport=ON
ssl_ca=/data/certs/client-ca-chain.pem
ssl_cert=/data/certs/general-radius-prod.pem.cert
ssl_key=/data/certs/radius-client.key


Подробнее здесь: https://stackoverflow.com/questions/797 ... ler-commit