Hi! I have cc'ed the maria-developers mailing list as this is potentially a dev issue and also the devs responsible for Windows and InnoDB development. There might be some people on vacation right now, but this should get looked into in a week or two at most.
Let's find a solution for your problem. :) Vicentiu On Sun, 10 Jun 2018, 19:11 TD, <t...@gmx.net> wrote: > 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 > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-disc...@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp >
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp