Maria MySQL upgraded from 10.1.x to 10.2.x but now has long semaphore waits

1
votes

Upgraded Maria MySQL from 10.1 to 10.2, but we are now experiencing InnoDB: A long semaphore wait(s) that then result in the database crashing. This happens when trying to restore a database, by running replace into tables command for a table containing 35M rows of data, at approx 20M - 32M rows, the system always crashes with semaphore waits (attempted this on multiple systems / VMS). This worked when using Maria 10.1 but doesn't since using 10.2, is there a magic setting needed? (other than setting innodb_adaptive_hash_index=0 that didn't work!) Also increased innodb_fatal_semaphore_wait_threshold from 600 to 1200, the Semaphore issue still remains.

The system:
Windows 10 Pro, 4GB RAM, Processor: 2GHz (x2) With an SSD (80+GB free)
Apache/2.4.33
10.2.14-MariaDB
PHP 7.2.3

my.ini (extract - without directory paths)
[mysqld] port = 3306
default_storage_engine = InnoDB
character_set_client_handshake = FALSE
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 = Barracuda
innodb_file_per_table = ON
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 = on
innodb_adaptive_hash_index = 0
innodb_fatal_semaphore_wait_threshold = 1200

On looking at the error log produced:

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 INTOcounter_activity{... 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

1
mysql

1 Answers