MySQL5.7.23在几个小时后会变慢,需要重新启动

pgky5nke  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(300)

三周前,我们使用逻辑方法从mysql 5.6.22迁移到5.7.23。之后,我们开始得到错误已经提交,但现在数据库开始放缓,我们需要重新启动大约每10小时。
这反映在一个每分钟接收700条记录的表上的触发器上,刚开始时,每条记录的处理时间约为22ms。逐渐开始需要越来越多的时间,最长可达300ms。当时间不够和瓶颈开始构建时,我们重新启动数据库并再次快速工作。innodb缓冲池的使用率甚至达不到15%。
触发器执行各种选择并收集信息以填充其余的记录,对于不超过3k条记录的表来说,没有什么特别的。
有什么建议可能导致这个问题吗?提前谢谢

Hardware Specs: 
Nombre del sistema operativo:              Microsoft Windows Server 2012 Standard
Versión del sistema operativo:             6.2.9200 N/D Compilación 9200
Fabricante del sistema operativo:          Microsoft Corporation
Configuración del sistema operativo:       Servidor independiente
Tipo de compilación del sistema operativo: Multiprocessor Free

Fabricante del sistema:                    VMware, Inc.
Modelo el sistema:                         VMware Virtual Platform
Tipo de sistema:                           x64-based PC
Procesador(es):                            2 Procesadores instalados.
                                           [01]: Intel64 Family 6 Model 45 Stepping 7 GenuineIntel ~2400 Mhz
                                           [02]: Intel64 Family 6 Model 45 Stepping 7 GenuineIntel ~2400 Mhz
Versión del BIOS:                          Phoenix Technologies LTD 6.00, 22/06/2012

Cantidad total de memoria física:          49,151 MB
Memoria física disponible:                 37,501 MB
Memoria virtual: tamaño máximo:            55,551 MB
Memoria virtual: disponible:               21,520 MB
Memoria virtual: en uso:                   34,031 MB
Server Configuration:  

[mysqld]

# Storage

basedir=e:/DB5723/
datadir=e:/DB5723/Data/
innodb_file_per_table=ON  

# Memory Resources

innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances=8
innodb_buffer_pool_dump_pct=75 

innodb_lru_scan_depth=1024
innodb_log_buffer_size = 128M  
innodb_log_file_size=512M

max_allowed_packet = 4M 
max_heap_table_size = 64M

join_buffer_size = 1M
sort_buffer_size = 64M
read_rnd_buffer_size = 16M 

query_cache_size=0
query_cache_type=OFF

# Connections

skip-name-resolve
max_connections = 151
max_connect_errors = 5
socket=e:/DB5723/tmp/mysql.sock
port=3307 
wait_timeout=600 

# Thread Setting

        innodb_thread_concurrency=16
        innodb_read_io_threads=8
        innodb_write_io_threads=8
        thread_cache_size = 9

# Error Control / Log

log_error_verbosity=3 
general_log_file=e:/DB5723/Data/General.log

# Security ( Brute Force Attack )

plugin-load-add=connection_control.dll
connection-control=FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
connection_control_failed_connections_threshold=3
connection_control_max_connection_delay=120000
connection_control_min_connection_delay=60000

# Replication

server_id=1
log_bin=g:/DB5723-bin
max_binlog_size=512M
binlog_format = ROW
sync_binlog=1

# Miscelaneous

character-set-server=utf8 
event_scheduler = ON
innodb_lock_wait_timeout=50
explicit_defaults_for_timestamp=ON
innodb_checksum_algorithm=crc32
innodb_checksums=ON   

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# I/O Hdd Performance Settings

innodb_io_capacity=800
innodb_io_capacity_max=1000
innodb_flush_log_at_trx_commit=2
de90aj5v

de90aj5v1#

为my.ini[mysqld]部分考虑的建议
每秒速率=rps

innodb_lru_scan_depth=100  # from 1024 see refman to reduce CPU load every SECOND
sort_buffer_size=2M  # from 64M  a per connection RAM request and 64M is TOO MUCH
read_rnd_buffer_size=256K  # from 16M to reduce handler_read_rnd_next RPS
thread_cache_size=48  # from 9 to reduce threads_created count
innodb_io_capacity_max=2000  # from 1000 for default max
innodb_io_capacity=1600  # from 800 to allow more IOPS to your HDD

如需其他建议,请查看我的个人资料,联系信息的网络资料。

t3irkdon

t3irkdon2#

一个快速的解决办法可能是改为

sync_binlog = 0

有关更多讨论,请提供触发触发器的查询、触发器定义, CREATE TABLE ,和 Max_used_connections .
另外,涉及哪种类型的磁盘(hdd与ssd)?

相关问题