Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32033

all queries get stuck until a timeout occurs on all of them

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.31, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2
    • 10.4
    • Locking
    • None
    • Ubuntu 20.04

    Description

      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

      Attachments

        1. debug_20230904135015.zip
          350 kB
        2. debug_20230904155641.zip
          206 kB
        3. debug_20230904155641-1.zip
          206 kB
        4. debug_20230904170300.zip
          165 kB
        5. debug_20230905091543.zip
          192 kB
        6. debug_20230905142728.zip
          788 kB
        7. debug_20230907075720.zip
          201 kB
        8. debug_20230910214351.zip
          159 kB
        9. debug_20230910214351-1.zip
          159 kB
        10. dump.sql.gz
          33 kB
        11. dump.sql-1.gz
          33 kB
        12. dump2023-09-01-143646.zip
          179 kB
        13. dump2023-09-01-163052.zip
          203 kB
        14. dump20230902-071200.zip
          108 kB
        15. processlist-202309011403.txt
          65 kB
        16. queries stuck in sending state.jpg
          queries stuck in sending state.jpg
          375 kB
        17. queries stuck in sending state.txt
          1.32 MB
        18. screenshot-1.png
          screenshot-1.png
          234 kB
        19. screenshot-2.png
          screenshot-2.png
          190 kB

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              ioweb.gr Gabriel Tziotzis
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.