mysql - Maria MySQL已从10.1.x升级到10.2.x，但现在等待信号的时间很长

将Maria MySQL从10.1升级到10.2，但是现在我们遇到了InnoDB：长时间的信号量等待，然后导致数据库崩溃。
尝试还原数据库时会发生这种情况，方法是对包含35M行数据的表（大约20M-32M行）运行replace into tables命令，系统总是因信号灯等待而崩溃（在多个系统/ VMS上尝试这样做）。这在使用Maria 10.1时有效，但是自从使用10.2以来没有，是否需要魔术设置？（除了设置无效的innodb_adaptive_hash_index = 0之外！）还将innodb_fatal_semaphore_wait_threshold从600增加到1200，信号量问题仍然存在。

系统：
Windows 10 Pro，4GB RAM，处理器：2GHz（x2）和SSD（80 + GB免费）
阿帕奇/2.4.33
10.2.14-MariaDB
PHP 7.2.3

my.ini（解压缩-没有目录路径）
[mysqld]
端口= 3306
default_storage_engine = InnoDB
character_set_client_handshake =否
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
key_buffer_size = 32M
myisam_recover_options = FORCE，BACKUP
max_allowed_packet = 16M
slave_max_allowed_packet = 16M
max_connect_errors = 100
lock_wait_timeout = 1010
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 1
query_cache_limit = 32M
query_cache_min_res_unit = 2k
query_cache_size = 64M
query_cache_strip_comments = 1
query_prealloc_size = 8388608
max_connections = 200
wait_timeout = 1000
Interactive_timeout = 1000
thread_cache_size = 50
open_files_limit = 1200
table_definition_cache = 400
table_open_cache = 200
innodb_flush_log_at_trx_commit = 1
innodb_file_format =梭子鱼
innodb_file_per_table =开
innodb_large_prefix = 1
innodb_doublewrite = 1
innodb_use_atomic_writes = 0
innodb_use_fallocate = 0
innodb_use_trim = 0
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_file_per_table = 1
innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 16M
innodb_purge_threads = 2
innodb_defragment = 1
innodb_use_mtflush = 0
event_scheduler = 1
log_queries_not_using_indexes = 1
long_query_time = 20
min_examined_row_limit = 100000
slow_query_log = 1
performance_schema =开
innodb_adaptive_hash_index = 0
innodb_fatal_semaphore_wait_threshold = 1200

查看产生的错误日志：

2018-04-30 17:15:05 4032 [Note] InnoDB: A semaphore wait:
--Thread 20080 has waited at buf0buf.cc line 4151 for 893.00 seconds the semaphore:
S-lock on RW-latch at 00000205B1323C38 created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0ins.cc line 2901
Last time write locked in file buf0buf.cc line 5376

=====================================
2018-04-30 17:15:07 0x4774 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1274 srv_active, 0 srv_shutdown, 2 srv_idle
srv_master_thread log flush and writes: 1275
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 26785
--Thread 9020 has waited at ibuf0ibuf.cc line 2720 for 257.00 seconds the semaphore:
Mutex at 00007FF7A63838E0, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2

------------
TRANSACTIONS
------------
Trx id counter 445872
Purge done for trx's n:o < 445826 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283698441286496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 445871, ACTIVE 258 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 16260
MySQL thread id 20, OS thread handle 19452, query id 6361 localhost ::1 peoplecounter update
REPLACE INTO对抗活动{... columns / values...}
--------
FILE I/O
--------
I/O thread 0 state: complete io for buf page (insert buffer thread)
I/O thread 1 state: complete io for buf page (log thread)
I/O thread 2 state: complete io for buf page (read thread)
I/O thread 3 state: complete io for buf page (read thread)
I/O thread 4 state: native aio handle (read thread)
I/O thread 5 state: native aio handle (read thread)
I/O thread 6 state: native aio handle (write thread)
I/O thread 7 state: native aio handle (write thread)
I/O thread 8 state: native aio handle (write thread)
I/O thread 9 state: native aio handle (write thread)
Pending normal aio reads: [1, 4, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
6773 OS file reads, 2856999 OS file writes, 60709 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

最佳答案

这似乎已在下一个MariaDB版本10.2.15中修复。
有关详细信息，请参见https://jira.mariadb.org/browse/MDEV-15707?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel&showAll=true。

关于mysql - Maria MySQL已从10.1.x升级到10.2.x，但现在等待信号的时间很长，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50115659/