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

MariaDB 10.6.12 / 10.11.2 hangs on large parallel insert

    XMLWordPrintable

Details

    Description

      Edit: Just tested 10.11.2 - happens there as well

      When parallel importing large amounts of datasets into the same table the imports suddenly hang at certain times. This sometimes happens after a few seconds, sometimes after a few minutes... at that point there have sometimes been 50MB of data imported, sometimes 1-2GB...

      in the processlist it looks like this - the time continues to go up, but nothing happens anymore, even after 50+ hours.

      94 root localhost emptytest Query 123 Update INSERT INTO testtable (xxx, xxx, xxx, xxx, 0.000
      95 root localhost emptytest Query 123 Update INSERT INTO testtable (xxx, xxx, xxx, xxx, 0.000
      96 root localhost emptytest Query 123 Update INSERT INTO testtable (xxx, xxx, xxx, xxx, 0.000
      97 root localhost emptytest Query 123 Update INSERT INTO testtable (xxx, xxx, xxx, xxx, 0.000

      I also can't kill the queries. If I stop the original process that imported them - nothing happens. If I kill the query on the console the status switches from Update to "killed" - but that's it. I have to kill -9 the whole database.

      This does not happen with 10.6.11
      It happens on different servers with different amounts of CPU / memory / harddrive combinations on debian11 and debian10 with the versions from the mariadb apt repository

      Attached is an output of show global variables.

      To have a testcase outside of our application, I created the following table in an empty database:

      CREATE TABLE `testtable` (
        `test01` decimal(24,6) DEFAULT NULL,
        `test02` bigint(20) DEFAULT NULL,
        `test03` varchar(255) DEFAULT NULL,
        `test04` varchar(255) DEFAULT NULL,
        `test05` varchar(100) DEFAULT NULL,
        `test06` varchar(255) DEFAULT NULL,
        `test07` varchar(255) DEFAULT NULL,
        `test08` varchar(255) DEFAULT NULL,
        `test09` varchar(255) DEFAULT NULL,
        `test10` varchar(255) DEFAULT NULL,
        `test11` varchar(255) DEFAULT NULL,
        `test12` varchar(255) DEFAULT NULL,
        `test13` varchar(255) DEFAULT NULL,
        `test14` varchar(8) DEFAULT NULL,
        `test15` varchar(255) DEFAULT NULL,
        `test16` varchar(255) DEFAULT NULL,
        `test17` varchar(255) DEFAULT NULL,
        `test18` varchar(255) DEFAULT NULL,
        `test19` varchar(255) DEFAULT NULL,
        `test20` varchar(255) DEFAULT NULL,
        `test21` varchar(50) DEFAULT NULL,
        `test22` varchar(255) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
      

      Then I generated four SQL files with random test data using https://filldb.info/dummy

      and ran a bash script like this

      #!/bin/bash
      # Drop and recreate table, disable keys, lock table
      mysql emptytest < testtable1.sql
      sleep 2
      # parallel import data
      mysql emptytest < testtable2.sql &
      mysql emptytest < testtable3.sql &
      mysql emptytest < testtable4.sql &
      mysql emptytest < testtable5.sql &
      wait $(jobs -p)
      sleep 2
      #reenable keys, unlock table
      mysql emptytest < testtable6.sql
      

      I then loop over this

      while true; do; ./import.sh ; done
      

      And wait... it happens usually after a few minutes with 10.6.12.

      Not sure if (and if so how / where) I should upload the complete testdata set, it's multiple gb big and basically just useless data - so please let me know how I can help

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              Catscrash Sebastian Wittgens
              Votes:
              1 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.