三周前,我们使用逻辑方法从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
2条答案
按热度按时间de90aj5v1#
为my.ini[mysqld]部分考虑的建议
每秒速率=rps
如需其他建议,请查看我的个人资料,联系信息的网络资料。
t3irkdon2#
一个快速的解决办法可能是改为
有关更多讨论,请提供触发触发器的查询、触发器定义,
CREATE TABLE
,和Max_used_connections
.另外,涉及哪种类型的磁盘(hdd与ssd)?