maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #05139
MariaDB crashes because of "long semaphore wait" after migrating from 10.1 to 10.3
Hi,
I have a Windows server that has been running MariaDB 10.1 successfully
for over a year. The server remains mostly idle for long times with some
read access, but occasionally there are transactions that add data
(about 500k rows per commit). There can be up to 10 such transactions
(one per database) at the same time and during those times the server is
under quite some load (the code processing the data resides on the same
server as the database). We're using an 8 core AMD Ryzen, 32 GB RAM and
a Samsung SSD.
Like I said, this worked fine with MariaDB 10.1 all the time.
We're currently in the process of setting up a new server (specs similar
to the ones above) and because MariaDB 10.3 has been released recently,
I was curious and installed it. The database now constantly shuts down
because of "[FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We
intentionally crash the server because it appears to be hung." whenever
there is one of the load situations described above. If I install
MariaDB 10.1 on the new server, it runs as fine as the old one. The
crashes also happen when I start with a clean database (no data migrated
from 10.1 databases).
I'm not a huge MariaDB config pro, as it's only a tool to store the
data, it just needs to run with reasonable performance and has always
worked until now. As far as I know and can judge from comments in the
file, the my.ini in use is (mostly?) based on my-innodb-heavy-4G.cnf,
but coworkers might have made modifications. I've attached the contents
below.
I don't know how I can debug this issue further. Windows event viewer
contains several log warnings with the text "InnoDB: A long semaphore
wait:" but no further information why this wait happens. I couldn't find
any other error logs. I guess it is some configuration error (I read
about MariaDB switching from XtraDB to InnoDB, so I guess that the issue
might be related).
Migrating to 10.3 is not an absolute must, but it would be nice to be
able to fix the problem.
Any help would be greatly appreciated and if you need more information,
I'll gladly try to provide it.
Thanks,
Tom.
my.ini:
[mysqld]
max_connections = 100
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 10
query_cache_size = 64M
query_cache_limit = 2M
thread_stack = 240K
tmp_table_size = 64M
slow_query_log
expire_logs_days = 14
log-bin = mysql-bin
character-set-server=utf8
collation-server=utf8_general_ci
#*** MyISAM Specific options
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
# *** INNODB Specific options ***
#innodb_fatal_semaphore_wait_threshold = 6000 #tried that, didn't help
#innodb_adaptive_hash_index = 0 #tried that (suggested in some stack
overflow post, didn't help)
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 10
innodb_io_capacity = 2000
innodb_lock_wait_timeout = 120
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqld_safe]
open-files-limit = 8192
Follow ups