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

concurrently used sequence hangs in SEQUENCE::next_value

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.0
    • 10.3.1
    • Sequences
    • None

    Description

      A workload where many workers concurrently pull a number from a InnoDB sequence and insert the number into another InnoDB table using transactions, occasionally causes all worker threads to hang in SEQUENCE::next_value. Everything is fine when no transactions are used (autocommit) or when the sequence object is using the MyISAM engine.

      How to repeat:

      You need Lua-enabled sysbench, i.e. from https://github.com/hgxl64/sysbench-mariadb.git. Then use the attached test script sequence_native.lua:

      mysql -S ... -u root -e "drop database if exists sbtest"
      mysql -S ... -u root -e "create database sbtest"
      sysbench --test=sequence_native.lua --oltp-tables-count=32 \
       --mysql-table-engine=InnoDB --sequence-cache=10 \
       --mysql-user=root --mysql-socket=... prepare
      sysbench --test=sequence_native.lua --oltp-tables-count=32 \
       --trx-size=10 --num-threads=2 --report-interval=1 --max-time=0 \
       --max-requests=0 --mysql-user=root --mysql-socket=... run
      

      You might need to alter the sequence cache size or number of threads. The Lua script understands a few options:
      --sequence-cache is used for the CACHE option when creating the sequence
      --sequence-engine is used for the ENGINE option of the sequence
      --trx-size gives the number of inserts in each transaction

      My guess is, that something goes wrong if the sequence cache runs empty for multiple worker threads and they have to dive for the base table at the same time. The bigger the sequence cache is, the more worker threads are needed to trigger the hang. I.e. with Cache=1000 I need 16 threads.

      Attached is also my.cnf and a stack trace from a hanging mysqld.

      Attachments

        1. hang.trace.txt.gz
          8 kB
        2. my.cnf
          0.3 kB
        3. sequence_native.lua
          2 kB

        Issue Links

          Activity

            People

              axel Axel Schwenke
              axel Axel Schwenke
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.