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

MariaDB hangs with multiple threads open

    XMLWordPrintable

Details

    Description

      It started happening soon after upgrade to 10.11.2 version from 10.10 I believe. The problem starts when PHP scripts starts using 250-350 threads for INSERT statements mostly (SELECT statements are frequent too). On average MariaDB receives around 6000 queries per second, but problem comes even when it is just 2000 queries per second.

      There is 1 master and 4 replicas (each using 16 parallel slave threads for replication).

      At some point server (sometimes one sometimes two) - either master or replica stop processing any INSERT or SELECT statements. Process list then shows several hundred threads connected and they show time in many minutes or hours. Stopping any further queries to that affected server does not resolve anything - nothing changes for hours.

      When this happens - a simple CONNECT to MariaDB is measured and it usually shows connection time of 60-460 ms instead of normal 0.5-1.1ms.

      When I try to kill all threads one by one - they are killed, process list shows them as killed but present on the list.

      If then (or before) I try to use systemctl stop mariadb - server is not stopped for hours (same with restart). The only option is to find any mariadb process and issue KILL -9 and then start Mariadb again which then starts in recovery mode. All tables are InnoDB in the database.

      Servers, which do not receive many SELECT statements (the last 2 on the image list) - they never get such a problem thus I believe replication is not an issue. Replicas are READ ONLY.

      This happens about once or twice a day at different times but when number of queries is on a high side. There is no shortage of RAM or disk.

      Attachments

        1. process_list2.txt
          79 kB
        2. process_list.txt
          63 kB
        3. master_mariadb_full_bt_all_threads.txt
          4.47 MB
        4. master_db_recovery.jpg
          master_db_recovery.jpg
          161 kB
        5. mariadbd_full_bt_all_threads.txt
          380 kB
        6. mariadbd_full_bt_all_threads_2.txt
          1.09 MB
        7. download.png
          download.png
          14 kB

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Erland22 Erlandas
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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