Type:
Bug
Priority:
Major
Resolution:
Unresolved
Affects Version/s:
10.4.31 , 10.5 , 10.6 , 10.7(EOL) , 10.8(EOL) , 10.9(EOL) , 10.10(EOL) , 10.11 , 11.0(EOL) , 11.1(EOL) , 11.2(EOL)
Environment:
Ubuntu 20.04
I am trying to troubleshoot an issue in a magento 2 website. It seems that at random times there comes a situation where mariadb seems to go into a deadlock situation and slowly stops serving requests. I end up with a state where every query is stuck in a waiting state. In the end some queries time out, I receive notifications about lock wait timeout exceeded and the queries eventually finish executing.
I'm trying to deduce what's happening and how to solve this but I'm stuck and need some help understanding the cause of this issue so I ended up in dba.stackexchange.com where it was suggested that I open a bug report. You can see the full thread here
https://dba.stackexchange.com/questions/330629/how-can-i-find-out-whats-causing-the-deadlock-in-mariadb?noredirect=1#comment643999_330629
And there's also some extra info here about the issue I'm encountering
https://github.com/magento/magento2/issues/36667
Here's the current config
~$ my_print_defaults --mysqld
--user=mysql
--pid-file=/run/mysqld/mysqld.pid
--socket=/run/mysqld/mysqld.sock
--basedir=/usr
--datadir=/var/lib/mysql
--tmpdir=/tmp
--lc-messages-dir=/usr/share/mysql
--bind-address=0.0.0.0
--key_buffer_size=64M
--max_allowed_packet=1G
--max_connections=300
--query_cache_size=0
--log_error=/var/log/mysql/error.log
--expire_logs_days=10
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--query_cache_size=0
--query_cache_type=0
--query_cache_limit=0
--join_buffer_size=512K
--tmp_table_size=128M
--max_heap_table_size=128M
--innodb_buffer_pool_size=32G
--innodb_buffer_pool_instances=16
--innodb_log_file_size=4G
--optimizer_use_condition_selectivity=1
--optimizer_switch=optimize_join_buffer_size=on
--in_predicate_conversion_threshold=4294967295
--innodb_data_home_dir=/var/lib/mysql/
--innodb_data_file_path=ibdata1:10M:autoextend
--innodb_log_group_home_dir=/var/lib/mysql/
--innodb_lock_wait_timeout=50
--innodb_file_per_table=1
--innodb_log_buffer_size=4M
--performance_schema
--optimizer_switch=rowid_filter=off
--optimizer_use_condition_selectivity=1
--optimizer_search_depth=0
--slow_query_log
--slow_query_log_file=/var/log/mysql/mariadb-slow.log
--long_query_time=20.0
--innodb_print_all_deadlocks=1
--lock_wait_timeout=240
--bind-address=0.0.0.0
--sql_mode=
--local-infile=0
--innodb_open_files=4000
--table_open_cache=3000
--table_definition_cache=3000
I'm attaching the image from what I see in mytop during the occurence and the results of
1. the full process list,
2. the mariadb full backtrace of all running threads and
3. the result of `show engine innodb status`
However I wasn't able to acquire them all together because I have to be connected during the issue and the duration is not the same everytime. Sometimes things resolve within a minute, sometimes within 10 minutes.
So they might have some different queries inside but I'm hoping we can find out the reason for this issue
relates to
MDEV-14486
InnoDB hang on shutdown
Closed
{"report":{"fcp":1331.1000000000931,"ttfb":366.5,"pageVisibility":"visible","entityId":124666,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"8f365c31-f965-4bf4-ab4b-c4c1687ccf5e","navigationType":0,"readyForUser":1428.2000000000698,"redirectCount":0,"resourceLoadedEnd":2273.8000000000466,"resourceLoadedStart":372.20000000006985,"resourceTiming":[{"duration":509.9000000000233,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bv2/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":372.20000000006985,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":372.20000000006985,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":882.1000000000931,"responseStart":0,"secureConnectionStart":0},{"duration":510.0999999999767,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bv2/820016/12ta74/2380add21a9a1006587582385952de73/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":372.4000000000233,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":372.4000000000233,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":882.5,"responseStart":0,"secureConnectionStart":0},{"duration":518.7999999999302,"initiatorType":"script","name":"https://jira.mariadb.org/s/e9b27a47da5fb0f74a35acd57e9847fb-CDN/lu2bv2/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":372.60000000009313,"connectEnd":372.60000000009313,"connectStart":372.60000000009313,"domainLookupEnd":372.60000000009313,"domainLookupStart":372.60000000009313,"fetchStart":372.60000000009313,"redirectEnd":0,"redirectStart":0,"requestStart":372.60000000009313,"responseEnd":891.4000000000233,"responseStart":891.4000000000233,"secureConnectionStart":372.60000000009313},{"duration":562.9000000000233,"initiatorType":"script","name":"https://jira.mariadb.org/s/c32eb0da7ad9831253f8397e6cc26afd-CDN/lu2bv2/820016/12ta74/2380add21a9a1006587582385952de73/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":372.80000000004657,"connectEnd":372.80000000004657,"connectStart":372.80000000004657,"domainLookupEnd":372.80000000004657,"domainLookupStart":372.80000000004657,"fetchStart":372.80000000004657,"redirectEnd":0,"redirectStart":0,"requestStart":372.80000000004657,"responseEnd":935.7000000000698,"responseStart":935.7000000000698,"secureConnectionStart":372.80000000004657},{"duration":566.4000000000233,"initiatorType":"script","name":"https://jira.mariadb.org/s/bc0bcb146314416123c992714ee00ff7-CDN/lu2bv2/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":373,"connectEnd":373,"connectStart":373,"domainLookupEnd":373,"domainLookupStart":373,"fetchStart":373,"redirectEnd":0,"redirectStart":0,"requestStart":373,"responseEnd":939.4000000000233,"responseStart":939.4000000000233,"secureConnectionStart":373},{"duration":566.5999999999767,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":373.20000000006985,"connectEnd":373.20000000006985,"connectStart":373.20000000006985,"domainLookupEnd":373.20000000006985,"domainLookupStart":373.20000000006985,"fetchStart":373.20000000006985,"redirectEnd":0,"redirectStart":0,"requestStart":373.20000000006985,"responseEnd":939.8000000000466,"responseStart":939.8000000000466,"secureConnectionStart":373.20000000006985},{"duration":566.8000000000466,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":373.4000000000233,"connectEnd":373.4000000000233,"connectStart":373.4000000000233,"domainLookupEnd":373.4000000000233,"domainLookupStart":373.4000000000233,"fetchStart":373.4000000000233,"redirectEnd":0,"redirectStart":0,"requestStart":373.4000000000233,"responseEnd":940.2000000000698,"responseStart":940.2000000000698,"secureConnectionStart":373.4000000000233},{"duration":657,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bv2/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":373.60000000009313,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":373.60000000009313,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1030.6000000000931,"responseStart":0,"secureConnectionStart":0},{"duration":567,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":373.70000000006985,"connectEnd":373.70000000006985,"connectStart":373.70000000006985,"domainLookupEnd":373.70000000006985,"domainLookupStart":373.70000000006985,"fetchStart":373.70000000006985,"redirectEnd":0,"redirectStart":0,"requestStart":373.70000000006985,"responseEnd":940.7000000000698,"responseStart":940.7000000000698,"secureConnectionStart":373.70000000006985},{"duration":656.9000000000233,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bv2/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":373.9000000000233,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":373.9000000000233,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1030.8000000000466,"responseStart":0,"secureConnectionStart":0},{"duration":567.2000000000698,"initiatorType":"script","name":"https://jira.mariadb.org/s/719848dd97ebe0663199f49a3936487a-CDN/lu2bv2/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":374,"connectEnd":374,"connectStart":374,"domainLookupEnd":374,"domainLookupStart":374,"fetchStart":374,"redirectEnd":0,"redirectStart":0,"requestStart":374,"responseEnd":941.2000000000698,"responseStart":941.2000000000698,"secureConnectionStart":374},{"duration":1240.2000000000698,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":374.9000000000233,"connectEnd":374.9000000000233,"connectStart":374.9000000000233,"domainLookupEnd":374.9000000000233,"domainLookupStart":374.9000000000233,"fetchStart":374.9000000000233,"redirectEnd":0,"redirectStart":0,"requestStart":374.9000000000233,"responseEnd":1615.1000000000931,"responseStart":1615.1000000000931,"secureConnectionStart":374.9000000000233},{"duration":1898.8000000000466,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":375,"connectEnd":375,"connectStart":375,"domainLookupEnd":375,"domainLookupStart":375,"fetchStart":375,"redirectEnd":0,"redirectStart":0,"requestStart":375,"responseEnd":2273.8000000000466,"responseStart":2273.70000000007,"secureConnectionStart":375},{"duration":652.8000000000466,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1041.8000000000466,"connectEnd":1041.8000000000466,"connectStart":1041.8000000000466,"domainLookupEnd":1041.8000000000466,"domainLookupStart":1041.8000000000466,"fetchStart":1041.8000000000466,"redirectEnd":0,"redirectStart":0,"requestStart":1041.8000000000466,"responseEnd":1694.6000000000931,"responseStart":1694.6000000000931,"secureConnectionStart":1041.8000000000466},{"duration":963.8000000000466,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1325,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1325,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":2288.8000000000466,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":195,"responseStart":366,"responseEnd":370,"domLoading":370,"domInteractive":2294,"domContentLoadedEventStart":2294,"domContentLoadedEventEnd":2337,"domComplete":3899,"loadEventStart":3899,"loadEventEnd":3900,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2278},{"name":"bigPipe.sidebar-id.end","time":2278.9000000000233},{"name":"bigPipe.activity-panel-pipe-id.start","time":2279},{"name":"bigPipe.activity-panel-pipe-id.end","time":2281.3000000000466},{"name":"activityTabFullyLoaded","time":2346.20000000007}],"measures":[],"correlationId":"f9facb51b812f2","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":104,"dbReadsTimeInMs":14,"dbConnsTimeInMs":23,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}