[Maria-discuss] 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
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, tdcf@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-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
I think this is a bug, and as such it is best to have in JIRA https://jira.mariadb.org/ . it is not really to possibly to give a good advice on a vaguely defined bug without extra info.
In that particular case, I suggest to add core-file=1 to the my.ini, wait for a crash, and attach both error log and core (which will be named mysqld.dmp and created in the data directory) to the bug report, and we’ll take it from there.
From: Vicențiu Ciorbaru Sent: Sunday, June 10, 2018 8:23 PM To: TD Cc: Maria Discuss; maria-developers; Marko Mäkelä; Vladislav Vaintroub Subject: Re: [Maria-discuss] MariaDB crashes because of "long semaphore wait"after migrating from 10.1 to 10.3
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, tdcf@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-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Thanks for the quick response, I opened a ticket (https://jira.mariadb.org/browse/MDEV-16467).
I cannot attach the core dump though as it contains sensitive information (I opened it in an editor and checked the strings). If absolutely neccessary, I can try to recreate the issue with test data, but this might be complicated. I've attached a full log from database startup up until the crash though.
Tom.
Am 10.06.2018 um 22:38 schrieb Vladislav Vaintroub:
I think this is a bug, and as such it is best to have in JIRA https://jira.mariadb.org/ . it is not really to possibly to give a good advice on a vaguely defined bug without extra info.
In that particular case, I suggest to add core-file=1 to the my.ini, wait for a crash, and attach both error log and core (which will be named mysqld.dmp and created in the data directory) to the bug report, and we’ll take it from there.
*From: *Vicențiu Ciorbaru mailto:vicentiu@mariadb.org *Sent: *Sunday, June 10, 2018 8:23 PM *To: *TD mailto:tdcf@gmx.net *Cc: *Maria Discuss mailto:maria-discuss@lists.launchpad.net; maria-developers mailto:maria-developers@lists.launchpad.net; Marko Mäkelä mailto:marko.makela@mariadb.com; Vladislav Vaintroub mailto:wlad@mariadb.com *Subject: *Re: [Maria-discuss] MariaDB crashes because of "long semaphore wait"after migrating from 10.1 to 10.3
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, <tdcf@gmx.net mailto:tdcf@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 https://launchpad.net/%7Emaria-discuss Post to : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss https://launchpad.net/%7Emaria-discuss More help : https://help.launchpad.net/ListHelp
On 12.06.2018 01:20, TD wrote:
Thanks for the quick response, I opened a ticket (https://jira.mariadb.org/browse/MDEV-16467).
I cannot attach the core dump though as it contains sensitive information (I opened it in an editor and checked the strings). If absolutely neccessary, I can try to recreate the issue with test data, but this might be complicated. I've attached a full log from database startup up until the crash though.
Tom.
Thank you. As I commented in the ticket , we do not need sensitive information, and only need all threads stacktraces. I also wrote how you can create the stacktraces yourself, with cdb or Visual Studio Debugger. Let's continue this dialog in the ticket, so we do not spam the discuss list.
Wlad.
Am 10.06.2018 um 22:38 schrieb Vladislav Vaintroub:
I think this is a bug, and as such it is best to have in JIRA https://jira.mariadb.org/ . it is not really to possibly to give a good advice on a vaguely defined bug without extra info.
In that particular case, I suggest to add core-file=1 to the my.ini, wait for a crash, and attach both error log and core (which will be named mysqld.dmp and created in the data directory) to the bug report, and we’ll take it from there.
*From: *Vicențiu Ciorbaru mailto:vicentiu@mariadb.org *Sent: *Sunday, June 10, 2018 8:23 PM *To: *TD mailto:tdcf@gmx.net *Cc: *Maria Discuss mailto:maria-discuss@lists.launchpad.net; maria-developers mailto:maria-developers@lists.launchpad.net; Marko Mäkelä mailto:marko.makela@mariadb.com; Vladislav Vaintroub mailto:wlad@mariadb.com *Subject: *Re: [Maria-discuss] MariaDB crashes because of "long semaphore wait"after migrating from 10.1 to 10.3
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, <tdcf@gmx.net mailto:tdcf@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 https://launchpad.net/%7Emaria-discuss Post to : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss https://launchpad.net/%7Emaria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (4)
-
TD -
Vicențiu Ciorbaru -
Vladislav Vaintroub -
Vladislav Vaintroub